Snowflake日期時間與資料分組聚合函式應用

– 8 分鐘閱讀

Snowflake 提供了豐富的日期與時間函式，方便開發者進行各種日期操作，例如 date_trunc() 可將日期截斷至指定單位，dateadd() 可對日期進行加減運算，date_part() 可提取日期的特定部分，datediff() 可計算日期差異，而 to_date() 和 cast() 則可進行日期格式轉換。此外，Snowflake 也支援多種資料分組與聚合函式，例如 GROUP BY 子句可根據指定欄位分組資料，HAVING 子句則可篩選分組後的結果，搭配 COUNT()、SUM()、AVG()、MIN()、MAX() 等聚合函式，可有效地進行資料彙總與分析。listagg() 函式則可將多行資料合併至單一欄位，方便資料扁平化處理。熟練運用這些函式，能大幅提升資料處理效率，滿足各種資料分析需求。

日期與時間函式的應用

在處理日期與時間資料時，Snowflake 提供了多種函式來滿足不同的需求。這些函式可以幫助我們對日期進行截斷、加減、提取等操作。

日期截斷

date_trunc() 函式可以將日期截斷到指定的單位，如年、月、季度等。以下是一個示例：

SELECT 
    date_trunc('YEAR', dt.val) AS start_of_year,
    date_trunc('MONTH', dt.val) AS start_of_month,
    date_trunc('QUARTER', dt.val) AS start_of_quarter
FROM 
    (VALUES (to_date('26-MAY-2023', 'DD-MON-YYYY'))) AS dt(val);

內容解密：

  • date_trunc('YEAR', dt.val) 將日期截斷到年份的第一天。
  • date_trunc('MONTH', dt.val) 將日期截斷到月份的第一天。
  • date_trunc('QUARTER', dt.val) 將日期截斷到季度的第一天。

日期加減

dateadd() 函式可以在日期上新增或減去指定的時間單位。以下是一個示例：

SELECT 
    dateadd(month, 1, to_date('01-JAN-2024', 'DD-MON-YYYY')) AS date1,
    dateadd(month, 1, to_date('15-JAN-2024', 'DD-MON-YYYY')) AS date2,
    dateadd(month, 1, to_date('31-JAN-2024', 'DD-MON-YYYY')) AS date3;

內容解密：

  • dateadd(month, 1, to_date('01-JAN-2024', 'DD-MON-YYYY')) 在日期上新增一個月。
  • 需要注意的是，當日期是月底時（如1月31日），新增一個月會得到2月的最後一天（2月29日如果是閏年）。

提取日期部分

date_part() 函式可以提取日期中的特定部分，如年、季度、月、周等。以下是一個示例：

SELECT 
    date_part(year, dt.val) AS year_num,
    date_part(quarter, dt.val) AS qtr_num,
    date_part(month, dt.val) AS month_num,
    date_part(week, dt.val) AS week_num
FROM 
    (VALUES (to_date('24-APR-2023', 'DD-MON-YYYY'))) AS dt(val);

內容解密：

  • date_part(year, dt.val) 提取日期的年份。
  • date_part(quarter, dt.val) 提取日期的季度。
  • date_part(month, dt.val) 提取日期的月份。
  • date_part(week, dt.val) 提取日期的周數。

日期差異計算

datediff() 函式可以計算兩個日期之間的差異，單位可以是年、月、日、時等。以下是一個示例：

SELECT 
    datediff(year, dt.val1, dt.val2) AS num_years,
    datediff(month, dt.val1, dt.val2) AS num_months,
    datediff(day, dt.val1, dt.val2) AS num_days,
    datediff(hour, dt.val1, dt.val2) AS num_hours
FROM 
    (VALUES (to_date('12-FEB-2022', 'DD-MON-YYYY'), to_date('06-MAR-2023', 'DD-MON-YYYY'))) AS dt(val1, val2);

內容解密：

  • datediff(year, dt.val1, dt.val2) 計算兩個日期之間的年數差異。
  • datediff(month, dt.val1, dt.val2) 計算兩個日期之間的月數差異。
  • datediff(day, dt.val1, dt.val2) 計算兩個日期之間的天數差異。
  • datediff(hour, dt.val1, dt.val2) 計算兩個日期之間的時數差異。

日期轉換

Snowflake 提供了多種方式來進行日期轉換，包括使用 to_date() 和 cast() 函式。以下是一個示例：

SELECT 
    cast('23-SEP-2023' AS date) AS format1,
    cast('09/23/2023' AS date) AS format2,
    cast('2023-09-23' AS date) AS format3;

內容解密：

  • cast('23-SEP-2023' AS date) 將字串轉換為日期格式。
  • Snowflake 可以自動識別多種日期格式，但如果格式不被識別，則會傳回錯誤。

資料分組與聚合函式在Snowflake中的應用

在資料函式倉管理中，經常需要將資料從最低粒度的層級提升至更高的層級以滿足不同的業務需求。例如，Snowflake的樣本資料函式庫儲存了客戶訂單的詳細資訊，這些資訊對於計費和庫存管理等業務營運至關重要。然而，其他業務部門，如行銷和銷售，可能更關心更高層級的資料，如年度區域銷售額。本章將討論如何在Snowflake中對資料進行分組，以滿足各種業務需求。

