例項詳解構建數倉中的行列轉換

華為雲開發者聯盟發表於2023-11-08

本文分享自華為雲社群《GaussDB資料庫SQL系列-行列轉換》，作者：Gauss松鼠會小助手2。

一、前言

在構建資料倉儲或做資料分析時，需要對原始資料的結構進行一定的處理，有時涉及到“行轉列”，有時涉及到“列轉行”，那麼這兩個轉換的方式具體是什麼，有什麼差異，怎麼實現，今天我們將以GaussDB資料庫為例，給大家做一下講解。

二、簡述

1、行轉列概念

即將多行一列資料轉為一行多列顯示。通常轉化後將某一列分類後的值作為新的列名，將此值對應的多行資料顯示成一行。

cke_130.png

2、列轉行概念

即將一行多列資料轉成多行一列顯示。通常將轉化後的列名為某一行中某一列的值，來識別原先對應的資料。

cke_131.png

三、GaussDB資料庫的行列轉換實驗示例

用一張學生成績來舉例：從老師的角度，在錄入成績時，每科老師都會單獨錄入每個學生的本科成績。而從學生的角度，學生只關心自己各科的成績分別是多少。所以如果把老師錄入資料作為原始表，那麼學生檢視自己的成績時就要用到行轉列，如果讓學生上報自己各科的成績，然後老師去查對應學科的學生考試成績時，那就是列轉行了。

1、行轉列示例

1）建立實驗表（行存表）

--建立實驗表（行存表）

CREATE TABLE grade(

name VARCHAR(10)

,course VARCHAR(10)

,score INT);

--初始化測試資料

INSERT INTO grade VALUES ('張三','數學',80);

INSERT INTO grade VALUES ('張三','英語',88);

INSERT INTO grade VALUES ('張三','語文',95);

INSERT INTO grade VALUES ('李四','數學',88);

INSERT INTO grade VALUES ('李四','英語',70);

INSERT INTO grade VALUES ('李四','語文',93);

--檢視結果

SELECT * FROM grade ORDER BY course;

cke_132.png

2）靜態行轉列

--靜態行轉列

SELECT name

,sum(case when course = '數學' then score else 0 end) AS "數學"

,sum(case when course = '英語' then score else 0 end) AS 英語

,sum(case when course = '語文' then score else 0 end) AS 語文

FROM grade

GROUP BY name;

使用sum、case when的方式：

cke_133.png

3）行轉列（結果值：拼接式）

使用listagg within group：

--行轉列（結果值：拼接式）

SELECT name, LISTAGG(score,',') WITHIN GROUP (ORDER BY course) FROM grade GROUP BY name;

cke_134.png

4）動態行轉列（拼接SQL式）

透過“listagg + 建立FUNCTION + VIEW”的方式實現

--動態行轉列（SQL拼接式）

SELECT listagg(concat('SUM(CASE WHEN course = ''', course, ''' THEN score ELSE 0 END) AS "', course,'"'),',') WITHIN GROUP(ORDER BY 1) AS concat_text FROM (SELECT DISTINCT course FROM grade);

--concat_text的結果：

SUM(CASE WHEN course = '數學' THEN score ELSE 0 END) AS "數學",SUM(CASE WHEN course = '英語' THEN score ELSE 0 END) AS "英語",SUM(CASE WHEN course = '語文' THEN score ELSE 0 END) AS "語文"

--建立一個函式。

CREATE OR REPLACE FUNCTION fun_test()

RETURNS VOID

LANGUAGE SQL

AS $$ DECLARE

s_sql text;

rec record;

BEGIN

s_sql := 'SELECT listagg(CONCAT(''SUM(CASE WHEN course = '''''', course, '''''' THEN score ELSE 0 END) AS "'', course, ''"'' ),'','' ) WITHIN GROUP(ORDER BY 1) AS concat_text FROM (SELECT DISTINCT course FROM grade);';

EXECUTE s_sql INTO rec;

s_sql := 'DROP VIEW IF EXISTS v_score; CREATE VIEW v_score AS SELECT name, ' || rec.concat_text || ' FROM grade GROUP BY name;';

EXECUTE s_sql;

END $$;

--呼叫

CALL fun_test();

--檢視執行結果

select * from v_score;

cke_135.png

Tip：請注意SQL拼寫時的單引號、雙引號。

2、列轉行示例

1）建立實驗表（複用前面的測試資料）

--建立實驗表（複用前面的測試資料）

CREATE TABLE grade1 AS

SELECT name

,sum(case when course = '數學' then score else 0 end) AS "數學"

,sum(case when course = '英語' then score else 0 end) AS 英語

,sum(case when course = '語文' then score else 0 end) AS 語文

FROM grade

GROUP BY name;

--檢視結果
SELECT * FROM grade1;

cke_136.png

2）使用union all，將各科目（數學、英語、語文）整合為一列

--使用union all，將各科目（數學、英語、語文）整合為一列

SELECT * FROM

(

SELECT name, '數學' AS course, 數學 AS score FROM grade1

union all

SELECT name, '英語' AS course, 英語 AS score FROM grade1

union all

SELECT name, '語文' AS course, 語文 AS score FROM grade1

)

order by name;

cke_137.png

四、小結

行列互轉在一些資料庫使用場景中經常用到，比如資料分析、數倉建設等。但不同的資料庫軟體有著不同處理方式，但是行列換的基本思路是一致的。本文主要是以GaussDB資料為平臺，為大家做了簡單的講述 ，歡迎測試。

點選關注，第一時間瞭解華為雲新鮮技術~

相關文章