Apache Doris 行列轉換可以這樣玩

行列轉換在做報表分析時還是經(jīng)常會遇到的，今天就說一下如何實現(xiàn)行列轉換吧。

行列轉換就是如下圖所示兩種展示形式的互相轉換

1. 行轉列

我們來看一個簡單的例子，我們要把下面這個表的數(shù)據(jù)，轉換成圖二的樣式

image-20230914151818953.png

要轉換的結果數(shù)據(jù)展示

image-20230914152642915.png

先看看建表語句：

CREATE TABLE tb_score_01(
 id INT(11) NOT NULL,
 userid VARCHAR(20) NOT NULL COMMENT '用戶id',
 subject VARCHAR(20) COMMENT '科目',
 score DOUBLE COMMENT '成績'
)
DUPLICATE 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_01  VALUES (1,'001','語文',90);
INSERT INTO tb_score_01  VALUES (2,'001','數(shù)學',92);
INSERT INTO tb_score_01  VALUES (3,'001','英語',80);
INSERT INTO tb_score_01  VALUES (4,'002','語文',88);
INSERT INTO tb_score_01  VALUES (5,'002','數(shù)學',90);
INSERT INTO tb_score_01  VALUES (6,'002','英語',75.5);
INSERT INTO tb_score_01  VALUES (7,'003','語文',70);
INSERT INTO tb_score_01  VALUES (8,'003','數(shù)學',85);
INSERT INTO tb_score_01  VALUES (9,'003','英語',90);
INSERT INTO tb_score_01  VALUES (10,'003','政治',82);

傳統(tǒng)的做法我們大概是這樣實現(xiàn)，一般是通過 case when 語句

SELECT userid,
SUM(CASE `subject` WHEN '語文' THEN score ELSE 0 END) as '語文',
SUM(CASE `subject` WHEN '數(shù)學' THEN score ELSE 0 END) as '數(shù)學',
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`='數(shù)學',score,0)) as '數(shù)學',
SUM(IF(`subject`='英語',score,0)) as '英語',
SUM(IF(`subject`='政治',score,0)) as '政治' 
FROM tb_score 
GROUP BY userid;

我們來看看 Doris 怎么實現(xiàn)這個行轉列呢，有沒有更簡單、性能更好的一種方式

  1. 我們是不是可以首先將這個科目、成績組成一個Map

  2. 然后在外層對這個 Map 進行遍歷展開

  3. 從而完成這樣一個行列轉換呢

我們來看看實現(xiàn)

select 
 userid,
 IFNULL(map['語文'],0) as '語文',
 IFNULL(map['英語'],0) as '英語',
 IFNULL(map['數(shù)學'],0) as '數(shù)學',
 IFNULL(map['政治'],0) as '政治'
from  (
 select userid ,map_agg(subject,score) as map from tb_score group by userid
) t ;

這樣實現(xiàn)上性能更好,我們來看一下效果

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

2. 列轉行

實際使用中我們還有很多場景要把數(shù)據(jù)沖列轉成行，下面我們來看一個例子，這個例子中每行是一個學生的，語文、數(shù)學、英語、政治的成績，

image-20230914152642915.png

我們想轉換成每門成績都是獨立的一行，轉出的效果如下：

image-20230914152846996.png

我們來看看一個寬表轉成高表我們之前的是怎么實現(xiàn)，一般我們是通過union all的方式，每科我們都是一個單獨的SQL語句，然后將這些SQL Unoin all 在一起得到我們想要的結果。

SELECT userid,'語文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,'數(shù)學' 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;

這樣做的缺點：

  1. SQL 冗余

  2. 大量的union all 也會帶來性能問題

我們來看看 Doris 怎么實現(xiàn)，首先 Doris 提供了 Lateral view，其實就是用來和像類似explode這種UDTF函數(shù)聯(lián)用的，lateral view會將 UDTF 生成的結果放到一個虛擬表中，然后這個虛擬表會和輸入行進行 join來達到連接 UDTF 外的 select 字段的目的

還是以上面的例子來看，Doris我怎么對這個寬表轉成高表，實現(xiàn)就是借助Lateral view

CREATE TABLE `tb_score1` (
 `id` int(11) NOT NULL,
 `userid` varchar(20) NOT NULL COMMENT '用戶id',
 `cn_score` double NULL COMMENT '語文成績',
 `math_score` double NULL COMMENT '數(shù)學成績',
 `en_score` double NULL COMMENT '英語成績',
 `po_score` double NULL COMMENT '政治成績'
) ENGINE=OLAP
UNIQUE KEY(`id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);;

INSERT INTO `tb_score1` (`id`, `userid`, `cn_score`, `math_score`, `en_score`, `po_score`) VALUES (1, '001', 90, 92, 80, 0);
INSERT INTO `tb_score1` (`id`, `userid`, `cn_score`, `math_score`, `en_score`, `po_score`) VALUES (2, '002', 88, 90, 75.5, 0);
INSERT INTO `tb_score1` (`id`, `userid`, `cn_score`, `math_score`, `en_score`, `po_score`) VALUES (3, '003', 70, 85, 90, 82);
  1. 首先我借助Lateral view 形成一個 UserID、客戶成績組成一個字符（使用逗號連接）,達到下面的效果
+--------+--------------------+
| userid | arr                |
+--------+--------------------+
| 001    | ["語文", "90"]     |
| 001    | ["數(shù)學", "92"]     |
| 001    | ["英語", "80"]     |
| 001    | ["政治", "0"]      |
| 002    | ["語文", "88"]     |
| 002    | ["數(shù)學", "90"]     |
| 002    | ["英語", "75.5"]   |
| 002    | ["政治", "0"]      |
| 003    | ["語文", "70"]     |
| 003    | ["數(shù)學", "85"]     |
| 003    | ["英語", "90"]     |
| 003    | ["政治", "82"]     |
+--------+--------------------+
12 rows in set (0.02 sec)

  1. 然后對這個上面的 arr 字符串，借助于 Doris 提供的 SPLIT_BY_STRING 函數(shù)完成字符串轉數(shù)組的動作

  2. 最后遍歷數(shù)組

  3. 完成列轉行的效果

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( '數(shù)學', ',', math_score ),
 concat( '英語', ',', en_score ),
 concat( '政治', ',', po_score )) AS scores 
 FROM
 tb_score1 
 ) t LATERAL VIEW explode ( scores ) tbl1 AS sub 
 ) aaa

最后的效果如下：

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( '數(shù)學', ',', 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    | 語文    | 90    |
| 001    | 數(shù)學    | 92    |
| 001    | 英語    | 80    |
| 001    | 政治    | 0     |
| 002    | 語文    | 88    |
| 002    | 數(shù)學    | 90    |
| 002    | 英語    | 75.5  |
| 002    | 政治    | 0     |
| 003    | 語文    | 70    |
| 003    | 數(shù)學    | 85    |
| 003    | 英語    | 90    |
| 003    | 政治    | 82    |
+--------+---------+-------+
12 rows in set (0.02 sec)
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

友情鏈接更多精彩內容