Como o GaussDB(DWS) implementa a conversão de linha para coluna e de coluna para linha?

Cenário

Use uma tabela de pontuação do aluno como exemplo:

Os professores registram a pontuação de cada matéria de cada aluno em uma tabela, mas os alunos se importam apenas com suas próprias pontuações. Um aluno precisa usar a conversão de linha em coluna para visualizar suas pontuações de todas as disciplinas. Se o professor de um assunto quer ver as feridas de todos os alunos desse assunto, o professor precisa usar a conversão de coluna para linha.

A figura a seguir mostra a conversão de linha para coluna e de coluna para linha.

Figura 1 Diagrama

• Conversão de linhas para coluna

  Converta várias linhas de dados em uma linha ou converta uma coluna de dados em várias colunas.

• Conversão de coluna para linha

  Converta uma linha de dados em várias linhas ou converta várias colunas de dados em uma coluna.

Exemplo

• Crie uma tabela de armazenamento de linha students_info e insira dados na tabela.

  1 2 3 4 5 6 7 8 9 10

  CREATE TABLE students_info(name varchar(20),subject varchar(100),score bigint) distribute by hash(name); INSERT INTO students_info VALUES('lily','math',95); INSERT INTO students_info VALUES('lily','physics',80); INSERT INTO students_info VALUES('lily','literature',92); INSERT INTO students_info VALUES('matu','math',75); INSERT INTO students_info VALUES('matu','physics',90); INSERT INTO students_info VALUES('matu','literature',85); INSERT INTO students_info VALUES('jack','math',90); INSERT INTO students_info VALUES('jack','physics',95); INSERT INTO students_info VALUES('jack','literature',95);

  Exiba informações sobre a tabela students_info.

  1 2 3 4 5 6 7 8 9 10 11 12

  SELECT * FROM students_info; name | subject | score ------+------------+------- matu | math | 75 matu | physics | 90 matu | literature | 85 lily | math | 95 lily | physics | 80 lily | literature | 92 jack | math | 90 jack | physics | 95 jack | literature | 95

• Crie uma tabela de armazenamento de colunas students_info1 e insira dados na tabela.

  1 2 3 4

  CREATE TABLE students_info1(name varchar(20), math bigint, physics bigint, literature bigint) with (orientation = column) distribute by hash(name); INSERT INTO students_info1 VALUES('lily',95,80,92); INSERT INTO students_info1 VALUES('matu',75,90,85); INSERT INTO students_info1 VALUES('jack',90,95,95);

  Exiba informações sobre a tabela students_info1.

  1 2 3 4 5 6 7

  SELECT * FROM students_info1; name | math | physics | literature ------+------+---------+------------ matu | 75 | 90 | 85 lily | 95 | 80 | 92 jack | 90 | 95 | 95 (3 rows)

Conversão estática de linha para coluna

A conversão estática de linha para coluna exige que você especifique manualmente os nomes das colunas usando os valores fornecidos. Se nenhum valor for dado a uma coluna, o valor padrão 0 será atribuído à coluna.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT name,
sum(case when subject='math' then score else 0 end) as math,
sum(case when subject='physics' then score else 0 end) as physics,
sum(case when subject='literature' then score else 0 end) as literature FROM students_info GROUP BY name;
 name | math | physics | literature
------+------+---------+------------
 matu |   75 |      90 |         85
 lily |   95 |      80 |         92
 jack |   90 |      95 |         95
(3 rows)

Conversão dinâmica de linha para coluna

Para clusters de 8.1.2 ou posterior, você pode usar GROUP_CONCAT para gerar instruções de armazenamento de colunas.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT group_concat(concat('sum(IF(subject = ''', subject, ''', score, 0)) AS "', name, '"'))FROM students_info;
      group_concat

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
 sum(IF(subject = 'literature', score, 0)) AS "jack",sum(IF(subject = 'literature', score, 0)) AS "lily",sum(IF(subject = 'literature', score, 0)) AS "matu",sum(IF(subject = 'math', score, 0)) AS "jack",sum(IF
(subject = 'math', score, 0)) AS "lily",sum(IF(subject = 'math', score, 0)) AS "matu",sum(IF(subject = 'physics', score, 0)) AS "jack",sum(IF(subject = 'physics', score, 0)) AS "lily",sum(IF(subject = 'physics
', score, 0)) AS "matu"
(1 row)

Na 8.1.1 e versões anteriores, você pode usar LISTAGG para gerar instruções de armazenamento de colunas.

1
2
3
4
5
6
7
8

SELECT listagg(concat('sum(case when subject = ''', subject, ''' then score else 0 end) AS "', subject, '"'),',') within GROUP(ORDER BY 1)FROM (select distinct subject from students_info);
                                                                                                      listagg

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
 sum(case when subject = 'literature' then score else 0 end) AS "literature",sum(case when subject = 'physics' then score else 0 end) AS "physics",sum(case when subject = 'math' then score else 0 end) AS "math
"
(1 row)

Reconstrua dinamicamente a visualização:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

CREATE OR REPLACE FUNCTION build_view()
RETURNS VOID
LANGUAGE plpgsql
AS $$ DECLARE
sql text;
rec record;
BEGIN
sql := 'select LISTAGG(
	CONCAT( ''sum(case when subject = '''''', subject, '''''' then score else 0 end) AS "'', subject, ''"'' )
	,'','' ) within group(order by 1) from (select distinct subject from students_info);';
	EXECUTE sql INTO rec;
	sql := 'drop view if exists get_score';
	EXECUTE sql;
	sql := 'create view get_score as select name, ' || rec.LISTAGG || ' from students_info group by name';
	EXECUTE sql;
END$$;

Reconstrua o banco de dados:

1

CALL build_view();

Visualização de consulta:

1
2
3
4
5
6
7

SELECT * FROM get_score;
 name | literature | physics | math
------+------------+---------+------
 matu |         85 |      90 |   75
 lily |         92 |      80 |   95
 jack |         95 |      95 |   90
(3 rows)

Conversão de coluna para linha

Use UNION ALL para mesclar assuntos (matemática, física e literatura) em uma coluna. O seguinte é um exemplo:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT * FROM 
(
SELECT name, 'math' AS subject, math AS score FROM students_info1
union all
SELECT name, 'physics' AS subject, physics AS score FROM students_info1
union all
SELECT name, 'literature' AS subject, literature AS score FROM students_info1
) 
order by name;
 name |  subject   | score
------+------------+-------
 jack | math       |    90
 jack | physics    |    95
 jack | literature |    95
 lily | math       |    95
 lily | physics    |    80
 lily | literature |    92
 matu | math       |    75
 matu | physics    |    90
 matu | literature |    85
(9 rows)