Usage and examples of the PIVOT and UNPIVOT keywords - MaxCompute - Centro de documentación de Alibaba Cloud

MaxCompute admite las palabras clave PIVOT y UNPIVOT. La palabra clave PIVOT permite transformar una o varias filas en columnas mediante una agregación. La palabra clave UNPIVOT permite transformar una o varias columnas en filas. Este tema describe cómo utilizar las palabras clave PIVOT y UNPIVOT, e incluye ejemplos de uso.

Palabra clave PIVOT

La palabra clave PIVOT genera una columna por cada grupo especificado de valores de fila. PIVOT forma parte de la cláusula FROM y se puede utilizar junto con otras palabras clave, como JOIN.

Nota

La palabra clave PIVOT se encuentra en fase de lanzamiento gradual. Es posible que algunos usuarios aún no tengan acceso a esta función.

Formato del comando

SELECT ... 
FROM ... 
PIVOT ( 
    <aggregate function> [AS <alias>] [, <aggregate function> [AS <alias>]] ... 
    FOR (<column> [, <column>] ...) 
    IN ( 
        (<value> [, <value>] ...) AS <new column> 
        [, (<value> [, <value>] ...) AS <new column>] 
        ... 
       ) 
    ) 
[...]

Parámetros:

Parámetro	Obligatorio	Descripción
aggregate function	Sí	Función de agregación. Para obtener más información, consulte Overview of aggregate functions.
alias	No	Alias de la función de agregación. Este alias determina los nombres de las columnas generadas tras la operación PIVOT. Para obtener más información, consulte Limits.
column	Sí	Nombre de la columna en la tabla de origen cuyos valores de fila se desea transformar en columnas.
value	Sí	Valores de fila que se transformarán en columnas.
new column	No	Nombre de la nueva columna resultante de la transformación.

Límites

Funciones de agregación:
- Las funciones de agregación no se pueden anidar dentro de otras funciones.
- Los parámetros de una función de agregación pueden ser una expresión compuesta por funciones escalares y columnas.
- Los parámetros de una función de agregación no pueden contener otras funciones de agregación ni funciones de ventana.
- Las columnas utilizadas en una función de agregación deben provenir de la tabla superior.
El elemento alias debe ser un nombre de columna y no puede ser una expresión.
El elemento value puede ser una expresión. Las columnas de dicha expresión deben provenir de la tabla superior. La expresión puede incluir funciones escalares, pero no funciones de agregación ni funciones de ventana.
Los alias utilizados en una operación PIVOT son importantes porque determinan los nombres de las columnas generadas. Las convenciones de nomenclatura son las siguientes:
- PIVOT (agg1 for axis1 in ('1', '2', '3', ...)):
  
  Si value es una constante en lugar de una expresión, y la función de agregación no tiene alias, los nombres de las columnas generadas corresponden a los propios valores de value, como '1', '2' y '3'.
- PIVOT (agg1 as a for axis1 in ('1', '2', '3', ...)):
  
  Si value es una constante y la función de agregación tiene un alias, los nombres de las columnas generadas siguen el formato value_AggregateFunctionAlias, como '1'_a y '2'_a.
  
  Si ejecuta el comando SET odps.sql.bigquery.compatible=true; para habilitar el modo de compatibilidad con BigQuery, los nombres de las columnas generadas seguirán el formato AggregateFunctionAlias_value, como a_'1' y a_'2'.
- PIVOT (agg1 as a, agg2 as b for axis1 in ('1', '2', '3', ...)):
  
  Si value es una constante y varias funciones de agregación tienen alias, los nombres de las columnas generadas seguirán el formato value_AggregateFunctionAlias, como '1'_a, '2'_a, ..., '1'_b, '2'_b, ....
  
  Si ejecuta el comando SET odps.sql.bigquery.compatible=true; para habilitar el modo de compatibilidad con BigQuery, los nombres de las columnas generadas seguirán el formato AggregateFunctionAlias_value, como a_'1', a_'2', ..., b_'1', b_'2', ....
