SQL Server中按步长将数值数组转换为连续范围的实现

需要将SQL Server表中的离散数值转换为连续范围，以此压缩记录数量。示例如下：

当前数据 #

期望结果 #

原本打算用窗口函数实现，但无法正确划分出3组连续范围，已尝试的SQL代码如下：

with axis_by_step as (
    select
      L2PARAM_ID,
      A_AXIS_RANGE,
      LEAD (A_AXIS_RANGE) OVER (PARTITION BY L2PARAM_ID ORDER BY A_AXIS_RANGE) - A_AXIS_RANGE AS STEP_3,
      COUNT (A_AXIS_RANGE) OVER (PARTITION BY L2PARAM_ID) cnt,
      CASE WHEN
        COUNT (A_AXIS_RANGE) OVER (PARTITION BY L2PARAM_ID) = 1
        THEN A_AXIS_RANGE
        ELSE LEAD (A_AXIS_RANGE) OVER (PARTITION BY L2PARAM_ID ORDER BY A_AXIS_RANGE)
        END AS TO_3
    from PMDM_LRSV_CL_L2_PARAM_PREP_MULTI_AXIS
)
select *
from axis_by_step

这是典型的连续值分组（岛屿问题），可以通过计算每个值与序列编号的差值来生成分组键，同一连续范围内的数值会得到相同的分组键。以下是适配需求的解决方案：

方案一（固定步长场景） #

如果确认所有连续范围的步长固定为10，可使用以下代码：

WITH ranked_data AS (
    SELECT 
        L2PARAM_ID,
        A_AXIS_RANGE,
        -- 按L2PARAM_ID分组后给数值排序
        ROW_NUMBER() OVER (PARTITION BY L2PARAM_ID ORDER BY A_AXIS_RANGE) AS rn,
        -- 生成分组键：同一连续范围的数值，A_AXIS_RANGE - rn的结果一致
        A_AXIS_RANGE - ROW_NUMBER() OVER (PARTITION BY L2PARAM_ID ORDER BY A_AXIS_RANGE) AS group_key
    FROM PMDM_LRSV_CL_L2_PARAM_PREP_MULTI_AXIS
)
SELECT 
    L2PARAM_ID,
    MIN(A_AXIS_RANGE) AS A_AXIS_RANGE_FROM,
    MAX(A_AXIS_RANGE) AS A_AXIS_RANGE_TO,
    -- 计算步长，这里固定为10，也可通过相邻值差动态计算
    MAX(A_AXIS_RANGE) - MIN(A_AXIS_RANGE) / (COUNT(*) - 1) AS A_AXIS_RANGE_STEP
FROM ranked_data
GROUP BY L2PARAM_ID, group_key;

方案二（动态步长场景） #

如果步长不固定，但需要按同一步长连续递增的规则分组，可使用以下代码：

WITH step_calc AS (
    SELECT 
        L2PARAM_ID,
        A_AXIS_RANGE,
        -- 计算当前值与下一个值的步长
        LEAD(A_AXIS_RANGE) OVER (PARTITION BY L2PARAM_ID ORDER BY A_AXIS_RANGE) - A_AXIS_RANGE AS current_step
    FROM PMDM_LRSV_CL_L2_PARAM_PREP_MULTI_AXIS
),
group_mark AS (
    SELECT 
        *,
        -- 当当前步长与上一步长不同时，标记为新组起点
        SUM(CASE WHEN current_step = LAG(current_step) OVER (PARTITION BY L2PARAM_ID ORDER BY A_AXIS_RANGE) THEN 0 ELSE 1 END) 
        OVER (PARTITION BY L2PARAM_ID ORDER BY A_AXIS_RANGE) AS group_key
    FROM step_calc
)
SELECT 
    L2PARAM_ID,
    MIN(A_AXIS_RANGE) AS A_AXIS_RANGE_FROM,
    MAX(A_AXIS_RANGE) AS A_AXIS_RANGE_TO,
    MAX(current_step) AS A_AXIS_RANGE_STEP
FROM group_mark
GROUP BY L2PARAM_ID, group_key;

逻辑说明 #

1. 方案一通过A_AXIS_RANGE - 排序编号生成分组键，利用连续数值的增长规律（每增加一个排序位，数值增加固定步长）将同范围数值归为一组；
2. 方案二先计算每个值的步长，再根据步长变化标记分组，适配步长不固定的场景；
3. 最终通过分组聚合提取每组的起始值、结束值和步长，实现记录压缩。

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