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