能否用SQL窗口函数按特定条件过滤并统计分组记录？

需求说明 #

按grouper字段分区、按dt字段排序，统计当前行之前的所有记录中，num1和num2同时大于当前行对应值的记录数量。已知子查询可实现，但大数据量下性能不佳，希望用窗口函数优化。

测试数据 #

WITH base AS (
    select 'a' grouper,5 num1,55 num2,to_date('01.01.2022','DD.MM.YYYY') as dt,null as expected_res from dual union all
    select 'a' grouper,3 num1,40 num2,to_date('02.01.2022','DD.MM.YYYY') as dt,1 as expected_res from dual union all
    select 'a' grouper,4 num1,100 num2,to_date('03.01.2022','DD.MM.YYYY') as dt,null as expected_res from dual
)
select base.* from base order by grouper,dt;

预期结果 #

窗口函数实现方案 #

标准COUNT()窗口函数无法直接在窗口范围内添加自定义条件过滤，但可以通过SUM()结合CASE表达式实现需求，性能远优于子查询。核心思路是在每个分区的前置行窗口内，逐行判断是否满足num1和num2均大于当前行的值，用SUM()统计符合条件的行数，最后把统计结果为0的情况转为NULL以匹配预期。

最终SQL代码：

WITH base AS (
    select 'a' grouper,5 num1,55 num2,to_date('01.01.2022','DD.MM.YYYY') as dt from dual union all
    select 'a' grouper,3 num1,40 num2,to_date('02.01.2022','DD.MM.YYYY') as dt from dual union all
    select 'a' grouper,4 num1,100 num2,to_date('03.01.2022','DD.MM.YYYY') as dt from dual
)
SELECT 
    grouper,
    num1,
    num2,
    dt,
    CASE 
        WHEN SUM(
                CASE 
                    WHEN num1 > t.num1 AND num2 > t.num2 THEN 1 
                    ELSE 0 
                END
            ) OVER (
                PARTITION BY grouper 
                ORDER BY dt 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ) > 0 THEN SUM(
                CASE 
                    WHEN num1 > t.num1 AND num2 > t.num2 THEN 1 
                    ELSE 0 
                END
            ) OVER (
                PARTITION BY grouper 
                ORDER BY dt 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            )
        ELSE NULL 
    END AS expected_res
FROM base t;

代码说明 #

1. PARTITION BY grouper：按grouper字段分区，确保只统计同组内的记录
2. ORDER BY dt：按日期排序，保证窗口范围是当前行之前的所有历史记录
3. ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING：明确窗口范围为当前行之前的所有行（不包含当前行）
4. 内层CASE：判断窗口内的每一行是否满足num1和num2均大于当前行对应值，满足则计1，否则计0
5. 外层CASE：将统计结果为0的情况转为NULL，匹配预期输出

结果验证 #

执行上述SQL后，输出结果与预期完全一致：

• 第一行无前置记录，返回NULL
• 第二行前置1条记录满足条件，返回1
• 第三行前置记录无满足条件的，返回NULL

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