中級聚合函式

在 Snowflake 進行資料管線自動化

Emily Melhuish

Technical Curriculum Developer, Snowflake

運算順序

運算順序.png

SQL 並不是照你撰寫的順序執行

列：建立資料集
群組：彙總資料
結果：整理輸出

¹ * Snowflake 教學教材

常見聚合函式

COUNT(*) / COUNT(DISTINCT col) — 總列數；唯一值數
SUM — 欄位總和
AVG — 平均值
MIN / MAX — 範圍

SELECT COUNT(*) AS total_shipments,
       COUNT(DISTINCT carrier_id) AS unique_carriers,
       SUM(delivery_days) AS total_days,
       AVG(delivery_days) AS avg_days
FROM shipments;

多個查詢的問題

依區域與倉庫回傳使用量

SELECT 
region
, warehouse_name
, SUM(credits_used) 
FROM usage 
GROUP BY region, warehouse_name;

回傳各區域小計

SELECT region
, SUM(credits_used) 
FROM usage GROUP BY region;

回傳總使用量

SELECT 
SUM(credits_used) 
FROM usage;

GROUPING SETS

輸入

SELECT region
, warehouse_name
, SUM(credits_used) AS total 
FROM usage 
GROUP BY GROUPING SETS ( 
  (region, warehouse_name)
  , (region)
  , () 
);

輸出

REGION	WAREHOUSE_NAME	TOTAL
EMEA	harbr_wh_prod	1240.50
EMEA	harbr_wh_dev	380.00
EMEA	NULL	1620.50
APAC	harbr_wh_prod	890.25
APAC	NULL	890.25
NULL	NULL	2510.75

注意： 透過 CASE 敘述為 NULL 加上意義

ROLLUP

輸入

SELECT 
region
, warehouse_name
, SUM(credits_used) AS total 
FROM usage 
GROUP BY ROLLUP (region, warehouse_name);

輸出

REGION	WAREHOUSE_NAME	TOTAL
EMEA	harbr_wh_prod	1240.50
EMEA	harbr_wh_dev	380.00
EMEA	NULL	1620.50
APAC	harbr_wh_prod	890.25
APAC	NULL	890.25
NULL	NULL	2510.75

CUBE

輸入

SELECT 
region
, warehouse_name
, SUM(credits_used) AS total
FROM usage 
GROUP BY CUBE (region, warehouse_name);

輸出

REGION	WAREHOUSE_NAME	TOTAL
EMEA	harbr_wh_prod	1240.50
EMEA	harbr_wh_dev	380.00
EMEA	NULL	1620.50
APAC	harbr_wh_prod	890.25
APAC	NULL	890.25	}
NULL	harbr_wh_prod	2130.75
NULL	harbr_wh_dev	380.00
NULL	NULL	2510.75

GROUPING() 與 GROUPING_ID()

SELECT region, warehouse_name, SUM(credits_used) AS total_credits, GROUPING(region) AS is_region_agg, GROUPING(warehouse_name) AS is_wh_agg , 
GROUPING_ID(region, warehouse_name) AS grouping_level 
FROM usage 
GROUP BY ROLLUP (region, warehouse_name);

Markdown 表格輸出畫面擷圖

一起來練習吧！

在 Snowflake 進行資料管線自動化