clausola PIVOT

Si applica a: segno di spunta sì Databricks SQL Databricks Runtime

Trasforma le righe del table_reference precedente ruotando valori univoci di un elenco di colonne specificato in colonne separate.

Sintassi

PIVOT ( { aggregate_expression [ [ AS ] agg_column_alias ] } [, ...]
    FOR column_list IN ( expression_list ) )

column_list
 { column_name |
   ( column_name [, ...] ) }

expression_list
 { expression [ AS ] [ column_alias ] |
   { ( expression [, ...] ) [ AS ] [ column_alias] } [, ...] ) }

Parametri

aggregate_expression

Espressione di qualsiasi tipo in cui tutti i riferimenti di colonna table_reference sono gli argomenti delle funzioni di aggregazione .
agg_column_alias

Alias facoltativo per il risultato dell'aggregazione. Se non viene specificato alcun alias, PIVOT genera un alias basato su aggregate_expression.
column_list

Il set di colonne da ruotare.
- column_name
  
  Una colonna da table_reference.
expression_list

Mappa i valori da column_list ai alias di colonna.
- expression
  
  Espressione letterale con un tipo che condivide un tipo meno comune con il rispettivo column_name.
  
  Il numero di espressioni in ogni tupla deve corrispondere al numero di column_names in column_list.
- column_alias
  
  Alias facoltativo che specifica il nome della colonna generata. Se non viene specificato PIVOT alcun alias genera un alias basato su expressions.

Risultato

Tabella temporanea del formato seguente:

Tutte le colonne del set di risultati intermedio di table_reference che non sono state specificate in nessun aggregate_expression o column_list.

Queste colonne sono colonne di raggruppamento.
Per ogni tupla expression e combinazione di aggregate_expression, PIVOT genera una colonna. Il tipo è il tipo di aggregate_expression.

Se è presente un solo aggregate_expression la colonna viene denominata usando column_alias. In caso contrario, è denominato column_alias_agg_column_alias.

Il valore in ogni cella è il risultato dell'utilizzo aggregation_expression di un oggetto FILTER ( WHERE column_list IN (expression, ...).

Esempi

-- A very basic PIVOT
-- Given a table with sales by quarter, return a table that returns sales across quarters per year.
> CREATE TEMP VIEW sales(year, quarter, region, sales) AS
   VALUES (2018, 1, 'east', 100),
          (2018, 2, 'east',  20),
          (2018, 3, 'east',  40),
          (2018, 4, 'east',  40),
          (2019, 1, 'east', 120),
          (2019, 2, 'east', 110),
          (2019, 3, 'east',  80),
          (2019, 4, 'east',  60),
          (2018, 1, 'west', 105),
          (2018, 2, 'west',  25),
          (2018, 3, 'west',  45),
          (2018, 4, 'west',  45),
          (2019, 1, 'west', 125),
          (2019, 2, 'west', 115),
          (2019, 3, 'west',  85),
          (2019, 4, 'west',  65);

