sql 對 count 指定條件

  • 72
  • 0

leetcode時發現對於sql count用法不熟,複習一下

leetcode原題:

https://leetcode.com/problems/trips-and-users/

依照題意每個日期要各別要計算取消率(取消率=取消數/總數),因此需要2個count的結果做相除
第一時間想到的竟然是pivot而不是2個條件不同的count,也是因為習慣了寫count(*)再把條件放到where,
久了就忘記count的其他用法,確實有必要複習一下count

先依照題意篩選條件(完成where clause),並把取消條件做出來(cxl=1為取消;0為完成),把到count之前的sql做好:

 select Request_at,  decode(t.Status, 'completed', 0, 1) cxl
   from Trips t
   join Users uc
     on t.Client_Id = uc.Users_Id 
    and uc.Role = 'client'
   join Users ud
     on t.Driver_Id = ud.Users_Id 
    and ud.Role = 'driver'
  where t.Request_at between '2013-10-01' and '2013-10-03'
    and uc.Banned = 'No'
    and ud.Banned = 'No'

接下來就是計算取消數與總數,總數很單純的直接count(*),至於取消數的cxl=1的條件應該如何加?
對cxl欄位做pivot、或是cxl=1條件寫在where多做一次查詢後再做join都是方法,而直接使用count即可做到
Linq 來說寫 xxx.Count(x => x.cxl == 1) 是可以的

但在sql不寫為 count(cxl = 1),sql count括號中的條件代表的是 "not null" 的篩選條件
因此取消數的語法為 count(decode(cxl, 1, 1, null))
或是不count了,使用 sum(decode(cxl, 1, 1, 0))

之後再整理一下sql,發現也沒必要使用子查詢作暫時欄位就移除了

select Request_at as Day, 
       round( count(decode(t.Status, 'completed', null, 1)) / count(*) , 2) as "Cancellation Rate"
  from Trips t
  join Users uc
    on t.Client_Id = uc.Users_Id 
   and uc.Role = 'client'
  join Users ud
    on t.Driver_Id = ud.Users_Id 
   and ud.Role = 'driver'
 where t.Request_at between '2013-10-01' and '2013-10-03'
   and uc.Banned = 'No'
   and ud.Banned = 'No'
 group by Request_at
 order by Request_at

stackoverflow上大佬的詳細說明:

https://stackoverflow.com/questions/1400078/is-it-possible-to-specify-condition-in-count