عبارة PIVOT
ينطبق على:
التحقق من Databricks SQL Databricks Runtime
تحويل صفوف table_reference عن طريق تدوير القيم الفريدة لقائمة أعمدة محددة إلى أعمدة منفصلة.
بناء الجمله
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] } [, ...] ) }
معلمات
-
يحدد موضوع
PIVOTالعملية. -
تعبير من أي نوع حيث تكون جميع مراجع الأعمدة
table_referenceوسيطات لتجميع الدالات. -
اسم مستعار اختياري لنتيجة التجميع. إذا لم يتم تحديد اسم مستعار،
PIVOTينشئ اسما مستعارا استناداaggregate_expressionإلى . column_list
مجموعة الأعمدة المراد تدويرها.
-
عمود من
table_reference.
-
expression_list
تعيين القيم من
column_listإلى الأسماء المستعارة للعمود.-
تعبير حرفي مع نوع يشارك نوع أقل شيوعا مع المعني
column_name.يجب أن يتطابق عدد التعبيرات في كل مجموعة مع عدد
column_namesفيcolumn_list. -
اسم مستعار اختياري يحدد اسم العمود الذي تم إنشاؤه. إذا لم يتم
PIVOTتحديد اسم مستعار، ينشئ اسما مستعارا استناداexpressionإلى s.
-
نتيجه
جدول مؤقت للنموذج التالي:
كافة الأعمدة من مجموعة النتائج الوسيطة من
table_referenceالتي لم يتم تحديدها في أيaggregate_expressionأوcolumn_list.هذه الأعمدة هي أعمدة تجميع.
expressionلكل مجموعة ومجموعةaggregate_expression،PIVOTتنشئ عمودا واحدا. النوع هو نوعaggregate_expression.إذا كان هناك عمود واحد
aggregate_expressionفقط يسمى باستخدامcolumn_alias. وإلا، فإنه يسمىcolumn_alias_agg_column_alias.القيمة في كل خلية هي نتيجة
aggregation_expressionاستخدامFILTER ( WHERE column_list IN (expression, ...).
امثله
-- 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