如何基于范围表实现ID分组并计算对应值的总和？

现有DUMMY_DATA表数据如下：

SELECT * FROM DUMMY_DATA;

id    value
------------
1     10
1     11
2     100
3     5
3     9
...
1000  1
1000  20

需要将id按指定范围分组，计算每组对应value的总和，预期结果格式如下：

IDs_range                   sum_of_values
-----------------------------------------
0-10   (up to 10)         xxxx
11-20  (up to 20)         yyyy
...
1001 - 2000 (up to 2000)  zzz

当分组范围数量较少时，可通过CASE表达式实现，示例代码如下：

SELECT CASE 
         WHEN age <= 10 THEN '1-10' 
         WHEN age <= 20 THEN '11-20' 
         ELSE '21+' 
       END AS age_group, 
       COUNT(*) AS n
FROM age
GROUP BY CASE 
           WHEN age <= 10 THEN '1-10' 
           WHEN age <= 20 THEN '11-20' 
           ELSE '21+' 
         END

但当前需处理上百个范围，CASE表达式已不适用。目前已掌握范围表的创建方式：

WITH IDS_RANGES AS (
SELECT COLUMN_VALUE AS ID_RANGE FROM TABLE(SYS.DBMS_DEBUG_VC2COLL(10,20,....,1000,2000))
)
SELECT ...

现需实现关联IDS_RANGES与DUMMY_DATA表，得到预期结果的查询语句。

1. 完善范围表，生成上下限与显示文本 #

原范围表仅包含每个分组的上限值，需通过窗口函数生成每个范围的起始、结束值，以及符合预期格式的范围名称：

WITH IDS_RANGES AS (
    -- 替换为你的实际上限值列表
    SELECT COLUMN_VALUE AS upper_bound 
    FROM TABLE(SYS.DBMS_DEBUG_VC2COLL(10,20,30,...,1000,2000))
),
RANGE_BOUNDS AS (
    SELECT 
        -- 计算当前范围起始值：前一个上限+1，第一个范围起始为0
        NVL(LAG(upper_bound) OVER (ORDER BY upper_bound), -1) + 1 AS lower_bound,
        upper_bound,
        -- 生成预期的范围显示文本
        CASE 
            WHEN NVL(LAG(upper_bound) OVER (ORDER BY upper_bound), -1) = -1 
                THEN '0-' || upper_bound || '   (up to ' || upper_bound || ')'
            ELSE (NVL(LAG(upper_bound) OVER (ORDER BY upper_bound), -1) + 1) || '-' || upper_bound || '  (up to ' || upper_bound || ')'
        END AS IDs_range
    FROM IDS_RANGES
)
SELECT * FROM RANGE_BOUNDS;

2. 关联原表计算分组总和 #

使用LEFT JOIN确保所有范围都被显示（即使该范围无数据，总和显示为0），最终查询语句如下：

WITH IDS_RANGES AS (
    SELECT COLUMN_VALUE AS upper_bound 
    FROM TABLE(SYS.DBMS_DEBUG_VC2COLL(10,20,30,...,1000,2000))
),
RANGE_BOUNDS AS (
    SELECT 
        NVL(LAG(upper_bound) OVER (ORDER BY upper_bound), -1) + 1 AS lower_bound,
        upper_bound,
        CASE 
            WHEN NVL(LAG(upper_bound) OVER (ORDER BY upper_bound), -1) = -1 
                THEN '0-' || upper_bound || '   (up to ' || upper_bound || ')'
            ELSE (NVL(LAG(upper_bound) OVER (ORDER BY upper_bound), -1) + 1) || '-' || upper_bound || '  (up to ' || upper_bound || ')'
        END AS IDs_range
    FROM IDS_RANGES
)
SELECT 
    rb.IDs_range,
    NVL(SUM(dd.value), 0) AS sum_of_values
FROM RANGE_BOUNDS rb
LEFT JOIN DUMMY_DATA dd 
    ON dd.id BETWEEN rb.lower_bound AND rb.upper_bound
GROUP BY rb.IDs_range, rb.lower_bound, rb.upper_bound
ORDER BY rb.lower_bound;

关键说明 #

• LAG()窗口函数用于获取前一个范围的上限值，以此计算当前范围的起始值，保证范围连续无重叠。
• LEFT JOIN确保所有预定义范围都出现在结果中，避免遗漏无数据的范围。
• NVL()函数将无数据范围的总和转为0，符合统计需求。

内容的提问来源于stack exchange，提问作者Vic VKh