RaqForum 41 No.
32 View  • 
     ...

From SQL to SPL: Flexible interval aggregation

 

The range of the ID field in a table of Oracle database is integers 1-2000.

id

value

1

10

1

11

2

100

3

5

3

9

20

0

21

1000

22

2000

1000

1

1000

20

Now we need to group IDs into multiple consecutive intervals, sum up the values within each group, and arrange the calculation results in order of intervals. The intervals are in the form of a sequence of numbers that can be flexibly modified. For example, when the numerical sequence is 10, 20, 100, 1000, 2000, it means grouping and aggregating IDs in the intervals of 1-10, 11-20, 21-100, 101-1000, and 1001-2000. Note that the starting value format of the grouping string is: member of the numerical sequence+1.

IDs_range

sum_of_values

1-10

135

11-20

0

21-100

3000

101-1000

21


SQL： 
WITH id_ranges (min_value, max_value) AS (
  SELECT LAG(COLUMN_VALUE, 1, 0) OVER (ORDER BY COLUMN_VALUE) + 1,
         COLUMN_VALUE
  FROM   TABLE(SYS.ODCINUMBERLIST(10,20,100,1000,2000))
)
SELECT r.min_value || '-' || r.max_value AS id_range,
       SUM(value) AS sum_of_values
FROM   dummy_data d
       INNER JOIN id_ranges r
       ON     d.id BETWEEN r.min_value AND r.max_value
GROUP BY r.min_value, r.max_value
order by  r.min_value

SQL does not provide a function to find which interval a value belongs to, so it cannot directly group by intervals. It needs to take a detour to create a temporary interval table, and then group and aggregate after association. The structure is quite complex.

SPL code is much simpler and easier to understand:

　A

1

=orcl.query("select * from dummy_data order by id")

2

=list=[10,20,100,1000,2000]

3

=A1.groups@u(list.pseg@r(id):IDs_range; sum(value):sum_of_values)

4

=A3.run(IDs_range=(ifn(list.m(IDs_range),0)+1) / "-" / list.m(IDs_range+1))

A1： Load data.

A3： Directly group and aggregate by interval, the pseg function returns the interval number where a certain value is located, without the need for table creation or association, @r represents left-open, right-closed interval.

A4： Change the group number to the specified string format.

Question source：https://stackoverflow.com/questions/78260383/how-to-group-by-over-a-range-range-values-are-defined-in-range-table


 
 •  32 View  •