> SELECT year, region, q1, q2, q3, q4
  FROM sales
  PIVOT (sum(sales) AS sales
    FOR quarter
    IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
 year  region  q1   q2   q3  q4
 2018  east   100   20   40  40
 2019  east   120  110   80  60
 2018  west   105   25   45  45
 2019  west   125  115   85  65

-- The same query written without PIVOT
> SELECT year, region,
         sum(sales) FILTER(WHERE quarter = 1) AS q1,
         sum(sales) FILTER(WHERE quarter = 2) AS q2,
         sum(sales) FILTER(WHERE quarter = 3) AS q2,
         sum(sales) FILTER(WHERE quarter = 4) AS q4
  FROM sales
  GROUP BY year, region;
 year  region  q1   q2   q3  q4
 2018  east   100   20   40  40
 2019  east   120  110   80  60
 2018  west   105   25   45  45
 2019  west   125  115   85  65

-- Also PIVOT on region
> SELECT year, q1_east, q1_west, q2_east, q2_west, q3_east, q3_west, q4_east, q4_west
    FROM sales
    PIVOT (sum(sales) AS sales
      FOR (quarter, region)
      IN ((1, 'east') AS q1_east, (1, 'west') AS q1_west, (2, 'east') AS q2_east, (2, 'west') AS q2_west,
          (3, 'east') AS q3_east, (3, 'west') AS q3_west, (4, 'east') AS q4_east, (4, 'west') AS q4_west));
 year  q1_east  q1_west  q2_east  q2_west  q3_east  q3_west  q4_east  q4_west
 2018      100      105       20       25       40       45       40       45
 2019      120      125      110      115       80       85       60       65

-- The same query written without PIVOT
> SELECT year,
    sum(sales) FILTER(WHERE (quarter, region) IN ((1, 'east'))) AS q1_east,
    sum(sales) FILTER(WHERE (quarter, region) IN ((1, 'west'))) AS q1_west,
    sum(sales) FILTER(WHERE (quarter, region) IN ((2, 'east'))) AS q2_east,
    sum(sales) FILTER(WHERE (quarter, region) IN ((2, 'west'))) AS q2_west,
    sum(sales) FILTER(WHERE (quarter, region) IN ((3, 'east'))) AS q3_east,
    sum(sales) FILTER(WHERE (quarter, region) IN ((3, 'west'))) AS q3_west,
    sum(sales) FILTER(WHERE (quarter, region) IN ((4, 'east'))) AS q4_east,
    sum(sales) FILTER(WHERE (quarter, region) IN ((4, 'west'))) AS q4_west
    FROM sales
    GROUP BY year;
 year  q1_east  q1_west  q2_east  q2_west  q3_east  q3_west  q4_east  q4_west
 2018      100      105       20       25       40       45       40       45
 2019      120      125      110      115       80       85       60       65

-- To aggregate across regions the column must be removed from the input.
> SELECT year, q1, q2, q3, q4
  FROM (SELECT year, quarter, sales FROM sales) AS s
  PIVOT (sum(sales) AS sales
    FOR quarter
    IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
  year   q1   q2   q3   q4
  2018  205   45   85   85
  2019  245  225  165  125

-- The same query without PIVOT
> SELECT year,
    sum(sales) FILTER(WHERE quarter = 1) AS q1,
    sum(sales) FILTER(WHERE quarter = 2) AS q2,
    sum(sales) FILTER(WHERE quarter = 3) AS q3,
    sum(sales) FILTER(WHERE quarter = 4) AS q4
    FROM sales
    GROUP BY year;
  year   q1   q2   q3   q4
  2018  205   45   85   85
  2019  245  225  165  125

-- A PIVOT with multiple aggregations
> SELECT year, q1_total, q1_avg, q2_total, q2_avg, q3_total, q3_avg, q4_total, q4_avg
    FROM (SELECT year, quarter, sales FROM sales) AS s
    PIVOT (sum(sales) AS total, avg(sales) AS avg
      FOR quarter
      IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4));
 year  q1_total  q1_avg  q2_total  q2_avg  q3_total  q3_avg  q4_total  q4_avg
 2018       205  102.5         45   22.5         85   42.5         85   42.5
 2019       245  122.5        225  112.5        165   82.5        125   62.5

-- The same query without PIVOT
> SELECT year,
         sum(sales) FILTER(WHERE quarter = 1) AS q1_total,
         avg(sales) FILTER(WHERE quarter = 1) AS q1_avg,
         sum(sales) FILTER(WHERE quarter = 2) AS q2_total,
         avg(sales) FILTER(WHERE quarter = 2) AS q2_avg,
         sum(sales) FILTER(WHERE quarter = 3) AS q3_total,
         avg(sales) FILTER(WHERE quarter = 3) AS q3_avg,
         sum(sales) FILTER(WHERE quarter = 4) AS q4_total,
         avg(sales) FILTER(WHERE quarter = 4) AS q4_avg
    FROM sales
    GROUP BY year;
 year  q1_total  q1_avg  q2_total  q2_avg  q3_total  q3_avg  q4_total  q4_avg
 2018       205  102.5         45   22.5         85   42.5         85   42.5
 2019       245  122.5        225  112.5        165   82.5        125   62.5

Commenti e suggerimenti

Questa pagina è stata utile?

Last updated on 2026-04-10