Postgres is similar to Oracle's decode implementation

Assume that the following two tables exist.

T_media:

Mediaid flags

11111 1 <17

22222 1 <18

33333 1 <17

44444 1 <2

T_media_locations:

Mediaid locationid

11111 999999

22222 999999

33333 999999

Now there is a need to count the number of media in the flags = 1 <17 and flag = 1 <18 under locationid = 999999. The first ugly implementation is as follows:

SELECT    a_tmp.locationid,a_tmp.attraction_count,r_tmp.restructant_countFROM    (        SELECT            COUNT(1)AS attraction_count,            tmp.locationid        FROM            (                SELECT                    A . ID,                    b.locationid                FROM                    t_media A,                    t_media_locations b                WHERE                    A . ID = b.mediaid                AND b.locationid IN(999999)                AND A .flags & 134217728 > 0                GROUP BY                    A . ID,                    b.locationid            )tmp        GROUP BY            tmp.locationid    )AS a_tmp,    (        SELECT            COUNT(1)AS restructant_count,            tmp.locationid        FROM            (                SELECT                    A . ID,                    b.locationid                FROM                    t_media A,                    t_media_locations b                WHERE                    A . ID = b.mediaid                AND b.locationid IN(999999)                AND A .flags & 268435456 > 0                GROUP BY                    A . ID,                    b.locationid            )tmp        GROUP BY            tmp.locationid    )AS r_tmpWHERE    a_tmp.locationid = r_tmp.locationid

The performance of so many joined tables must be poor. The improvements are as follows:

SELECTsum ( CASE WHEN (flags & 268435456 > 0)  THEN 1 ELSE 0 END ),sum ( CASE WHEN (flags & 134217728 > 0)  THEN 1 ELSE 0 END ),b.locationidFROM    t_media a,    t_media_locations bWHERE    a.id= b.mediaidAND b.locationid IN(99999)and a.flags is not nullgroup by b.locationid

Summary:

1. if the locationid is not required, the group by operation can also be omitted. Note that the aggregate function is not necessarily used after the group by operation for multiple records, and the group by group record is also a multi-record operation. Therefore, fields used by Aggregate functions do not need to appear in group. However, for a direct select field, Postgres requires the group by field. Some databases do not need this field, such as SQL lite.

2. Postgres's replacement for the decode function in Oracle: case when .....

3. Type values such as flag are directly stored as int in dB, and the bitwise AND operator of S can be directly calculated.

4. Count (expr) If expr is a Boolean value, count will be misused.

Incorrect usage:

Select -- the method count (flags & 134217728> 0) for incorrect statistics on the number of rows) from t_media A, t_media_locations bwhere A. ID = B. mediaidand B. locationid in (999999)

The correct method is sum after case when is used:

SELECTsum( CASE WHEN (flags & 134217728 > 0)  THEN 1 ELSE 0 END )FROM    t_media a,    t_media_locations bWHERE    a.id= b.mediaidAND b.locationid IN(999999)