SQLite3中计算tests表同一id下日期列表的平均日期间隔

刚接触数据库碰到这种日期计算的需求确实容易懵，我来给你拆解一下怎么实现，保证你能看懂～

核心思路 #

要算出相邻日期的平均差，咱们得先把同一个ID的日期按顺序排好，接着找到每个日期对应的下一个日期，算出两者的天数差，最后把这些差值求个平均值就行。

针对主流数据库的实现代码 #

假设你的tests表完整数据类似这样（补全了你提到的不完整示例）：

1. MySQL 版本 #

SELECT AVG(diff_days) AS avg_day_diff
FROM (
    SELECT 
        id,
        date,
        -- 拿到下一个日期，计算和当前日期的天数差
        DATEDIFF(LEAD(date) OVER (PARTITION BY id ORDER BY date), date) AS diff_days
    FROM tests
    WHERE id = 1 -- 指定要计算的ID，去掉这行就是计算所有ID的平均差
) AS date_diffs
WHERE diff_days IS NOT NULL; -- 过滤掉最后一行（没有下一个日期，差值为null）

2. PostgreSQL 版本 #

SELECT AVG(diff_days) AS avg_day_diff
FROM (
    SELECT 
        id,
        date,
        -- 计算下一个日期与当前日期的天数差
        DATE_PART('day', LEAD(date) OVER (PARTITION BY id ORDER BY date) - date) AS diff_days
    FROM tests
    WHERE id = 1
) AS date_diffs
WHERE diff_days IS NOT NULL;

额外说明 #

• 日期类型检查：一定要确保你的date列是日期类型（比如DATE、DATETIME），如果存的是字符串，得先转成日期格式。比如MySQL用STR_TO_DATE(date, '%Y-%m-%d')，PostgreSQL用TO_DATE(date, 'YYYY-MM-DD')替换代码里的date就行。
• 单日期情况处理：如果某个ID只有一条日期记录，那没有相邻日期，计算结果会是null。要是想把这种情况返回0，可以用AVG(COALESCE(diff_days, 0))替代原来的AVG(diff_days)。
• 批量计算所有ID：如果要一次性算出所有ID的相邻日期平均差，去掉WHERE id = 1，然后在外层按id分组就行，比如MySQL的代码改成：

SELECT id, AVG(diff_days) AS avg_day_diff
FROM (
    SELECT 
        id,
        DATEDIFF(LEAD(date) OVER (PARTITION BY id ORDER BY date), date) AS diff_days
    FROM tests
) AS date_diffs
WHERE diff_days IS NOT NULL
GROUP BY id;

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