SQL如何计算每月每ID平均在线时长 现有语句仅支持单日统计

基于现有代码的修改方案 #

你可以通过CTE（公共表表达式）封装现有单日统计逻辑，去重后按月度分组聚合即可得到月度统计结果：

WITH daily_online AS (
    -- 先拿到每个ID单日的在线小时数，去重避免重复计算
    SELECT DISTINCT
        id,
        DATE_TRUNC('month', created) AS stat_month,
        DATE(created) AS stat_date,
        (COUNT(*) OVER (PARTITION BY id, DATE(created)) / 4.0) AS daily_online_hours
    FROM #tempres
    GROUP BY 
        id,
        DATE(created),
        DATE_PART(hour, created),
        FLOOR(DATE_PART(minute, created) / 15)
)
SELECT 
    id,
    stat_month,
    -- 月均每日在线小时数，按有活跃的日期求平均
    AVG(daily_online_hours) AS avg_daily_online_hours,
    -- 月度累计总在线小时数
    SUM(daily_online_hours) AS total_monthly_online_hours
FROM daily_online
GROUP BY id, stat_month
ORDER BY id, stat_month;

更优实现思路 #

原有逻辑中先分组再开窗再去重的步骤有冗余，你可以直接对15分钟活跃窗口去重后统计，性能更高：

WITH active_15min_window AS (
    SELECT DISTINCT
        id,
        DATE_TRUNC('month', created) AS stat_month,
        DATE(created) AS stat_date,
        -- 给15分钟窗口打唯一标记，同一个窗口只算一次活跃
        DATE_TRUNC('minute', created) - INTERVAL '1 minute' * (DATE_PART('minute', created)::int % 15) AS window_tag
    FROM #tempres
)
SELECT
    id,
    stat_month,
    -- 总活跃窗口数/4得到月度总在线小时
    COUNT(*) / 4.0 AS total_monthly_online_hours,
    -- 总在线小时除以当月活跃天数，得到月均每日在线小时
    (COUNT(*) / 4.0) / COUNT(DISTINCT stat_date) AS avg_daily_online_hours
FROM active_15min_window
GROUP BY id, stat_month
ORDER BY id, stat_month;

口径说明 #

• 以上代码默认沿用你原有的「15分钟内有活动就算该窗口在线」的统计逻辑
• 如果你的业务需求需要按当月自然日天数计算平均每日在线时长，将平均逻辑的分母替换为DATE_PART('day', DATE_TRUNC('month', stat_month) + INTERVAL '1 month' - INTERVAL '1 day')即可

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