求替代Datediff over()的方案：计算用户起止事件时间差

嘿，我懂你的困扰——因为每个用户的started和ended事件分散在不同行里，直接用case when嵌套datediff肯定没法跨行抓取对应的日期值。这里有几个实用的方案，你可以根据自己使用的SQL数据库（比如SQL Server、MySQL、PostgreSQL这类）调整：

方案1：用窗口函数快速关联起止日期 #

大部分现代SQL数据库都支持窗口函数，我们可以用MAX() OVER (PARTITION BY user_id)把同一个用户的开始日期“映射”到结束事件的行中，然后计算天数差：

SELECT 
    user_id,
    DATEDIFF(day, 
             MAX(CASE WHEN event = 'started' THEN date END) OVER (PARTITION BY user_id),
             date) AS duration_days
FROM your_table
WHERE event = 'ended' -- 只保留结束事件的行，直接计算时长

如果你想先统一获取每个用户的起止日期再计算，也可以用聚合子查询的方式：

SELECT 
    user_id,
    DATEDIFF(day, start_date, end_date) AS duration_days
FROM (
    SELECT 
        user_id,
        MAX(CASE WHEN event = 'started' THEN date END) AS start_date,
        MAX(CASE WHEN event = 'ended' THEN date END) AS end_date
    FROM your_table
    GROUP BY user_id
    HAVING start_date IS NOT NULL AND end_date IS NOT NULL -- 过滤掉只有开始/只有结束的无效用户
) AS user_date_pairs

方案2：自连接适配老版本数据库 #

如果你的数据库不支持窗口函数（比如一些老版本的MySQL），可以用自连接来匹配同一个用户的开始和结束事件：

SELECT 
    s.user_id,
    DATEDIFF(day, s.date, e.date) AS duration_days
FROM your_table s
INNER JOIN your_table e 
    ON s.user_id = e.user_id
WHERE s.event = 'started' 
  AND e.event = 'ended'

要是用户存在多组started/ended事件（比如多次启停），可以用ROW_NUMBER()给每组事件编号，确保匹配对应的起止对：

WITH numbered_events AS (
    SELECT 
        user_id,
        event,
        date,
        ROW_NUMBER() OVER (PARTITION BY user_id, event ORDER BY date) AS event_number
    FROM your_table
)
SELECT 
    s.user_id,
    DATEDIFF(day, s.date, e.date) AS duration_days
FROM numbered_events s
JOIN numbered_events e 
    ON s.user_id = e.user_id 
    AND s.event_number = e.event_number
WHERE s.event = 'started' 
  AND e.event = 'ended'

这几个方案里，窗口函数的写法最简洁高效，适合单组事件的场景；多组事件的话，带编号的自连接能精准匹配对应的起止事件。

内容的提问来源于stack exchange，提问作者user8834780