- PIVOT (agg1 as a, agg2 for axis1 in (expr1, expr2, '3', ...)):
  
  Si un elemento value es una expresión, MaxCompute genera primero alias para las expresiones, como expr1 y expr2, así como para cualquier función de agregación sin alias, como agg2. La instrucción se interpreta como PIVOT (agg1 as a, agg2 as generated_alias1 for axis1 in (expr1 as generated_alias2, expr2 as generated_alias3, '3', ...)). Los nombres de las columnas generadas siguen el formato ValueAlias_AggregateFunctionAlias, como generated_alias2_a, generated_alias3_a, '3'_a, ..., generated_alias2_generated_alias1, generated_alias3_generated_alias1, '3'_generated_alias1, ....
  
  Si ejecuta el comando SET odps.sql.bigquery.compatible=true; para habilitar el modo de compatibilidad con BigQuery, los nombres de las columnas generadas seguirán el formato AggregateFunctionAlias_ValueAlias, como a_generated_alias2, a_generated_alias3, a_'3', ..., generated_alias1_generated_alias2, generated_alias1_generated_alias3, generated_alias1_'3', ....

Notas de uso

La sintaxis PIVOT equivale a una combinación de GROUP BY, una función de agregación y FILTER. Por ejemplo:

SELECT ...
FROM ...
PIVOT (
  agg1 AS a, agg2 AS b, ...
  FOR (axis1, ..., axisN)
  IN (
      (v11, ..., v1N) AS label1,
      (v21, ..., v2N) AS label2, 
      ...)
)

Esto equivale a la siguiente instrucción:

select 
k1, ... kN, 
agg1 AS label1_a filter (where axis1 = v11 and ... and axisN = v1N), 
agg2 AS label1_b filter (where axis1 = v11 and ... and axisN = v1N), 
..., 
agg1 AS label2_a filter (where axis1 = v21 and ... and axisN = v2N),
agg2 AS label2_b filter (where axis1 = v21 and ... and axisN = v2N), 
..., 
from xxxxxx
group by k1, ... kN

En esta instrucción, la tabla en la cláusula FROM es el resultado de la operación PIVOT anterior. k1, ... kN representa el conjunto de todas las columnas que no aparecen en agg1, agg2, ... ni en axis1, ..., axisN.

Ejemplos

Los datos de muestra muestran las ventas de frutas de una empresa por temporada. La instrucción de lenguaje de definición de datos (DDL) para crear la tabla es la siguiente.

-- Create a table
create table mf_cop_sales (tran_id bigint,
                           productID string,
                           tran_amt decimal,
                           season string);
insert into table mf_cop_sales values(1,'apple',100,'Q1'),
                                     (2,'orange',200,'Q1'),
                                     (3,'banana',300,'Q1'),
                                     (4,'apple',400,'Q2'),
                                     (5,'orange',500,'Q2'),
                                     (6,'banana',600,'Q2'),
                                     (7,'apple',700,'Q3'),
                                     (8,'orange',800,'Q3'),
                                     (9,'banana',700,'Q3'),
                                     (10,'apple',500,'Q4'),
                                     (11,'orange',400,'Q4'),
                                     (12,'banana',200,'Q4');
-- The details of the sales table are as follows
select * from mf_cop_sales;
+------------+------------+------------+------------+
| tran_id    | productid  | tran_amt   | season     | 
+------------+------------+------------+------------+
| 1          | apple      | 100        | Q1         | 
| 2          | orange     | 200        | Q1         | 
| 3          | banana     | 300        | Q1         | 
| 4          | apple      | 400        | Q2         | 
| 5          | orange     | 500        | Q2         | 
| 6          | banana     | 600        | Q2         | 
| 7          | apple      | 700        | Q3         | 
| 8          | orange     | 800        | Q3         | 
| 9          | banana     | 700        | Q3         | 
| 10         | apple      | 500        | Q4         | 
| 11         | orange     | 400        | Q4         | 
| 12         | banana     | 200        | Q4         | 
+------------+------------+------------+------------+

Consulte las ventas de cada temporada del año.

