带随机值过滤列的Group By场景下如何获取正确求和结果

现有一个order表，包含多列及数千行数据，每个order_id对应多行记录，order_value列除每组1-2行有有效值外，其余大多为0。需求是按order_id分组求和order_value，同时基于Filter1、Filter2两列筛选订单——其中Filter1的有效过滤值随机出现在组内某一行，其余行值为0。原SQL使用WHERE子句同时过滤Filter1和Filter2，导致求和结果为0：满足Filter1条件的行order_value为0，而有有效order_value的行不满足Filter1条件。需要实现应用过滤条件的同时，获取每个订单的正确order_value总和。

示例数据 #

原SQL语句 #

select
order_id,
sum(order_value) as total
from `order`
where Filter1 in ('888653','888621')
and Filter2 in ('type 1','type 2')
group by order_id;

实际结果 #

预期结果 #

问题原因 #

原SQL的WHERE子句要求同一行记录同时满足Filter1和Filter2的筛选条件，但现有数据中不存在这样的行：带有有效Filter1值的行，Filter2为NULL且order_value为0；而带有有效order_value的行，Filter1值为0，无法同时满足两个条件。最终筛选出的行order_value都是0，求和结果自然为0。

解决方案 #

方法一：使用EXISTS子查询关联筛选 #

先确认每个order_id是否存在符合Filter1条件的记录，再对这些订单中符合Filter2条件的行求和order_value：

SELECT 
    o.order_id,
    SUM(o.order_value) AS total
FROM `order` o
WHERE 
    o.Filter2 IN ('type 1', 'type 2')
    AND EXISTS (
        SELECT 1 
        FROM `order` o2 
        WHERE o2.order_id = o.order_id 
          AND o2.Filter1 IN ('888653','888621')
    )
GROUP BY o.order_id;

方法二：使用窗口函数标记符合条件的订单 #

先通过窗口函数为每个订单标记是否包含有效Filter1记录，再筛选符合条件的行求和：

WITH order_with_filter_tag AS (
    SELECT 
        *,
        -- 标记当前订单是否存在有效Filter1值
        MAX(CASE WHEN Filter1 IN ('888653','888621') THEN 1 ELSE 0 END) OVER (PARTITION BY order_id) AS has_valid_filter1
    FROM `order`
)
SELECT 
    order_id,
    SUM(order_value) AS total
FROM order_with_filter_tag
WHERE has_valid_filter1 = 1
  AND Filter2 IN ('type 1', 'type 2')
GROUP BY order_id;

两种方法都能正确筛选出包含有效Filter1值的订单，并对这些订单中符合Filter2条件的行求和order_value，得到预期结果。

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