分組概念

假設行銷部門的副執行長要求我們協助制定一項特別促銷活動，對特定客戶提供下次訂單25%的折扣。促銷物件是那些累計消費額達到或超過$1,800,000，或是累計下單次數達到或超過8次的客戶。我們的任務是找出符合這些條件的客戶。

樣本資料函式庫中有115,269筆訂單，直接檢視這些資料顯然是不切實際的。因此，我們需要根據客戶ID（o_custkey）對訂單進行分組，然後計算每個客戶的訂單數量和總消費金額。這可以透過GROUP BY子句來實作：

SELECT o_custkey
FROM orders
GROUP BY o_custkey;

內容解密：

  • 這段SQL查詢根據o_custkey欄位對orders表格中的資料進行分組。
  • GROUP BY子句用於將具有相同o_custkey值的列聚合在一起。
  • 查詢結果傳回了66,076行，這代表了Orders表格中不同的custkey值的數量。

接下來，我們需要計算每個客戶的訂單總數和總消費金額。這可以透過使用聚合函式SUM()和COUNT()來實作：

SELECT 
    o_custkey,
    SUM(o_totalprice) AS total_sales,
    COUNT(*) AS number_of_orders
FROM orders
GROUP BY o_custkey;

內容解密：

  • SUM(o_totalprice)計算每個客戶的總消費金額，並將結果命名為total_sales。
  • COUNT(*)計算每個客戶的訂單數量，並將結果命名為number_of_orders。
  • 查詢結果顯示了每個客戶的o_custkey、total_sales和number_of_ordesr。

最後，為了找出頂級客戶，我們需要過濾掉總消費金額少於$1,800,000或訂單數量少於8的客戶。雖然通常使用WHERE子句進行過濾，但這裡需要在資料分組後進行過濾，因此需要使用其他方法。

資料轉換與操作

在進行資料分組之前，瞭解Snowflake中如何進行資料轉換是非常重要的。Snowflake提供了多種函式來進行資料轉換，例如將字串轉換為日期或數字。

SELECT 
    '09/23/2023'::date AS date_val,
    '23-SEP-2023'::timestamp AS tmstmp_val,
    '123.456'::number(6,3) AS num_val;

內容解密：

  • ::運算子用於將字串轉換為指定的資料型別，如日期、時間戳或數字。
  • '09/23/2023'::date將字串轉換為日期型別。
  • '23-SEP-2023'::timestamp將字串轉換為時間戳型別。
  • '123.456'::number(6,3)將字串轉換為數字型別，總共6位數字，其中3位是小數。

練習題

  1. 編寫一個查詢，使用函式將字串 'cow it maked dende' 中的所有 c 替換為 n，並將所有 d 替換為 s。
  2. 編寫一個查詢，傳回數字1到10，每行一個數字。
  3. 編寫一個查詢，傳回日期 ‘01-JAN-2024’ 和 ‘15-AUG-2025’ 之間的天數。
  4. 編寫一個查詢，計算日期 ‘27-SEP-2025’ 的年份、月份和日期的數字總和。

這些練習旨在幫助讀者熟悉Snowflake的內建函式，並提高在實際業務場景中應用這些函式的能力。

在SQL查詢中使用Having子句進行分組資料篩選

在進行資料分析時，我們經常需要對資料進行分組並篩選出符合特定條件的分組結果。SQL語言中的GROUP BY子句用於將資料分組，而HAVING子句則用於對分組後的資料進行篩選。

為什麼需要Having子句？

在SQL查詢中，WHERE子句用於對原始資料進行篩選，但它無法直接對分組後的資料進行篩選。這是因為WHERE子句在分組之前就已經被評估過了。因此，當我們需要根據分組後的資料進行篩選時，就需要使用HAVING子句。

Having子句的基本用法

以下是一個例子，展示瞭如何使用HAVING子句來篩選出總銷售額大於或等於1800000或訂單數量大於或等於8的客戶：

SELECT 
  o_custkey,
  SUM(o_totalprice) AS total_sales,
  COUNT(*) AS number_of_orders
FROM 
  orders
GROUP BY 
  o_custkey
HAVING 
  SUM(o_totalprice) >= 1800000
  OR COUNT(*) >= 8;

這個查詢首先根據o_custkey欄位將資料分組，然後計算每個分組的總銷售額和訂單數量。最後，HAVING子句篩選出總銷售額大於或等於1800000或訂單數量大於或等於8的分組結果。

聚合函式（Aggregate Functions）

聚合函式用於對分組後的資料進行計算，例如計算分組內的資料行數、總和、平均值等。常見的聚合函式包括：

  • COUNT()：計算分組內的資料行數
  • SUM()：計算分組內的某個欄位的總和
  • AVG()：計算分組內的某個欄位的平均值
  • MAX()：計算分組內的某個欄位的最大值
  • MIN()：計算分組內的某個欄位的最小值

COUNT()函式的變體

COUNT()函式有多種變體，例如：

