筛选特定的日期(Filter for specific dates)

筛选特定的日期(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