查询自加入还是 cte 最佳解决方案?

Question

我有一个表格，如下所示：

userID   |       Date         | Type |
B     2018-01-19 03:14:07   Start
B     2019-06-23 02:11:03   End
V     2017-01-19 03:14:07   Start
V     2019-08-19 02:33:07   End
C     2019-01-20 03:14:07   Start
C     2019-10-19 03:11:07   End
C     2019-05-19 03:33:07   Mid

如果我想按 ID 计算每个用户的不同天数，我是否可以使用 cte 为每个 CTE 过滤类型 = "开始"和"结束"，然后加入它们，或者使用自我加入 ID ？ 每个表将在哪里进行类型筛选？

所以CTE例如：

with start as 
(select id, date, 
from table 
where type = 'Start')
with end as 
(select id, date, 
from table 
where type = 'End')
select s.id, date_diff('day', s.date, e.date) as duration
from start s
join end e
on s.id = e.id

自行加入为：

select s.id, date_diff('day', s.date, e.date) as duration
from table t 
join table t2 
on t.id = t2.id
where t.type = 'start' and t2.type = 'end'

两者都会正确给出预期的输出吗？

谢谢

Answer 1

评论太长了，所以作为答案发布..

这并不是一种优越的方法，但是如果您认为如果不使用CTEs，subqueries和joins就无法做到这一点，则可以使用window-functions。它不关心类型列，因此即使您有多个日期，它也可以正常工作。

select distinct 
id,
date_diff('day', min(date) over (partition by id), max(date) over (partition by id)) as duration
from your_table;

使用 SQL 服务器演示