SELECT 
  COUNT(*) AS total_orders,
  COUNT(DISTINCT o_custkey) AS num_customers,
  COUNT(DISTINCT DATE_PART(year, o_orderdate)) AS num_years
FROM 
  orders;

這個查詢計算了訂單表的總行數、不同客戶的數量和不同年份的數量。

另外，COUNT_IF()函式可以用於計算滿足特定條件的資料行數，例如：

SELECT 
  COUNT_IF(1992 = DATE_PART(year, o_orderdate)) AS num_1992,
  COUNT_IF(1995 = DATE_PART(year, o_orderdate)) AS num_1995
FROM 
  orders;

這個查詢計算了1992年和1995年的訂單數量。

MIN()、MAX()、AVG()和SUM()函式

這些函式用於計算分組內的某個欄位的最小值、最大值、平均值和總和。例如：

SELECT 
  DATE_PART(year, o_orderdate) AS order_year,
  COUNT(*) AS num_orders,
  MIN(o_totalprice) AS min_price,
  MAX(o_totalprice) AS max_price,
  AVG(o_totalprice) AS avg_price
FROM 
  orders
GROUP BY 
  DATE_PART(year, o_orderdate);

這個查詢計算了每個年份的訂單數量、最小銷售額、最大銷售額和平均銷售額。

程式碼解密：

  1. DATE_PART(year, o_orderdate)用於提取訂單日期的年份部分。
  2. GROUP BY DATE_PART(year, o_orderdate)根據年份對資料進行分組。
  3. MIN(o_totalprice)、MAX(o_totalprice)和AVG(o_totalprice)分別計算每個年份的最小銷售額、最大銷售額和平均銷售額。

資料分組與聚合函式的高階應用

在資料分析與處理過程中，分組與聚合函式的使用是不可或缺的一環。本篇將探討如何使用SQL中的分組與聚合函式來進行複雜的資料分析。

使用聚合函式進行資料分析

聚合函式如MIN、MAX、AVG、SUM等，可以對資料進行匯總分析。以下是一個範例，展示如何使用這些函式來分析訂單資料：

SELECT 
    date_part(year, o_orderdate) AS year,
    MIN(o_orderdate) AS first_order,
    MAX(o_orderdate) AS last_order,
    AVG(o_totalprice) AS avg_price,
    SUM(o_totalprice) AS tot_sales
FROM 
    orders
GROUP BY 
    date_part(year, o_orderdate);

內容解密：

  1. date_part(year, o_orderdate)：提取訂單日期的年份部分，用於分組。
  2. MIN(o_orderdate) 和 MAX(o_orderdate)：計算每年的第一筆和最後一筆訂單日期。
  3. AVG(o_totalprice) 和 SUM(o_totalprice)：計算每年的平均訂單總價和總銷售額。

使用listagg()函式進行資料扁平化

當需要將多行資料合併成一個欄位時，listagg()函式非常有用。以下範例展示如何使用listagg()來列出每個地區的國家：

SELECT 
    r.r_name,
    listagg(n.n_name, ',')
    WITHIN GROUP (ORDER BY n.n_name) AS nation_list
FROM 
    region r 
INNER JOIN 
    nation n ON r.r_regionkey = n.n_regionkey
GROUP BY 
    r.r_name;

內容解密：

  1. listagg(n.n_name, ',')：將每個地區的國家名稱合併成一個逗號分隔的列表。
  2. WITHIN GROUP (ORDER BY n.n_name)：確保國家列表按照名稱排序。

多欄位分組

除了單一欄位分組外，SQL還允許對多個欄位進行分組。以下範例展示如何計算每個國家和市場細分的客戶數量：

SELECT 
    n.n_name, 
    c.c_mktsegment, 
    COUNT(*)
FROM 
    customer c 
INNER JOIN 
    nation n ON c.c_nationkey = n.n_nationkey
WHERE 
    n.n_regionkey = 1
GROUP BY 
    n.n_name, 
    c.c_mktsegment
ORDER BY 
    1, 2;

內容解密：

  1. GROUP BY n.n_name, c.c_mktsegment：同時按照國家名稱和市場細分進行分組。
  2. COUNT(*)：計算每個分組內的客戶數量。

使用表示式進行分組

除了直接使用欄位名稱外，還可以使用表示式來進行分組。以下範例展示如何計算1997年和1998年之間的訂單處理時間：

SELECT 
    date_part(year, o.o_orderdate) AS year,
    datediff(month, o.o_orderdate, l.l_shipdate) AS months_to_ship,
    COUNT(*)
FROM 
    orders o 
INNER JOIN 
    lineitem l ON o.o_orderkey = l.l_orderkey
WHERE 
    o.o_orderdate >= '01-JAN-1997'::date
GROUP BY 
    date_part(year, o.o_orderdate),
    datediff(month, o.o_orderdate, l.l_shipdate)
ORDER BY 
    1, 2;

內容解密：

  1. date_part(year, o.o_orderdate)：提取訂單日期的年份。
  2. datediff(month, o.o_orderdate, l.l_shipdate)：計算訂單日期和發貨日期之間的月份差異。
  3. COUNT(*)：計算每個分組內的訂單數量。
玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。