如何在SQL窗口函数中优雅统计type='sale'的行数？（替代CTE/子查询方案）

AI 小北

2026-4-27

解决方案：用条件聚合实现精准统计

当然有更优雅的实现方式！你不需要依赖CTE或子查询，直接在窗口函数里用条件聚合就能实现只统计type='sale'的行数。

具体修改思路

把原来的count(*) over (partition by order_num, item_id)替换成sum(case when type = 'sale' then 1 else 0 end) over (partition by order_num, item_id)。这个逻辑很直观：

当type等于sale时，返回1，否则返回0
对这个结果求和，就得到了分区内type='sale'的总行数，正好满足你需要QTY返回2的需求

修改后的完整SQL

select 
    order_num, 
    item_id, 
    type, 
    sum(amount) over (partition by order_num, item_id) as total_amount, 
    sum(tax) over (partition by order_num, item_id) as tax, 
    sum(case when type = 'sale' then 1 else 0 end) over (partition by order_num, item_id) as qty 
from scratch.saqib_ali.temp_table 
qualify row_number() over(partition by order_num order by order_num) = 1;