如何在SQL中按分组排序后获取列的首尾值及对应交易ID

表结构 #

Item: Varchar
Date: Date
Quantity: Float
transactionid: int

示例数据 #

问题原因 #

使用first_value和last_value未得到预期结果，核心原因是窗口函数默认的范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即仅包含当前行及之前的记录。这会导致last_value只能返回当前行之前的最后一条数据，而非整个分组的最后一条。

解决方案 #

方案1：显式指定窗口范围的first_value/last_value用法 #

通过指定窗口范围覆盖整个分组，确保first_value取分组内排序后的第一条，last_value取分组内排序后的最后一条：

WITH sorted_data AS (
    SELECT 
        Item,
        Date,
        Quantity,
        transactionid,
        -- 取分组内按transactionid升序的第一条Quantity和transactionid
        first_value(Quantity) OVER (
            PARTITION BY Item, Date 
            ORDER BY transactionid 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS OpeningBal,
        first_value(transactionid) OVER (
            PARTITION BY Item, Date 
            ORDER BY transactionid 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS OpeningTransID,
        -- 取分组内按transactionid升序的最后一条Quantity和transactionid
        last_value(Quantity) OVER (
            PARTITION BY Item, Date 
            ORDER BY transactionid 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS ClosingBal,
        last_value(transactionid) OVER (
            PARTITION BY Item, Date 
            ORDER BY transactionid 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS ClosingTransID
    FROM your_table_name
)
SELECT DISTINCT
    Item,
    Date,
    OpeningBal,
    OpeningTransID,
    ClosingBal,
    ClosingTransID
FROM sorted_data;

方案2：用ROW_NUMBER标记首尾行后聚合 #

通过给分组内的记录按transactionid升序/降序编号，标记出第一条和最后一条，再通过聚合函数提取对应值：

WITH ranked_data AS (
    SELECT 
        Item,
        Date,
        Quantity,
        transactionid,
        -- 按transactionid升序编号，1为分组第一条
        ROW_NUMBER() OVER (PARTITION BY Item, Date ORDER BY transactionid) AS rn_asc,
        -- 按transactionid降序编号，1为分组最后一条
        ROW_NUMBER() OVER (PARTITION BY Item, Date ORDER BY transactionid DESC) AS rn_desc
    FROM your_table_name
)
SELECT 
    Item,
    Date,
    MAX(CASE WHEN rn_asc = 1 THEN Quantity END) AS OpeningBal,
    MAX(CASE WHEN rn_asc = 1 THEN transactionid END) AS OpeningTransID,
    MAX(CASE WHEN rn_desc = 1 THEN Quantity END) AS ClosingBal,
    MAX(CASE WHEN rn_desc = 1 THEN transactionid END) AS ClosingTransID
FROM ranked_data
GROUP BY Item, Date;

预期结果 #

两种方案都会输出如下结果：

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