聚集函數
說明
聚集函數運算列中的值以執行數學運算，例如加總、平均、計數、最小/最大值、標準差和估計值，以及一些非數學運算。
語法
aggregate_function(input1 [, input2, ...]) FILTER (WHERE boolean_expression)
參數
-
aggregate_function
請參閱 內建聚集函數 文件以取得 Spark 聚集函數的完整清單。
-
boolean_expression
指定任何會評估為布林結果類型的運算式。可以使用邏輯運算子 (AND、OR) 將兩個或多個運算式組合在一起。
範例
請參閱 內建聚集函數 文件以取得 Spark 聚集函數的所有範例。
已排序集合聚集函數
這些聚集函數使用與其他聚集函數不同的語法，以便指定一個運算式 (通常是欄位名稱) 以用來排序值。
語法
{ PERCENTILE_CONT | PERCENTILE_DISC }(percentile) WITHIN GROUP (ORDER BY { order_by_expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ... ] }) FILTER (WHERE boolean_expression)
參數
-
percentile
您要尋找值的百分比。百分比必須是 0.0 到 1.0 之間的常數。
-
order_by_expression
在聚集值之前用來排序值的運算式 (通常是欄位名稱)。
-
boolean_expression
指定任何會評估為布林結果類型的運算式。可以使用邏輯運算子 (AND、OR) 將兩個或多個運算式組合在一起。
範例
CREATE OR REPLACE TEMPORARY VIEW basic_pays AS SELECT * FROM VALUES
('Diane Murphy','Accounting',8435),
('Mary Patterson','Accounting',9998),
('Jeff Firrelli','Accounting',8992),
('William Patterson','Accounting',8870),
('Gerard Bondur','Accounting',11472),
('Anthony Bow','Accounting',6627),
('Leslie Jennings','IT',8113),
('Leslie Thompson','IT',5186),
('Julie Firrelli','Sales',9181),
('Steve Patterson','Sales',9441),
('Foon Yue Tseng','Sales',6660),
('George Vanauf','Sales',10563),
('Loui Bondur','SCM',10449),
('Gerard Hernandez','SCM',6949),
('Pamela Castillo','SCM',11303),
('Larry Bott','SCM',11798),
('Barry Jones','SCM',10586)
AS basic_pays(employee_name, department, salary);
SELECT * FROM basic_pays;
+-----------------+----------+------+
| employee_name|department|salary|
+-----------------+----------+------+
| Anthony Bow|Accounting| 6627|
| Barry Jones| SCM| 10586|
| Diane Murphy|Accounting| 8435|
| Foon Yue Tseng| Sales| 6660|
| George Vanauf| Sales| 10563|
| Gerard Bondur|Accounting| 11472|
| Gerard Hernandez| SCM| 6949|
| Jeff Firrelli|Accounting| 8992|
| Julie Firrelli| Sales| 9181|
| Larry Bott| SCM| 11798|
| Leslie Jennings| IT| 8113|
| Leslie Thompson| IT| 5186|
| Loui Bondur| SCM| 10449|
| Mary Patterson|Accounting| 9998|
| Pamela Castillo| SCM| 11303|
| Steve Patterson| Sales| 9441|
|William Patterson|Accounting| 8870|
+-----------------+----------+------+
SELECT
department,
percentile_cont(0.25) WITHIN GROUP (ORDER BY salary) AS pc1,
percentile_cont(0.25) WITHIN GROUP (ORDER BY salary) FILTER (WHERE employee_name LIKE '%Bo%') AS pc2,
percentile_cont(0.25) WITHIN GROUP (ORDER BY salary DESC) AS pc3,
percentile_cont(0.25) WITHIN GROUP (ORDER BY salary DESC) FILTER (WHERE employee_name LIKE '%Bo%') AS pc4,
percentile_disc(0.25) WITHIN GROUP (ORDER BY salary) AS pd1,
percentile_disc(0.25) WITHIN GROUP (ORDER BY salary) FILTER (WHERE employee_name LIKE '%Bo%') AS pd2,
percentile_disc(0.25) WITHIN GROUP (ORDER BY salary DESC) AS pd3,
percentile_disc(0.25) WITHIN GROUP (ORDER BY salary DESC) FILTER (WHERE employee_name LIKE '%Bo%') AS pd4
FROM basic_pays
GROUP BY department
ORDER BY department;
+----------+-------+--------+-------+--------+-----+-----+-----+-----+
|department| pc1| pc2| pc3| pc4| pd1| pd2| pd3| pd4|
+----------+-------+--------+-------+--------+-----+-----+-----+-----+
|Accounting|8543.75| 7838.25| 9746.5|10260.75| 8435| 6627| 9998|11472|
| IT|5917.75| NULL|7381.25| NULL| 5186| NULL| 8113| NULL|
| Sales|8550.75| NULL| 9721.5| NULL| 6660| NULL|10563| NULL|
| SCM|10449.0|10786.25|11303.0|11460.75|10449|10449|11303|11798|
+----------+-------+--------+-------+--------+-----+-----+-----+-----+