CODEWITHSUNDEEP
sqlsql-servert-sqlgaps-and-islandspartition-by
Frits Nagtegaal
Frits Nagtegaal

Reputation: 61

from array to range by step in SQL Server

The array of numbers should be transferred to ranges to compress the number of records. This is an example:

Current:

L2PARAM_ID A_AXIS_RANGE
CL_L2_Params_2688303 10
CL_L2_Params_2688303 20
CL_L2_Params_2688303 70
CL_L2_Params_2688303 80
CL_L2_Params_2688303 90
CL_L2_Params_2688303 100
CL_L2_Params_2688303 110
CL_L2_Params_2688303 160
CL_L2_Params_2688303 170
CL_L2_Params_2688303 180

Needed:

L2PARAM_ID A_AXIS_RANGE_FROM A_AXIS_RANGE_TO A_AXIS_RANGE_STEP
CL_L2_Params_2688303 10 20 10
CL_L2_Params_2688303 70 110 10
CL_L2_Params_2688303 160 180 10

Seems quite simple with the windows functions, but so far hard to get the 3 'groups'. Any help would be appreciated

tried so far:

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

Upvotes: 1

Views: 52

Answers (1)

lemon
lemon

Reputation: 15482

In this specific gaps-and-islands problem you need to:

  • look for when the axis_range between consecutive rows changes by more than 10, given this is your prefixed step, and get to flag with 1 those rows
  • compute a running sum over your flag value, in order to generate a new partitioning inside the "L2PARAM_ID" partition
  • aggregate exploiting your just generated partition, alongside with "L2PARAM_ID"
WITH cte1 AS (
    SELECT *, 
           CASE WHEN A_AXIS_RANGE 
                     - LAG(A_AXIS_RANGE) OVER(PARTITION BY L2PARAM_ID ORDER BY A_AXIS_RANGE) > 10
                THEN 1 ELSE 0 
           END AS change_part 
    FROM tab
), cte2 AS (
    SELECT *, 
           SUM(change_part) OVER(PARTITION BY L2PARAM_ID ORDER BY A_AXIS_RANGE) AS part 
    FROM cte1
)
SELECT L2PARAM_ID, 
       MIN(A_AXIS_RANGE) AS A_AXIS_RANGE_FROM,
       MAX(A_AXIS_RANGE) AS A_AXIS_RANGE_TO,
       10                AS AXIS_RANGE_STEP
FROM cte2 
GROUP BY L2PARAM_ID, part

Check the demo here.

Upvotes: 2

Related Questions