如何优化三表关联SQL查询效率？聚合时机等问题咨询

是否应该先聚合再关联？ #

完全应该。原查询先关联三张完整表再聚合，会生成大量中间数据集，大幅增加计算开销。先对每张表按关联键（ISRC）+必要维度聚合，再进行关联，能显著减少中间数据量，直接提升查询速度。

改写后的SQL示例 #

WITH aggregated_streams AS (
    SELECT
        EXTRACT(YEAR FROM stream_date) AS year,
        isrc,
        SUM(youtube.vevo_streams) AS vevo,
        SUM(youtube.shorts_player_streams) AS shorts,
        SUM(youtube.direct_streams) AS direct,
        SUM(youtube.ugc_streams) AS ugc
    FROM `umg-kpi.streams.kpi_streams`
    WHERE stream_date BETWEEN '2020-01-01' AND '2022-12-31'
    GROUP BY year, isrc
),
aggregated_revenue AS (
    SELECT
        isrc,
        SUM(youtube_revenue) AS Revenue
    FROM `umg-kpi.consumption.track_streams`
    WHERE stream_date BETWEEN '2020-01-01' AND '2022-12-31'
    GROUP BY isrc
),
filtered_metadata AS (
    SELECT
        mdm.artist_name,
        mdm.title,
        kpi_label.label_name,
        isrc
    FROM `umg-kpi.metadata.product_isrc`
    WHERE kpi_label.label_name = 'UMLE'
)
SELECT
    as_.year,
    fm.artist_name,
    fm.title,
    fm.label_name,
    as_.isrc,
    as_.vevo,
    as_.shorts,
    as_.direct,
    as_.ugc,
    ar.Revenue
FROM aggregated_streams as_
JOIN filtered_metadata fm ON as_.isrc = fm.isrc
JOIN aggregated_revenue ar ON as_.isrc = ar.isrc
ORDER BY Revenue DESC
LIMIT 200

其他提升效率的方法 #

1. 优化表结构与索引 #

• 分区表验证：确认kpi_streams和track_streams是否按stream_date分区。BigQuery中分区表会自动跳过非目标日期的分区，大幅减少数据扫描量。
• 聚类索引设置：为三张表的isrc字段创建聚类索引，查询时能快速定位特定ISRC的数据，避免全表扫描。
• 嵌套字段精简：对于product_isrc中的嵌套字段（如mdm、kpi_label），只提取需要的子字段，不要读取整个嵌套结构，降低数据传输开销。

2. 过滤逻辑前置 #

显式先过滤product_isrc中符合label_name='UMLE'的ISRC列表，再关联其他表，避免关联不必要的ISRC数据。

3. 排查数据倾斜 #

如果部分ISRC的记录量远高于其他ISRC，会导致聚合时出现数据倾斜，拖慢查询速度。可通过以下SQL排查热点ISRC：

SELECT isrc, COUNT(*) AS record_count
FROM `umg-kpi.streams.kpi_streams`
WHERE stream_date BETWEEN '2020-01-01' AND '2022-12-31'
GROUP BY isrc
ORDER BY record_count DESC
LIMIT 10

若存在热点ISRC，可考虑拆分聚合逻辑，或对该ISRC单独处理。

4. 精简字段选择 #

确保SELECT语句只包含所需字段，不要读取无关字段或使用SELECT *，减少数据处理量。

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