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    |

このようにグループ化します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411106/442a1d4b-c40b-eb98-d47b-2d7446225383.png)
どういうケースで使用するかは例題を参照ください。


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

```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        |

## スキーマ定義

```SQL
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の実行

```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        |

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

```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 *
  FROM SUB2
```

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


| 会社名 | 年度 | 売上 | 前年売上 | 条件 | グループ番号 |
| ------ | ---- | ---- | -------- | ---- | ------------ |
| 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            |


## こちらでお試しできます
http://sqlfiddle.com/#!17/093cf/9/0

## 関連記事
- [こちらの記事](https://qiita.com/annie/items/2f14ffc500667b24224e#comment-5f672ef514a6e1cf1a6a)のコメント欄を書くときに思いついたアイデアです
- [SQLの 分析関数（ウインドウ関数）でちょっと応用が利きそうな便利な使い方を思いついた](https://qiita.com/takahasinaoki/items/01fe792c9e83bb15c7c5)

- [SQLで特定の条件を満たすレコードを1件でも持つグループのレコードをすべて抽出する](https://qiita.com/takahasinaoki/items/c74e177577aa34b5520a)