如何简化Trino SQL中多表同模式关联的冗长查询？

针对你因AWS Glue Catalog schema大小限制拆分Delta表后的查询需求，这里提供两种简化方案，同时保留原有的性能优化逻辑：

方案一：基于基准时间戳集合批量关联（推荐） #

这个方案先从任意一个表（比如motor_0）获取最新1000个时间戳，再以此为过滤条件关联所有表，避免重复的子查询逻辑：

WITH top_timestamps AS (
    SELECT timestamp 
    FROM delta.my_delta_db.motor_0
    ORDER BY timestamp DESC LIMIT 1000
)
SELECT 
    m0.*, 
    m1.* EXCLUDE timestamp, 
    m2.* EXCLUDE timestamp,
    m3.* EXCLUDE timestamp, 
    m4.* EXCLUDE timestamp, 
    m5.* EXCLUDE timestamp,
    m6.* EXCLUDE timestamp, 
    m7.* EXCLUDE timestamp
FROM top_timestamps ts
JOIN delta.my_delta_db.motor_0 m0 ON ts.timestamp = m0.timestamp
JOIN delta.my_delta_db.motor_1 m1 ON ts.timestamp = m1.timestamp
JOIN delta.my_delta_db.motor_2 m2 ON ts.timestamp = m2.timestamp
JOIN delta.my_delta_db.motor_3 m3 ON ts.timestamp = m3.timestamp
JOIN delta.my_delta_db.motor_4 m4 ON ts.timestamp = m4.timestamp
JOIN delta.my_delta_db.motor_5 m5 ON ts.timestamp = m5.timestamp
JOIN delta.my_delta_db.motor_6 m6 ON ts.timestamp = m6.timestamp
JOIN delta.my_delta_db.motor_7 m7 ON ts.timestamp = m7.timestamp
ORDER BY ts.timestamp DESC;

优势： #

• 仅需一次获取top1000时间戳的逻辑，大幅减少代码重复
• 每个表仅需过滤出目标时间戳对应的数据，数据处理量更小，关联效率更高
• 用Trino的EXCLUDE语法自动剔除重复的timestamp列，无需手动罗列所有字段（若你的Trino版本不支持EXCLUDE，可手动指定需要的列，比如m1.col10, m1.col11...）

方案二：保留原查询的"多表top1000交集"逻辑 #

如果业务需要严格保留"仅关联所有表各自最新1000条中共同存在的时间戳"的逻辑，可以用INTERSECT先获取时间戳交集，再关联：

WITH all_top_timestamps AS (
    SELECT timestamp FROM delta.my_delta_db.motor_0 ORDER BY timestamp DESC LIMIT 1000
    INTERSECT
    SELECT timestamp FROM delta.my_delta_db.motor_1 ORDER BY timestamp DESC LIMIT 1000
    INTERSECT
    SELECT timestamp FROM delta.my_delta_db.motor_2 ORDER BY timestamp DESC LIMIT 1000
    INTERSECT
    SELECT timestamp FROM delta.my_delta_db.motor_3 ORDER BY timestamp DESC LIMIT 1000
    INTERSECT
    SELECT timestamp FROM delta.my_delta_db.motor_4 ORDER BY timestamp DESC LIMIT 1000
    INTERSECT
    SELECT timestamp FROM delta.my_delta_db.motor_5 ORDER BY timestamp DESC LIMIT 1000
    INTERSECT
    SELECT timestamp FROM delta.my_delta_db.motor_6 ORDER BY timestamp DESC LIMIT 1000
    INTERSECT
    SELECT timestamp FROM delta.my_delta_db.motor_7 ORDER BY timestamp DESC LIMIT 1000
    ORDER BY timestamp DESC LIMIT 1000
)
SELECT 
    m0.*, 
    m1.* EXCLUDE timestamp, 
    m2.* EXCLUDE timestamp,
    m3.* EXCLUDE timestamp, 
    m4.* EXCLUDE timestamp, 
    m5.* EXCLUDE timestamp,
    m6.* EXCLUDE timestamp, 
    m7.* EXCLUDE timestamp
FROM all_top_timestamps ts
JOIN delta.my_delta_db.motor_0 m0 ON ts.timestamp = m0.timestamp
JOIN delta.my_delta_db.motor_1 m1 ON ts.timestamp = m1.timestamp
JOIN delta.my_delta_db.motor_2 m2 ON ts.timestamp = m2.timestamp
JOIN delta.my_delta_db.motor_3 m3 ON ts.timestamp = m3.timestamp
JOIN delta.my_delta_db.motor_4 m4 ON ts.timestamp = m4.timestamp
JOIN delta.my_delta_db.motor_5 m5 ON ts.timestamp = m5.timestamp
JOIN delta.my_delta_db.motor_6 m6 ON ts.timestamp = m6.timestamp
JOIN delta.my_delta_db.motor_7 m7 ON ts.timestamp = m7.timestamp
ORDER BY ts.timestamp DESC;

额外性能优化建议 #

• 确保所有拆分表的timestamp列都创建了索引，这样获取top1000和过滤数据的速度会显著提升
• 若业务允许，优先选择方案一，因为它的执行逻辑更简单，性能损耗更小
• 尽量避免SELECT *，如果能明确列出业务需要的字段，可进一步减少数据传输量

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