SQL按地点分组统计起止日期差值平均值（含空起始日期场景）

核心逻辑 #

• 第一步：过滤start date为无效值的行，默认无效值包含NULL，如有其他非法日期脏数据可补充过滤规则
• 第二步：计算单条有效行start date与end date的差值，可根据需求选择天、小时、分钟等统计单位
• 第三步：对差值求平均值，按location字段分组

通用SQL示例（以统计差值天数平均值为例） #

MySQL 写法 #

SELECT
    location,
    AVG(DATEDIFF(`end date`, `start date`)) AS avg_date_diff_day
FROM Loc
WHERE `start date` IS NOT NULL
-- 如有其他无效值可补充过滤条件，例：AND `start date` > '1970-01-01'
GROUP BY location;

PostgreSQL 写法 #

SELECT
    location,
    AVG(("end date" - "start date")) AS avg_date_diff_day
FROM Loc
WHERE "start date" IS NOT NULL
GROUP BY location;

SQL Server 写法 #

SELECT
    location,
    AVG(DATEDIFF(day, [start date], [end date])) AS avg_date_diff_day
FROM Loc
WHERE [start date] IS NOT NULL
GROUP BY location;

补充说明 #

如果需要统计其他时间单位的差值，只需修改对应日期函数的单位参数即可，例如MySQL中要统计小时差值，把DATEDIFF换成TIMESTAMPDIFF(hour, start date, end date)即可。

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