基于单日期列实现多日期分组计算滚动3日均价（SSMS场景）

问题描述 #

在SSMS中有一张生鲜商品日价表fresh_prices，表结构及数据如下：

需要计算滚动3日均价，输出格式如下：

需解决两个问题：

1. 如何实现上述基础的滚动3日均价计算？
2. 若表新增city列（每个城市每日有对应蔬菜价格），且查询中无法按date排序，仍需按蔬菜维度忽略城市计算3日均价，该如何处理？

解决方案 #

1. 基础场景：无city列，计算滚动3日均价 #

在SQL Server中，使用窗口函数结合日期范围拼接即可实现：

SELECT
    item AS vegetable,
    CONCAT(FORMAT(MIN(date) OVER (PARTITION BY item ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 'M/d'), '-', FORMAT(date, 'M/d')) AS three_dates,
    ROUND(AVG(price) OVER (PARTITION BY item ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS average_price
FROM fresh_prices
WHERE date >= DATEADD(day, 2, (SELECT MIN(date) FROM fresh_prices WHERE item = fresh_prices.item))
ORDER BY item, date;

关键说明：

• PARTITION BY item：按蔬菜品种分组，保证每个蔬菜单独计算滚动窗口
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：定义窗口为当前行及前2行，即连续3天的数据
• CONCAT+FORMAT：拼接窗口内的首尾日期，生成1/1-1/3格式的日期范围
• WHERE子句：过滤掉无法形成完整3日窗口的初始行（如1/1、1/2日的记录）

2. 扩展场景：新增city列，无法按date排序 #

当表新增city列且无法直接依赖物理行的日期顺序时，先给每个蔬菜的日期生成时间序号，再基于序号计算滚动窗口：

方案1：直接纳入所有城市价格计算滚动均价

WITH ranked_dates AS (
    SELECT
        item,
        date,
        price,
        -- 给每个蔬菜的日期按时间顺序生成序号，不受物理行顺序影响
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) AS date_rank
    FROM fresh_prices
)
SELECT
    item AS vegetable,
    CONCAT(
        FORMAT(MAX(CASE WHEN date_rank = r.date_rank - 2 THEN date END), 'M/d'),
        '-',
        FORMAT(date, 'M/d')
    ) AS three_dates,
    ROUND(AVG(price) OVER (PARTITION BY item ORDER BY date_rank ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS average_price
FROM ranked_dates r
WHERE date_rank >= 3 -- 过滤不足3天的行
ORDER BY item, date_rank;

方案2：先算每日城市均价，再计算滚动3日均价

如果需要先对同一蔬菜同日的多城市价格取平均，再计算滚动3日均价：

WITH daily_city_avg AS (
    -- 先计算同一蔬菜同日所有城市的均价
    SELECT
        item,
        date,
        AVG(price) AS daily_price
    FROM fresh_prices
    GROUP BY item, date
),
ranked_dates AS (
    SELECT
        item,
        date,
        daily_price,
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) AS date_rank
    FROM daily_city_avg
)
SELECT
    item AS vegetable,
    CONCAT(FORMAT(MAX(CASE WHEN date_rank = r.date_rank - 2 THEN date END), 'M/d'), '-', FORMAT(date, 'M/d')) AS three_dates,
    ROUND(AVG(daily_price) OVER (PARTITION BY item ORDER BY date_rank ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS average_price
FROM ranked_dates r
WHERE date_rank >= 3
ORDER BY item, date_rank;

关键说明：

• ranked_dates CTE：通过ROW_NUMBER()生成时间序号，确保窗口按时间顺序取连续3天，不受物理行排序影响
• 忽略city列：仅按item分组计算，city列不参与分组或排序，自然被纳入统一计算

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