SQL LAST_VALUE函数失效 求解2021年10月模型期初/期末库存查询写法

问题原因 #

你使用LAST_VALUE未得到预期结果，是因为SQL窗口函数的默认帧规则：当OVER子句中指定了ORDER BY时，默认的计算范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是仅统计分区开头到当前行的内容，因此LAST_VALUE永远返回当前行的库存值，无法读取到月末的库存数据。

修正方案 #

根据你的需求（按模型统计单月期初、期末库存），提供两种实现方式： #

方案1：按模型+月份聚合输出（最终统计结果，每个模型一行）

这种方式直接输出你需要的统计结果，不需要保留每日明细行：

SELECT
    N_YEAR,
    N_MONTH_Y,
    modc_barc2,
    -- 取当月第一天的聚合库存作为期初
    MAX(CASE WHEN id_calendar = DATE_TRUNC('month', id_calendar) THEN qty END) AS opening_stock,
    -- 取当月最后一天的聚合库存作为期末
    MAX(CASE WHEN id_calendar = LAST_DAY(id_calendar) THEN qty END) AS closing_stock
    -- 可在此处补充入库、出库的聚合计算逻辑，完善出入库统计
FROM (
    -- 先按日期+模型聚合全部门店的库存数据
    SELECT 
        N_YEAR,
        N_MONTH_Y,
        modc_barc2,
        id_calendar,
        SUM(qty) AS qty
    FROM vt_stocks_cube_sz
    WHERE N_YEAR = 2021 AND N_MONTH_Y = 10
    GROUP BY N_YEAR, N_MONTH_Y, modc_barc2, id_calendar
) t
GROUP BY N_YEAR, N_MONTH_Y, modc_barc2;

注：如果你的数据库不支持DATE_TRUNC/LAST_DAY函数，可以替换为对应环境的日期处理语法，比如MySQL中可以用DATE_FORMAT(id_calendar, '%Y-%m-01')获取当月第一天。

方案2：保留每日明细行，每行关联当月期初/期末库存

如果需要保留每日明细，同时每行都带当月的期初期末值，可以使用修正窗口帧后的窗口函数实现：

SELECT
    id_calendar,
    modc_barc2,
    N_MONTH_Y,
    qty AS daily_total_stock,
    -- 当月期初库存
    FIRST_VALUE(qty) OVER (
        PARTITION BY N_YEAR, N_MONTH_Y, modc_barc2
        ORDER BY id_calendar
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS opening_stock,
    -- 当月期末库存
    LAST_VALUE(qty) OVER (
        PARTITION BY N_YEAR, N_MONTH_Y, modc_barc2
        ORDER BY id_calendar
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS closing_stock
FROM (
    SELECT 
        vt_stocks_cube_sz.modc_barc2, 
        vt_stocks_cube_sz.N_MONTH_Y, 
        vt_stocks_cube_sz.id_calendar,
        vt_stocks_cube_sz.N_YEAR,
        SUM(vt_stocks_cube_sz.qty) qty
    FROM vt_stocks_cube_sz
    GROUP BY vt_stocks_cube_sz.modc_barc2, vt_stocks_cube_sz.N_MONTH_Y, vt_stocks_cube_sz.id_calendar, vt_stocks_cube_sz.N_YEAR
) stocks
WHERE stocks.N_YEAR = 2021 AND stocks.N_MONTH_Y = 10 AND stocks.modc_barc2 = 'BAC008663 черный'

注：新增的ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING会指定窗口函数计算整个分区（同模型、同月份）的所有行，而非仅计算到当前行，因此可以正确获取到整月的首/尾库存值。

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