今回の初心者訓練所#13は、SQLの「ウィンドウ関数」の1つ、SUM関数とOVER句について説明していきます。集計関数にOVER句がある場合、集計ウィンドウ関数とも呼ばれます。データ分析には欠かせない便利な関数なので、是非、使い方をマスターしてください。今回もデータベースは、これまでに引き続き、いつもの「SQLite」を使用していきますので、直ぐに実践できます。
ウィンドウ関数とは？
「Sqlite」の公式ホームページには、以下の説明があります。
『ウィンドウ関数とは、SELECT ステートメントの結果セット内の 1 つ以上の行の「ウィンドウ」から入力値が取得される SQL 関数です。』
簡単に書くと、行ごとに集計を行い、その結果を各行に返すことができる便利な機能です。集計関数とは異なり、ウィンドウ関数は行をまとめず、それぞれの行で集計を実行します。具体的には、以下の特徴があります。
（１）集計対象の範囲を指定可能
集計関数ではGROUP BYで指定した同じカラム値を持つ全行を1つに集計しますが、ウィンドウ関数ではOVER句を使用して、集計対象となる行の範囲を任意に指定できます。
（２）行間参照が可能
ウィンドウ関数は、他の行を参照できるため、行間で計算された集計値を取得できます。例えば、1つ前の行や1つ後の行から得た値の合計を計算することができます。
では、早速、ウィンドウ関数を試してみましょう。
SUM関数とOVER句
今回は、SUM関数とOVER句を使用して、ソートした初めのデータがら該当データまでの売上金額の累計を算出してみましょう。準備として、売上テーブルを作成して、確認に使用するデータを挿入します。
【売上テーブルのcreate文とinsert文】
| create table tbl_sales ( sales_no varchar not null, sales_date varchar not null, dept_code varchar not null, sales_amt integer default 0 not null, primary key(sales_no)); |
| insert into tbl_sales ( sales_no, sales_date, dept_code, sales_amt) values ('S0001','2024-03-01','D001','1000') ,('S0002','2024-03-02','D002','1500') ,('S0003','2024-03-03','D001','1300') ,('S0004','2024-03-04','D001','1200') ,('S0005','2024-03-05','D002','2000') ,('S0006','2024-03-06','D002','2400') ,('S0007','2024-03-07','D001','1800'); |
出力フォーマットを調整して、select文で登録内容を確認してみましょう。
|
.headers on (ヘッダ情報を出力) select * from tbl_sales; |
insert文で作成した、1週間分の7件のデータを確認できます。
（１）はじめに、SUM関数で全データの売上金額の合計を求めてみましょう。
| select sum ( sales_amt ) as TOTAL_AMT from tbl_sales; |
売上金額の合計が、11,200であることが確認できます。
（２）SUM関数にOVER句を追加して売上データ一覧の各行に合計金額を表示してみましょう。OVER句には何も記述しないで実行してみましょう。
| select sales_date, sales_amt, sum ( sales_amt ) over ( ) as TOTAL_AMT from tbl_sales; |
OVER句に何も記述しない場合は、売上データ一覧の各行に集計した売上合計金額が表示されていることが確認できます。
（３）次にOVER句にORDER BYを使用して、各データの売上日までの売上金額の累計を求めて各行に表示してみましょう。
| select sales_date, sales_amt ,sum ( sales_amt ) over( order by sales_date ) as CUMUL_AMT from tbl_sales order by sales_date asc; |
OVER句に「order by sales_date」と売上日の並び順を指定することにより、売上データを売上日順に並べて、先頭のデータから現在のデータまでの売上金額が累計されて表示されます。通常のORDER BYと同様に最後にASCを指定しない場合は、昇順となります。
2024-03-01は、先頭のデータなので、累積金額は売上金額と同じ、1,000になります。
2024-03-02は、03-01の1,000と03-02の1,500を加算し、累積金額は2,500になります。
2024-03-03は、03-01の1,000と03-02の1,500と03-03の1,300を加算し、累積金額は3,800になります。
このように、累積金額を求めるにはとても便利は関数なので是非、覚えてください。
今回は日毎の累積金額を求めましたが、月毎のデータがあれば、ORDER BY に年月を指定して、月次累積金額も簡単に求めることができます。
（４）OVER句のORDER BYを降順にするとどうなるでしょうか？
| select sales_date,sales_amt ,sum ( sales_amt ) over ( order by sales_date desc ) as CUMUL_AMT from tbl_sales order by sales_date asc; |
先頭から該当データまでの累積になるので、売上日を降順にした場合は、
2024-03-01は、先頭が03-07のデータとなるので、03-07から03-01までの累積となり、
1,800 + 2,400 + 2,000 + 1,200 + 1,300 + 1,500 + 1,000 = 11,200になります。
2024-03-02は、03-07から03-02までの累積となり、10,200になります。
あまり、このような集計はしないかもしれませんが、参考までに載せています。
（５）更に今度は、本体のSELECT文のORDER BYを降順にするとどうなるでしょうか？
| select sales_date,sales_amt ,sum ( sales_amt ) over ( order by sales_date desc ) as CUMUL_AMT from tbl_sales order by sales_date desc; |
売上日が降順で表示されています。また、累積金額も売上日の降順と指定されているので、降順で順番に累積されているのが確認できます。このように、OVER句の並び順は本体のSELECT文の並び順とは別に指定できますので、覚えておいてください。
「集計ウィンドウ関数」の１つ、SUM関数とOVER句のORDER BYを使用して累積を求める方法の説明は以上となります。
おすすめの学習コンテンツ
本記事を読み終わって、さらに学びたい方、次は問題にチャレンジしてみたい方には、以下の無料コンテンツをご用意しておりますので、ぜひ活用してみてください！
SQLiteについて
・本記事で使用したデータベース「SQLite」について解説する資料です。
SQLiteとは？他のデータベースとの違いも解説
SQLの教科書
・一般的な解説から、実際にSQLを動かす操作イメージまで幅広く解説する資料です。
「SQLの教科書」ビックデータ時代の必須科目
SQL無料トライアルテスト
・TOPSIC-SQLの無料トライアルテストをご受験していただけます。
TOPSIC-SQL無料トライアルテスト
SQLコンテスト
・簡単な会員登録をするだけでSQLのオンラインコンテストに無料で参加できます。
過去に開催されたコンテストの問題にもチャレンジできます。
TOPSIC SQL CONTEST
SQL基礎解説YouTube
・「TOPSIC-SQL」を元にしたオンラインウェビナーです。
SQL-BOOT CAMP #1
ビッグデータ時代の必須科目SQLを学ぶ「実戦で通用する武器を身につけよう！」
あとがき
初心者訓練所の第13弾となる今回は、データ分析の世界で不可欠なウィンドウ関数の基礎にチャレンジしました。ウィンドウ関数の初回ということで、「ORDER BY」を用いたOVER句の使い方にスポットを当て、その強力な機能を紹介しました。ウィンドウ関数には「PARTITION BY」や「RANGE」など、さらに多くの魅力的な要素がありますが、それらの詳細は次回以降の記事でじっくりとご紹介していく予定です。
データの集計や分析をより効率的に、そして楽しく進めるためのウィンドウ関数。これからもその魅力的な力を皆さんと共に探求していきたいと思います。次回の記事も、どうぞお楽しみに！
(株)システムインテグレータ TOPSICチーム 元鬼軍曹 久保 司
