如何在dbt Jinja2宏中传入外部SQL并实现分组循环生成UNION ALL？

步骤1：提取核心SQL逻辑到单独文件 #

创建sql/grouped_base_query.sql文件，仅编写一次分组查询的业务逻辑，用Jinja变量接收动态分组列参数：

select
    {{ group_cols | join(', ') }},
    -- 替换成你实际需要的聚合字段r、s、t
    count(*) as record_count,
    sum(sales) as total_sales,
    avg(price) as avg_price
from table_a
group by {{ group_cols | join(', ') }}

步骤2：编写动态生成UNION ALL的宏 #

在macros/generate_unioned_grouped_data.sql中定义宏，负责遍历分组配置、加载核心SQL并拼接所有结果：

{% macro generate_unioned_grouped_data(group_config) %}
    {% set grouped_queries = [] %}
    {% for group_cols in group_config %}
        {% set single_group_query = load_group_query(group_cols) %}
        {% do grouped_queries.append(single_group_query) %}
    {% endfor %}
    {{ grouped_queries | join(' UNION ALL ') }}
{% endmacro %}

{# 辅助宏：加载核心SQL文件并传入分组列参数 #}
{% macro load_group_query(group_cols) %}
    {% with group_cols=group_cols %}
        {% include 'sql/grouped_base_query.sql' %}
    {% endwith %}
{% endmacro %}

步骤3：在模型中调用宏生成最终表 #

创建models/final_unioned_results.sql，在这里定义你的40个分组配置，并调用宏生成完整SQL：

{% set group_config = [
    ['State'],
    ['Country'],
    ['Year'],
    ['State', 'Year'],
    ['Country', 'ProductType'],
    -- 继续添加剩余的35个分组组合，比如['Region', 'Quarter']等实际列名组合
] %}

{{ generate_unioned_grouped_data(group_config) }}

关键说明 #

• 分组配置完全自定义：支持任意列名组合，无需遵循固定格式，直接用业务实际列名即可
• 逻辑解耦：核心查询逻辑集中在单独SQL文件，宏仅负责动态拼接，后续修改业务逻辑只需改SQL文件
• 自动处理UNION ALL：宏会自动遍历所有分组配置，生成对应查询并拼接，无需手动重复编写UNION ALL语句

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