PIVOT-component
Van toepassing op: Databricks Databricks Runtime
Hiermee worden de rijen van de table_reference getransformeerd door unieke waarden van een opgegeven kolomlijst te roteren in afzonderlijke kolommen.
Syntaxis
table_reference 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] } [, ...] ) }
Parameters
-
Identificeert het onderwerp van de
PIVOT
bewerking. -
Een expressie van elk type waarbij alle kolomverwijzingen
table_reference
argumenten zijn voor het samenvoegen van functies. -
Een optionele alias voor het resultaat van de aggregatie. Als er geen alias is opgegeven,
PIVOT
genereert een alias opaggregate_expression
basis van . column_list
De set kolommen die moeten worden gedraaid.
-
Een kolom van
table_reference
.
-
expression_list
Wijst waarden toe van
column_list
naar kolomaliassen.-
Een letterlijke expressie met een type dat een minst gemeenschappelijk type deelt met de betreffende
column_name
.Het aantal expressies in elke tuple moet overeenkomen met het aantal in
column_names
column_list
. -
Een optionele alias waarmee de naam van de gegenereerde kolom wordt opgegeven. Als er geen alias is opgegeven
PIVOT
, wordt er een alias gegenereerd op basis van deexpression
s.
-
Resultaat
Een tijdelijke tabel van de volgende vorm:
Alle kolommen uit de tussenliggende resultatenset van de
table_reference
die niet zijn opgegeven in ofaggregate_expression
column_list
.Deze kolommen zijn gegroepeerde kolommen.
Voor elke
expression
tuple enaggregate_expression
combinatie wordtPIVOT
één kolom gegenereerd. Het type is het type .aggregate_expression
Als er slechts één
aggregate_expression
is, krijgt de kolom een naam met behulp vancolumn_alias
. Anders heeft het de naamcolumn_alias_agg_column_alias
.De waarde in elke cel is het resultaat van het
aggregation_expression
gebruik van eenFILTER ( WHERE column_list IN (expression, ...)
.
Voorbeelden
-- 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));
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;
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));
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) = (1, 'east')) AS q1_east,
sum(sales) FILTER(WHERE (quarter, region) = (1, 'west')) AS q1_west,
sum(sales) FILTER(WHERE (quarter, region) = (2, 'east')) AS q2_east,
sum(sales) FILTER(WHERE (quarter, region) = (2, 'west')) AS q2_west,
sum(sales) FILTER(WHERE (quarter, region) = (3, 'east')) AS q3_east,
sum(sales) FILTER(WHERE (quarter, region) = (3, 'west')) AS q3_west,
sum(sales) FILTER(WHERE (quarter, region) = (4, 'east')) AS q4_east,
sum(sales) FILTER(WHERE (quarter, region) = (4, 'west')) AS q4_west
FROM sales
GROUP BY year, region;
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));
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;
-- 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));
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;
> CREATE TEMP VIEW person (id, name, age, class, address) AS
VALUES (100, 'John', 30, 1, 'Street 1'),
(200, 'Mary', NULL, 1, 'Street 2'),
(300, 'Mike', 80, 3, 'Street 3'),
(400, 'Dan', 50, 4, 'Street 4');
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