如何基于共享关联键的其他行进行SQL聚合查询？

算力阿峰

2026-10-3

错误原因

原有语句报错是因为子查询未限定关联外层的event_id，导致过滤条件d.event_id = event_id实际等价于d.event_id = d.event_id恒成立，返回所有key='Score'的行；即使加上外层表限定，同一个event_id下存在多条Score记录时仍然会触发多行返回错误。

解决方案

方案1：CTE预聚合（扩展性最优，适合多过滤key场景）

先按event_id聚合出所有需要用来过滤的属性值，再关联做求和计算，后续新增过滤key只需修改CTE部分即可：

WITH event_attrs AS (
    SELECT 
        event_id,
        MAX(CASE WHEN key = 'Score' THEN value::float END) AS score
        -- 新增其他过滤key直接在这里加对应聚合项即可，示例：
        -- MAX(CASE WHEN key = 'PlayDuration' THEN value::float END) AS play_duration
    FROM data
    WHERE user_id = '9765f312-0d0b-4db0-b4c5-217eec81d7c3'
    GROUP BY event_id
    -- 预过滤符合条件的event，减少后续关联数据量
    HAVING MAX(CASE WHEN key = 'Score' THEN value::float END) >= 3
    -- 新增其他过滤条件直接加在HAVING后即可，示例：
    -- AND MAX(CASE WHEN key = 'PlayDuration' THEN value::float END) > 10
)
SELECT SUM(d.value::float) AS target_sum
FROM data d
JOIN event_attrs ea ON d.event_id = ea.event_id
WHERE d.key = 'MediaLength'
  AND d.user_id = '9765f312-0d0b-4db0-b4c5-217eec81d7c3';

方案2：窗口函数（性能最优，只需单次扫表）

通过窗口函数按event_id计算关联属性值，无需二次扫描表，适合数据量较大的场景：

SELECT SUM(value::float) AS target_sum
FROM (
    SELECT 
        *,
        MAX(CASE WHEN key = 'Score' THEN value::float END) OVER (PARTITION BY event_id) AS event_score
        -- 新增其他过滤key同理加对应的窗口函数即可
    FROM data
    WHERE user_id = '9765f312-0d0b-4db0-b4c5-217eec81d7c3'
) t
WHERE key = 'MediaLength' AND event_score >= 3;