1

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
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Frits Nagtegaal
  • 61
  • 1
  • 6
  • Sounds like gaps and islands problem. Not sure where that 10 is coming from though. – James Z Jan 22 '23 at 10:34
  • gaps and islands? 10 is the step. So eg from 70 you go by steps from 10 to 110, covering the values 70, 80, 90, 100 and 110 – Frits Nagtegaal Jan 22 '23 at 10:38
  • Yes, gaps and island Iis the name of problem you are facing, @FritsNagtegaal . It even has its own tag where you can find plenty of related questions: [[tag:gaps-and-islands]] – Thom A Jan 22 '23 at 10:44

1 Answers1

2

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.

lemon
  • 14,875
  • 6
  • 18
  • 38
  • 1
    Thank you so much @lemon , the computing of a running sum was the part that helped me out. Actually the AXIS_RANGE_STEP can be other than 10, so I had to tweak it a little, but that was easy to calculate. Thanks!!!!!! – Frits Nagtegaal Jan 22 '23 at 20:55