替代Datediff over()？

Question

我有这份表格的资料：

user_id   event   started   ended   date
1         started 1         0       3/1/2018
1         ended   0         1       3/2/2018
2         started 1         0       3/5/2018
2         ended   0         1       3/22/2018   
3         started 1         0       3/25/2018  

对于0/1还有其他事件和列，但它们是无关的。

我试着得到每个用户从开始到结束所需的时间。

我尝试过datediff(day, case when started=1 then date end, case when ended=1 then date end)，但是由于它们位于不同的行，所以不能工作。类似于datediff over()的东西可以工作，但这显然不是一个有效的函数。

提前感谢！

Answer 1

假设您不能在开始之前结束，您只需要MIN & MAX作为窗口的集合。

select user_id, 
   datediff(day, 
            min(date) over (partition by user_id),
            max(date) over (partition by user_id))
from myTable
where event in ('started', 'ended')

使用它，您也可以添加任何其他列。

如果一个结果行也可以，则可以进行简单的聚合：

select user_id,
   min(date) as started, 
   max(date) as ended,
   datediff(day, 
            min(date),
            max(date)) as duration
from myTable
where event in ('started', 'ended')
group by user_id

Answer 2

您可以使用user_id列内部连接表本身：

SELECT a.[user_id]
     , a.[date] AS StartDate
     , b.EndDate
     , DATEDIFF(DAY, a.[date], b.EndDate) AS DateDifference
FROM dbo.TableNameHere AS a
    INNER JOIN
    (
        SELECT [user_id]
             , [date] AS EndDate
        FROM dbo.TableNameHere
        WHERE [ended] = 1
    ) AS b
        ON a.[user_id] = b.[user_id] 
WHERE a.[started] = 1

在上面的示例中，除了SELECT之外，您实际上并不需要第一个DateDifference中的任何列，我只是在测试中为可见性提供了它们。