postgres類似oracle的decode實現

假設存在下面二張表

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

現在有一個需求需要統計locationid＝999999下面flags=1<<17和flag=1<<18的media數量，最初的醜陋實現如下：

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

如此多的聯表，效能肯定很差，改進如下：

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

小結:

1.如果不需要locationid，group by的操作也可以省略，注意，彙總函式本身針對多記錄操作並不一定是group by之後才使用，group by分組記錄也是多記錄。所以彙總函式使用的欄位不需要出現在group by裡。但是如果是直接select欄位，postgres就需要group by該欄位，有的db是不需要的比如sql lite.

2.postgres對於oracle中的decode函數的替代：CASE WHEN .....

3.flag這樣的type類型值在db中直接儲存為int，postgres的位與運算子&可以直接計算。

4.count(expr)如果expr是一個boolean值，count會被誤用。

錯誤的使用方式：

SELECT--錯誤的統計行數的方式count((flags & 134217728 > 0) )FROM    t_media a,    t_media_locations bWHERE    a.id= b.mediaidAND b.locationid IN(999999)

正確的方式就是case when使用之後sum：

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)