如何加速SQL中带QUALIFY的LEFT JOIN操作？十万级大表优化

原SQL在小数据集上运行正常，但面对10万条以上的业务表时，单次LEFT JOIN耗时约30分钟，需要更高效的实现方式。原SQL如下：

with mytable(stock, datetime, price) as (
    select * from values
    (1, '2022-12-13 12:31:45.00'::timestamp, 10.0),
    (1, '2022-12-13 12:31:45.01'::timestamp, 10.1),
    (1, '2022-12-13 12:31:45.02'::timestamp, 10.2),
    (1, '2022-12-13 12:31:46.00'::timestamp, 11.0),  
    (1, '2022-12-13 12:31:46.01'::timestamp, 11.1),  
    (1, '2022-12-13 12:31:46.02'::timestamp, 11.2),  
    (1, '2022-12-13 12:31:46.03'::timestamp, 11.3),
    (1, '2022-12-13 12:31:47.03'::timestamp, 11.3),
    (1, '2022-12-13 12:31:48.00'::timestamp, 11.3)  
)
select t1.*
    ,t2.datetime as next_datetime
    ,t2.price as next_price
    ,t3.datetime as next2_datetime
    ,t3.price as next2_price
    ,t4.datetime as next3_price
    ,t4.price as next3_price
from mytable as t1
left join mytable as t2
    on t1.stock = t2.stock and timediff(second, t1.datetime, t2.datetime) < 1
left join mytable as t3
     on t1.stock = t3.stock and timediff(second, t1.datetime, t3.datetime) < 2
left join mytable as t4
     on t1.stock = t4.stock and timediff(second, t1.datetime, t4.datetime) < 3
qualify row_number() over (partition by t1.stock, t1.datetime order by (t2.datetime, t3.datetime, t4.datetime) desc) = 1
ORDER BY 1,2;

原SQL性能瓶颈分析 #

• 多表关联导致数据爆炸：三次LEFT JOIN会让每条源表记录关联所有符合时间窗口的行，中间结果集体积呈几何级增长，大表场景下会消耗大量CPU和存储资源。
• 无法利用索引：关联条件中的timediff函数属于计算型条件，数据库无法直接使用(stock, datetime)复合索引，导致每次关联都需要全表扫描，性能急剧下降。

优化方案：使用窗口函数替代多表关联 #

利用窗口函数的LAST_VALUE结合时间范围窗口，仅需一次表扫描即可计算出所需的后续时间窗口内的最新数据，彻底避免多表关联的开销。

支持时间范围窗口的数据库（如Snowflake、BigQuery）写法： #

WITH mytable(stock, datetime, price) AS (
    SELECT * FROM VALUES
    (1, '2022-12-13 12:31:45.00'::TIMESTAMP, 10.0),
    (1, '2022-12-13 12:31:45.01'::TIMESTAMP, 10.1),
    (1, '2022-12-13 12:31:45.02'::TIMESTAMP, 10.2),
    (1, '2022-12-13 12:31:46.00'::TIMESTAMP, 11.0),  
    (1, '2022-12-13 12:31:46.01'::TIMESTAMP, 11.1),  
    (1, '2022-12-13 12:31:46.02'::TIMESTAMP, 11.2),  
    (1, '2022-12-13 12:31:46.03'::TIMESTAMP, 11.3),
    (1, '2022-12-13 12:31:47.03'::TIMESTAMP, 11.3),
    (1, '2022-12-13 12:31:48.00'::TIMESTAMP, 11.3)  
),
window_calculations AS (
    SELECT 
        stock,
        datetime,
        price,
        -- 1秒时间窗口内的最新数据
        LAST_VALUE(datetime) OVER (
            PARTITION BY stock 
            ORDER BY datetime 
            RANGE BETWEEN CURRENT ROW AND INTERVAL '1' SECOND FOLLOWING
        ) AS next_datetime,
        LAST_VALUE(price) OVER (
            PARTITION BY stock 
            ORDER BY datetime 
            RANGE BETWEEN CURRENT ROW AND INTERVAL '1' SECOND FOLLOWING
        ) AS next_price,
        -- 2秒时间窗口内的最新数据
        LAST_VALUE(datetime) OVER (
            PARTITION BY stock 
            ORDER BY datetime 
            RANGE BETWEEN CURRENT ROW AND INTERVAL '2' SECOND FOLLOWING
        ) AS next2_datetime,
        LAST_VALUE(price) OVER (
            PARTITION BY stock 
            ORDER BY datetime 
            RANGE BETWEEN CURRENT ROW AND INTERVAL '2' SECOND FOLLOWING
        ) AS next2_price,
        -- 3秒时间窗口内的最新数据
        LAST_VALUE(datetime) OVER (
            PARTITION BY stock 
            ORDER BY datetime 
            RANGE BETWEEN CURRENT ROW AND INTERVAL '3' SECOND FOLLOWING
        ) AS next3_datetime,
        LAST_VALUE(price) OVER (
            PARTITION BY stock 
            ORDER BY datetime 
            RANGE BETWEEN CURRENT ROW AND INTERVAL '3' SECOND FOLLOWING
        ) AS next3_price
    FROM mytable
)
SELECT *
FROM window_calculations
ORDER BY stock, datetime;

