如何在Vertica中实现类似PostgreSQL generate_series的区间数字序列生成？

给定如下输入表：

输入表 #

需要将每个id对应的low_esn到high_esn区间展开为连续的数字行，得到如下结果：

期望输出 #

以下是两种在Vertica中实现的方法：

方法1：递归CTE（最直观） #

利用递归公共表表达式（CTE）生成连续序列，逻辑和PostgreSQL的generate_series类似：

WITH RECURSIVE series AS (
    -- 初始行：取每个id的low_esn作为起始值
    SELECT 
        id,
        low_esn AS value,
        high_esn
    FROM your_table_name
    UNION ALL
    -- 递归递增，直到value达到high_esn
    SELECT 
        id,
        value + 1,
        high_esn
    FROM series
    WHERE value < high_esn
)
-- 最终输出并排序
SELECT id, value
FROM series
ORDER BY id, value;

方法2：使用SEQUENCE函数+交叉连接 #

如果数据量较大且区间长度相对均匀，这种方法效率可能更高：

WITH max_range AS (
    -- 计算所有区间中最大的长度，用于生成足够的偏移序列
    SELECT MAX(high_esn - low_esn + 1) AS max_len FROM your_table_name
),
num_series AS (
    -- 生成从0到max_len-1的偏移值序列
    SELECT SEQUENCE(0, max_len - 1) AS offset FROM max_range
)
-- 交叉连接原表和偏移序列，计算每个id对应的连续值
SELECT 
    t.id,
    t.low_esn + ns.offset AS value
FROM your_table_name t
CROSS JOIN num_series ns
-- 过滤掉超过当前id的high_esn的数值
WHERE t.low_esn + ns.offset <= t.high_esn
ORDER BY t.id, value;

注意：将上述代码中的your_table_name替换为实际的表名即可。

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