如何在SparkSQL中不使用explode统计指定时间范围的小时频次

样本数据 #

需求与预期输出 #

需要统计上述指定时间范围内每个小时的出现次数，预期输出如下：

当前使用explode函数实现但受集群限制计算耗时过高，需在不使用explode的前提下完成需求。

解决方案 #

通过生成静态小时维度表结合时间范围逻辑计算实现，避免explode带来的数据膨胀：

1. 生成0-23小时维度表 #

创建包含所有小时数的固定小数据集，无性能负担：

WITH hour_dim AS (
    SELECT 0 AS hour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
    SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
    SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
    SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
)

2. 预处理时间范围信息 #

提取每个id的时间跨度关键参数：

, time_range_info AS (
    SELECT 
        id,
        DATEDIFF(end_time, start_time) AS full_days,
        HOUR(start_time) AS start_hour,
        HOUR(end_time) AS end_hour
    FROM your_table
)

3. 关联维度表统计频次 #

通过逻辑判断计算每个小时的覆盖次数：

SELECT 
    t.id,
    h.hour,
    SUM(
        CASE 
            WHEN h.hour >= t.start_hour AND h.hour < t.end_hour THEN t.full_days + 1
            WHEN h.hour < t.start_hour AND h.hour >= t.end_hour THEN t.full_days + 1
            WHEN h.hour >= t.start_hour THEN t.full_days
            WHEN h.hour < t.end_hour THEN t.full_days
            ELSE 0
        END
    ) AS cnt
FROM time_range_info t
CROSS JOIN hour_dim h
GROUP BY t.id, h.hour
ORDER BY t.id, h.hour;

逻辑说明 #

• 完整天数部分：每个完整自然日的所有小时都会被覆盖full_days次
• 起始/结束当天补充：起始日从start_hour到23点、结束日从0点到end_hour-1的小时，额外多算1次
• 跨天场景兼容：当start_hour >= end_hour时，自动识别跨天覆盖逻辑

这种方法仅通过小维度表关联和数值计算完成统计，不会生成大量中间数据，有效降低集群计算压力。

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