不支持时间范围窗口的数据库（如MySQL）写法： #

将时间转换为时间戳数值，基于数值范围定义窗口：

WITH mytable(stock, datetime, price) AS (
    SELECT * FROM (
        VALUES
        (1, '2022-12-13 12:31:45.00', 10.0),
        (1, '2022-12-13 12:31:45.01', 10.1),
        (1, '2022-12-13 12:31:45.02', 10.2),
        (1, '2022-12-13 12:31:46.00', 11.0),  
        (1, '2022-12-13 12:31:46.01', 11.1),  
        (1, '2022-12-13 12:31:46.02', 11.2),  
        (1, '2022-12-13 12:31:46.03', 11.3),
        (1, '2022-12-13 12:31:47.03', 11.3),
        (1, '2022-12-13 12:31:48.00', 11.3)  
    ) AS t(stock, datetime, price)
),
data_with_timestamp AS (
    SELECT 
        *,
        UNIX_TIMESTAMP(datetime) AS ts
    FROM mytable
),
window_calculations AS (
    SELECT 
        stock,
        datetime,
        price,
        -- 1秒窗口（时间戳范围+1）
        LAST_VALUE(datetime) OVER (
            PARTITION BY stock 
            ORDER BY ts 
            RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING
        ) AS next_datetime,
        LAST_VALUE(price) OVER (
            PARTITION BY stock 
            ORDER BY ts 
            RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING
        ) AS next_price,
        -- 2秒窗口（时间戳范围+2）
        LAST_VALUE(datetime) OVER (
            PARTITION BY stock 
            ORDER BY ts 
            RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING
        ) AS next2_datetime,
        LAST_VALUE(price) OVER (
            PARTITION BY stock 
            ORDER BY ts 
            RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING
        ) AS next2_price,
        -- 3秒窗口（时间戳范围+3）
        LAST_VALUE(datetime) OVER (
            PARTITION BY stock 
            ORDER BY ts 
            RANGE BETWEEN CURRENT ROW AND 3 FOLLOWING
        ) AS next3_datetime,
        LAST_VALUE(price) OVER (
            PARTITION BY stock 
            ORDER BY ts 
            RANGE BETWEEN CURRENT ROW AND 3 FOLLOWING
        ) AS next3_price
    FROM data_with_timestamp
)
SELECT stock, datetime, price, next_datetime, next_price, next2_datetime, next2_price, next3_datetime, next3_price
FROM window_calculations
ORDER BY stock, datetime;

额外性能优化建议 #

在业务表上创建复合索引(stock, datetime)，无论是原SQL还是优化后的SQL，都能大幅提升分区、排序和关联操作的效率，进一步缩短查询时间。

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