SQL Server中按city_id和store_type分段获取最小最大store_id

要解决同一city_id和store_type下store_id不连续分段的统计问题，核心是识别出连续的store_id序列，再对每个序列做聚合统计。可以通过计算store_id与行号的差值来分组连续序列，具体SQL实现如下：

步骤1：生成分组键 #

先通过窗口函数生成每个store_id对应的分组标识，同一连续序列的分组键值相同：

SELECT
    store_id,
    city_id,
    store_type,
    -- 同一连续序列的store_id减去行号的结果固定，以此作为分组依据
    store_id - ROW_NUMBER() OVER (PARTITION BY city_id, store_type ORDER BY store_id) AS group_key
FROM store

步骤2：按分组键聚合统计 #

基于上面的子查询，按city_id、store_type和group_key分组，计算每个分段的最小和最大store_id：

SELECT
    city_id,
    store_type,
    MIN(store_id) AS min_store_id,
    MAX(store_id) AS max_store_id
FROM (
    SELECT
        store_id,
        city_id,
        store_type,
        store_id - ROW_NUMBER() OVER (PARTITION BY city_id, store_type ORDER BY store_id) AS group_key
    FROM store
) t
GROUP BY city_id, store_type, group_key
ORDER BY city_id, store_type, min_store_id;

逻辑说明 #

在同一city_id和store_type的分组内，按store_id升序排列后：

• 连续的store_id（如1、2、3）对应的行号是1、2、3，store_id - 行号的结果都是0，属于同一分组
• 断开的store_id（如5、6）对应的行号是4、5，store_id - 行号的结果都是1，属于另一个分组
  通过这个分组键就能精准区分不同的不连续分段，再聚合得到每个分段的边界值。

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