"Oracle 数据库某表的 id 字段的范围是整数 1-2000。 id value 1 10 1 11 2 100 3 5 3 9 20 0 21 1000 22 2000 1000 1 1 .."

jinxing
乾学院 53 号会员
1 回帖 • 192 浏览 • 7 个月前

从 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