乾学院 53 号会员
1 回帖  •  175 浏览  • 
     ...

从 SPL 到 SQL：灵活区间汇总

 

Oracle 数据库某表的 id 字段的范围是整数 1-2000。

id value
1 10
1 11
2 100
3 5
3 9
20 0
21 1000
22 2000
1000 1
1000 20

现在要对 id 按多个连续的区间进行分组，对组内的 value 进行求和，计算结果按区间顺序排列。区间是数字序列的形式，可以灵活修改。比如数字序列是 10,20,100,1000,2000 时，表示将 id 按 1-10、11-20、21-100、101-1000、1001-2000 的区间进行分组汇总。注意分组字符串的起始值的格式是：数字序列的成员 +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 没有提供求某个值在哪个区间的函数，不能直接按区间分组，要绕大弯造个临时的区间表，关联后再分组汇总，结构比较复杂。

SPL 代码要简单易懂很多： https://try.esproc.com/splx?3KN


 　A
1 $select * from dummy_data.txt 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：加载数据。

A3：直接按区间分组汇总，函数 pseg 返回某个值所在的区间序号，不需要造表，也不用关联，@r 表示左开右闭。

A4：将分组号改成指定的字符串格式。

问题来源：https://stackoverflow.com/questions/78260383/how-to-group-by-over-a-range-range-values-are-defined-in-range-table