【最佳实践】Doris 中实现行列转换

行列转换在做报表分析时还是经常会遇到的，今天就说一下如何实现行列转换吧。

行列转换就是如下图所示两种展示形式的互相转换行转列我们来看一个简单的例子，我们要把下面第一个表格的数据转换成下边第二个表格的样式

先看看建表语句：

CREATE TABLE tb_score(
    id INT(11) NOT NULL,
    userid VARCHAR(20) NOT NULL COMMENT '用户id',
    subject VARCHAR(20) COMMENT '科目',
    score DOUBLE COMMENT '成绩'
)
unique KEY(`id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false"
);

INSERT INTO tb_score  VALUES (1,'001','语文',90);
INSERT INTO tb_score  VALUES (2,'001','数学',92);
INSERT INTO tb_score  VALUES (3,'001','英语',80);
INSERT INTO tb_score  VALUES (4,'002','语文',88);
INSERT INTO tb_score  VALUES (5,'002','数学',90);
INSERT INTO tb_score  VALUES (6,'002','英语',75.5);
INSERT INTO tb_score  VALUES (7,'003','语文',70);
INSERT INTO tb_score  VALUES (8,'003','数学',85);
INSERT INTO tb_score  VALUES (9,'003','英语',90);
INSERT INTO tb_score  VALUES (10,'003','政治',82);

我们来看看传统的做法是怎么处理：

SELECT userid,
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
FROM tb_score 
GROUP BY userid;

或者

SELECT userid,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='政治',score,0)) as '政治' 
FROM tb_score 
GROUP BY userid;

上面可以看到我们通过大量的case when，然后外层还要在使用 sum 聚合函数，操作起来比较复杂，语句也比较繁琐我们来看看 Doris 怎么实现这个行转列呢，有没有更简单、性能更好的一种方式我们是不是可以首先按照用户分组将科目、成绩使用 doris 提供的 map_agg 函数组成一个 Map,然后在外层对这个 Map 进行遍历展开，从而完成这样一个行列转换。

我们来看看实现：

select 
    userid,
    IFNULL(map['语文'],0) as '语文',
    IFNULL(map['英语'],0) as '英语',
    IFNULL(map['数学'],0) as '数学',
    IFNULL(map['政治'],0) as '政治'
from  (
    select userid ,map_agg(subject,score) as map from tb_score group by userid
) t ;

这样看起来SQL 逻辑就清晰很多，而且在性能上也会比之前 case when 方式好的很多，下面来看看这个执行结果：

mysql> select
-> userid,
-> IFNULL(map['语文'],0) as '语文',
-> IFNULL(map['英语'],0) as '英语',
-> IFNULL(map['数学'],0) as '数学',
-> IFNULL(map['政治'],0) as '政治'
-> from (
-> select userid ,map_agg(subject,score) as map from tb_score group by userid
-> ) t ;
+--------+--------+--------+--------+--------+
| userid | 语文 | 英语 | 数学 | 政治 |
+--------+--------+--------+--------+--------+
| 001 | 90 | 80 | 92 | 0 |
| 002 | 88 | 75.5 | 90 | 0 |
| 003 | 70 | 90 | 85 | 82 |
+--------+--------+--------+--------+--------+
3 rows in set (0.02 sec)

列转行实际使用中，我们还有很多场景要把数据冲列转成行，下面我们来看一个例子，这个例子中每行是一个学生的，语文、数学、英语、政治的成绩。我们想转换成每门成绩都是独立的一行，将第一个表格转换成第二个的表格样式：

我们来看看一个宽表转成高表我们之前的是怎么实现，一般我们是通过 union all 的方式，每科我们都是一个单独的 SQL 语句，然后将这些 SQL Unoin all 在一起，得到我们想要的结果。

SELECT userid,'语文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,'数学' AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT userid,'英语' AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
ORDER BY userid;

这样做的缺点：SQL 冗余大量的 union all 也会带来性能问题。

我们来看看 Doris 怎么实现，首先 Doris 提供了 Lateral view，其实就是用来和像类似 explode 这种UDTF函数联用的，lateral view会将 UDTF 生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行 join 来达到连接 UDTF 外的 select 字段的目的还是以上面的例子来看，Doris 怎么对这个宽表转成高表，实现就是借助 Lateral view。首先我借助 Lateral view 形成一个 UserID、客户成绩组成一个字符（使用逗号连接）,达到下面的效果：

+--------+--------------------+
| userid | arr |
+--------+--------------------+
| 001 | ["语文", "90"] |
| 001 | ["数学", "92"] |
| 001 | ["英语", "80"] |
| 001 | ["政治", "0"] |
| 002 | ["语文", "88"] |
| 002 | ["数学", "90"] |
| 002 | ["英语", "75.5"] |
| 002 | ["政治", "0"] |
| 003 | ["语文", "70"] |
| 003 | ["数学", "85"] |
| 003 | ["英语", "90"] |
| 003 | ["政治", "82"] |
+--------+--------------------+
12 rows in set (0.02 sec)

然后对这个上面的 arr 字符串，借助于 Doris 提供的 SPLIT_BY_STRING 函数来完成字符串转数组的动作，最后遍历数组完成列转行的效果。

SELECT
userid,
element_at ( arr, 1 ) AS SUBJECT,
element_at ( arr, 2 ) AS score
FROM
(
SELECT
userid,
SPLIT_BY_STRING ( sub, ',' ) arr
FROM
(
SELECT
userid,
array (
concat( '语文', ',', cn_score ),
concat( '数学', ',', math_score ),
concat( '英语', ',', en_score ),
concat( '政治', ',', po_score )) AS scores
FROM
tb_score1
) t LATERAL VIEW explode ( scores ) tbl1 AS sub
) aaa

最后的效果如下：

mysql> select userid,element_at(arr, 1) as subject,element_at(arr, 1) as score from (
-> select userid,SPLIT_BY_STRING(sub ,',') arr from ( select userid, array(concat('语文',',',cn_score),concat('数学',',',math_score),concat('英语',',',en_score),concat('政治',',',po_score)) as scores from tb_score1 ) t LATERAL VIEW explode(scores) tbl1 AS sub
-> ) aaa
-> ;
+--------+---------+--------+
| userid | subject | score |
+--------+---------+--------+
| 001 | 语文 | 语文 |
| 001 | 数学 | 数学 |
| 001 | 英语 | 英语 |
| 001 | 政治 | 政治 |
| 002 | 语文 | 语文 |
| 002 | 数学 | 数学 |
| 002 | 英语 | 英语 |
| 002 | 政治 | 政治 |
| 003 | 语文 | 语文 |
| 003 | 数学 | 数学 |
| 003 | 英语 | 英语 |
| 003 | 政治 | 政治 |
+--------+---------+--------+
12 rows in set (0.03 sec)