Oracle 19按时间间隔移位interval列数据至对应位置的SQL方案

已知前提 #

• 源表为外部表load_ext，所有字段均为varchar2类型，目标表与源表结构完全一致
• 表字段清单：
  • 基础字段：customer（客户标识）、interval_type（间隔单位，分钟）、data_count（从起始时间开始的有效间隔数据条数）、Start_time（数据起始时间）
  • 时段字段：interval1~interval24共24个字段，默认interval1对应每日00:00-01:00时段数值，后续字段依次对应每小时时段
• 转换规则：根据Start_time对应的时段移位对齐，例：起始时间为06022022040000AM（对应凌晨4点时段）时，源表interval1的值写入目标表interval4列，后续值依次顺延，无有效数值的时段列填'0'
• 环境限制：仅可使用Oracle 19c原生SQL实现

实现SQL #

逻辑说明：先解析Start_time得到起始时段对应的偏移量，再逐列判断目标时段是否落在有效数据范围内，匹配对应源字段取值，否则填0。

INSERT INTO target_table -- 替换为实际目标表名
SELECT
    customer,
    interval_type,
    data_count,
    Start_time,
    -- 计算interval1取值
    CASE WHEN 1 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (1 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(1 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval1,
    -- 计算interval2取值
    CASE WHEN 2 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (2 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(2 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval2,
    -- 计算interval3取值
    CASE WHEN 3 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (3 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(3 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval3,
    -- 计算interval4取值
    CASE WHEN 4 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (4 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(4 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval4,
    -- 计算interval5取值
    CASE WHEN 5 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (5 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(5 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval5,
    -- 计算interval6取值
    CASE WHEN 6 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (6 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(6 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval6,
    -- 计算interval7取值
    CASE WHEN 7 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (7 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(7 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval7,
    -- 计算interval8取值
    CASE WHEN 8 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (8 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(8 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval8,
    -- 计算interval9取值
    CASE WHEN 9 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (9 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(9 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval9,
    -- 计算interval10取值
    CASE WHEN 10 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (10 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(10 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval10,
    -- 计算interval11取值
    CASE WHEN 11 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (11 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(11 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval11,
    -- 计算interval12取值
    CASE WHEN 12 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (12 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(12 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval12,
    -- 计算interval13取值
    CASE WHEN 13 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (13 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(13 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval13,
    -- 计算interval14取值
    CASE WHEN 14 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (14 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(14 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval14,
    -- 计算interval15取值
    CASE WHEN 15 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (15 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(15 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval15,
    -- 计算interval16取值
    CASE WHEN 16 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (16 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(16 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,interval8,9,interval9,10,interval10,11,interval11,12,interval12,13,interval13,14,interval14,15,interval15,16,interval16,17,interval17,18,interval18,19,interval19,20,interval20,21,interval21,22,interval22,23,interval23,24,interval24)
         ELSE '0' END AS interval16,
    -- 计算interval17取值
    CASE WHEN 17 BETWEEN hour_offset AND hour_offset + TO_NUMBER(data_count) - 1
         AND (17 - hour_offset + 1) BETWEEN 1 AND 24
         THEN DECODE(17 - hour_offset + 1, 1,interval1,2,interval2,3,interval3,4,interval4,5,interval5,6,interval6,7,interval7,8,