gpt4 book ai didi

SQL - 分组时聚合函数中的条件

转载 作者:行者123 更新时间:2023-11-29 14:06:35 25 4
gpt4 key购买 nike

表1

person    | zipcode     | timestamp               | event  | device
--------------------------------------------------|--------|------
amar | 11111 | 2016-09-28 20:05:03.001 | email | phone
akbar | 11111 | 2016-09-28 20:05:03.001 | email | phone
antony | 11111 | 2016-09-28 20:07:03.001 | chat | pc
amar | 11111 | 2016-09-28 20:08:03.001 | email | phone
amar | 11111 | 2016-09-28 20:08:03.001 | chat | phone
amar | 22222 | 2016-09-28 20:09:03.001 | email | phone
akbar | 22222 | 2016-09-28 20:10:03.001 | email | phone
antony | 22222 | 2016-09-28 20:10:03.001 | chat | phone
amar | 11111 | 2016-09-28 21:05:03.001 | email | phone
akbar | 11111 | 2016-09-28 21:05:03.001 | email | phone
antony | 11111 | 2016-09-28 21:07:03.001 | chat | phone

需要的输出

 person    | total_events | email_events 
---------------------------------------
amar | 5 | 4
akbar | 3 | 3
antony | 3 | 0

考虑像下面这样使用 group by - 如何在此处获取 event = 'email' 的事件计数 - 有没有办法在不使用的情况下执行此操作子选择?

select
person
, count (*) as total_events
from table1
group by person

最佳答案

CASE 语句中添加条件,并将其包装在COUNT 聚合中

试试这个

select
person
, count (*) as total_events
, Count(case when event = 'email' then 1 end) as email_events
from table1
group by person

关于SQL - 分组时聚合函数中的条件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/40192926/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com