在SQL中，窗口函數(shù)是一種強(qiáng)大的工具，用于在查詢結(jié)果中執(zhí)行聚合、排序和分組操作，而不影響原始表的行數(shù)。這篇文章將介紹窗口函數(shù)的基本概念以及如何使用它們來解決各種數(shù)據(jù)分析和報(bào)告需求。
1.窗口函數(shù)基礎(chǔ)
窗口函數(shù)的基本語法如下：
<窗口函數(shù)> over (partition by <用于分組的列名> order by <用于排序的列名>)其中，partition by用于對(duì)表進(jìn)行分組，而不會(huì)減少原表的行數(shù)。例如，按班級(jí)分組可以使用 partition by 班級(jí)。
order by則用于對(duì)分組后的結(jié)果進(jìn)行排序，可以選擇升序（asc）或降序（desc）。例如，order by 成績 desc 表示按成績降序排列。
窗口函數(shù)的優(yōu)勢(shì)在于它們能夠同時(shí)實(shí)現(xiàn)分組和排序的功能，而不像 group by 子句那樣減少表的行數(shù)。這意味著你可以在不失去原始數(shù)據(jù)的情況下執(zhí)行聚合和排名操作。
2.窗口函數(shù)示例
示例：統(tǒng)計(jì)每個(gè)班級(jí)的人數(shù)
讓我們看一個(gè)示例，假設(shè)我們要統(tǒng)計(jì)每個(gè)班級(jí)的學(xué)生人數(shù)。使用窗口函數(shù)，我們可以輕松實(shí)現(xiàn)這一目標(biāo)：
SELECT *,
COUNT(*) OVER (PARTITION BY 班級(jí)) AS 班級(jí)人數(shù)
FROM 學(xué)生表在這個(gè)示例中，COUNT(*) 函數(shù)作為窗口函數(shù)，使用 PARTITION BY 班級(jí) 實(shí)現(xiàn)了按班級(jí)分組并計(jì)算每個(gè)班級(jí)的學(xué)生人數(shù)，同時(shí)原始表的行數(shù)依舊不變，而每行數(shù)據(jù)則增加了一列新列`班級(jí)人數(shù)`。
3.窗口函數(shù)的三種排序方式
窗口函數(shù)有多種類型，包括 rank()、dense_rank() 和 row_number()。它們?cè)谔幚聿⒘忻螘r(shí)的行為不同：
rank()
函數(shù)會(huì)占用下一名次的位置，如果有并列名次的行。例如，如果前3名是并列的名次，結(jié)果將是 1，1，1，4。
dense_rank()
函數(shù)不會(huì)占用下一名次的位置，如果有并列名次的行。例如，前3名是并列的名次，結(jié)果是 1，1，1，2。
row_number()
函數(shù)不考慮并列名次，排名是正常的。例如，前3名是并列的名次，排名是正常的 1，2，3，4。
窗口函數(shù)還可以與聚合函數(shù)一起使用，以在結(jié)果中執(zhí)行聚合操作，如總和、平均、計(jì)數(shù)、最大和最小值。以下是示例：
SELECT *,
SUM(成績) OVER (ORDER BY 學(xué)號(hào)) AS 當(dāng)前總分,
AVG(成績) OVER (ORDER BY 學(xué)號(hào)) AS 當(dāng)前平均分,
COUNT(成績) OVER (ORDER BY 學(xué)號(hào)) AS 當(dāng)前人數(shù),
MAX(成績) OVER (ORDER BY 學(xué)號(hào)) AS 最高分,
MIN(成績) OVER (ORDER BY 學(xué)號(hào)) AS 最低分
FROM 班級(jí)表在此示例中，我們使用窗口函數(shù)將聚合函數(shù)應(yīng)用于成績，并按學(xué)號(hào)排序，以計(jì)算每個(gè)學(xué)生的當(dāng)前總分、平均分、人數(shù)、最高分和最低分。
4.結(jié)論
窗口函數(shù)是 SQL 查詢中強(qiáng)大的工具，它們?cè)试S我們?cè)诓粶p少原始數(shù)據(jù)行數(shù)的情況下執(zhí)行分組、排序和聚合操作。通過了解窗口函數(shù)的基本語法和應(yīng)用，您可以更靈活地分析數(shù)據(jù)和生成報(bào)告。無論是數(shù)據(jù)分析師還是數(shù)據(jù)庫開發(fā)人員，掌握窗口函數(shù)都是一個(gè)有用的技能，可以大大簡化復(fù)雜查詢的編寫和理解。
