在電商或零售領域，分析季度銷售額是掌握營運狀況的關鍵。利用 SQL 視窗函式，我們可以更有效率地比較不同時間段的業績，找出表現最佳和最差的季度。SQL 視窗函式可以在結果集中，針對特定資料列的周圍資料進行計算，而不需要改變原有的資料結構。這在處理時間序列資料，例如銷售額分析時，特別有用。舉例來說，要計算每個季度的銷售額佔全年銷售額的百分比，傳統 SQL 需要子查詢或複雜的 JOIN 操作。但使用視窗函式，我們可以在單一查詢中完成這個計算，大幅提升查詢效率。這篇文章將會以實際案例，逐步說明如何使用 first_value()、last_value() 和 nth_value() 等視窗函式來分析季度銷售額。
假設我們有一個名為 orders 的資料表，包含訂單日期 o_orderdate 和總價 o_totalprice。我們想分析 1995 年到 1997 年間的季度銷售額資料，並找出每個年度中表現最佳和最差的季度。首先，我們可以使用 date_part() 函式提取年份和季度資訊，並用 SUM() 函式計算每個季度的總銷售額。接著，利用 first_value() 和 last_value() 函式，搭配 ORDER BY 子句，可以找出每個年度中銷售額最高和最低的季度。為了計算每個季度銷售額佔全年銷售額的百分比，我們可以結合 partition by 子句，將計算範圍限定在每個年度內。如此一來，就能更精準地比較每個季度在當年度的表現。
除了比較季度銷售額，我們還可以利用 LAG() 和 LEAD() 函式來分析銷售額的變化趨勢。LAG() 函式可以取得前一季度的銷售額，而 LEAD() 函式則可以取得下一季度的銷售額。透過這兩個函式，我們可以輕鬆計算季度銷售額的增長率或下降率，進一步瞭解銷售額的波動情況。此外，QUALIFY 子句可以根據視窗函式的結果進行篩選，例如找出銷售額排名前三的季度，簡化查詢的複雜度。最後，listagg() 函式可以將多個值聚合成單一字串，例如將每個區域的所有國家名稱合併成一個逗號分隔的字串，方便報表呈現。
總而言之，SQL 視窗函式提供了一套強大的工具，可以讓我們更有效率地分析季度銷售額資料，並從中挖掘出有價值的商業洞見。透過靈活運用這些函式，我們可以更深入地瞭解銷售額的變化趨勢，並制定更有效的銷售策略。
如何運用SQL視窗函式找出最佳與最差季度銷售額
在資料分析中，我們經常需要比較不同時間段的業績，找出表現最佳和最差的時間點。今天，玄貓將分享如何使用 SQL 視窗函式 first_value()、last_value() 和 nth_value()，輕鬆找出各季度的銷售額，並計算與最佳和最差季度的百分比差異。
範例資料與目標
假設我們有一張名為 orders 的資料表，其中包含訂單日期 o_orderdate 和總價 o_totalprice。我們的目標是：
- 計算 1995 年至 1997 年間，每個季度的總銷售額。
- 找出每個季度的銷售額與當年最佳和最差季度銷售額的百分比差異。
找出各季度總銷售額
首先，我們可以使用以下 SQL 語法計算各季度總銷售額：
select
date_part(year, o_orderdate) as year,
date_part(quarter, o_orderdate) as qrter,
sum(o_totalprice) as tot_sales
from orders
where date_part(year, o_orderdate) between 1995 and 1997
group by date_part(year, o_orderdate), date_part(quarter, o_orderdate)
order by 1,2;
程式碼解密：
date_part(year, o_orderdate): 提取訂單日期的年份。date_part(quarter, o_orderdate): 提取訂單日期的季度。sum(o_totalprice): 計算每個季度的總銷售額。group by date_part(year, o_orderdate), date_part(quarter, o_orderdate): 按照年份和季度分組。order by 1,2: 按照年份和季度排序。
執行以上程式碼後，我們會得到一份包含年份、季度和總銷售額的報表。
使用 first_value() 和 last_value() 找出最佳和最差銷售額
接下來，我們可以使用 first_value() 和 last_value() 視窗函式，找出每個季度的銷售額與總體最佳和最差季度銷售額的百分比差異：
select
date_part(year, o_orderdate) as year,
date_part(quarter, o_orderdate) as qrter,
sum(o_totalprice) as tot_sales,
first_value(sum(o_totalprice)) over (order by sum(o_totalprice) desc) as top_sales,
last_value(sum(o_totalprice)) over (order by sum(o_totalprice) desc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as btm_sales
from orders
where date_part(year, o_orderdate) between 1995 and 1997
group by date_part(year, o_orderdate), date_part(quarter, o_orderdate)
order by 1,2;
程式碼解密：
first_value(sum(o_totalprice)) over (order by sum(o_totalprice) desc): 找出所有季度中的最高銷售額。last_value(sum(o_totalprice)) over (order by sum(o_totalprice) desc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING): 找出所有季度中的最低銷售額。RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：這個子句是為了確保last_value()能夠正確地抓取到最後一筆資料，因為預設情況下，它可能會受到視窗框架的影響。
計算與最佳和最差銷售額的百分比差異
現在，我們已經取得了總體最佳和最差銷售額，可以計算每個季度的銷售額與它們的百分比差異：
select
date_part(year, o_orderdate) as year,
date_part(quarter, o_orderdate) as qrter,
sum(o_totalprice) as tot_sales,
round(sum(o_totalprice) / first_value(sum(o_totalprice)) over (order by sum(o_totalprice) desc) * 100, 1) as pct_top_sales,
round(sum(o_totalprice) / last_value(sum(o_totalprice)) over (order by sum(o_totalprice) desc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) * 100, 1) as pct_btm_sales
from orders
where date_part(year, o_orderdate) between 1995 and 1997
group by date_part(year, o_orderdate), date_part(quarter, o_orderdate)
order by 1,2;
程式碼解密：
round(sum(o_totalprice) / first_value(sum(o_totalprice)) over (order by sum(o_totalprice) desc) * 100, 1): 計算每個季度的銷售額與總體最佳銷售額的百分比。round(sum(o_totalprice) / last_value(sum(o_totalprice)) over (order by sum(o_totalprice) desc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) * 100, 1): 計算每個季度的銷售額與總體最差銷售額的百分比。
找出與當年最佳和最差銷售額的百分比差異
如果我們想要比較每個季度的銷售額與當年最佳和最差季度的百分比差異，只需在 first_value() 和 last_value() 函式中加入 partition by 子句：
select
date_part(year, o_orderdate) as year,
date_part(quarter, o_orderdate) as qrter,
sum(o_totalprice) as tot_sales,
round(sum(o_totalprice) / first_value(sum(o_totalprice)) over (partition by date_part(year, o_orderdate) order by sum(o_totalprice) desc) * 100, 1) as pct_top_sales,
round(sum(o_totalprice) / last_value(sum(o_totalprice)) over (partition by date_part(year, o_orderdate) order by sum(o_totalprice) desc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) * 100, 1) as pct_btm_sales
from orders
where date_part(year, o_orderdate) between 1995 and 1997
group by date_part(year, o_orderdate), date_part(quarter, o_orderdate)
order by 1,2;
程式碼解密：
partition by date_part(year, o_orderdate): 按照年份劃分視窗，使得first_value()和last_value()函式只在同一年份的季度中尋找最佳和最差銷售額。
使用 nth_value() 找出第二佳季度
除了 first_value() 和 last_value()，我們還可以使用 nth_value() 函式找出排名第 N 的數值。例如，以下程式碼找出每個年份中，銷售額第二高的季度：
select
date_part(year, o_orderdate) as year,
date_part(quarter, o_orderdate) as qrter,
sum(o_totalprice),
first_value(date_part(quarter, o_orderdate)) over (partition by date_part(year, o_orderdate) order by sum(o_totalprice) desc) as best_qtr,
nth_value(date_part(quarter, o_orderdate),2) over (partition by date_part(year, o_orderdate) order by sum(o_totalprice) desc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as next_best_qtr
from orders
where date_part(year, o_orderdate) between 1995 and 1997
group by date_part(year, o_orderdate), date_part(quarter, o_orderdate)
order by 1,2;
程式碼解密：
nth_value(date_part(quarter, o_orderdate),2) over (partition by date_part(year, o_orderdate) order by sum(o_totalprice) desc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING): 找出每個年份中，銷售額排名第二的季度。
探索 nth_value() 函式：尋找特定排名的數值
nth_value() 函式提供了一個額外的引數，讓開發者可以指定要查詢的數值排名 “N”。以下是如何使用 nth_value() 函式來找出每個年度最佳季度和次差季度的範例：
SELECT
date_part(year, o_orderdate) AS year,
date_part(quarter, o_orderdate) AS qrter,
SUM(o_totalprice),
FIRST_VALUE(date_part(quarter, o_orderdate)) OVER (PARTITION BY date_part(year, o_orderdate) ORDER BY SUM(o_totalprice) DESC) AS best_qtr,
NTH_VALUE(date_part(quarter, o_orderdate), 2) OVER (PARTITION BY date_part(year, o_orderdate) ORDER BY SUM(o_totalprice) DESC) AS next_worst_qtr
FROM
orders
WHERE
date_part(year, o_orderdate) BETWEEN 1995 AND 1997
GROUP BY
date_part(year, o_orderdate),
date_part(quarter, o_orderdate)
ORDER BY
1, 2;
程式碼解密
date_part(year, o_orderdate): 從o_orderdate欄位中提取年份。date_part(quarter, o_orderdate): 從o_orderdate欄位中提取季度。SUM(o_totalprice): 計算每個季度o_totalprice的總和。FIRST_VALUE(...) OVER (PARTITION BY ... ORDER BY ...): 視窗函式，用於取得每個年度中，依總銷售額排序的第一個季度（最佳季度）。NTH_VALUE(..., 2) OVER (PARTITION BY ... ORDER BY ...): 視窗函式，用於取得每個年度中，依總銷售額排序的第二個季度（次差季度）。PARTITION BY date_part(year, o_orderdate): 將資料依年份分割。ORDER BY SUM(o_totalprice) DESC: 依總銷售額降序排序。WHERE date_part(year, o_orderdate) BETWEEN 1995 AND 1997: 篩選 1995 年至 1997 年的資料。GROUP BY date_part(year, o_orderdate), date_part(quarter, o_orderdate): 依年份和季度分組。ORDER BY 1, 2: 依年份和季度排序結果。
結果如下：
+
---
---
+
---
-
---
+
---
-
---
-
---
-
---
-
---
+
---
-
---
---
+
---
-
---
-
---
-
---
-+
| YEAR | QRTER | SUM(O_TOTALPRICE) | BEST_QTR | NEXT_WORST_QTR |
|
---
---
+
---
-
---
+
---
-
---
-
---
-
---
-
---
+
---
-
---
---
+
---
-
---
-
---
-
---
-|
| 1995 | 1 | 828280426.28 | 3 | 4 |
| 1995 | 2 | 818992304.21 | 3 | 4 |
| 1995 | 3 | 845652776.68 | 3 | 4 |
| 1995 | 4 | 824596303.26 | 3 | 4 |
| 1996 | 1 | 805551195.59 | 3 | 2 |
| 1996 | 2 | 809903462.32 | 3 | 2 |
| 1996 | 3 | 841091513.43 | 3 | 2 |
| 1996 | 4 | 839827181.45 | 3 | 2 |
| 1997 | 1 | 793402839.95 | 2 | 4 |
| 1997 | 2 | 824211569.74 | 2 | 4 |
| 1997 | 3 | 824176170.61 | 2 | 4 |
| 1997 | 4 | 813296140.78 | 2 | 4 |
+
---
---
+
---
-
---
+
---
-
---
-
---
-
---
-
---
+
---
-
---
---
+
---
-
---
-
---
-
---
-+
Qualify 語法：更優雅的過濾方式
在資料分析中，我們經常需要在視窗函式的結果上進行過濾。傳統上，這需要使用子查詢，但 Snowflake 引入了 QUALIFY 語法，讓這個過程更加簡潔。
QUALIFY 子句在 WHERE、GROUP BY 和 HAVING 子句之後執行，專門用於根據視窗函式的結果進行過濾。
以下是如何使用子查詢來找出供應商最多的前五個國家的範例：
SELECT
name,
num_suppliers
FROM
(
SELECT
n.n_name AS name,
COUNT(*) AS num_suppliers,
RANK() OVER (
ORDER BY
COUNT(*) DESC
) AS rnk
FROM
supplier s
INNER JOIN nation n ON n.n_nationkey = s.s_nationkey
GROUP BY
n.n_name
) top_suppliers
WHERE
rnk <= 5;
程式碼解密
- 最內層的 SELECT 查詢計算每個國家的供應商數量，並使用
RANK()函式依供應商數量降序排名。 - 外部 SELECT 查詢從子查詢的結果中選擇國家名稱和供應商數量，並使用
WHERE子句過濾出排名前五的國家。
結果如下：
+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
| NAME | NUM_SUPPLIERS |
|
---
-
---
-
---
+
---
-
---
-
---
-
---
|
| PERU | 325 |
| ALGERIA | 318 |
| ARGENTINA | 312 |
| CHINA | 310 |
| IRAQ | 309 |
+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
使用 QUALIFY 子句，可以將上述查詢簡化為：
SELECT
n.n_name AS name,
COUNT(*) AS num_suppliers,
RANK() OVER (
ORDER BY
COUNT(*) DESC
) AS rnk
FROM
supplier s
INNER JOIN nation n ON n.n_nationkey = s.s_nationkey
GROUP BY
n.n_name
QUALIFY
rnk <= 5;
程式碼解密
- 此查詢與先前的子查詢範例執行相同的計算，但使用
QUALIFY子句來過濾結果，而不是使用外部WHERE子句。 QUALIFY rnk <= 5子句僅保留rnk（排名）小於或等於 5 的那些列。
結果如下：
+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
--+
| NAME | NUM_SUPPLIERS | RNK |
|
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
--|
| PERU | 325 | 1 |
| ALGERIA | 318 | 2 |
| ARGENTINA | 312 | 3 |
| CHINA | 310 | 4 |
| IRAQ | 309 | 5 |
+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
--+
QUALIFY 子句不僅可以參照 SELECT 子句中的視窗函式別名，還可以將視窗函式直接放在 QUALIFY 子句中：
SELECT
n.n_name AS name,
COUNT(*) AS num_suppliers
FROM
supplier s
INNER JOIN nation n ON n.n_nationkey = s.s_nationkey
GROUP BY
n.n_name
QUALIFY
RANK() OVER (
ORDER BY
COUNT(*) DESC
) <= 5;
程式碼解密
- 在此版本中，
RANK()函式直接在QUALIFY子句中使用，而沒有在SELECT子句中指定別名。 - 此查詢產生與之前相同的結果，但僅選擇國家名稱和供應商數量，而不包括排名。
結果如下：
+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
| NAME | NUM_SUPPLIERS |
|
---
-
---
-
---
+
---
-
---
-
---
-
---
|
| PERU | 325 |
| ALGERIA | 318 |
| ARGENTINA | 312 |
| CHINA | 310 |
| IRAQ | 309 |
+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
使用 TOP 子句
另一種查詢前 N 行的方法是使用 SELECT 子句的 TOP 子句。以下是如何使用 TOP 子句查詢前五個國家的範例：
SELECT
TOP 5 n.n_name AS name,
COUNT(*) AS num_suppliers,
RANK() OVER (
ORDER BY
COUNT(*) DESC
) AS rnk
FROM
supplier s
INNER JOIN nation n ON n.n_nationkey = s.s_nationkey
GROUP BY
n.n_name
ORDER BY
3;
程式碼解密
TOP 5子句指定要從查詢中傳回的前 5 列。ORDER BY 3子句指定結果應依第三列（排名）排序。
結果如下：
+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
--+
| NAME | NUM_SUPPLIERS | RNK |
|
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
--|
| PERU | 325 | 1 |
| ALGERIA | 318 | 2 |
| ARGENTINA | 312 | 3 |
| CHINA | 310 | 4 |
| IRAQ | 309 | 5 |
+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
--+
總結來說，QUALIFY 子句在靈活性方面更勝一籌，因為它可以指定完整的過濾條件。
總結：
nth_value() 函式在資料分析中用於取得排序後的特定數值，而 Snowflake 的 QUALIFY 子句和 TOP 子句則提供了更簡潔和靈活的方式來過濾和選擇前 N 行資料。
視窗函式：報表功能的無限可能
除了產生排名，視窗函式的另一個常見用途是找出異常值（例如，最小值或最大值），或者產生整個資料集的總和或平均值。對於這些型別的用途，玄貓會使用像 min()、max() 和 sum() 這樣的聚合函式，但不會使用 group by 子句，而是將它們與 partition by 和/或 order by 子句配對使用。
讓玄貓從一個查詢開始，該查詢計算亞洲地區所有國家每年的總銷售額：
select
n.n_name,
date_part(year, o_orderdate) as year,
sum(o.o_totalprice) as total_sales
from region r
inner join nation n
on r.r_regionkey = n.n_regionkey
inner join customer c
on n.n_nationkey = c.c_nationkey
inner join orders o
on o.o_custkey = c.c_custkey
where r.r_name = 'ASIA'
group by n.n_name, date_part(year, o_orderdate)
order by 1,2;
這個查詢的結果會顯示每個亞洲國家每年的總銷售額。接下來，讓玄貓增加兩個額外的欄位：一個顯示所有年份中每個國家的總銷售額，另一個顯示每年所有國家的總銷售額。為此，玄貓將使用兩個 sum() 函式以及 partition by 子句來產生適當的資料視窗：
select
n.n_name,
date_part(year, o_orderdate) as year,
sum(o.o_totalprice) as total_sales,
sum(sum(o.o_totalprice))
over (partition by n.n_name) as tot_cntry_sls,
sum(sum(o.o_totalprice))
over (partition by date_part(year, o_orderdate))
as tot_yrly_sls
from region r
inner join nation n
on r.r_regionkey = n.n_regionkey
inner join customer c
on n.n_nationkey = c.c_nationkey
inner join orders o
on o.o_custkey = c.c_custkey
where r.r_name = 'ASIA'
group by n.n_name, date_part(year, o_orderdate)
order by 1,2;
在這個查詢中，玄貓使用了視窗函式 sum(sum(o.o_totalprice)) over (partition by n.n_name) 來計算每個國家的總銷售額，以及 sum(sum(o.o_totalprice)) over (partition by date_part(year, o_orderdate)) 來計算每年的總銷售額。partition by 子句定義了視窗，over 子句指定了在每個視窗上執行的聚合函式。
玄貓可以使用這些額外的欄位來計算每個國家或每年的百分比。玄貓也可能對比較視窗內的平均值或最大值感興趣：
select
n.n_name,
date_part(year, o_orderdate) as year,
sum(o.o_totalprice) as total_sales,
max(sum(o.o_totalprice))
over (partition by n.n_name) as max_cntry_sls,
avg(round(sum(o.o_totalprice)))
over (partition by date_part(year, o_orderdate))
as avg_yrly_sls
from region r
inner join nation n
on r.r_regionkey = n.n_regionkey
inner join customer c
on n.n_nationkey = c.c_nationkey
inner join orders o
on o.o_custkey = c.c_custkey
where r.r_name = 'ASIA'
group by n.n_name, date_part(year, o_orderdate)
order by 1,2;
在這個查詢中，玄貓使用了視窗函式 max(sum(o.o_totalprice)) over (partition by n.n_name) 來計算每個國家的最大銷售額，以及 avg(round(sum(o.o_totalprice))) over (partition by date_part(year, o_orderdate)) 來計算每年的平均銷售額。
視窗函式的報表應用：玄貓的經驗分享
從玄貓的經驗來看，視窗函式在報表應用中非常強大。它們允許玄貓在不使用 group by 子句的情況下，對資料集進行聚合和分析。這使得玄貓能夠輕鬆地計算排名、異常值、總和、平均值和百分比。
玄貓曾經在一個金融科技專案中使用視窗函式來計算客戶的信用評分。玄貓使用了視窗函式來計算客戶的平均交易金額、最大交易金額和總交易金額。然後，玄貓使用這些值來計算客戶的信用評分。
玄貓還在一個零售專案中使用視窗函式來計算產品的銷售排名。玄貓使用了視窗函式來計算每個產品的總銷售額，然後使用這些值來計算產品的銷售排名。
視窗函式是報表應用中一個非常有用的工具。它們允許玄貓在不使用 group by 子句的情況下，對資料集進行聚合和分析。這使得玄貓能夠輕鬆地計算排名、異常值、總和、平均值和百分比。
總結來說，視窗函式在資料分析和報表生成中扮演了關鍵角色，它們提供了一種強大而靈活的方式來處理和轉換資料，使得資料科學家和分析師能夠從不同的角度審視資料，從而發現有價值的洞見。透過掌握視窗函式，玄貓可以更有效地解決實際問題，並為業務決策提供更可靠的依據。
資料分組的藝術：Positional Windows 的妙用
在資料分析的世界裡，我們經常需要對資料進行分組，以便更好地理解資料的特性和趨勢。先前我們介紹瞭如何使用 PARTITION BY 子句來建立資料視窗，將具有共同值的資料列分在同一個視窗中。但有時候，我們需要根據資料列的鄰近性來定義資料視窗，而不是依賴於特定的值。這時候，Positional Windows 就派上用場了。
Positional Windows 允許我們根據資料列的順序來建立視窗，這在計算移動平均或累計總和等指標時非常有用。例如，我們可能需要計算過去、現在和未來三個月份的平均銷售額，或者計算從第一筆交易到當前交易的總銷售額。
要使用 Positional Windows，我們需要結合 ORDER BY 子句和 ROWS 子句。ORDER BY 子句定義了資料列的排序方式，而 ROWS 子句則指定了哪些資料列應該包含在資料視窗中。
案例重現：計算季度銷售額的移動平均
讓我們先回到先前計算年度和季度銷售額的例子：
PUBLIC> SELECT
date_part(year, o_orderdate) AS year,
date_part(quarter, o_orderdate) AS qrter,
sum(o_totalprice) AS total_sales
FROM
orders
WHERE
date_part(year, o_orderdate) BETWEEN 1995 AND 1997
GROUP BY
date_part(year, o_orderdate),
date_part(quarter, o_orderdate)
ORDER BY
1, 2;
這個查詢會產生一個包含年度、季度和總銷售額的結果集。現在，假設我們需要計算三個月的移動平均，也就是說，每個季度的資料視窗應該包含前一個季度、當前季度和下一個季度的資料。我們可以這樣做：
PUBLIC> SELECT
date_part(year, o_orderdate) AS year,
date_part(quarter, o_orderdate) AS qrter,
sum(o_totalprice) AS total_sales,
AVG(SUM(o_totalprice)) OVER (
ORDER BY
date_part(year, o_orderdate),
date_part(quarter, o_orderdate) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS rolling_avg
FROM
orders
WHERE
date_part(year, o_orderdate) BETWEEN 1995 AND 1997
GROUP BY
date_part(year, o_orderdate),
date_part(quarter, o_orderdate)
ORDER BY
1, 2;
在這個查詢中，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 子句定義了資料視窗的範圍。它表示每個資料列的視窗應該包含前一個資料列和後一個資料列。
玄貓解密：程式碼分析
AVG(SUM(o_totalprice)) OVER (...): 計算移動平均值。ORDER BY date_part(year, o_orderdate), date_part(quarter, o_orderdate): 指定排序方式，先按年份排序，再按季度排序。ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 定義視窗範圍，包含前一個資料列和後一個資料列。
你可能會好奇，這個計算對於第一列（沒有前一個資料列）或最後一列（沒有後一個資料列）是如何運作的？在這種情況下，平均值只會使用可用的資料列進行計算。
累計總和：追蹤銷售額的增長
除了移動平均，Positional Windows 也可以用於計算累計總和。累計總和是指從第一列到當前列的所有值的總和。例如，我們可以計算每個季度的累計銷售額，以瞭解銷售額的增長趨勢。
要計算累計總和，我們可以使用 ROWS UNBOUNDED PRECEDING 子句：
PUBLIC> SELECT
date_part(year, o_orderdate) AS year,
date_part(quarter, o_orderdate) AS qrter,
sum(o_totalprice) AS total_sales,
SUM(SUM(o_totalprice)) OVER (
ORDER BY
date_part(year, o_orderdate),
date_part(quarter, o_orderdate) ROWS UNBOUNDED PRECEDING
) AS running_total
FROM
orders
WHERE
date_part(year, o_orderdate) BETWEEN 1995 AND 1997
GROUP BY
date_part(year, o_orderdate),
date_part(quarter, o_orderdate)
ORDER BY
1, 2;
在這個查詢中，ROWS UNBOUNDED PRECEDING 子句表示資料視窗應該從第一列開始，一直延伸到當前列。
玄貓解密：程式碼分析
SUM(SUM(o_totalprice)) OVER (...): 計算累計總和。ORDER BY date_part(year, o_orderdate), date_part(quarter, o_orderdate): 指定排序方式，與先前相同。ROWS UNBOUNDED PRECEDING: 定義視窗範圍，從第一列到當前列。
Positional Windows 的價值
Positional Windows 提供了一種強大而靈活的方式來定義資料視窗，使我們能夠執行各種複雜的資料分析任務。無論是計算移動平均、累計總和，還是其他根據資料列順序的計算，Positional Windows 都能幫助我們更好地理解資料的模式和趨勢。
在資料分析的旅程中，掌握 Positional Windows 絕對能為你開啟更多可能性，讓你從資料中挖掘出更深層次的洞見。下次當你需要根據資料列的鄰近性進行分析時，不妨試試 Positional Windows，相信它會給你帶來意想不到的收穫。
### 如何使用 LAG 和 LEAD 函式進行前期和後期資料檢索
在資料分析中，有時需要根據結果集中某個位置的值，來取得單一列的值。這時，`LAG()` 和 `LEAD()` 函式就派上用場了。它們允許你從鄰近的列中檢索資料，這在計算前期變動百分比之類別的場景非常有用。
以下是如何使用 `LAG()` 函式來查詢前一季度的總銷售額：
```sql
select
date_part(year, o_orderdate) year,
date_part(quarter, o_orderdate) qrter,
sum(o_totalprice) as total_sales,
lag(sum(o_totalprice),1)
over (order by date_part(year, o_orderdate),
date_part(quarter, o_orderdate)) as prior_qtr
from orders
where date_part(year, o_orderdate) between 1995 and 1997
group by date_part(year, o_orderdate),
date_part(quarter, o_orderdate)
order by 1,2;
程式碼解密
date_part(year, o_orderdate)和date_part(quarter, o_orderdate)：從o_orderdate欄位中提取年份和季度。sum(o_totalprice) as total_sales：計算每個季度總銷售額，並將結果命名為total_sales。lag(sum(o_totalprice),1) over (order by date_part(year, o_orderdate), date_part(quarter, o_orderdate))：使用lag()函式取得前一季度的總銷售額。over (order by ...)子句指定排序方式，這裡按照年份和季度排序。from orders：指定從orders表中查詢資料。where date_part(year, o_orderdate) between 1995 and 1997：篩選出 1995 年至 1997 年的資料。group by date_part(year, o_orderdate), date_part(quarter, o_orderdate)：按照年份和季度分組，以便計算每個季度的總銷售額。order by 1,2：按照年份和季度排序結果集。
上述範例中，lag(sum(o_totalprice),1) 指示 Snowflake 在排序後的資料中，回溯一行並檢索 sum(o_totalprice) 的值。由於第一行沒有前一行，因此該值為 null。
現在，你已經在同一行中有了當前季度和前一季度的值，接下來只需進行計算，即可確定變動百分比 ((當前值 – 前期值) / 前期值 * 100)。
如果你想從排序後的資料中的下一行提取值，而不是前一行，則可以使用 lead() 函式：
select
date_part(year, o_orderdate) year,
date_part(quarter, o_orderdate) qrter,
sum(o_totalprice) as total_sales,
lead(sum(o_totalprice),1)
over (order by date_part(year, o_orderdate),
date_part(quarter, o_orderdate)) as next_qtr
from orders
where date_part(year, o_orderdate) between 1995 and 1997
group by date_part(year, o_orderdate),
date_part(quarter, o_orderdate)
order by 1,2;
程式碼解密
date_part(year, o_orderdate)和date_part(quarter, o_orderdate)：從o_orderdate欄位中提取年份和季度。sum(o_totalprice) as total_sales：計算每個季度總銷售額，並將結果命名為total_sales。lead(sum(o_totalprice),1) over (order by date_part(year, o_orderdate), date_part(quarter, o_orderdate))：使用lead()函式取得下一季度的總銷售額。over (order by ...)子句指定排序方式，這裡按照年份和季度排序。from orders：指定從orders表中查詢資料。where date_part(year, o_orderdate) between 1995 and 1997：篩選出 1995 年至 1997 年的資料。group by date_part(year, o_orderdate), date_part(quarter, o_orderdate)：按照年份和季度分組，以便計算每個季度的總銷售額。order by 1,2：按照年份和季度排序結果集。
在這個範例中，next_qtr 欄位會從下一季度提取 total_sales 的值。正如你可能預料的那樣，最後一行的 next_qtr 值為 null，因為結果集中沒有更多的行。
其他視窗函式：listagg() 的妙用
Snowflake 中還有許多其他的視窗函式，其中許多函式對於統計分析非常有用。例如，有些函式可用於計算標準差和變異數、百分位數和線性迴歸。玄貓在這裡不打算涵蓋所有這些函式，但想演示最後一個名為 listagg() 的函式，玄貓發現它對於分析和開發都很有用。
listagg() 函式將資料透視到單個分隔字串中，以下查詢示範了這一點，該查詢傳回每個區域以及逗號分隔的相關國家/地區清單：
select
r.r_name as region,
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
order by 1;
程式碼解密
r.r_name as region：從region表中選擇區網域名稱，並將其命名為region。listagg(n.n_name, ',') within group (order by n.n_name) as nation_list：使用listagg()函式將每個區域的國家名稱連線成一個逗號分隔的字串，並按照國家名稱排序。結果命名為nation_list。from region r inner join nation n on r.r_regionkey = n.n_regionkey：將region表和nation表進行內連線，連線條件是r.r_regionkey = n.n_regionkey。group by r.r_name：按照區網域名稱分組，以便為每個區域生成一個國家清單。order by 1：按照區網域名稱排序結果集。
在這個範例中，listagg() 為每個區域產生一個按字母順序排列的國家/地區清單。不幸的是，listagg() 的語法與其他視窗函式不同，它使用 within group 子句來指定排序，而不是 over 子句。
此外，如果你想將結果集進一步拆分為資料視窗，則也可以新增 over (partition by ...) 子句。在某些情況下，你可以使用 partition by 子句來代替使用 group by 子句，儘管你可能需要新增 distinct 來刪除重複項。以下是不使用 group by 子句，而是使用 partition by 的先前查詢：
select distinct
r.r_name as region,
listagg(n.n_name, ',')
within group (order by n.n_name)
over (partition by r.r_name) as nation_list
from region r
inner join nation n
on r.r_regionkey = n.n_regionkey
order by 1;
程式碼解密
r.r_name as region：從region表中選擇區網域名稱，並將其命名為region。listagg(n.n_name, ',') within group (order by n.n_name) over (partition by r.r_name) as nation_list：使用listagg()函式將每個區域的國家名稱連線成一個逗號分隔的字串，並按照國家名稱排序。over (partition by r.r_name)子句指定按照區網域名稱進行分割槽。結果命名為nation_list。from region r inner join nation n on r.r_regionkey = n.n_regionkey：將region表和nation表進行內連線，連線條件是r.r_regionkey = n.n_regionkey。order by 1：按照區網域名稱排序結果集。
無論你以何種方式使用它，玄貓認為你會發現 listagg() 在任何需要展平或透視一組值時，都是一個方便的函式。
玄貓（BlackCat）希望本章涵蓋的各種視窗函式，包括排名和報表函式，能幫助你使用額外的欄位來擴充結果集，以執行小計和排名、計算滾動總和與平均值，以及從周圍的列中檢索欄位值。本章還介紹了一個名為 qualify 的新子句，該子句專門設計用於根據視窗函式的結果進行篩選。
