¿Cómo implementa GaussDB(DWS) las conversiones de fila a columna y de columna a fila?

Escenario

Utilice una tabla de puntuación de un estudiante como ejemplo:

Los maestros registran la puntuación de cada asignatura de cada estudiante en una tabla, pero a los estudiantes solo les importan sus propias puntuaciones. Un estudiante necesita usar la conversión fila a columna para ver sus puntuaciones de todas las asignaturas. Si el profesor de una asignatura quiere ver las llagas de todos los estudiantes de esa asignatura, el profesor debe usar la conversión de columna a fila.

La siguiente figura muestra la conversión de fila a columna y de columna a fila.

Figura 1 Diagrama

• Conversión de filas a columnas

  Convierta varias filas de datos en una fila o convierta una columna de datos en varias columnas.

• Conversión de columna a fila

  Convierta una fila de datos en varias filas o convierta varias columnas de datos en una sola columna.

Ejemplo

• Cree un students_info de tabla de almacén de filas e inserte datos en la tabla.

  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);

  Ver información sobre la tabla 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

• Crear una tabla de almacenamiento de columnas students_info1 e inserte datos en la tabla.

  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);

  Ver la información sobre la tabla 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)

Conversión estática de filas a columnas

La conversión estática de fila a columna requiere que especifique manualmente los nombres de columna con los valores indicados. Si no se da ningún valor a una columna, se asigna el valor predeterminado 0 a la columna.

 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)

Conversión dinámica de filas a columnas

Para clústeres de 8.1.2 o posterior, puede usar GROUP_CONCAT para generar instrucciones de almacén de columnas.

 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)

En 8.1.1 y versiones anteriores, puede usar LISTAGG para generar instrucciones de almacén de columnas.

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)

Reconstruir dinámicamente la vista:

 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$$;

Reconstruir la base de datos:

1

CALL build_view();

Consultar la vista:

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)

Conversión de columna a fila

Use UNION ALL para fusionar materias (matemáticas, física y literatura) en una columna. A continuación se presenta un ejemplo:

 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)