Oracle SQL如何为每个客户生成随机起止的连续整数序列

问题根源 #

原代码的核心问题有三点：

1. 未关联客户数据集，仅能处理单个客户场景
2. 子查询T0在笛卡尔积关联时会被重复执行，每次生成不同的INIT和FIN值，导致筛选出的小时序列断裂
3. 随机值生成逻辑未保证终止时间FIN大于等于起始时间INIT，可能出现无效区间

解决思路 #

1. 为每个客户一次性生成唯一且合法的起始/终止小时区间（确保1 ≤ INIT ≤ FIN ≤24）
2. 借助CONNECT BY和分组约束（等价于PARTITION BY窗口逻辑），为每个客户生成对应区间内的连续小时序列

完整SQL实现 #

场景1：已有客户表（假设表名为clients，包含client字段）

WITH client_hour_ranges AS (
    SELECT
        client,
        -- 生成1-24之间的随机起始小时
        FLOOR(DBMS_RANDOM.VALUE(1, 24)) AS init_hour,
        -- 生成从起始小时到24之间的随机终止小时，确保终止时间≥起始时间
        FLOOR(DBMS_RANDOM.VALUE(init_hour, 25)) AS fin_hour
    FROM clients
)
SELECT
    chr.client,
    -- 生成当前客户区间内的连续小时
    chr.init_hour + LEVEL - 1 AS hours
FROM client_hour_ranges chr
CONNECT BY
    -- 控制生成的小时数不超过区间长度
    LEVEL <= chr.fin_hour - chr.init_hour + 1
    -- 保证仅对当前客户生成序列（等价于PARTITION BY client的窗口约束）
    AND PRIOR client = client
    -- 避免CONNECT BY产生重复行的Oracle特有处理
    AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL;

场景2：无客户表，生成测试客户（示例生成10个客户）

WITH clients AS (
    -- 生成1到10的测试客户ID
    SELECT LEVEL AS client
    FROM DUAL
    CONNECT BY LEVEL <= 10
),
client_hour_ranges AS (
    SELECT
        client,
        FLOOR(DBMS_RANDOM.VALUE(1, 24)) AS init_hour,
        FLOOR(DBMS_RANDOM.VALUE(init_hour, 25)) AS fin_hour
    FROM clients
)
SELECT
    chr.client,
    chr.init_hour + LEVEL - 1 AS hours
FROM client_hour_ranges chr
CONNECT BY
    LEVEL <= chr.fin_hour - chr.init_hour + 1
    AND PRIOR client = client
    AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL;

关键说明 #

• client_hour_ranges CTE一次性为每个客户生成唯一的时间区间，避免了原代码中随机值重复生成的问题
• CONNECT BY结合LEVEL生成连续小时，通过PRIOR client = client确保每个客户的序列独立生成，等价于窗口函数OVER (PARTITION BY client)的分组效果
• 使用FLOOR替代ROUND，避免生成25的无效值，同时确保fin_hour范围是init_hour到24，保证区间合法性

内容的提问来源于stack exchange，提问作者Nicolás Rivera