ClickHouse按时间戳分组计算占比结果异常问题排查

首先，你的占比总和没到100%的核心原因是：分母用了整个时间区间的全局总记录数，而不是每个timestamp分组内的总记录数。你需要的是每个时间戳分组下，各type的数量占该分组总数量的比例，而非占全局的比例。

修正思路 #

1. 用窗口函数计算每个timestamp分组的总记录数，替代原来的全局子查询
2. 简化重复的dateDiff计算，避免多次调用函数浪费性能
3. 用ClickHouse的multiIf替代嵌套if，让代码更易读

修正后的查询语句 #

SELECT 
    timestamp,
    type,
    total_count,
    round((total_count * 100) / group_total, 4) AS percentage_cnt
FROM (
    SELECT 
        -- 时间分组逻辑保持不变
        (intDiv(toUInt32(toDateTime(atime)), 120) * 120) * 1000 AS timestamp,
        -- 用multiIf简化嵌套if，同时只计算一次时间差
        multiIf(
            diff_sec <=5, 'sec5',
            diff_sec <=30, 'sec30',
            diff_sec <=60, 'sec60',
            'secgt60'
        ) AS type,
        count() AS total_count,
        -- 窗口函数：按timestamp分组，计算当前分组的总记录数
        sum(count()) OVER (PARTITION BY timestamp) AS group_total
    FROM (
        SELECT 
            t1.atime,
            t1.trid,
            -- 预先计算时间差，避免重复计算
            dateDiff('second', toDateTime(t1.atime), toDateTime(t2.unixdsn)) AS diff_sec
        FROM sess_logs.logs_view AS t1 
        INNER JOIN (
            SELECT trid, atime, unixdsn, status 
            FROM sess_logs.logs_view 
            WHERE status = 1 
                AND toDate(date) >= toDate(1621410625)
                AND ('all' = 'all' OR userid = (SELECT userid FROM sess_logs.user_details WHERE username = 'all'))
        ) AS t2 ON t1.trid = t2.trid 
        WHERE t1.status = 0 
            AND t2.status = 1 
            AND toDateTime(t1.atime) BETWEEN toDateTime(1621410625) AND toDateTime(1621421425)
            AND ('all' = 'all' OR userid = (SELECT userid FROM sess_logs.user_details WHERE username = 'all'))
    )
    GROUP BY timestamp, type, diff_sec
)
ORDER BY timestamp ASC, type;

关键优化点说明 #

1. 窗口函数sum(count()) OVER (PARTITION BY timestamp)：
   这会为每个timestamp分组计算所有type的总记录数，作为当前分组的占比分母，这样每个时间戳下的percentage_cnt总和就是100%。
2. 预计算时间差diff_sec：
   把原来重复调用3次的dateDiff移到子查询里只计算一次，大幅减少函数调用开销，提升查询性能。
3. multiIf替代嵌套if：
   ClickHouse原生支持的multiIf语法更简洁，逻辑层级清晰，比嵌套if更易维护。
4. 简化条件判断：
   把if('all' = 'all', 1, ...)改写成'all' = 'all' OR ...，符合SQL标准写法，可读性更强。

示例结果变化 #

修正后，每个timestamp分组的占比会像这样（以第一个分组为例）：

┌─────timestamp─┬─type────┬─total_count─┬─percentage_cnt─┐
│ 1621410600000 │ sec5    │ 15190       │ 90.12          │
│ 1621410600000 │ sec30   │ 1525        │ 9.03           │
│ 1621410600000 │ sec60   │ 33          │ 0.19           │
│ 1621410600000 │ secgt60 │ 61          │ 0.36           │
└───────────────┴─────────┴─────────────┴────────────────┘

该分组内的占比总和将恰好为100%，完全符合你的需求。

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