在Power BI中按多字段分组计算近6个月平均时间间隔

• 第一步：合并date和time字段为完整的datetime类型字段，作为时间差计算的基础
• 第二步：使用LAG()窗口函数，按source、name、type分组后按完整时间升序排序，取每条记录对应的同组上一条记录的时间
• 第三步：计算每条记录与上一条记录的时间差，自动过滤分组第一条无前置记录的空差值
• 第四步：按分组计算所有时间差的平均值，分组仅1条记录时按示例要求默认赋值为24小时
• 第五步：将分组平均间隔关联回原表全量记录，实现同组所有记录均携带对应平均间隔值

WITH base_data AS (
    -- 合并日期时间、过滤近6个月数据
    SELECT 
        source, name, type, date, time,
        CONVERT(DATETIME, date + ' ' + time, 101) AS full_datetime
    FROM myTable
    WHERE CONVERT(DATETIME, date + ' ' + time, 101) >= DATEADD(month, -6, GETDATE())
),
time_diff AS (
    -- 计算每条记录与上一条同组记录的时间差（单位：秒）
    SELECT 
        *,
        DATEDIFF(SECOND, LAG(full_datetime) OVER(PARTITION BY source, name, type ORDER BY full_datetime), full_datetime) AS diff_second
    FROM base_data
),
group_avg AS (
    -- 计算每个分组的平均间隔，单条记录默认返回24小时
    SELECT 
        source, name, type,
        CASE 
            WHEN COUNT(diff_second) = 0 THEN 86400 -- 24小时对应的秒数
            ELSE AVG(diff_second) 
        END AS avg_diff_second
    FROM time_diff
    GROUP BY source, name, type
)
-- 关联回原表得到最终结果，将秒数转为HH:mm:ss格式
SELECT 
    b.source, b.name, b.type, b.date, b.time,
    CONCAT(
        FLOOR(g.avg_diff_second/3600), ':', 
        RIGHT('0' + CAST(FLOOR(g.avg_diff_second%3600/60) AS VARCHAR), 2), ':', 
        RIGHT('0' + CAST(g.avg_diff_second%60 AS VARCHAR), 2)
    ) AS [avg interval]
FROM base_data b
JOIN group_avg g ON b.source = g.source AND b.name = g.name AND b.type = g.type
ORDER BY b.source, b.name, b.type, b.full_datetime

• 若使用MySQL、PostgreSQL等其他数据库，仅需替换对应日期拼接、时间差计算函数即可，核心逻辑不变
• 单条记录的默认间隔值可根据实际业务需求调整，当前按示例要求设置为24小时
• 近6个月过滤条件放在最内层CTE执行，避免计算无效历史数据，提升查询效率

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