使用dbt CTE合并多表性能优化问题（DuckDB）

方案1：用UNION ALL + PIVOT替代连续内连接 #

连续内连接15次会触发多次全表扫描和嵌套连接开销，而UNION ALL + 行转宽的方式更适配DuckDB的优化器特性，能大幅降低计算成本。

实现步骤： #

1. 通过UNION ALL合并所有赛季表，同时保留赛季字段或字段名标识
2. 筛选出在所有15个赛季都存在的fan_id（和原内连接结果逻辑完全一致）
3. 用DuckDB原生PIVOT或CASE WHEN语法转成目标宽表

示例代码（dbt模型）： #

WITH all_seasons AS (
    SELECT fan_id, 'attendance_season_1' AS col_name, attendance_season_1 AS value FROM {{ ref('season_1') }}
    UNION ALL
    SELECT fan_id, 'attendance_season_2' AS col_name, attendance_season_2 AS value FROM {{ ref('season_2') }}
    -- 依次添加剩余13个赛季表，直到season_15
    SELECT fan_id, 'attendance_season_15' AS col_name, attendance_season_15 AS value FROM {{ ref('season_15') }}
),
-- 过滤出所有赛季都存在的fan_id，匹配原内连接结果
valid_fans AS (
    SELECT fan_id
    FROM all_seasons
    GROUP BY fan_id
    HAVING COUNT(DISTINCT col_name) = 15
)
SELECT *
FROM all_seasons
PIVOT (
    MAX(value) FOR col_name IN (
        'attendance_season_1', 'attendance_season_2', 
        'attendance_season_3', 'attendance_season_4',
        -- 补全剩余11个字段名
        'attendance_season_15'
    )
)
WHERE fan_id IN (SELECT fan_id FROM valid_fans);

如果偏好兼容性更强的写法，用CASE WHEN替代PIVOT：

WITH all_seasons AS (
    SELECT fan_id, 1 AS season_num, attendance_season_1 AS attended FROM {{ ref('season_1') }}
    UNION ALL
    SELECT fan_id, 2 AS season_num, attendance_season_2 AS attended FROM {{ ref('season_2') }}
    -- 补全剩余赛季
),
valid_fans AS (
    SELECT fan_id
    FROM all_seasons
    GROUP BY fan_id
    HAVING COUNT(DISTINCT season_num) = 15
)
SELECT
    fan_id,
    MAX(CASE WHEN season_num = 1 THEN attended END) AS attendance_season_1,
    MAX(CASE WHEN season_num = 2 THEN attended END) AS attendance_season_2,
    -- 补全剩余13个字段
    MAX(CASE WHEN season_num = 15 THEN attended END) AS attendance_season_15
FROM all_seasons
WHERE fan_id IN (SELECT fan_id FROM valid_fans)
GROUP BY fan_id;

方案2：使用dbt物化表/物化视图 #

如果赛季数据静态或更新频率低，直接将合并后的宽表预计算为物化表，后续查询直接读取预计算结果，避免重复计算。

实现方式： #

在dbt模型的schema.yml中配置：

models:
  - name: fan_attendance_wide
    config:
      materialized: table  # 静态数据选table性能更高，动态数据可选view

将方案1中的查询作为该模型的SQL内容，每次dbt run生成静态表，后续查询速度可接近单表查询的0.3秒级别。

方案3：优化连接逻辑（保留连接方式但降低开销） #

如果必须使用连接方式，避免连续内连接，先通过INTERSECT获取所有赛季都存在的fan_id，再批量左连接，减少嵌套连接的开销。

示例代码： #

WITH valid_fans AS (
    SELECT fan_id FROM {{ ref('season_1') }}
    INTERSECT SELECT fan_id FROM {{ ref('season_2') }}
    INTERSECT SELECT fan_id FROM {{ ref('season_3') }}
    -- 补全剩余12个赛季的INTERSECT
)
SELECT
    v.fan_id,
    s1.attendance_season_1,
    s2.attendance_season_2,
    -- 补全剩余赛季的字段
    s15.attendance_season_15
FROM valid_fans v
LEFT JOIN {{ ref('season_1') }} s1 ON v.fan_id = s1.fan_id
LEFT JOIN {{ ref('season_2') }} s2 ON v.fan_id = s2.fan_id
-- 补全剩余赛季的左连接
WHERE
    -- 确保所有字段非空（和内连接逻辑一致，因valid_fans是INTERSECT结果，可省略）
    s1.attendance_season_1 IS NOT NULL
    AND s2.attendance_season_2 IS NOT NULL
    -- 补全剩余字段的非空判断

方案4：DuckDB环境层面优化 #

调整DuckDB配置参数，最大化利用硬件资源：

• 开启自动并行：SET threads = auto;（默认已开启，可确认）
• 调整内存限制：SET memory_limit = '16GB';（根据机器内存调整，32GB机器可设为24GB）
• 启用查询缓存：SET enable_query_cache = true;（适合重复查询场景）

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