【Databend】行列转化：数据透视和逆透视

数据准备

学生学科得分等级测试数据如下：

drop table if exists fact_suject_data;
create table if not exists fact_suject_data
(
    student_id    int          null comment '编号',
    subject_level varchar null comment '科目等级',
    subject_level_json variant null comment '科目等级json数据'
);
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (12,'china e,english d,math e','{"china": "e","english": "d","math": "e"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (2,'china b,english b','{"china": "b","english": "b"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (3,'english a,math c','{"english": "a","math": "c"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (4,'china c,math a','{"china": "c","math": "a"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (5,'china d,english a,math c','{"china": "d","english": "a","math": "c"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (6,'china c,english a,math d','{"china": "c","english": "a","math": "d"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (7,'china a,english e,math b','{"china": "a","english": "e","math": "b"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (8,'china d,english e,math e','{"china": "d","english": "e","math": "e"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (9,'china c,english e,math c','{"china": "c","english": "e","math": "c"}');

利用上一篇 【Databend】行列转化：一行变多行和简单分列 文章一行变多行，得到如下效果数据：

select t1.student_id
     , t1.subject_level
     , split_part(unnest(split(t1.subject_level, ',')), ' ', 1) as subject
     , split_part(unnest(split(t1.subject_level, ',')), ' ', 2) as level1
from fact_suject_data as t1
order by t1.student_id;

数据透视

Databend 中的 pivot 功能可以轻松实现数据透视，使用语法如下：

select ...
from ...
   pivot ( <aggregate_function> ( <pivot_column> )
            for <value_column> in ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )
[ ... ]

参数解释如下：

• <aggregate_function>：用于组合来自 <pivot_column> 的分组值的聚合函数。
• <pivot_column>：将使用指定的 <aggregate_function> 聚合的列。
• <value_column>：其唯一值将成为数据透视结果集中的新列。
• <pivot_value_N>：来自<value_column>的唯一值，将成为透视结果集中的新列。

with a as
         (select t1.student_id
               , t1.subject_level
               , split_part(unnest(split(t1.subject_level, ',')), ' ', 1) as subject
               , split_part(unnest(split(t1.subject_level, ',')), ' ', 2) as level1
          from fact_suject_data as t1
          order by t1.student_id)
select *
from a pivot (max(level1) for subject in ('china','math','english'));

数据逆透视

Databend 中 unpivot 功能通过将列转换为行，起到数据逆透视效果。它是一个关系运算符，接受两列（来自表或子查询）以及列列表，并为列表中指定的每列生成一行。使用语法如下：

select ...
from ...
    unpivot ( <value_column>
    for <name_column> in ( <column_list> ) )
[ ... ]

参数解释：

• <value_column>：将存储从<column_list>中列出的列中提取的值的列。
• <name_column>：将存储提取值的列名称的列。
• <column_list>：要旋转的列列表，用逗号分隔。

利用数据透视的结果，使用 unpivot 恢复原样实现数据逆透视。

with a as
         (select t1.student_id
               , t1.subject_level
               , split_part(unnest(split(t1.subject_level, ',')), ' ', 1) as subject
               , split_part(unnest(split(t1.subject_level, ',')), ' ', 2) as level1
          from fact_suject_data as t1
          order by t1.student_id),
     b as
         (select *
          from a pivot (max(level1) for subject in ('china','math','english')) )
select *
from b unpivot (level2 for subject in (`china`,`math`,`english`));

总结

Databend 的 pivot 和 unpivot 功能更好地实现数据的透视和逆透视，并且非常易读和分析大量数据，相较于 Mysql 实现数据透视 (case …when…) 和逆透视 (union all) 来说更简单易读，方法不闲多主要是解决实际问题，学习了解更多方法和工具，在面对问题时也能更好的应对，赶紧实操起来，当遇到也能很自信地说“这题我会”。

参考资料：

• Mysql 行列变换《你想要的都有》：https://blog.csdn.net/weixin_50357986/article/details/134161183
• Databend Query Pivot：https://docs.databend.com/sql/sql-commands/query-syntax/query-pivot
• Databend Query UnPivot：https://docs.databend.com/sql/sql-commands/query-syntax/query-unpivot
• Databend 行列转化：一行变多行和简单分列：https://blog.csdn.net/weixin_50357986/article/details/135568736