ClickHouse中按Spot去重Respondent计算累计权重的查询方案

我有一个包含respondent、spot和weight三列的ClickHouse表，数据如下：

resp, spot, weight
1, 10, 100
2, 10, 200
3, 10, 300
1, 10, 100
1, 20, 100
3, 20, 300
4, 20, 400
1, 30, 100
2, 30, 200
3, 30, 300
5, 30, 500

其中spot列已排序。需要编写查询语句，按spot=10、20、30的顺序计算累计权重，要求每个respondent仅被统计一次：即spot=20时需包含spot=10中的respondent但只统计一次，spot=30时包含spot=10和20中的respondent且仅统计一次。

预期结果如下：

spot, summed_weight
10, 600
20, 1000
30, 1500

我之前尝试用group_array函数组合respondent与其权重，再通过row_number函数获取每个spot的最后一行，但这种方法内存占用极高，示例数据如下：

resp, spot, weight, group_array, row_number
1, 10, 100, [(1,100)], 4
2, 10, 200, [(1,100), (2,200)], 3
3, 10, 300, [(1,100), (2,200), (3,300)], 2
1, 10, 100, [(1,100), (2,200), (3,300)], 1
1, 20, 100, [(1,100), (2,200), (3,300)], 3
3, 20, 300, [(1,100), (2,200), (3,300)], 2
4, 20, 400, [(1,100), (2,200), (3,300), (4,400)], 1
1, 30, 100, [(1,100), (2,200), (3,300), (4,400)], 4
2, 30, 200, [(1,100), (2,200), (3,300), (4,400)], 3
3, 30, 300, [(1,100), (2,200), (3,300), (4,400)], 2
5, 30, 500, [(1,100), (2,200), (3,300), (4,400), (5,500)], 1

可以通过以下高效方法实现需求，避免内存占用过高的问题：

1. 先对respondent去重：保留每个respondent的唯一权重记录（同一respondent的weight值一致，取任意一次即可）。
2. 用窗口函数累计求和：按spot排序，逐步累加所有已出现的唯一respondent的权重。

具体查询语句如下：

WITH
-- 提取每个respondent的唯一权重记录
unique_resp AS (
    SELECT resp, weight
    FROM your_table_name
    GROUP BY resp, weight
),
-- 按spot顺序计算累计权重和
spot_cumulative AS (
    SELECT DISTINCT spot,
           sum(weight) OVER (ORDER BY spot) AS summed_weight
    FROM your_table_name
    LEFT JOIN unique_resp USING (resp)
    ORDER BY spot
)
SELECT * FROM spot_cumulative;

逻辑说明 #

• unique_resp CTE先完成去重，确保每个respondent只对应一条权重记录，从根源避免重复统计。
• spot_cumulative CTE中，通过左关联将每个spot的记录与唯一respondent数据绑定，再用sum(weight) OVER (ORDER BY spot)窗口函数，按spot的顺序自动累计所有已出现的唯一respondent权重，完美实现“spot越大，包含的历史唯一respondent越多”的需求。

执行结果 #

运行上述查询后，会得到符合预期的结果：

spot, summed_weight
10, 600
20, 1000
30, 1500

该方案摒弃了内存密集型的group_array操作，充分利用ClickHouse的分组和窗口函数特性，计算效率更高。

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