SQL窗口函数计算4周售罄率与覆盖率 提取各条码对应最大周数据

• 需基于销售库存表计算两个核心指标：
  • sellthru = 近4周销量总和 / 4周周期期初库存 × 100
  • coverage = 4周周期期末库存 / 近4周周均销量 × 100（注：原公式笔误已修正，移除多余的斜杠符号）
• 最终输出仅保留每个barcode对应最大周数的计算结果

你写的窗口函数存在两处错误：

1. order by后错误使用了聚合函数SUM(week_number)，排序字段直接取week_number即可
2. 窗口范围设置错误，你用的是前4行到前1行，没有包含当前周的销量，导致近4周统计范围不全

以下为PostgreSQL语法实现，其他数据库仅需调整类型转换逻辑即可适配：

WITH barcode_max_week AS (
    -- 先获取每个barcode对应的最大周数
    SELECT 
        barcode,
        MAX(week_number) AS max_week
    FROM sell
    GROUP BY barcode
),
week_window_calc AS (
    SELECT
        s.*,
        -- 统计近4周销量总和（当前周+往前3周，共4周）
        SUM(quantity) OVER (
            PARTITION BY s.barcode 
            ORDER BY s.week_number 
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        ) AS last_4w_sales_sum,
        -- 统计近4周周均销量
        AVG(quantity) OVER (
            PARTITION BY s.barcode 
            ORDER BY s.week_number 
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        ) AS last_4w_sales_avg,
        -- 取4周周期期初库存，即窗口第一周的库存
        FIRST_VALUE(stock) OVER (
            PARTITION BY s.barcode 
            ORDER BY s.week_number 
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        ) AS period_begin_stock,
        -- 当前周库存即为4周周期期末库存
        stock AS period_end_stock
    FROM sell s
)
SELECT
    wwc.barcode,
    wwc.week_number,
    -- 计算sellthru，做除以0异常处理
    ROUND(
        wwc.last_4w_sales_sum / NULLIF(wwc.period_begin_stock, 0)::DECIMAL * 100,
        2
    ) AS sellthru,
    -- 计算coverage，做除以0异常处理
    ROUND(
        wwc.period_end_stock / NULLIF(wwc.last_4w_sales_avg, 0)::DECIMAL * 100,
        2
    ) AS coverage
FROM week_window_calc wwc
INNER JOIN barcode_max_week bmw 
    ON wwc.barcode = bmw.barcode 
    AND wwc.week_number = bmw.max_week
-- 过滤周数不足4周的barcode，不需要该逻辑可直接删除
WHERE bmw.max_week >=4;

基于你提供的测试样例计算输出如下：

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