今回の「初心者訓練所」#14は、前回に引き続きOVER句で使用する、集計対象のデータを範囲指定する３つの方法について説明していきます。これらを理解すると、OVER句を自由自在に使いこなせるようになると思いますので、しっかりマスターしてください。今回もデータベースは、いつもの「SQLite」を使用していきますので、実践しながら確認できます。
PARTITION BY
PARTITION BYはOVER句の中で使用され、GROUP BY句の様に指定した項目の値で区切ってデータを集計します。準備として、前回と同様に売上テーブルを作成して、確認に使用するデータを挿入します。（売上テーブルが作成済の場合は、既存のデータを削除してから、下記のINSERT文を実行してください。）
【売上テーブルの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-05','D002','1500' ) ,( 'S0003','2024-03-06','D001','1300' ) ,( 'S0004','2024-03-07','D001','1200' ) ,( 'S0005','2024-03-10','D002','2000' ) ,( 'S0006','2024-03-13','D002','2400' ) ,( 'S0007','2024-03-15','D001','1800' ); |
出力フォーマットを調整して、SELECT文で登録内容を確認してみましょう。
|
.headers on (ヘッダ情報を出力) SELECT * FROM tbl_sales; |
INSERT文で作成した、7件のデータを確認できます。
（１）はじめに、SUM関数とGROUP BY句で部門毎に売上金額の合計を求めてみましょう。
| SELECT dept_code,SUM ( sales_amt ) AS DEPT_AMT FROM tbl_sales GROUP BY dept_code; |
部門コード毎の売上合計金額が確認できます。
「D001」：1000 + 1300 + 1200 + 1800 = 5300
「D002」：1500 + 2000 + 2400 = 5900
（２）次に、OVER句にPARTITION BYを指定して実行してみましょう。
PARTITION BYの後に、dept_codeを指定して部門コード毎に売上金額を集計します。
| SELECT dept_code,sales_date,sales_amt ,SUM ( sales_amt ) OVER ( PARTITION BY dept_code ) AS DEPT_AMT FROM tbl_sales; |
各行のDEPT_AMTに部門コード毎に集計した、売上金額が表示されていることが確認できます。
（３） 続いて、前回登場したORDER BY句を追加して売上日順に集計してみましょう。
| SELECT dept_code,sales_date,sales_amt ,SUM ( sales_amt ) OVER ( PARTITION BY dept_code ORDER BY sales_date ) AS DEPT_AMT FROM tbl_sales; |
売上金額が売上日順に累計されており、部門コードが変わると累積金額がリセットされて再度累計されていることが確認できます。
ROWS
ROWSは、ORDER BY句を使用する際に集計対象の範囲を指定する場合に使用します。
「ROWS 2 PRECEDING」と指定すると、現在行から2行前までのデータを集計対象とします。
また、「ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING」と指定した場合は、現在行とそれぞれ現在行から指定した数値までの前後のデータを集計対象とします。
実際に確認してみましょう。
| SELECT dept_code,sales_date,sales_amt ,SUM ( sales_amt ) OVER ( ORDER BY sales_date ROWS 2 PRECEDING ) AS PRE_AMT ,SUM ( sales_amt ) OVER ( ORDER BY sales_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS PF_AMT FROM tbl_sales; |
PRE_AMTの値は、現在行から前の２行分のデータを集計した値になっています。ただし、現在行より前のデータが2行存在しない場合は、存在するデータのみでの集計となりますので注意してください。
一方、PF_AMTの値は、現在行と前後１行のデータを集計した値になっています。こちらも、該当するデータが存在しない場合は、存在するデータのみでの集計となります。
範囲の指定方法は上記のように1、2といった数値の代わりに、文字で指定することも出来ますので試してみてください。
「CURRENT ROW」：現在行を指します
「UNBOUNDED PRECEDING」：先頭行を指します
「UNBOUNDED FOLLOWING」：最終行を指します
RANGE
RANGEもROWSと同様に、ORDER BY句で集計対象の範囲を指定する場合に使用します。ただし、ROWSは行数を指定していましたが、RANGEは対象項目の内容を指定します。また、RANGEを利用する場合、ORDER BY句に指定できる項目は１つのみで、数値項目または、日付項目に限定されますので、注意してください。
例を見ながら確認してみましょう。
下記のクエリは、該当レコードの２日前からの売上金額の累計(PRE_AMT)と、該当レコードと前後１日の累計(PF_AMT)を取得するクエリになります。SQLiteでは、日付型がないので、JULIANDAY関数で、VACHAR(TEXT)型で定義していた売上日(sales_date)をユリウス日に変換して記述しています。
| SELECT dept_code , sales_date, JULIANDAY( sales_date ) AS J_DATE,sales_amt , SUM( sales_amt ) OVER ( ORDER BY JULIANDAY( sales_date ) RANGE 2 PRECEDING ) AS PRE_AMT , SUM( sales_amt ) OVER ( ORDER BY JULIANDAY( sales_date ) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING )AS PF_AMT FROM tbl_sales; |
それでは、PRE＿AMTの値について１行目から確認してみましょう。
| SALES_DATE | PRE_AMT | 説 明 |
| 2024-03-01 | 1000 | 先頭レコードで2日前までのデータが存在しないため、 自レコードのみの金額の1000となる。 |
| 2024-03-05 | 1500 | 2日前(03-04,03-03)までのデータが存在しないため、 自レコードのみの金額の1500となる。 |
| 2024-03-06 | 2800 | 2日前(03-05,03-04)までのうち03-05が存在するので、 1500 ＋ 1300 ＝ 2800となる。 |
| 2024-03-07 | 4000 | 2日前(03-06,03-05)までのレコードが存在するので、 1500 ＋ 1300 ＋ 1200 ＝ 4000となる。 |
| 2024-03-10 | 2000 | 2日前(03-09,03-08)までのデータが存在しないため、 自レコードのみの金額の2000となる。 |
| 2024-03-13 | 2400 | 2日前(03-12,03-11)までのデータが存在しないため、 自レコードのみの金額の2400となる。 |
| 2024-03-15 | 4200 | 2日前(03-14,03-13)までのうち03-13が存在するので、 2400 ＋ 1800 ＝ 4200となる。 |
次に、PF_AMTの値も確認してみましょう。
| SALES_DATE | PRE_AMT | 説 明 |
| 2024-03-01 | 1000 | 前日翌日(02-29,03-02)のレコードが存在しないため、 自レコードのみの金額の1000となる。 |
| 2024-03-05 | 2800 | 前日翌日(03-04,03-06)のうち03-06が存在するので、 1500 ＋ 1300 ＝ 2800となる。 |
| 2024-03-06 | 4000 | 前日翌日(03-05,03-07)のうレコードが存在するので、 1500 ＋ 1300 ＋ 1200 ＝ 4000となる。 |
| 2024-03-07 | 2500 | 前日翌日(03-06,03-08)のうち03-06が存在するので、 1300 ＋ 1200 ＝ 2500となる。 |
| 2024-03-10 | 2000 | 前日翌日(03-09,03-11)のレコードが存在しないため、 自レコードのみの金額の2000となる。 |
| 2024-03-13 | 2400 | 前日翌日(03-12,03-14)のレコードが存在しないため、 自レコードのみの金額の2400となる。 |
| 2024-03-15 | 1800 | 前日翌日(03-14,03-16)のレコードが存在しないため、 自レコードのみの金額の1800となる。 |
RANGEは、このように指定した項目の内容を判断して集計を行いますので、集計の対象となるデータの行数が不特定の場合に利用しやすい指定方法になります。集計対象により、ROWSやRANGEの使い分けの検討をおこない利用してください。補足となりますが、前回のようにOVER句で範囲指定を使用しない場合は、全データが対象となります。
PARTITION BY とOVER句での範囲指定の方法についての説明は以上となります。
おすすめの学習コンテンツ
本記事を読み終わって、さらに学びたい方、次は問題にチャレンジしてみたい方には、以下の無料コンテンツをご用意しておりますので、ぜひ活用してみてください！
SQLiteについて
・本記事で使用したデータベース「SQLite」について解説する資料です。
SQLiteとは？他のデータベースとの違いも解説
SQLの教科書
ビックデータ時代の必須科目「SQLの教科書」（初級編）
ビックデータ時代の必須科目「SQLの教科書」（中級編 UPDATE文）NEW！！
ビックデータ時代の必須科目「SQLの教科書」（中級編 テーブル結合）NEW！！
SQL無料トライアルテスト
・TOPSIC-SQLの無料トライアルテストをご受験していただけます。
TOPSIC-SQL無料トライアルテスト
SQLコンテスト
・簡単な会員登録をするだけでSQLのオンラインコンテストに無料で参加できます。
過去に開催されたコンテストの問題にもチャレンジできます。
TOPSIC SQL CONTEST
SQL基礎解説YouTube
・「TOPSIC-SQL」を元にしたオンラインウェビナーです。
SQL-BOOT CAMP #1
ビッグデータ時代の必須科目SQLを学ぶ「実戦で通用する武器を身につけよう！」
あとがき
「初心者訓練所」の第14弾では、前回紹介したOVER句を深掘りしてみました。新しい単語や概念が飛び交い、SQLの世界は時に複雑に思えるかもしれません。しかし、実際に手を動かし、コードを実行することで、知識はより深く、そして確実に身についていきます。手元に「SQLite」があれば、今すぐにでもその力を試すことができます。ぜひこの機会に、データの集計や分析を変革するウィンドウ関数の魅力を体験してください。ウィンドウ関数という強力なツールを使いこなし、データの海をより効率的に、そして楽しく航海しましょう。次回の記事も、どうぞお楽しみに！
(株)システムインテグレータ TOPSICチーム 元鬼軍曹 久保 司
