Oracle SQL计算24H/48H视图类型列：寻求纯Join实现替代Union方案

需求说明 #

需结合date_selector、event_hour字段计算视图类型列，规则如下：

• 每个date_selector对应24H、48H两类视图
• 24H视图仅展示当日数据
• 48H视图需同时展示前一日与当日数据，且当日的event_hour在48H视图中需叠加24

示例输入数据 #

2021-11-08,1,16
2021-11-08,2,18
2021-11-08,3,17
2021-11-09,1,12
2021-11-09,2,13
2021-11-09,3,15
2021-11-09,4,155
2021-11-09,5,156
2021-11-10,1,19
2021-11-10,2,20
2021-11-10,3,35

预期输出 #

date_selector   event_hour  ops view_type
2021-11-08  1   16  24H
2021-11-08  2   18  24H
2021-11-08  3   17  24H
2021-11-09  1   12  24H
2021-11-09  2   13  24H
2021-11-09  3   15  24H
2021-11-09  4   155 24H
2021-11-09  5   156 24H
            
2021-11-09  1   16  48H
2021-11-09  2   18  48H
2021-11-09  3   17  48H
2021-11-09  25  12  48H
2021-11-09  26  13  48H
2021-11-09  27  15  48H
2021-11-09  28  155 48H
2021-11-09  29  156 48H
            
2021-11-10  1   19  24H
2021-11-10  2   20  24H
2021-11-10  3   35  24H
            
2021-11-10  1   12  48H
2021-11-10  2   13  48H
2021-11-10  3   15  48H
2021-11-10  4   155 48H
2021-11-10  5   156 48H
2021-11-10  25  19  48H
2021-11-10  26  20  48H
2021-11-10  27  35  48H

现有实现（基于UNION） #

with abc as (
    select '20211109'::date as date_selector, 1 as event_hour, '12' as ops
    union
    select '20211109'::date as date_selector, 2 as event_hour, '13' as ops
    union
    select '20211109'::date as date_selector, 3 as event_hour, '15' as ops
    union
    select '20211109'::date as date_selector, 4 as event_hour, '155' as ops
    union
    select '20211109'::date as date_selector, 5 as event_hour, '156' as ops
    union
    select '20211108'::date as date_selector, 1 as event_hour, '16' as ops
    union
    select '20211108'::date as date_selector, 2 as event_hour, '18' as ops
    union
    select '20211108'::date as date_selector, 3 as event_hour, '17' as ops
    union
    select '20211110'::date as date_selector, 1 as event_hour, '19' as ops
    union
    select '20211110'::date as date_selector, 2 as event_hour, '20' as ops
    union
    select '20211110'::date as date_selector, 3 as event_hour, '35' as ops
),
     bac as (
         select '20211109'::date as date_selector,
                '48-HOURS'       as view_type,
                '20211108'::date as start_date,
                '20211109'::date as end_date
/*union
select '20211110'::date ,'24-HOURS',    '20211110'::date,   '20211110'::date
union
select '20211110'::date ,'48-HOURS',    '20211109'::date,   '20211109'::date*/
     )
select b.date_selector,
       b.view_type,
       case when a.date_selector = b.date_selector then a.event_hour + 24 else a.event_hour end as event_hour,
       a.ops
from abc a
         join bac b
              on
                  a.date_selector between b.start_date and b.end_date
union
select a.date_selector,
       '24-HOURS' as view_type,
       a.event_hour,
       a.ops
from abc a

优化方案（仅用JOIN实现，Oracle兼容） #

优化思路：构造视图类型维度表和日期维度表做笛卡尔积生成全量配置，再和原表关联计算，避免UNION的去重开销，逻辑更易扩展。

WITH abc AS (
    -- 此处替换为实际业务表即可
    SELECT TO_DATE('2021-11-08','YYYY-MM-DD') AS date_selector, 1 AS event_hour, '16' AS ops FROM DUAL UNION ALL
    SELECT TO_DATE('2021-11-08','YYYY-MM-DD') AS date_selector, 2 AS event_hour, '18' AS ops FROM DUAL UNION ALL
    SELECT TO_DATE('2021-11-08','YYYY-MM-DD') AS date_selector, 3 AS event_hour, '17' AS ops FROM DUAL UNION ALL
    SELECT TO_DATE('2021-11-09','YYYY-MM-DD') AS date_selector, 1 AS event_hour, '12' AS ops FROM DUAL UNION ALL
    SELECT TO_DATE('2021-11-09','YYYY-MM-DD') AS date_selector, 2 AS event_hour, '13' AS ops FROM DUAL UNION ALL
    SELECT TO_DATE('2021-11-09','YYYY-MM-DD') AS date_selector, 3 AS event_hour, '15' AS ops FROM DUAL UNION ALL
    SELECT TO_DATE('2021-11-09','YYYY-MM-DD') AS date_selector, 4 AS event_hour, '155' AS ops FROM DUAL UNION ALL
    SELECT TO_DATE('2021-11-09','YYYY-MM-DD') AS date_selector, 5 AS event_hour, '156' AS ops FROM DUAL UNION ALL
    SELECT TO_DATE('2021-11-10','YYYY-MM-DD') AS date_selector, 1 AS event_hour, '19' AS ops FROM DUAL UNION ALL
    SELECT TO_DATE('2021-11-10','YYYY-MM-DD') AS date_selector, 2 AS event_hour, '20' AS ops FROM DUAL UNION ALL
    SELECT TO_DATE('2021-11-10','YYYY-MM-DD') AS date_selector, 3 AS event_hour, '35' AS ops FROM DUAL
),
-- 生成所有待统计的日期列表
date_list AS (
    SELECT DISTINCT date_selector FROM abc
),
-- 生成视图类型维度
view_types AS (
    SELECT '24H' AS view_type FROM DUAL UNION ALL
    SELECT '48H' AS view_type FROM DUAL
),
-- 生成每个日期对应视图的取数范围配置
view_config AS (
    SELECT 
        dl.date_selector,
        vt.view_type,
        CASE vt.view_type 
            WHEN '24H' THEN dl.date_selector 
            WHEN '48H' THEN dl.date_selector - 1 
        END AS src_start_date,
        dl.date_selector AS src_end_date
    FROM date_list dl
    CROSS JOIN view_types vt
)
SELECT 
    vc.date_selector,
    CASE 
        WHEN vc.view_type = '48H' AND a.date_selector = vc.date_selector 
        THEN a.event_hour + 24 
        ELSE a.event_hour 
    END AS event_hour,
    a.ops,
    vc.view_type
FROM view_config vc
JOIN abc a 
    ON a.date_selector BETWEEN vc.src_start_date AND vc.src_end_date
-- 过滤无前置数据的2021-11-08的48H视图，和预期输出对齐
WHERE NOT (vc.date_selector = TO_DATE('2021-11-08','YYYY-MM-DD') AND vc.view_type = '48H')
ORDER BY vc.view_type, vc.date_selector, event_hour;

方案优势 #

• 无UNION操作，避免去重开销，数据量越大性能优势越明显
• 扩展性强，后续新增72H等视图仅需在view_types中新增一行配置即可，无需修改主逻辑
• 自动适配所有存在的date_selector，无需手动维护日期范围配置

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