如何获取最近7天qty总和？现有SQL代码得滚动求和需排查

问题分析与解决方案 #

你的代码核心问题在于未按产品+日期聚合销量，且窗口逻辑依赖行数量而非明确的日期范围，导致结果不符合"最近7天总和"的预期。以下是修正方案：

通用修正代码（适配所有SQL Server版本） #

DECLARE @startdate date = '2023-03-25';
DECLARE @enddate date = '2023-04-11';

WITH date_cte AS (
    -- 生成连续日期序列
    SELECT @startdate AS order_date
    UNION ALL
    SELECT DATEADD(day, 1, order_date)
    FROM date_cte
    WHERE DATEADD(day, 1, order_date) <= @enddate
),
daily_product_sales AS (
    -- 聚合原表：按产品+日期计算每日总销量
    SELECT
        product_id,
        order_date,
        SUM(qty) AS daily_qty
    FROM zadanie5
    GROUP BY product_id, order_date
),
all_product_dates AS (
    -- 生成所有产品+日期的完整组合，缺失日期的销量补0
    SELECT
        dc.order_date,
        p.product_id,
        ISNULL(dps.daily_qty, 0) AS qty
    FROM date_cte dc
    -- 交叉连接所有产品，确保每个日期每个产品都有记录
    CROSS JOIN (SELECT DISTINCT product_id FROM zadanie5) p
    LEFT JOIN daily_product_sales dps 
        ON dc.order_date = dps.order_date 
        AND p.product_id = dps.product_id
)
SELECT
    order_date,
    product_id,
    qty,
    -- 计算当前日期及前6天（共7天）的销量总和
    SUM(qty) OVER (
        PARTITION BY product_id
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS sum_qty
FROM all_product_dates
ORDER BY product_id, order_date;

原代码的问题点 #

• 未聚合每日销量：如果同一天同一产品有多条订单，原代码会保留所有行，ROWS BETWEEN 6 PRECEDING会计算最近7行的总和，而非7天的总和。
• RIGHT JOIN的缺陷：原代码的RIGHT JOIN会产生product_id为NULL的无效行，且未确保每个产品在每个日期都有记录，可能导致同一产品的行不连续，窗口覆盖的日期范围超出7天。
• 窗口逻辑歧义：依赖行数量的窗口在日期补全但未关联所有产品时，无法精准对应7天的时间范围。

SQL Server 2022+优化写法 #

如果使用SQL Server 2022及以上版本，可直接用日期范围定义窗口，逻辑更直观：

DECLARE @startdate date = '2023-03-25';
DECLARE @enddate date = '2023-04-11';

WITH date_cte AS (
    SELECT @startdate AS order_date
    UNION ALL
    SELECT DATEADD(day, 1, order_date)
    FROM date_cte
    WHERE DATEADD(day, 1, order_date) <= @enddate
),
daily_product_sales AS (
    SELECT
        product_id,
        order_date,
        SUM(qty) AS daily_qty
    FROM zadanie5
    GROUP BY product_id, order_date
),
all_product_dates AS (
    SELECT
        dc.order_date,
        p.product_id,
        ISNULL(dps.daily_qty, 0) AS qty
    FROM date_cte dc
    CROSS JOIN (SELECT DISTINCT product_id FROM zadanie5) p
    LEFT JOIN daily_product_sales dps 
        ON dc.order_date = dps.order_date 
        AND p.product_id = dps.product_id
)
SELECT
    order_date,
    product_id,
    qty,
    -- 直接指定日期范围：当前日期及前6天
    SUM(qty) OVER (
        PARTITION BY product_id
        ORDER BY order_date
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS sum_qty
FROM all_product_dates
ORDER BY product_id, order_date;

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