筛选特定的日期(Filter for specific dates)
我有一张显示用户注册漏斗的表格:
user_id event date 1 Start 1/1/2018 1 End 1/1/2018 2 Start 1/2/2018 2 End 1/4/2018 etc.我想添加一个“过滤器”,以便我可以查看基于event=Start (用户注册开始日期)的数据; where子句不会where date between '2/1/2018' and '2/28/2018'因为如果我说where date between '2/1/2018' and '2/28/2018' - 在2/1/2018之前开始注册的用户将出现在表内。
我尝试了以下内容:
select user_id, event, date from table where case when event='Start' then date end between '2/1/2018' and '2/28/2018'但是,这完成删除了event=End的值。
有什么建议么?
I have a table that shows a users registration funnel:
user_id event date 1 Start 1/1/2018 1 End 1/1/2018 2 Start 1/2/2018 2 End 1/4/2018 etc.I would like to add a "filter" so that I can look at data based on event=Start (user registration start date); a where clause would not help because if I say where date between '2/1/2018' and '2/28/2018'- users that started registration before 2/1/2018 will appear inside the table.
I tried the following:
select user_id, event, date from table where case when event='Start' then date end between '2/1/2018' and '2/28/2018'But that completes removes the values for event=End.
Any suggestions?
最满意答案
select user_id, event, max(case when event='Start' then date else null end) start_date, max(case when event='End' then date else null end) end_date from table group by user_id, event select user_id, event, max(case when event='Start' then date else null end) start_date, max(case when event='End' then date else null end) end_date from table group by user_id, event更多推荐
发布评论