优化含大量窗口函数的HQL查询性能求助

针对你这张25亿行、334列的大表查询，核心问题集中在重复计算、冗余数据读取和窗口函数开销上，以下是可落地的优化方案：

一、砍断冗余数据读取 #

• 只取必需字段：原查询从334列全量读取，但实际用到的只有c_id, s_id, id, rat, dt, date, time, hour，直接在最内层SELECT指定这些字段，避免全列扫描带来的IO浪费。
• 验证过滤条件有效性：确认dt = "${rundate}"是否触发了分区裁剪（如果表按dt分区），ID <> 0是否能利用索引加速过滤，把数据量在源头就压下来。

二、复用重复计算逻辑 #

原查询大量重复调用窗口函数和时间转换函数，这是最大的性能杀手，必须提前计算复用：

1. 时间转换只算一次：把UNIX_TIMESTAMP(CONCAT(date, " ", REGEXP_REPLACE(time, '\\.\\d+', '')), 'dd/MM/yyyy HH:mm:ss')提前计算成current_unix_time字段，后续所有用到该值的地方直接引用，避免重复执行字符串拼接、正则替换和时间转换。
2. 窗口函数结果复用：把LEAD(hour) OVER (PARTITION BY id ORDER BY time)、LAG(s_id) OVER (PARTITION BY id ORDER BY time)这类重复调用的窗口函数，提前计算成next_hour、prev_s_id等字段，后续条件判断直接用这些字段做运算。

三、简化嵌套CTE层级 #

原查询的三层CTE嵌套存在冗余，合并计算逻辑减少中间数据生成：

• 把hour_overlap_add的计算直接整合到最内层查询，不用单独拆成一层CTE。
• 外层CASE中的条件判断，直接复用内层已经计算好的next_hour - hour（即原difference_hour），不用重复执行窗口函数。

四、优化时间计算逻辑 #

• 替换低效正则：如果time是时间类型，用DATE_FORMAT(time, 'HH:mm:ss')截断小数部分，比REGEXP_REPLACE高效；如果是字符串类型，尽量在ETL阶段预处理好该字段，避免查询时重复正则计算。
• 整点时间计算优化：把UNIX_TIMESTAMP(CONCAT(date, " ", LPAD((hour + 1), 2, 0), ":00:00"), 'dd/MM/yyyy HH:mm:ss')换成UNIX_TIMESTAMP(DATE_ADD(TO_TIMESTAMP(CONCAT(date, ' ', hour, ':00:00')), INTERVAL 1 HOUR))，减少字符串拼接开销。

五、集群资源与存储调优 #

• 调整并行度：根据集群资源，设置合适的mapreduce.job.reduces（Hive）或spark.sql.shuffle.partitions（Spark on Hive）参数，提升并行处理能力。
• 开启谓词下推：确保Hive的hive.optimize.ppd参数开启，让过滤条件尽可能下推到存储层执行。
• 切换列式存储：如果表是行式存储（如TextFile），转换成Parquet或ORC格式，大幅降低IO开销。

优化后的示例代码 #

WITH t1 AS ( 
SELECT 
    *, 
    CASE 
        WHEN next_hour - hour > 1 THEN NULL 
        WHEN next_hour - hour = 1 THEN next_hour_unix - current_unix_time   
        WHEN prev_s_id = s_id AND hour - prev_hour = 1 AND next_hour - hour = 0 
            THEN (next_unix_time - current_unix_time + prev_hour_overlap_add) 
        ELSE LEAD(current_unix_time, 1) OVER (PARTITION BY id, hour ORDER BY `time`) - current_unix_time 
    END AS time_to_next_trans 
FROM( 
    SELECT 
        c_id, 
        s_id, 
        id, 
        rat, 
        dt, 
        `date`, 
        `time`, 
        hour, 
        current_unix_time,
        next_hour,
        prev_hour,
        prev_s_id,
        next_unix_time,
        next_hour_unix,
        (next_unix_time - sec_to_next_hr) as hour_overlap_add,
        LAG((next_unix_time - sec_to_next_hr)) OVER (PARTITION BY id ORDER BY `time`) AS prev_hour_overlap_add
    FROM( 
        SELECT 
            c_id, 
            s_id, 
            id, 
            rat, 
            dt, 
            `date`, 
            `time`, 
            hour, 
            -- 提前计算当前时间的unix值，避免重复计算
            UNIX_TIMESTAMP(CONCAT(`DATE`, " ", REGEXP_REPLACE(`TIME`, '\\.\\d+', '')), 'dd/MM/yyyy HH:mm:ss') AS current_unix_time,
            -- 提前计算窗口函数结果，复用
            LEAD(hour) OVER (PARTITION BY id ORDER BY `time`) AS next_hour,
            LAG(hour) OVER (PARTITION BY id ORDER BY `time`) AS prev_hour,
            LAG(s_id) OVER (PARTITION BY id ORDER BY `time`) AS prev_s_id,
            LEAD(current_unix_time, 1) OVER (PARTITION BY id ORDER BY `time`) AS next_unix_time,
            -- 优化整点时间计算
            UNIX_TIMESTAMP(DATE_ADD(TO_TIMESTAMP(CONCAT(`date`, ' ', hour, ':00:00')), INTERVAL 1 HOUR)) AS next_hour_unix,
            next_hour_unix - current_unix_time AS sec_to_next_hr
        FROM database.tablename
        WHERE dt = "${rundate}" AND ID <> 0 
    )j 
)o 
)

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