SELECT  *
FROM    (
            SELECT  season
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (SUM(tran_amt) FOR season IN ('Q1' AS spring,'Q2' AS summer,'Q3' AS autumn,'Q4' AS winter))
;
-- The following result is returned:
+--------+--------+--------+--------+
| spring | summer | autumn | winter |
+--------+--------+--------+--------+
| 600    | 1500   | 2200   | 1100   |
+--------+--------+--------+--------+

Consulte las ventas de cada producto del año.

SELECT  *
FROM    (
            SELECT  productid
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (SUM(tran_amt)
AS sumbypro FOR productid IN ('apple','orange','banana'))
;
-- The following result is returned:
+------------------+-------------------+-------------------+
| 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

-- Enable BigQuery compatibility mode
SET odps.sql.bigquery.compatible=true;
SELECT  *
FROM    (
            SELECT  productid
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (SUM(tran_amt)
AS sumbypro FOR productid IN ('apple','orange','banana'))
;
-- The following result is returned:
+------------------+-------------------+-------------------+
| sumbypro_'apple' | sumbypro_'orange' | sumbypro_'banana' |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

Consulte el producto con mayores ventas en el cuarto trimestre (Q4).

SELECT  *
FROM    (
            SELECT  season
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (MAX(tran_amt) FOR season IN ('Q4'))
;
-- The following result is returned:
+------+
| 'q4' |
+------+
| 500  |
+------+

Palabra clave UNPIVOT

La palabra clave UNPIVOT transforma columnas en filas. UNPIVOT forma parte de la cláusula FROM y se puede utilizar junto con otras palabras clave, como JOIN.

Formato del comando

SELECT ...
FROM ...
UNPIVOT (
  <new column of value> [, <new column of value>] ...
  FOR (<new column of name> [, <new column of name>] ...)
  IN (
      (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]
      [, (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]]
      ...
    )
)
[...]

Los parámetros se describen a continuación:

Parámetro	Obligatorio	Descripción
new column of value	Sí	Nombre de la nueva columna generada tras la transformación. Los valores de esta columna se obtienen a partir de los valores de las columnas transformadas en filas.
new column of name	Sí	Nombre de la nueva columna generada tras la transformación. Los valores de esta columna se obtienen a partir de los nombres de las columnas transformadas en filas.
column	Sí	Nombres de las columnas que se transformarán en filas. Los nombres de columna se utilizan para llenar new column of name. Los valores de columna se utilizan para llenar new column of value.
column value	No	Alias de las columnas que se transforman en filas.

Límites

Cada nueva columna de valor (new column of value) corresponde a un grupo de columnas especificado para la transformación ((<column1> [, <column2>] ...)). Por lo tanto, la cantidad de elementos new column of value debe coincidir con la cantidad de grupos de columnas. Por ejemplo, new column of value1, ..., new column of valueM corresponde a:
```
(column11, ..., column1N) AS (column value11, ..., column value1N),
(column21, ..., column2N) AS (column value21, ..., column value2N),
...
(columnM1, ..., columnMN) AS (column valueM1, ..., column valueMN)
```
Cada nueva columna de nombre (new column of name) corresponde a un grupo de alias de columna ((<column value1> [, <column value2>] ...)). Por lo tanto, la cantidad de elementos new column of name debe coincidir con la cantidad de grupos de alias. Por ejemplo, new column of name1, ..., new column of nameM corresponde a:
```
(column value11, ..., column value1N), 
(column value21, ..., column value2N), 
...
(column valueM1, ..., column valueMN)
```
Nota
Puede omitir (<column value> [, <column value>] ...). MaxCompute genera automáticamente alias para las columnas especificadas. Si desea personalizar los alias, asegúrese de que su cantidad coincida con la cantidad de columnas.
Las colecciones new column of value y new column of name deben contener únicamente nombres de columna, no expresiones. Además, dichas colecciones no pueden contener nombres duplicados, ya que los elementos de new column of valuenew column of namenew column of value y new column of name se devuelven como columnas.
El parámetro column debe ser un nombre de columna de la tabla superior.
El parámetro column value puede ser una constante o una expresión. Si es una expresión, no puede contener columnas. Esto garantiza que pueda reducirse a una constante mediante plegamiento de constantes.
La cantidad de grupos de columnas (<column1> [, <column2>] ...) no puede superar 100. De lo contrario, podría producirse una expansión excesiva de datos.
Si omite los alias de columna ((<column value> [, <column value>] ...)), MaxCompute genera automáticamente un conjunto de valores de cadena para reemplazarlos. Las reglas son las siguientes:
- Para UNPIVOT (measure1 for axis in (c1, c2, c3, ...)), los valores generados son (c1, c2, c3, ...). La sintaxis original se reescribe como: UNPIVOT (measure1 for axis in (c1 as c1, c2 as c2, c3 as c3, ...)).
- En todos los demás casos donde no se especifiquen alias, MaxCompute genera automáticamente alias para las columnas indicadas.
- Si omite alias para algunas columnas pero los especifica para otras, asegúrese de que los alias indicados sean de tipo STRING. Esto garantiza la compatibilidad con los alias STRING generados automáticamente. Si utiliza un tipo distinto de STRING, debe especificar todos los alias sin omitir ninguno.

Notas de uso

La sintaxis UNPIVOT equivale a una combinación de CROSS JOIN y FILTER (una expresión CASE WHEN). Por ejemplo:

SELECT ...
FROM ...
UNPIVOT (
    (measure1, ..., measureM)
    FOR (axis1, ..., axisN)
    IN ((c11, ..., c1M) AS (value11, ..., value1N),
        (c21, ..., c2M) AS (value21, ..., value2N), ...))
[...]

Esto equivale a la siguiente instrucción:

select 
    k1, ... kN,
    case 
    when axis1 = value11 and ... and axisN = value1N then c11
    when axis1 = value21 and ... and axisN = value2N then c21
    ...
    else null 
    end as measure1,
    ..., 
    case 
    when axis1 = value11 and ... and axisN = value1N then c1M
    when axis1 = value21 and ... and axisN = value2N then c2M
    else null 
    end as measureM, 
    axis1, ..., axisN
    from xxxx 
    join (values (value11, ..., value1N),(value21, ..., value2N), ...) as generated_table_name(axis1, ..., axisN))

Ejemplos

Los datos de muestra muestran las ventas de artículos en diferentes tiendas durante años específicos. La instrucción DDL para crear la tabla es la siguiente.

-- Create a table 
create table mf_shops(item_id bigint, 
                      year string, 
                      shop1 decimal, 
                      shop2 decimal, 
                      shop3 decimal, 
                      shop4 decimal); 
-- Insert data
with shops_table as  
		 (select * from values(1, 2020, 100, 200, 300, 400), 
                          (1, 2021, 100, 200, 200, 100), 
                          (2, 2020, 300, 400, 300, 200), 
                          (2, 2021, 400, 300, 100, 100)
                shops(item_id, year, shop1, shop2, shop3, shop4) 
     ) 
insert overwrite table mf_shops 
select * from shops_table; 
-- Query data
select * from mf_shops;
-- The following result is returned:
+------------+------+-------+-------+-------+-------+
| item_id    | year | shop1 | shop2 | shop3 | shop4 |
+------------+------+-------+-------+-------+-------+
| 1          | 2020 | 100   | 200   | 300   | 400   |
| 1          | 2021 | 100   | 200   | 200   | 100   |
| 2          | 2020 | 300   | 400   | 300   | 200   |
| 2          | 2021 | 400   | 300   | 100   | 100   |
+------------+------+-------+-------+-------+-------+

Combine las cifras de ventas de todas las tiendas y muéstrelas en una nueva columna llamada sales.

-- Merge the sales figures from all stores.
select * from mf_shops
unpivot (sales for shop in (shop1, shop2, shop3, shop4));

-- The following result is returned:
+------------+------------+------------+------+
| item_id    | year       | sales      | shop |
+------------+------------+------------+------+
| 1          | 2020       | 100        | shop1 |
| 1          | 2020       | 200        | shop2 |
| 1          | 2020       | 300        | shop3 |
| 1          | 2020       | 400        | shop4 |
| 1          | 2021       | 100        | shop1 |
| 1          | 2021       | 200        | shop2 |
| 1          | 2021       | 200        | shop3 |
| 1          | 2021       | 100        | shop4 |
| 2          | 2020       | 300        | shop1 |
| 2          | 2020       | 400        | shop2 |
| 2          | 2020       | 300        | shop3 |
| 2          | 2020       | 200        | shop4 |
| 2          | 2021       | 400        | shop1 |
| 2          | 2021       | 300        | shop2 |
| 2          | 2021       | 100        | shop3 |
| 2          | 2021       | 100        | shop4 |
+------------+------------+------------+------+

Puede asignar un alias a cada nombre de tienda. El alias puede ser un valor de la tabla o una cadena.

select * from mf_shops
unpivot (sales for shop in (shop1 as 'shop_name_1', shop2 as 'shop_name_2', shop3 as 'shop_name_3', shop4 as 'shop_name_4'));

-- The following result is returned:
+------------+------------+------------+------+
| item_id    | year       | sales      | shop |
+------------+------------+------------+------+
| 1          | 2020       | 100        | shop_name_1 |
| 1          | 2020       | 200        | shop_name_2 |
| 1          | 2020       | 300        | shop_name_3 |
| 1          | 2020       | 400        | shop_name_4 |
| 1          | 2021       | 100        | shop_name_1 |
| 1          | 2021       | 200        | shop_name_2 |
| 1          | 2021       | 200        | shop_name_3 |
| 1          | 2021       | 100        | shop_name_4 |
| 2          | 2020       | 300        | shop_name_1 |
| 2          | 2020       | 400        | shop_name_2 |
| 2          | 2020       | 300        | shop_name_3 |
| 2          | 2020       | 200        | shop_name_4 |
| 2          | 2021       | 400        | shop_name_1 |
| 2          | 2021       | 300        | shop_name_2 |
| 2          | 2021       | 100        | shop_name_3 |
| 2          | 2021       | 100        | shop_name_4 |
+------------+------------+------------+------+

Suponga que shop1 y shop2 son tiendas de la región este, mientras que shop3 y shop4 son tiendas de la región oeste. La siguiente consulta muestra las ventas de las regiones este y oeste. Las columnas sales1 y sales2 almacenan las cifras de ventas de las dos tiendas de cada región, respectivamente.

select * from mf_shops
unpivot ((sales1, sales2) for shop in ((shop1, shop2) as 'east_shop', (shop3, shop4) as 'west_shop'));

-- The following result is returned:
+------------+------------+------------+------------+------+
| item_id    | year       | sales1     | sales2     | shop |
+------------+------------+------------+------------+------+
| 1          | 2020       | 100        | 200        | east_shop |
| 1          | 2020       | 300        | 400        | west_shop |
| 1          | 2021       | 100        | 200        | east_shop |
| 1          | 2021       | 200        | 100        | west_shop |
| 2          | 2020       | 300        | 400        | east_shop |
| 2          | 2020       | 300        | 200        | west_shop |
| 2          | 2021       | 400        | 300        | east_shop |
| 2          | 2021       | 100        | 100        | west_shop |
+------------+------------+------------+------------+------+

Puede utilizar varias columnas para los alias. La cantidad de columnas de valor también debe aumentarse para que coincida.

select * from mf_shops
unpivot ((sales1, sales2) for (shop_name, location) in ((shop1, shop2) as ('east_shop', 'east'), (shop3, shop4) as ('west_shop', 'west')));

+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+

Puede utilizar EXCLUDE NULLS para filtrar las filas donde sales1 y sales2 son nulas.

with 
shops as (select * from values
          (1, 2020, 100, 200, 300, 400),
          (1, 2021, 100, 200, 200, 100),
          (2, 2020, 300, 400, 300, 200),
          (2, 2021, 400, 300, 100, 100),
          (3, 2020, null, null, null, null) 
          shops(item_id, year, shop1, shop2, shop3, shop4)) 
select * from shops 
unpivot exclude nulls ((sales1, sales2) for (shop_name, location) in ((shop1, shop2) as ('east_shop', 'east'), (shop3, shop4) as ('west_shop', 'west')));

-- The following result is returned:
+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+