SQLで分析関数を使って、特定の条件を満たしたレコードの前と後で別々のグループとしてグループ化する

はじめに

タイトルからは何をしたいか分かりにくいと思いますが、以下のようなデータがあった時に

番号	分類	データ	条件
1	A001	xxxxxxx	0
2	A001	xxxx	0
3	A001	xxxxx	1
4	A001	xxxxx	0
5	A002	xxxx	0
6	A002	xxxxxxxxxx	0
7	A002	xxxx	0
8	A002	xxxxxxx	1
9	A002	xxxxxxxxxx	1
10	A003	xxxx	1

このようにグループ化します。

どういうケースで使用するかは例題を参照ください。

汎用的なひな型

上記のようなグループ化を実行するSQLのひな型

SELECT *
     , SUM(CASE WHEN [条件] THEN 1 ELSE 0 END)
         OVER (PARTITION BY [分類項目] ORDER BY [ソート項目]) AS グループ番号
  FROM XXテーブル

条件
[条件]のところに必要な条件を記述

分類項目
[分類項目]のところにグループ分けしたいカラムを記述（複数カラムの指定可）

ソート項目
[ソート項目]のところにソートしたいカラムを記述（複数カラムの指定可）

例題

売上テーブルのうち、前年度より売上が増加している期間の開始年度と終了年度およびその期間の年数を一覧化する。
前提として、年度の歯抜けはないものとする。

売上テーブル

会社名	年度	売上（億円）
AA電器	2001	31
AA電器	2002	32
AA電器	2003	40
AA電器	2004	38
AA電器	2005	40
BB電器	2001	8
BB電器	2002	7
BB電器	2003	10
BB電器	2004	11
BB電器	2005	10

実行結果

会社名	開始年度	終了年度	増加年数
AA電器	2001	2003	3
AA電器	2004	2005	2
BB電器	2001	2001	1
BB電器	2002	2004	3
BB電器	2005	2005	1

スキーマ定義

CREATE TABLE 売上テーブル(会社名 varchar(4), 年度 int, 売上 int);
INSERT INTO 売上テーブル(会社名,年度,売上)
VALUES
    ('AA電器', 2001, 31),
    ('AA電器', 2002, 32),
    ('AA電器', 2003, 40),
    ('AA電器', 2004, 38),
    ('AA電器', 2005, 40),
    ('BB電器', 2001, 8),
    ('BB電器', 2002, 7),
    ('BB電器', 2003, 10),
    ('BB電器', 2004, 11),
    ('BB電器', 2005, 10);

SQLの実行

WITH SUB AS ( 
SELECT *
     , LAG(売上) OVER(PARTITION BY 会社名 ORDER BY 年度) AS 前年売上
  FROM 売上テーブル
), SUB2 AS (
SELECT *
--   , CASE WHEN 売上 < 前年売上 THEN 1 ELSE 0 END AS 条件
     , SUM(CASE WHEN 売上 < 前年売上 THEN 1 ELSE 0 END)
         OVER (PARTITION BY 会社名 ORDER BY 年度) AS グループ番号
  FROM SUB
)

SELECT 会社名
     , MIN(年度) AS 開始年度 
     , MAX(年度) AS 終了年度
     , COUNT(*) AS 増加年数
  FROM SUB2
 GROUP BY 会社名, グループ番号

実行結果

会社名	開始年度	終了年度	増加年数
AA電器	2001	2003	3
AA電器	2004	2005	2
BB電器	2001	2001	1
BB電器	2002	2004	3
BB電器	2005	2005	1

参考：サブクエリ部分の実行結果

WITH SUB AS ( 
SELECT *
     , LAG(売上) OVER(PARTITION BY 会社名 ORDER BY 年度) AS 前年売上
  FROM 売上テーブル
), SUB2 AS (
SELECT *
     , CASE WHEN 売上 < 前年売上 THEN 1 ELSE 0 END AS 条件
     , SUM(CASE WHEN 売上 < 前年売上 THEN 1 ELSE 0 END)
         OVER (PARTITION BY 会社名 ORDER BY 年度) AS グループ番号
  FROM SUB
)

SELECT *
  FROM SUB2

分析関数のLAG()は１つ前のレコードの値を取得します。
分析関数のSUM()はORDER BYを指定することにより先頭行からカレント行までの合計を計算します。この機能を利用してグループ化を実現しています。

会社名	年度	売上	前年売上	条件	グループ番号
AA電器	2001	31	NULL	0	0
AA電器	2002	32	31	0	0
AA電器	2003	40	32	0	0
AA電器	2004	38	40	1	1
AA電器	2005	40	38	0	1
BB電器	2001	8	NULL	0	0
BB電器	2002	7	8	1	1
BB電器	2003	10	7	0	1
BB電器	2004	11	10	0	1
BB電器	2005	10	11	1	2

こちらでお試しできます

関連記事

• こちらの記事のコメント欄を書くときに思いついたアイデアです

• SQLの 分析関数（ウインドウ関数）でちょっと応用が利きそうな便利な使い方を思いついた

• SQLで特定の条件を満たすレコードを1件でも持つグループのレコードをすべて抽出する