如何筛选窗口聚合查询中每个分组的最后一行数据？

我编写了如下SQL查询语句：

select  b.Project_Id,b.Id,
FIRST_VALUE(i.Number) OVER(PARTITION BY b.id ORDER BY i.Date) as FirstNumDDT
,sum( ir.Qty) OVER(PARTITION BY b.id ORDER BY i.date)  as sumqty
from InvoiceRow ir 
inner join Invoice i on i.id=Invoice_Id 
inner join BillOfMaterial b on b.id=ir.BOM_Id

查询返回的部分结果如下：

我需要仅保留每个分组（按b.id分组）的最后一行数据，请问该如何进行筛选？

可以通过窗口函数标记分组内的行序，进而筛选出每组的最后一行，以下是两种常用方法：

方法一：使用ROW_NUMBER()子查询 #

WITH ranked_data AS (
    select  
        b.Project_Id,
        b.Id,
        FIRST_VALUE(i.Number) OVER(PARTITION BY b.id ORDER BY i.Date) as FirstNumDDT,
        sum( ir.Qty) OVER(PARTITION BY b.id ORDER BY i.date)  as sumqty,
        -- 按日期倒序标记行号，每组最后一行的行号为1
        ROW_NUMBER() OVER(PARTITION BY b.id ORDER BY i.date DESC) AS row_num
    from InvoiceRow ir 
    inner join Invoice i on i.id=Invoice_Id 
    inner join BillOfMaterial b on b.id=ir.BOM_Id
)
SELECT Project_Id, Id, FirstNumDDT, sumqty
FROM ranked_data
WHERE row_num = 1;

方法二：使用QUALIFY子句（适用于Snowflake、BigQuery等方言） #

如果你的SQL支持QUALIFY子句，写法会更简洁：

select  
    b.Project_Id,
    b.Id,
    FIRST_VALUE(i.Number) OVER(PARTITION BY b.id ORDER BY i.Date) as FirstNumDDT,
    sum( ir.Qty) OVER(PARTITION BY b.id ORDER BY i.date)  as sumqty
from InvoiceRow ir 
inner join Invoice i on i.id=Invoice_Id 
inner join BillOfMaterial b on b.id=ir.BOM_Id
QUALIFY ROW_NUMBER() OVER(PARTITION BY b.id ORDER BY i.date DESC) = 1;

注意事项 #

• 排序逻辑以i.date为准，如果存在日期重复的情况，建议添加额外排序字段（比如i.id）来明确行的先后顺序，避免结果出现不确定性。
• 两种方法的核心都是通过窗口函数定位到每组的最后一行，再进行筛选。

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