销售数据压缩：SQL分组统计ID数量错误的解决方案咨询

针对你的需求——按销售日期、商品名称、销售地点分组，将连续的商品ID合并为起始ID、结束ID和实际销售数量，同时正确处理ID不连续的情况，可以用间隙识别法来实现，具体方案如下：

核心思路 #

连续的ID在分组内排序后，ID - 行号的结果会保持一致；当ID出现断档时，这个差值会发生变化，以此作为区分连续组的标识，再基于这个标识进行聚合统计，就能得到准确的数量。

完整SQL代码 #

WITH grouped_data AS (
    SELECT
        date,
        name,
        place,
        id,
        -- 生成连续组标识：ID减去分组内排序后的行号
        id - ROW_NUMBER() OVER (PARTITION BY date, name, place ORDER BY id) AS group_id
    FROM t
)
SELECT
    date,
    name,
    place,
    MIN(id) AS id_start,
    MAX(id) AS id_end,
    COUNT(*) AS count
FROM grouped_data
GROUP BY date, name, place, group_id
ORDER BY date, name, place, id_start;

代码说明 #

1. CTE阶段（grouped_data）：
   • 按date, name, place分组，对每组内的id排序并生成行号
   • 计算id - ROW_NUMBER()：连续ID的差值固定（比如ID5、6、7对应行号1、2、3，差值均为4）；ID断档时差值会变化（比如ID9和55对应行号1、2，差值分别为8和53），以此区分不同的连续组
2. 聚合查询阶段：
   • 按date, name, place+group_id分组，确保连续ID归为一组、不连续ID分开
   • 用MIN(id)取起始ID，MAX(id)取结束ID，COUNT(*)统计实际销售数量（不受ID断档影响）

测试验证 #

连续ID场景 #

输入数据：

(15.02.2020, ff, rt, 5)
(15.02.2020, ff, rt, 6)
(15.02.2020, ff, rt, 7)
(15.02.2020, ss, rt, 8)
(15.02.2020, ss, rt, 9)

输出结果：

(15.02.2020, ff, rt, 5, 7, 3)
(15.02.2020, ss, rt, 8, 9, 2)

完全匹配你的需求。

ID不连续场景 #

输入数据：

(15.02.2020, ss, rt, 9)
(15.02.2020, ss, rt, 55)

输出结果：

(15.02.2020, ss, rt, 9, 9, 1)
(15.02.2020, ss, rt, 55, 55, 1)

正确统计出实际销售2件，避免了之前用max-min得到错误数量的问题。

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