如何编写SQL计算客户首次与第二次预订的datetime时间差及平均值

需要统计每个客户首次预订和第二次预订的时间差，仅计算用户前两笔订单的间隔，只有1笔预订记录的客户时间差返回-。

以下SQL兼容MySQL 8.0+、PostgreSQL、Spark SQL、Hive等支持窗口函数的主流数据库：

WITH ranked_booking AS (
    SELECT
        customer_id,
        time_created,
        -- 对每个客户的订单按创建时间正序排序打编号
        ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY time_created ASC) AS rn
    FROM booking
)
SELECT
    a.customer_id,
    CASE
        WHEN b.time_created IS NOT NULL THEN TIMESTAMPDIFF(MICROSECOND, a.time_created, b.time_created)
        ELSE '-'
    END AS datetime_diff
FROM ranked_booking a
LEFT JOIN ranked_booking b
    ON a.customer_id = b.customer_id
    AND b.rn = 2
WHERE a.rn = 1;

如果需要返回你补充数据中展示的微秒级数值，直接用上面的写法即可；如果需要换算成天、小时等单位，修改TIMESTAMPDIFF的第一个参数即可，比如改为DAY就返回间隔天数。

如果你使用的是不支持窗口函数的旧版本数据库（比如MySQL 5.7及更早），可以改用关联聚合的写法：

SELECT
    b1.customer_id,
    CASE
        WHEN MIN(b2.time_created) IS NOT NULL THEN TIMESTAMPDIFF(MICROSECOND, MIN(b1.time_created), MIN(b2.time_created))
        ELSE '-'
    END AS datetime_diff
FROM booking b1
LEFT JOIN booking b2
    ON b1.customer_id = b2.customer_id
    AND b2.time_created > b1.time_created
GROUP BY b1.customer_id;

如果需要计算所有客户首次和第二次预订的整体平均时间差，在外层嵌套平均聚合即可：

SELECT AVG(datetime_diff) AS avg_diff
FROM (
    -- 上面的查询语句，注意把ELSE '-'改为ELSE NULL避免计算错误
    WITH ranked_booking AS (
        SELECT
            customer_id,
            time_created,
            ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY time_created ASC) AS rn
        FROM booking
    )
    SELECT
        a.customer_id,
        CASE
            WHEN b.time_created IS NOT NULL THEN TIMESTAMPDIFF(MICROSECOND, a.time_created, b.time_created)
            ELSE NULL
        END AS datetime_diff
    FROM ranked_booking a
    LEFT JOIN ranked_booking b
        ON a.customer_id = b.customer_id
        AND b.rn = 2
    WHERE a.rn = 1
) t
WHERE datetime_diff IS NOT NULL;

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