【dbt】使用dbt对表格行转列

【dbt】使用dbt对表格行转列
使用dbt的 Jinja 语法，可以实现动态SQL的效果，拼接出想要的SQL语句。以下是关于行转列的例子，使用了Jinja的变量、遍历语法，以及dbt的run_query()方法和 dbt_utils.get_column_values() 方法。

环境

• 数据库：Postgres 14
• dbt 1.5

生成测试表

1. 创建源表模型 <dbt_project>/models/fct_score.sql。

with source_data as (
    select 1 as id, '张三' as name, 'chinese' as class, 98 as score union all 
    select 2 as id, '张三' as name, 'math'    as class, 97 as score union all 
    select 3 as id, '张三' as name, 'english' as class, 96 as score union all 
    select 4 as id, '李四' as name, 'chinese' as class, 88 as score union all 
    select 5 as id, '李四' as name, 'math'    as class, 87 as score union all 
    select 6 as id, '李四' as name, 'english' as class, 86 as score 
)

select * from source_data

2. 执行 dbt run -s fct_score，生成源表。
   

实现行转列

把 class 字段的值转成列，有三种方法，其实都是用jinja生成需要的代码。
使用sql把class字段的值转成列，我们用常使用以下sql。

select name,
        sum(case when class='chinese' then score else 0 end) as "chinese" ,
        sum(case when class='math'    then score else 0 end) as "math" ,
        sum(case when class='english' then score else 0 end) as "english"
from "fct_score"
group by name
order by name
)

在dbt中，可以利用 Jinja 语法构造出 class 课程的数组，再遍历数组的每个元素，生成 sum(...)代码。但需要注意，最后一个sum()语句末尾，不能带,符号。

一、遍历数组

创建行转列的表模型 ./models/test_r2c_1.sql。

{#
    1. {% set 变量名=变量值 %}: 定义数组变量 class_name_list。
    2. 写出基本的SQL。
    3. {{ for 元素名 in 数组名}}...{% endfor %}: 遍历数组, 用元素值构造出SQL。
    4. {% if not loop.last %}, {% endif %}: 遍历到最后一个元素时，不加逗号。
#}

{% set class_name_list = ['chinese', 'math', 'english'] %}

with source_data as (
    select name, 
        {% for class_name in class_name_list %}
            sum(case when class='{{ class_name }}' then score else 0 end) as "{{ class_name }}" {% if not loop.last %}, {% endif %}
        {% endfor %}
    from {{ ref('fct_score') }}
    group by name 
    order by name 
)

select * from source_data 

运行模型dbt run -s test_r2c_1，生成结果表。

二、使用 run_query() 方法（不推荐）

实际工作时，往往需要转换的行值很多，比如上百个行值，甚至行值是不固定的。可以使用dbt的run_query(()方法，先查询出字段的唯一值，再赋值给变量后进行遍历。

1. 定义变量，保存查询语句。

{% set class_name_query %}
    select distinct class from {{ ref('fct_score') }}
{% endset %}

2. 用 run_query() 方法执行查询。

{% set class_name_result = run_query(class_name_query) %}

3. run_query() 返回结果是对象数组, 需要取对象的第0个元素才是所需要的值。

{% set class_name_list = class_name_result.columns[0].values() %}

4. 在dbt compile阶段, run_query() 会返回 None 值，导致报错, 所以需要把 解析run_query()返回结果 的代码使用 {% if execute %}{% endif %} 包裹, 并赋一个空数组, 以避免报错。

{% if execute %}
    {% set class_name_list = class_name_result.columns[0].values() %}
{% else %}
    {% set class_name_list=[] %}
{% endif %}

5. 遍历数组, 构造SQL，代码同方法一。
6. 完整代码, 保存到 ./models/test_r2c_2.sql 模型中。

{% set class_name_query %}
    select distinct class from {{ ref('fct_score') }}
{% endset %}

{% set class_name_result = run_query(class_name_query) %}

{% if execute %}
    {% set class_name_list = class_name_result.columns[0].values() %}
{% else %}
    {% set class_name_list=[] %}
{% endif %}

with source_data as (
    select name, 
        {% for class_name in class_name_list %}
            sum(case when class='{{ class_name }}' then score else 0 end) as "{{ class_name }}" {% if not loop.last %}, {% endif %}
        {% endfor %}
    from {{ ref('fct_score') }}
    group by name 
    order by name 
)

select * from source_data 

7. 运行模型 dbt run test_r2c_2 ，获得结果如下：
   
   注意：使用 run_query() 方法时，dbt会在本地进行编译后执行，所以如果来源字段还有上家的话，可能会导致字段值变多。

使用 dbt_utils.get_column_values() 方法（推荐）

该方法也是通过查表获得需要转换的值，但比 run_query() 方法准确，推荐使用该方法。

1. get_column_values()方法需要安装 dbt_utils 包，将以下内容 copy 到 <dbt_project>/ 项目目录下的 packages.yml 文件中（没有该文件就新建一个），再执行 dbt deps 即可安装。

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.0

2. 使用 get_column_values(table=ref('表名'), column='字段名') 即可查到指定字段的唯一值，并且以数组形式返回，因此使用很简单。

{% set class_name_list = dbt_utils.get_column_values(table=ref('fct_score'), column='class') %}

3. 构造行转列的语句，同方法一。完整代码保存到 ./models/test_r2c_3.sql 模型中。

{% set class_name_list = dbt_utils.get_column_values(table=ref('fct_score'), column='class') %}

with source_data as (
    select name, 
        {% for class_name in class_name_list %}
            sum(case when class='{{ class_name }}' then score else 0 end) as "{{ class_name }}" {% if not loop.last %}, {% endif %}
        {% endfor %}
    from {{ ref('fct_score') }}
    group by name 
    order by name 
)

select * from source_data 

4. 执行 dbt run -s test_r2c_3 ，得到结果如下：