我有一个具有日期列和user_type列的表。如何获取按日期分组的user_type计数
表格-用户日期
Date user_type
09/20/2020 Sales
09/20/2020 Sales
09/20/2020 Customer
09/20/2020 Customer
09/21/2020 Sales
09/21/2020 Customer
所需结果
Date Customer Sales
09/20/2020 2 2
09/21/2020 1 1
在标准SQL中,可以使用:
select date,
sum(case when user_type = 'Sales' then 1 else 0 end) as sales,
sum(case when user_type = 'Customer' then 1 else 0 end) as customer
from t
group by date;
这是几乎所有数据库都支持的标准语法。一些数据库具有快捷方式。
在Postgres中,我将其表述为:
select date,
count(*) filter (where user_type = 'Sales') as sales,
count(*) filter (where user_type = 'Customer') as customer
from t
group by date;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句