SQL のウィンドウ関数を 1 つの記事で理解する

この記事では、SQL サーバー に関する関連知識を提供します。分析関数とも呼ばれるウィンドウ関数には 2 種類あり、1 つは集計ウィンドウ関数、もう 1 つは並べ替えウィンドウ関数です。以下の記事では SQL のウィンドウ関数に関する関連情報を中心に、サンプルコードを通して詳しく紹介していますので、必要な方は参考にしてください。

推奨学習: 「SQL チュートリアル 」

OVER の定義

OVER は行の定義に使用されます。値のセットを操作する Window は、データをグループ化するために GROUP BY 句を使用する必要がなく、同じ行内の基本行列と集計列の両方を返すことができます。

OVER 構文

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )

グループ化のための PARTITION BY 句;

ORDER BY 句を使用して並べ替えます。

ウィンドウ関数 OVER() は行のセットを指定し、ウィンドウ関数はウィンドウ関数から出力された結果セット内の各行の値を計算します。

ウィンドウ関数は、GROUP BY を使用せずにデータをグループ化でき、ベース行の列と集計列を同時に返すこともできます。

OVER の使用法

OVER ウィンドウ関数は、集計関数または並べ替え関数と併用する必要があります。集計関数とは、一般に SUM()、MAX()、MIN、COUNT()、AVG( ) およびその他の一般的な関数。並べ替え関数は通常、RANK()、ROW_NUMBER()、DENSE_RANK()、NTILE() などを指します。

集計関数での OVER の使用例

デモのための例として、SUM 関数と COUNT 関数を使用します。

--建立测试表和测试数据
CREATE TABLE Employee
(
ID INT  PRIMARY KEY,
Name VARCHAR(20),
GroupName VARCHAR(20),
Salary INT
)
INSERT INTO  Employee
VALUES(1,'小明','开发部',8000),
      (4,'小张','开发部',7600),
      (5,'小白','开发部',7000),
      (8,'小王','财务部',5000),
      (9, null,'财务部',NULL),
      (15,'小刘','财务部',6000),
      (16,'小高','行政部',4500),
      (18,'小王','行政部',4000),
      (23,'小李','行政部',4500),
      (29,'小吴','行政部',4700);

SUM後のウィンドウ関数

SELECT *,
     SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资,
     SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资,
     SUM(Salary) OVER(ORDER BY ID) 累计工资,
     SUM(Salary) OVER() 总工资
from Employee

(ヒント: コードを左右にスライドできます)

結果は次のとおりです:

各ウィンドウ関数の意味は異なります。詳しく説明しましょう:

SUM(Salary) OVER (PARTITION BY Groupname)

PARTITION BY のみ 次の列 Groupname がグループ化され、Salary の合計はグループ化後に計算されます。

SUM(給与) OVER (PARTITION BY Groupname ORDER BY ID)

PARTITION BY の後の Groupname 列の場合グループ化し、ORDER BY以降のIDでソートし、グループ内のSalaryを累計します。

SUM(給与) OVER (ORDER BY ID)

ORDER BY のみ 並べ替えソート後のID内容を取得し、ソートされたSalaryを集計します。

SUM(Salary) OVER ()

Salary の集計処理

After COUNTウィンドウ関数

SELECT *,
       COUNT(*) OVER(PARTITION BY Groupname ) 每个组的个数,
       COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) 每个组的累积个数,
       COUNT(*) OVER(ORDER BY ID) 累积个数 ,
       COUNT(*) OVER() 总个数
from Employee

によって返される結果は次のとおりです:

後続の各ウィンドウ関数は 1 つずつ解釈されなくなります。上記 SUM 以降のウィンドウ関数を 1 つずつ比較します。

ソート関数での OVER の使用例

4 つのソート関数を 1 つずつ説明します

--先建立测试表和测试数据
WITH t AS
(SELECT 1 StuID,'一班' ClassName,70 Score
UNION ALL
SELECT 2,'一班',85
UNION ALL
SELECT 3,'一班',85
UNION ALL
SELECT 4,'二班',80
UNION ALL
SELECT 5,'二班',74
UNION ALL
SELECT 6,'二班',80
)
SELECT * INTO Scores FROM t;
SELECT * FROM Scores

ROW_NUMBER()

定義 : ROW_NUMBER() 関数の機能は、SELECT でクエリされたデータを並べ替えることです。各データにはシリアル番号が追加されます。学生の成績のランク付けには使用できません。通常は、次のようなページング クエリに使用されます。上位 10 のクエリを 10 ～ 100 人の学生にクエリします。 ROW_NUMBER() は ORDER BY と一緒に使用する必要があります。そうしないと、エラーが報告されます。

学生のスコアを並べ替えます

SELECT *,
ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班内排序,
ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序
FROM Scores;

結果は次のとおりです:

ここでの PARTITION BY と ORDER BY の機能は次と同じです。上で見たこと 集計関数は同じ機能を持ち、グループ化と並べ替えに使用されます。

さらに、ROW_NUMBER() 関数は、指定された順序でデータを取得することもできます。

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序
FROM Scores
) t WHERE t.总排序=2;

結果は次のとおりです:

RANK()

定義: RANK() 関数は、名前が示すように、特定のフィールドを順位付けできるランキング関数 ランキング、これとROW_NUMBER()の違いは何ですか？ ROW_NUMBER()はソートです。同じ成績の生徒がいる場合、ROW_NUMBER()は順番にソートします。シリアル番号は異なりますが、Rank()は異なります。見た目が同じであれば、ランキングも同じです。以下の例を見てください:

Example

SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;
 
SELECT RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

結果:

#上の図は ROW_NUMBER です。 ( ) の場合、下図は RANK() の結果です。 2 人の生徒が同じ成績の場合、変化が生じます。 RANK() は 1-1-3-3-5-6 ですが、ROW_NUMBER() は 1-2-3-4-5-6 のままです。これが RANK() と ROW_NUMBER() の違いです。

DENSE_RANK() 

定义：DENSE_RANK()函数也是排名函数，和RANK()功能相似，也是对字段进行排名，那它和RANK()到底有什么不同那？特别是对于有成绩相同的情况，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，一般情况下用的排名函数就是RANK() 我们看例子：

示例

SELECT 
RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;
 
SELECT 
DENSE_RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

结果如下：

上面是RANK()的结果，下面是DENSE_RANK()的结果

NTILE()

定义：NTILE()函数是将有序分区中的行分发到指定数目的组中，各个组有编号，编号从1开始，就像我们说的'分区'一样 ，分为几个区，一个区会有多少个。  

SELECT *,NTILE(1) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;
SELECT *,NTILE(2) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;
SELECT *,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;

结果如下：

就是将查询出来的记录根据NTILE函数里的参数进行平分分区。

总结

OVER开窗函数是我们工作中经常要使用到的，特别是在做数据分析计算的时候，经常要对数据进行分组排序。上面我们额外介绍了聚合函数和排序函数的与OVER结合的使用方法，此外还有很多与OVER一起使用的函数，比如LEAD函数，LAG函数，STRING_AGG函数等等都会使用到开窗函数OVER，其使用方法也要务必掌握。

推荐学习：《SQL教程》

以上がSQL のウィンドウ関数を 1 つの記事で理解するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。