SQL Server 2022で追加された「WINDOW句」を紹介
?Download as PPTX, PDF?
0 likes?163 views
SQL Server 2022で追加された「WINDOW句」を紹介します。 ※2023/2/25 JSSUG「Microsoft Data Platform Day」発表資料 https://sqlserver.connpass.com/event/270620/
![/34
ウィンドウ関数の基本
6
OVER句の構文
■OVER句の概要
SELECT
???
[ウィンドウ関数]
※順位付け関数、集計関数、分析関数など
OVER(
PARTITION BY [分割キーの列名]
※ 省略時は分割せず、結果セットの全行が対象
ORDER BY [分割した行内のソートキーの列名]
※ 省略時は分割した行全体が対象
※「ROWS(RANGE)」 指定時は必須
ROWS/RANGE [分割した行内の開始点と終了点]
※ 「ORDER BY」のオプションのような位置付け
※ 「ORDER BY」省略時は分割した範囲の最初から現
在行
)
???
詳細はMSのドキュメントを
参照](https://image.slidesharecdn.com/jssug-20230225-sqlserverselect-window-230225050754-898e10d9/85/SQL-Server-2022-WINDOW-6-320.jpg)
![/34
ROWS/RANGE
? 基本的な構文
パーテーション内の行セットをAからBの範囲内に絞ります。
AとBの書き方のパターン
UNBOUNDED PRECEDING
? 最初の行。開始位置としてのみ指定可能。
[n] PRECEDING
? 現在の行からみてn行前の行。 RANGEでは指定不可。
CURRENT ROW
? 現在の行 (ROWS の場合) または、現在の値 (RANGE の場合)
[n] FOLLOWING
? 現在の行からみてn行後の行RANGEでは指定不可。
UNBOUNDED FOLLOWING
? 最後の行。終了位置としてのみ指定可能。
10
ウィンドウ関数の基本
ROWS BETWEEN A AND B / RANGE BETWEEN A AND B
参考](https://image.slidesharecdn.com/jssug-20230225-sqlserverselect-window-230225050754-898e10d9/85/SQL-Server-2022-WINDOW-10-320.jpg)
![/34
WINDOW句の紹介
27
WINDOW句の構文
■WINDOW句の概要
SELECT
???
WINDOW [ウィンドウ名] AS (
[参照ウィンドウ名]
※参照されるウィンドウはWINDOW句で定義さ
れている
ウィンドウの中にあることが必要
PARTITION BY句
ORDER BY句
ROWS/RANGE句
)
???
詳細はMSのドキュメントを
参照](https://image.slidesharecdn.com/jssug-20230225-sqlserverselect-window-230225050754-898e10d9/85/SQL-Server-2022-WINDOW-27-320.jpg)
SQL Server 2022で追加された「WINDOW句」を紹介
- 2. /34 自己紹介 山田 公次（Yamada Koji） ? 名古屋在住の C#per（JavaはJava6まで経験） ? 現在は市販のWebアプリの開発に携わっており、主に ドメイン分析やサーバー開発（モデル?WebAPI? DB）を担当しています。 ? また、Scrumを導入しており、Scrum inc.認定Scrum Masterも取得しました。 2 @hamu502 製品開発部署(現在) 市販アプリの開発だ けでなく、企画から サポートまで幅広く 対応 フリーランス時代 業務アプリ開発、 クラウドのセールスSE、 市販アプリの開発 中小SIer時代 金融や流通、 不動産業界などの 業務アプリ開発 主に扱ったDB製品 Oracle8～11g,SQL Server2000～2005,etc 主に扱ったDB製品 Oracle11g,SQL Server2008～ 1998～ 2008～ 2018～
- 6. /34 ウィンドウ関数の基本 6 OVER句の構文 ■OVER句の概要 SELECT ??? [ウィンドウ関数] ※順位付け関数、集計関数、分析関数など OVER( PARTITION BY [分割キーの列名] ※ 省略時は分割せず、結果セットの全行が対象 ORDER BY [分割した行内のソートキーの列名] ※ 省略時は分割した行全体が対象 ※「ROWS(RANGE)」 指定時は必須 ROWS/RANGE [分割した行内の開始点と終了点] ※ 「ORDER BY」のオプションのような位置付け ※ 「ORDER BY」省略時は分割した範囲の最初から現 在行 ) ??? 詳細はMSのドキュメントを 参照
- 7. /34 PARTITION BY ? クエリ結果セットをパーティションに分割します。 ? ウィンドウ関数は各パーティションに対して個別に適用され、各 パーティションで計算が再開されます。 7 ウィンドウ関数の基本 部品名 調達先名 単価 部品A B社 100 部品A A社 140 部品B B社 200 部品C A社 200 部品C B社 180 ??? ??? ??? 部品調達単価 部品名 調達先名 単価 部品A B社 100 部品A A社 140 部品B B社 200 部品C B社 180 部品C A社 200 ウィンドウ関数のイメージ 部品名 調達先名 単価 平均 部品A B社 100 120 部品A A社 140 120 部品B B社 200 200 部品C B社 180 190 部品C A社 200 190 PARTITION BYに 指定した列で分割 SELECT 部品名, 調達先名, 単価, AVG(単価) OVER(PARTITION BY 部品名) AS 平均 FROM 部品調達単価 ??? 分割した行全体の AVG()の結果を各行に 設定 この分割した集合が「ウィン ドウ」
- 8. /34 ORDER BY ? 結果セットの各パーティション内の行の論理的な順序を定義しま す。 ? つまり、ウィンドウ関数の計算が実行される論理的な順序を指定 します。 8 ウィンドウ関数の基本 部品名 調達先名 単価 部品A B社 100 部品A A社 140 部品B B社 200 部品C A社 200 部品C B社 180 ??? ??? ??? 部品調達単価 部品名 調達先名 単価 部品A B社 100 部品A A社 140 部品B B社 200 部品C B社 180 部品C A社 200 ウィンドウ関数のイメージ 部品名 調達先名 単価 行番号 部品A B社 100 1 部品A A社 140 2 部品B B社 200 1 部品C B社 180 1 部品C A社 200 2 PARTITION BYに 指定した列で分割 SELECT 部品名, 調達先名, 単価, ROW_NUMBER() OVER(PARTITION BY 部品名 ORDER BY 単価) AS 行番号 FROM 部品調達単価 ??? 分割した範囲の順序で ROW_NUMBER() の結果を各行に設定 ORDER BYに 指定した列でソート
- 9. /34 ROWS/RANGE ? パーティション内の開始点と終了点を指定することで、パーティ ション内の行をさらに制限します。 ? 前後の行は、ORDER BY 句での順序に基づいて定義されます。 ROWS ? 現在行の前または後にある固定数の行を指定することにより、パー ティション内の行が限定されます。 RANGE ? 現在行の値を基準とする値の範囲を指定することにより、パーティ ション内の行を論理的に限定します。 ? ORDER BY 句を指定し、ROWS/RANGEを省略した場合、以下 の規定値が使用されます。 ※ ROWS/RANGE句の指定を受け入れることができるウィンドウ関数に対して のみ適用されます。（順位付け関数は ROWS/RANGE を受け入れないため、 適用されません） 9 ウィンドウ関数の基本 RANGE UNBOUNDED PRECEDING AND CURRENT ROW
- 10. /34 ROWS/RANGE ? 基本的な構文 パーテーション内の行セットをAからBの範囲内に絞ります。 AとBの書き方のパターン UNBOUNDED PRECEDING ? 最初の行。開始位置としてのみ指定可能。 [n] PRECEDING ? 現在の行からみてn行前の行。 RANGEでは指定不可。 CURRENT ROW ? 現在の行 (ROWS の場合) または、現在の値 (RANGE の場合) [n] FOLLOWING ? 現在の行からみてn行後の行RANGEでは指定不可。 UNBOUNDED FOLLOWING ? 最後の行。終了位置としてのみ指定可能。 10 ウィンドウ関数の基本 ROWS BETWEEN A AND B / RANGE BETWEEN A AND B 参考
- 11. /34 ROWS/RANGE ? 基本的な構文 パーテーション内の行セットをAからBの範囲内に絞ります。 ROWS句での書き方例 ①最初の行から現在の行まで ②1つ前の行から2つの後の行まで ③1つ前の行から1つ前の行まで（つまり1つ前の行） 11 ウィンドウ関数の基本 ROWS BETWEEN A AND B / RANGE BETWEEN A AND B ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING 参考
- 12. /34 ROWS/RANGE ? 基本的な構文 パーテーション内の行セットをAからBの範囲内に絞ります。 現在行が最初の行の場合のROWS句で指定した範囲のイメージ ※ 前述のスライドの①②③でのイメージ 12 ウィンドウ関数の基本 ROWS BETWEEN A AND B / RANGE BETWEEN A AND B No. 営業担当 売上日 売上額 1 Suzuki 2023/2/1 120 2 Suzuki 2023/2/2 200 3 Suzuki 2023/2/5 90 4 Suzuki 2023/2/6 90 5 Suzuki 2023/2/7 110 6 Suzuki 2023/2/8 40 7 Suzuki 2023/2/9 100 ??? 現在行→ ※関数を実行 している行 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ???① ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING ???② ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ???③ ②や③の先頭行より前が開始位置の 場合の関数の実行結果 ②???NULLとなる ③???先頭行（現在行と同じ行）から2 行後 の行（No.3の行）を範囲とした 関数の 実行結果となる 参考
- 13. /34 ROWS/RANGE ? 基本的な構文 パーテーション内の行セットをAからBの範囲内に絞ります。 現在行が2行目の場合のROWS句で指定した範囲のイメージ ※ 前述のスライドの①②③でのイメージ 13 ウィンドウ関数の基本 ROWS BETWEEN A AND B / RANGE BETWEEN A AND B No. 営業担当 売上日 売上額 1 Suzuki 2023/2/1 120 2 Suzuki 2023/2/2 200 3 Suzuki 2023/2/5 90 4 Suzuki 2023/2/6 90 5 Suzuki 2023/2/7 110 6 Suzuki 2023/2/8 40 7 Suzuki 2023/2/9 100 ??? 現在行→ ※関数を実行 している行 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ???① ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING ???② ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ???③ 参考
- 14. /34 ROWS/RANGE ? 基本的な構文 パーテーション内の行セットをAからBの範囲内に絞ります。 現在行が3行目の場合のROWS句で指定した範囲のイメージ ※ 前述のスライドの①②③でのイメージ 14 ウィンドウ関数の基本 ROWS BETWEEN A AND B / RANGE BETWEEN A AND B No. 営業担当 売上日 売上額 1 Suzuki 2023/2/1 120 2 Suzuki 2023/2/2 200 3 Suzuki 2023/2/5 90 4 Suzuki 2023/2/6 90 5 Suzuki 2023/2/7 110 6 Suzuki 2023/2/8 40 7 Suzuki 2023/2/9 100 ??? 現在行→ ※関数を実行 している行 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ???① ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING ???② ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ???③ 参考
- 15. /34 ROWS/RANGE ? 基本的な構文 パーテーション内の行セットをAからBの範囲内に絞ります。 現在行が4行目の場合のROWS句で指定した範囲のイメージ 15 ウィンドウ関数の基本 ROWS BETWEEN A AND B / RANGE BETWEEN A AND B No. 営業担当 売上日 売上額 1 Suzuki 2023/2/1 120 2 Suzuki 2023/2/2 200 3 Suzuki 2023/2/5 90 4 Suzuki 2023/2/6 90 5 Suzuki 2023/2/7 110 6 Suzuki 2023/2/8 40 7 Suzuki 2023/2/9 100 ??? 現在行→ ※関数を実行 している行 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ???① ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING ???② ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ???③ 参考
- 16. /34 ウィンドウ関数の基本 16 ウィンドウ関数の種類 カテゴリ 使用例 関数例 順位付け関数 Window毎に連番を振る ROW_NUMBER、RANK、 DENSE_RANK など 集計関数 累積集計を計算する COUNT、SUM、AVG、MIN、 MAX など 分析関数 前後のレコードを取得 する LAG、LEAD、FIRST_VALUE、 LAST_VALUE など NEXT VALUE FOR ※ シーケンス オブジェクトからシーケンス番号を生成する関 数であり、今回は割愛します。
- 17. /34 ウィンドウ関数の基本 17 順位付け関数 ROW_NUMBER ? 結果セットの出力に番号を設定します。 RANK ? 結果セットのパーティション毎の順位を返します。 DENSE_RANK ? 結果セットのパーティション毎の順位をギャップなしで返します。 など 他の関数や詳細はMSドキュメントを参照 https://learn.microsoft.com/ja-jp/sql/t-sql/functions/ranking-functions-transact-sql 参考
- 18. /34 ウィンドウ関数の基本 18 順位付け関数 SELECT ID,部品名, 調達先名, 単価, ROW_NUMBER() OVER(ORDER BY 単価 DESC), RANK() OVER(ORDER BY 単価 DESC), DENSE_RANK() OVER(ORDER BY 単価 DESC) FROM 部品調達単価 ID 部品名 調達先名 単価 ROW_NUMBER RANK DENSE_RANK 3 部品B B社 200 1 1 1 4 部品C A社 200 2 1 1 5 部品C B社 180 3 3 2 2 部品A A社 140 4 4 3 1 部品A B社 100 5 5 4 参考
- 19. /34 ウィンドウ関数の基本 19 集計関数 COUNT ? 結果セットのパーティション毎の項目数を返します。 SUM ? 結果セットのパーティション毎の合計値を返します。 AVG ? 結果セットのパーティション毎の平均値を返します。 MIN ? 結果セットのパーティション毎の最小値を返します。 MAX ? 結果セットのパーティション毎の最大値を返します。 など 他の関数や詳細はMSドキュメントを参照 https://learn.microsoft.com/ja-jp/sql/t-sql/functions/aggregate-functions-transact-sql 参考
- 20. /34 ウィンドウ関数の基本 20 集計関数 SELECT ID,部品名, 調達先名, 単価, COUNT(単価) OVER(PARTITION BY 部品名) AS 件数, SUM(単価) OVER(PARTITION BY 部品名) AS 合計, AVG(単価) OVER(PARTITION BY 部品名) AS 平均, MIN(単価) OVER(PARTITION BY 部品名) AS 最小, MAX(単価) OVER(PARTITION BY 部品名) AS 最大 FROM 部品調達単価 ID 部品名 調達先名 単価 件数 合計 平均 最小 最大 1 部品A B社 100 2 240 120 100 140 2 部品A A社 140 2 240 120 100 140 3 部品B B社 200 1 200 200 200 200 4 部品C A社 200 2 380 190 180 200 5 部品C B社 180 2 380 190 180 200 参考
- 21. /34 ウィンドウ関数の基本 21 分析関数 LAG ? 結果セットのパーティション毎に現在の行の前にある指定された オフセットの行にアクセスします。 LEAD ? 結果セットのパーティション毎に現在の行の後に続く指定された オフセットの行にアクセスします。 FIRST_VALUE ? 結果セットのパーティション毎に最初の値を返します。 LAST_VALUE ? 結果セットのパーティション毎に最後の値を返します。 など 他の関数や詳細はMSドキュメントを参照 https://learn.microsoft.com/ja-jp/sql/t-sql/functions/analytic-functions-transact-sql 参考
- 22. /34 ウィンドウ関数の基本 22 分析関数 SELECT ID,部品名, 調達先名, 単価, LAG(単価,1) OVER(PARTITION BY 部品名 ORDER BY 単価), LEAD(単価,1) OVER(PARTITION BY 部品名 ORDER BY 単価), FIRST_VALUE(単価) OVER(PARTITION BY 部品名 ORDER BY 単価), LAST_VALUE(単価) OVER(PARTITION BY 部品名 ORDER BY 単価) FROM 部品調達単価 ID 部品名 調達先名 単価 LAG LEAD FIRST_VALUE LAST_VALUE 1 部品A B社 100 NULL 140 100 100 2 部品A A社 140 100 NULL 100 140 3 部品B B社 200 NULL NULL 200 200 5 部品C B社 180 NULL 200 180 180 4 部品C A社 200 180 NULL 180 200 参考
- 23. /34 GROUP BYとの違い 23 ? GROUP BYを使った集計関数との違いは？ 部品名 調達先名 単価 部品A B社 100 部品A A社 140 部品B B社 200 部品C A社 200 部品C B社 180 部品調達単価 GROUP BYのイメージ 部品名 平均 部品A 120 部品B 200 部品C 190 部品ごとの平均単価を集計 部品名 調達先名 単価 平均 部品A B社 100 120 部品A A社 140 120 部品B B社 200 200 部品C A社 200 190 部品C B社 180 190 GROUP BYで集約し、計 算 ウィンドウ関数のイメージ SELECT 部品名, 調達先名, 単価, AVG(単価) OVER(PARTITION BY 部品名) AS 平均 FROM 部品調達単価 部品ごとの平均単価も取得 PARTITION BYで分割し た範囲の集計結果を取得 SELECT 部品名, AVG(単価) AS 平均 FROM 部品調達単価 GROUP BY 部品名 参考
- 24. /34 GROUP BYとの違い 24 ? ウィンドウ関数と同じクエリを GROUP BY で表現すると。。。 SELECT 単価.部品名, 単価.調達先名, 単価.単価, 平均単価.平均 FROM 部品調達単価 AS 単価 INNER JOIN ( SELECT 部品名, AVG(単価) AS 平均 FROM 部品調達単価 GROUP BY 部品名 ) AS 平均単価 ON 単価.部品名 = 平均単価.部品名 SELECT 部品名, 調達先名, 単価, AVG(単価) OVER(PARTITION BY 部品名) AS 平均 FROM 部品調達単価 参考
- 25. 2． WINDOW句の紹介 25
- 27. /34 WINDOW句の紹介 27 WINDOW句の構文 ■WINDOW句の概要 SELECT ??? WINDOW [ウィンドウ名] AS ( [参照ウィンドウ名] ※参照されるウィンドウはWINDOW句で定義さ れている ウィンドウの中にあることが必要 PARTITION BY句 ORDER BY句 ROWS/RANGE句 ) ??? 詳細はMSのドキュメントを 参照
- 29. /34 WINDOW句の紹介 29 MSドキュメントの「一般的な注釈」の主な点を解説 ? WINDOW 句では、複数の名前付きウィンドウを定義できます。 SELECT SalesOrderID AS OrderNumber, ProductID, OrderQty AS Qty, SUM(OrderQty) OVER win1 AS Total, AVG(OrderQty) OVER win2 AS Avg FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664) AND ProductID LIKE '71%' WINDOW win1 AS (ORDER BY SalesOrderID, ProductID), win2 AS (PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID) Win1とwin2の複数を定義
- 30. /34 WINDOW句の紹介 30 MSドキュメントの「一般的な注釈」の主な点を解説 ? OVER 句の名前付きウィンドウに追加コンポーネントを追加するには、後ろに追加の仕様が 続く window_name を使用します。 しかし、WINDOW 句で指定されたプロパティを OVER 句で再定義することはできません。 SELECT SalesOrderID AS OrderNumber, ProductID, OrderQty AS Qty, SUM(OrderQty) OVER win1 AS Total, AVG(OrderQty) OVER(win1 PARTITION BY SalesOrderID) AS Avg FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664) AND ProductID LIKE '71%' WINDOW win1 AS (ORDER BY SalesOrderID, ProductID) SELECT SalesOrderID AS OrderNumber, ProductID, OrderQty AS Qty, SUM(OrderQty) OVER win1 AS Total, AVG(OrderQty) OVER(win1 PARTITION BY SalesOrderID ORDER BY SalesOrderID DESC) AS Avg FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664) AND ProductID LIKE '71%' WINDOW win1 AS (ORDER BY SalesOrderID, ProductID) ORDER BYはwin1で定義済 PARTITION BYを追加
- 31. /34 WINDOW句の紹介 31 MSドキュメントの「一般的な注釈」の主な点を解説 ? クエリで複数のウィンドウが使用されている場合、ある名前付きウィンドウで、 window_name を使用して別の名前付きウィンドウを参照できます。 この場合、参照される window_name は、参照元ウィンドウのウィンドウ定義で指定する必要があります。 ある ウィンドウで定義されているウィンドウ コンポーネントは、それを参照する別のウィンドウ では再定義できません。 SELECT SalesOrderID AS OrderNumber, ProductID, OrderQty AS Qty, SUM(OrderQty) OVER win1 AS Total, AVG(OrderQty) OVER win2 AS Avg FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664) AND ProductID LIKE '71%' WINDOW win1 AS (ORDER BY SalesOrderID, ProductID), win2 AS (win1 PARTITION BY SalesOrderID) SELECT SalesOrderID AS OrderNumber, ProductID, OrderQty AS Qty, SUM(OrderQty) OVER win1 AS Total, AVG(OrderQty) OVER win2 AS Avg FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664) AND ProductID LIKE '71%' WINDOW win1 AS (ORDER BY SalesOrderID, ProductID), win2 AS (win1 PARTITION BY SalesOrderID ORDER BY SalesOrderID DESC, ProductID) ORDER BYはwin1で定義済 win2でwin1を参照
- 32. /34 WINDOW句の紹介 32 MSドキュメントの「一般的な注釈」の主な点を解説 ? ウィンドウ句でウィンドウが定義されている順序に基づいて、前方および後方のウィンドウ 参照が許可されます。 つまり、ウィンドウでは、定義されている順序に関係なく、 reference_window_name として、その一部である <window_expression> で定義されている 他のウィンドウを使用できます。 循環参照、および単一ウィンドウでの複数のウィンドウ参 照の使用は許可されません。 SELECT SalesOrderID AS OrderNumber, ProductID, OrderQty AS Qty, SUM(OrderQty) OVER win1 AS Total, AVG(OrderQty) OVER win3 AS Avg FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664) AND ProductID LIKE '71%' WINDOW win3 AS (win2), win1 AS (ORDER BY SalesOrderID, ProductID), win2 AS (win1 PARTITION BY SalesOrderID) SELECT SalesOrderID AS OrderNumber, ProductID, OrderQty AS Qty, SUM(OrderQty) OVER win1 AS Total, AVG(OrderQty) OVER win3 AS Avg FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664) AND ProductID LIKE '71%' WINDOW win3 AS (win2 win1), win1 AS (ORDER BY SalesOrderID, ProductID), win2 AS (PARTITION BY SalesOrderID) 後方で定義したwin2を参照 複数のウィンドウ参照
- 33. /34 WINDOW句の使用例 33 WINDOW句を使わない従来の記法 例：営業担当ごとの売上推移データ（日ごとと累積の売上額）を取 得する。 売上ID 売上日 営業担当 売上額 1 2023/2/1 Yamada 100 2 2023/2/2 Yamada 150 3 2023/2/2 Suzuki 120 4 2023/2/3 Suzuki 200 5 2023/2/5 Yamada 80 6 2023/2/4 Suzuki 90 7 2023/2/4 Tanaka 110 8 2023/2/5 Yamada 50 9 2023/2/6 Suzuki 90 10 2023/2/6 Tanaka 40 営業担当 売上日 売上額 累積売上額 Suzuki 2023/2/2 120 120 Suzuki 2023/2/3 200 320 Suzuki 2023/2/4 90 410 Suzuki 2023/2/6 90 500 Tanaka 2023/2/4 110 110 Tanaka 2023/2/6 40 150 Yamada 2023/2/1 100 100 Yamada 2023/2/2 150 250 Yamada 2023/2/5 80 330 Yamada 2023/2/5 50 380 売上テーブル 営業担当ごとの売上推移データ SELECT 営業担当, 売上日, 売上額, SUM(売上額) OVER (PARTITION BY 営業担当 ORDER BY 売上日, 売上ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累積売上額 FROM 売上 ORDER BY 営業担当, 売上日, 売上ID 参考
- 34. /34 WINDOW句の使用例 WINDOW句を使わない従来の記法 例：営業担当ごとの売上推移データ（日ごとと累積の売上額）を取 得する。 WINDOW句を使用した記法 SELECT 営業担当, 売上日, 売上額, SUM(売上額) OVER (PARTITION BY 営業担当 ORDER BY 売上日, 売上ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累積売上額 FROM 売上 ORDER BY 営業担当, 売上日, 売上ID 参考 SELECT 営業担当, 売上日, 売上額, SUM(売上額) OVER 累積条件 AS 累積売上額 FROM 売上 WINDOW 累積条件 AS (PARTITION BY 営業担当 ORDER BY 売上日, 売上ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ORDER BY 営業担当, 売上日, 売上ID
- 35. /34 WINDOW句の使用例 35 WINDOW句を使わない従来の記法 例：月ごとに直近3カ月の売上の合計と移動平均を取得する。 年月 売上額 2022年10月 100 2022年11月 200 2022年12月 300 2023年01月 200 2023年02月 400 2023年03月 200 年月 売上額 直近3ヶ月の 合計 直近3ヶ月の 移動平均 2022年10月 100 100 100 2022年11月 200 300 150 2022年12月 300 600 200 2023年01月 200 700 233 2023年02月 400 900 300 2023年03月 200 800 266 売上テーブル 月ごとの直近3カ月の売上合計と移動平均 SELECT *, SUM(売上額) OVER(ORDER BY 年月 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS 直近3ヶ月の合計, AVG(売上額) OVER(ORDER BY 年月 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS 直近3ヶ月の移動平均 FROM 売上 ORDER BY 年月 参考
- 36. /34 WINDOW句の使用例 36 WINDOW句を使わない従来の記法 例：月ごとに直近3カ月の売上の合計と移動平均を取得する。 WINDOW句を使用した記法 SELECT *, SUM(売上額) OVER(ORDER BY 年月 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS 直近3ヶ月の合計, AVG(売上額) OVER(ORDER BY 年月 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS 直近3ヶ月の移動平均 FROM 売上 ORDER BY 年月 参考 SELECT *, SUM(売上額) OVER 直近3か月 AS 直近3ヶ月の合計, AVG(売上額) OVER 直近3か月 AS 直近3ヶ月の移動平均 FROM 売上 WINDOW 直近3か月 AS (ORDER BY 年月 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ORDER BY 年月
- 37. お知らせ 37