SparkSQL聚合函式

本文介紹 SparkSQL 中的聚合函式文法。

範例原表如下圖所示：

count(*)：統計檢索出的行的個數，包括 NULL 值的行。 

在 SparSQL 中使用語句：select COUNT(*) from DB表輸入-複本，得到統計的檢索行個數，如下圖所示：

注：其中查詢的資料表、欄位需要根據輸入提示手動選擇，不能直接輸入。

count(expr)：傳回指定欄位 expr 的非空值的個數。

在 SparSQL 中使用語句：select COUNT(`貨主地區` ) from DB表輸入-複本，得到統計「貨主地區」欄位非空值的個數，如下圖所示：

count(DISTINCT expr[, expr_.])：傳回欄位 expr 是唯一的且非 NULL 的行的數量。 

在 SparSQL 中使用語句：select count(DISTINCT `貨主名稱` ) FROM DB表輸入，得到統計「貨主地區」欄位非空值且去重的個數，如下圖所示：

範例原表如下圖所示：

sum(col)：對指定列求和（包含重複值）。

在 SparSQL 中使用語句：

select `運貨商`,sum( `應付金額` ) 
from DB表輸入
group by `運貨商` 

得到統計「運貨商」欄位的「應付金額」匯總值，如下圖所示：

注：其中查詢的資料表、欄位需要根據輸入提示手動選擇，不能直接輸入。

sum(DISTINCT col)：對指定列求和（不包含重複值）。

在 SparSQL 中使用語句：

select `運貨商`,sum(DISTINCT `應付金額` ) 
from DB表輸入 
group by `運貨商` 

得到統計「運貨商」欄位去重的「應付金額」匯總值，如下圖所示：

得到以下結果：

範例原表如下圖所示：

avg(col)：對指定列求平均值者（包含重複值）。

在 SparSQL 中使用語句：

select `運貨商`,avg( `應付金額` ) 
from DB表輸入
group by `運貨商` 

得到統計「運貨商」欄位的「應付金額」平均值，如下圖所示：

注：其中查詢的資料表、欄位需要根據輸入提示手動選擇，不能直接輸入。

avg(DISTINCT col)：對指定列元素求平均值者（不包含重複值）。 

在 SparSQL 中使用語句：

select `運貨商`,avg(distinct `應付金額` ) 
from DB表輸入
group by `運貨商` 

得到統計「運貨商」欄位去重的「應付金額」平均值，如下圖所示：

範例原表如下圖所示：

min(col)：傳回指定列的最小值。 

在 SparSQL 中使用語句：select min(`應付金額` ) from DB表輸入，如下圖所示：

注：其中查詢的資料表、欄位需要根據輸入提示手動選擇，不能直接輸入。

範例原表如下圖所示：

max(col)：傳回指定列的最大值。 

在 SparSQL 中使用語句：select max(`應付金額` ) from DB表輸入，如下圖所示：

注：其中查詢的資料表、欄位需要根據輸入提示手動選擇，不能直接輸入。

原資料表如下圖所示：

variance(col)：傳回指定列數值的樣本方差，多用於統計學。

範例：select variance(`單價` ),variance(`進價` )  from DB表輸入，結果如下圖所示：

var_pop(col)：傳回指定列數值的總體方差。

範例：select var_pop(`單價` ),var_pop(`進價` )  from DB表輸入，結果如下圖所示：

var_samp(col)：傳回指定列數值的樣本方差。 

範例：select var_samp(`單價` ),var_samp(`進價` )  from DB表輸入，結果如下圖所示：

原資料表如下圖所示：

stddev_pop(col)：求指定列數值的標準偏差。 

範例：select stddev_pop(`單價` ),stddev_pop(`進價`)  from DB表輸入，結果如下圖所示：

stddev_samp(col)：求指定列數值的樣本標準偏差。

範例：select stddev_samp(`單價` ),stddev_samp(`進價`)  from DB表輸入，結果如下圖所示：

原資料表如下圖所示：

covar_pop(col1, col2)：求指定列數值的總體協方差。 

範例：select covar_pop(`單價` ,`進價`) from DB表輸入，結果如下圖所示：

covar_samp(col1, col2)：求指定列數值的樣本協方差。 

範例：select covar_samp(`單價` ,`進價`) from DB表輸入，結果如下圖所示：

原資料表如下圖所示：

corr(col1, col2)：傳回兩列數值的相關係數。 

範例：select corr(`單價` ,`進價`) from DB表輸入，結果如下圖所示：

原資料表如下圖所示：

percentile(col, p)：傳回 col 的 p%分位數。

col 列為 int 型；p 取值 0~1；percentile(col, 0.5)：取中位數。

範例：select percentile(`單價` ,0.5) from DB表輸入，結果如下圖所示：

主要用於生成陣列

collect_list ( expr))。expr為欄位名

範例：例如想要將 JSON 物件列生成為陣列列，則可以使用 concat_ws 和 collect_list 組合。

原物件列資料如下圖所示（資料表名稱為 JSON生成）：

使用公式：SELECT CONCAT("[",concat_ws(",",collect_list(`JSON_DATA`)),"]") as a from JSON生成，即將 JSON_DATA 欄位生成為陣列形式，如下圖所示：

注：JSON生成表需要手動點選輸入源下的資料表。