SQL語法中的過濾方法

在統計系統中,很常用到不同維度的分類和過澽,

比如按類加總/平均等等

Id Gender Salary Country Position Status
1 M 1000 China Software working
2 F 2000 USA Software holiday
3 M 5000 UK Hardware holiday
4 F 4000 Germany Hardware working
5 M 5000 USA Admin holiday
6 F 4000 Germany Admin holiday

如果在上要一次過找出Admin和非Admin人員的平均Salary可以在 SQL中用 filter 或 case

PostgreSQL




select avg(salary) filter (where position = 'admin')  as admin_avg_salary,
avg(salary) filter (where position <> 'admin') as non_admin_avg_salary
from raw_data

MySQL








select avg(salary CASE
WHEN position = 'admin' THEN salary
ELSE 0 END) as admin_avg_salary,
avg(salary CASE
WHEN position <> 'admin' THEN salary
ELSE 0 END) as non_admin_avg_salary
from raw_data