我有一张这样的table:
id email_sent email_opened ad_clicked ts
1001 1 0 0 2017-05-01 02:40:00.0
1001 1 1 0 2017-07-01 02:40:00.0
1001 1 0 0 2017-08-01 02:40:00.0
1001 1 1 0 2017-09-01 02:40:00.0
1001 1 1 1 2017-10-01 02:40:00.0
所需输出:
user_id first_email_sent first_email_opened first_ad_clicked
1001 2017-05-01 02:40:00.0 2017-07-01 02:40:00.0 2017-10-01 02:40:00.0
我正在使用以下查询来获得此结果:
select id,
min(case when email_sent = 1 then ts end)as first_email_sent,
min(case when email_opened = 1 then ts end)as email_opened,
min(case when ad_clicked = 1 then ts end)as ad_clicked
from adcamp group by id;
还有更好的查询吗?
暂无答案!
目前还没有任何答案,快来回答吧!