SQL实现按连续相同卖家分组聚合计算均价及日期区间问题

这是典型的**岛屿与缺口（Gaps and Islands）**序列分组场景，通过窗口函数即可实现需求，无需自定义逻辑。

实现思路 #

• 第一步：给全表所有数据按日期升序排序，生成全局连续行号global_rn
• 第二步：按卖家分组，给每个卖家的行按日期升序排序，生成卖家内连续行号seller_rn
• 第三步：计算分组标记group_flag = global_rn - seller_rn。同一卖家的连续行的group_flag值固定，当卖家出现中断后再次出现时，group_flag会发生变化，因此可以把Seller + group_flag作为唯一分组键
• 第四步：按Seller和group_flag聚合，计算平均价格、最早日期、最晚日期即可

标准SQL实现代码 #

WITH ranked_data AS (
    SELECT
        Seller,
        price,
        date,
        -- 全局行号，按日期排序
        ROW_NUMBER() OVER (ORDER BY date) AS global_rn,
        -- 每个卖家内部的行号，按日期排序
        ROW_NUMBER() OVER (PARTITION BY Seller ORDER BY date) AS seller_rn
    FROM your_table_name
)
SELECT
    Seller,
    AVG(price) AS Avg_Price,
    MIN(date) AS From_Date,
    MAX(date) AS To_Date
FROM ranked_data
GROUP BY Seller, global_rn - seller_rn
ORDER BY From_Date;

注意说明 #

• 上述SQL兼容所有支持窗口函数的SQL引擎：MySQL 8.0+、PostgreSQL、Spark SQL、Hive、SQL Server等
• 你给出的预期结果中B卖家的平均价格存在笔误，正确结果应为(20+40)/2 = 30，上述SQL会输出正确的计算结果
• 如果你的表数据存在相同日期的多条记录，可以把排序逻辑调整为ORDER BY date, 其他唯一排序字段保证行号稳定性

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