SQLite3版 分析関数(ウインドウ関数) コードサンプル

## はじめに
この記事は、「[分析関数（ウインドウ関数）をわかりやすく説明してみた](https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5) (Qiita)」に記載されたコードを[SQLite3](https://www.sqlite.org/windowfunctions.html)で試すにあたり、元のままでは不都合な部分を書き替えたものです。
書き替えたコードだけを記載していますので、元の記事と併せて読んでいただくことが前提となります。
分かり易く大変有用な元記事を書かれた@tlokwengさんへ、この場を借りて感謝申し上げます。

### 検証環境
この記事のコードは、以下の環境で検証いたしました。

- Windows 10
- DB Browser for SQLite
    - バージョン 3.12.0
    - x86_64-little_endian-llp64 向けビルド, x86_64 で動作中
    - Qt バージョン 5.12.8
    - SQLite バージョン 3.32.2

### テストデータ
日付データが、テキストだと`RANGE`がうまくいかないので、ユリウス通日のデータを用意して、比較のためにテキストも残してあります。
なお、`TEXT`型の日付を元の名前のまま残しているため、そちらが使われている箇所は基本的にそのまま動作し、この記事では言及されません。`TEXT`を`REAL`に置き換える場合は、`date()`などのテキスト化の処理が必要になる箇所があります。

```sql:テストデータ作成
CREATE TABLE [test_orders] (
	[order_id] INTEGER,
	[item] TEXT,
	[qty] INTEGER,
	[order_date] TEXT, -- 日時をtextで保持
	[_order_date] REAL-- 日時をjuliandayで保持
);

INSERT INTO "main"."test_orders" ("order_id", "item", "qty", "order_date", "_order_date") VALUES ('1001', 'Apple', '4', '2018-01-10 00:00:00', '2458128.5');
INSERT INTO "main"."test_orders" ("order_id", "item", "qty", "order_date", "_order_date") VALUES ('1005', 'Banana', '8', '2018-01-20 00:00:00', '2458138.5');
INSERT INTO "main"."test_orders" ("order_id", "item", "qty", "order_date", "_order_date") VALUES ('1010', 'Banana', '2', '2018-02-01 00:00:00', '2458150.5');
INSERT INTO "main"."test_orders" ("order_id", "item", "qty", "order_date", "_order_date") VALUES ('1021', 'Apple', '10', '2018-02-15 00:00:00', '2458164.5');
INSERT INTO "main"."test_orders" ("order_id", "item", "qty", "order_date", "_order_date") VALUES ('1025', 'Apple', '6', '2018-02-22 00:00:00', '2458171.5');
INSERT INTO "main"."test_orders" ("order_id", "item", "qty", "order_date", "_order_date") VALUES ('1026', 'Apple', '5', '2018-02-23 00:00:00', '2458172.5');
```
## OVER句
### WINDOW (FRAME)
#### RANGE
日付の形式による違いを比較するために記載しただけで、変更の必要なものではありません。

```sql:RANGE指定
SELECT order_id, item, qty
	, date(order_date) as "order_date(TEXT)"
	, SUM(qty) OVER (ORDER BY order_date RANGE 10 PRECEDING) as "sum"
	, COUNT(*) OVER (ORDER BY order_date RANGE 10 PRECEDING) as "cnt"
	, date(_order_date) as "order_date(REAL)"
	, SUM(qty) OVER (ORDER BY _order_date RANGE 10 PRECEDING) as "sum"
	, COUNT(*) OVER (ORDER BY _order_date RANGE 10 PRECEDING) as "cnt"
FROM test_orders;
```

|order_id	|item	|qty	|order_date(TEXT)	|sum	|cnt	|order_date(REAL)	|sum	|cnt
|--:|:--|--:|:-:|--:|--:|:-:|--:|--:|
|1001	|Apple	|4	|2018-01-10	|4	|1	|2018-01-10	|4	|1
|1005	|Banana	|8	|2018-01-20	|8	|1	|2018-01-20	|12	|2
|1010	|Banana	|2	|2018-02-01	|2	|1	|2018-02-01	|2	|1
|1021	|Apple	|10	|2018-02-15	|10	|1	|2018-02-15	|10	|1
|1025	|Apple	|6	|2018-02-22	|6	|1	|2018-02-22	|16	|2
|1026	|Apple	|5	|2018-02-23	|5	|1	|2018-02-23	|21	|3

## 分析関数がよく使われる例
### 行間参照によるグループ化
`DECODE()`の代わりに`iif()`を、`LISTAGG()`の代わりに`group_concat()`を使用しています。

```sql:行間参照グループ化
SELECT grp,
	item,
	date (MIN (order_date)) first_order_date,
	date (MAX (order_date)) last_order_date,
	SUM (qty) sum_qty,
	group_concat(order_id, ',') orders
FROM (
	SELECT order_id, item, order_date, qty,
		SUM (flag) OVER (ORDER BY order_id) grp
	FROM (
		SELECT order_id, item, order_date, qty, 
			LAG (item) OVER (ORDER BY order_id) previous,
			iif(LAG (item) OVER (ORDER BY order_id) = item, 0, 1) flag
		FROM test_orders
	)
)
GROUP BY grp;
```

|grp	|item	|first_order_date	|last_order_date	|sum_qty	|orders
|--:|:--|:-:|:-:|--:|:--|
|1	|Apple	|2018-01-10	|2018-01-10	|4	|1001
|2	|Banana	|2018-01-20	|2018-02-01	|10	|1005,1010
|3	|Apple	|2018-02-15	|2018-02-23	|21	|1021,1025,1026

```sql:フラグ化
SELECT order_id, item, order_date, qty, 
		LAG (item) OVER (ORDER BY order_id) previous,
		iif(LAG (item) OVER (ORDER BY order_id) = item, 0, 1) flag
	FROM test_orders;
```

order_id	|item	|order_date	|qty	|previous	|flag
|--:|:--|:-:|--:|:--|--:|
1001	|Apple	|2018-01-10 00:00:00	|4	|_NULL_	|1
1005	|Banana	|2018-01-20 00:00:00	|8	|Apple	|1
1010	|Banana	|2018-02-01 00:00:00	|2	|Banana	|0
1021	|Apple	|2018-02-15 00:00:00	|10	|Banana	|1
1025	|Apple	|2018-02-22 00:00:00	|6	|Apple	|0
1026	|Apple	|2018-02-23 00:00:00	|5	|Apple	|0

```sql:グループ化
SELECT order_id, item, order_date, qty,
		SUM (flag) OVER (ORDER BY order_id) grp
	FROM (
		SELECT order_id, item, order_date, qty, 
			LAG (item) OVER (ORDER BY order_id) previous,
			iif(LAG (item) OVER (ORDER BY order_id) = item, 0, 1) flag
		FROM test_orders
	);
```

order_id	|item	|order_date	|qty	|grp
|--:|:--|:-:|--:|--:|
1001	|Apple	|2018-01-10 00:00:00	|4	|1
1005	|Banana	|2018-01-20 00:00:00	|8	|2
1010	|Banana	|2018-02-01 00:00:00	|2	|2
1021	|Apple	|2018-02-15 00:00:00	|10	|3
1025	|Apple	|2018-02-22 00:00:00	|6	|3
1026	|Apple	|2018-02-23 00:00:00	|5	|3

※「パターンマッチ検索（参考）」は実現できませんでした。

## 分析関数リスト
この表の元の意味について、私が誤解している可能性があります。
この記事では、`OVER`なしで使えるのが「(素の)集計(Aggregate)関数」で、`OVER`がないと使えないのが「分析(Aggregate Window)関数」という区分けです。

| 関数名 | 集計関数 | 分析関数 | 説明 |
|:-:|:-:|:-:|:--|
| AVG | ✔ | ✔ | 平均値 |
| COUNT | ✔ | ✔ | 行カウント  |
| FIRST_VALUE <br> LAST_VALUE <br> NTH_VALUE |  | ✔ | 最初、最後、\<N>番目の行 |
| LAG <BR> LEAD |  | ✔ | 前の行、後ろの行 |
| MAX <BR> MIN  | ✔ | ✔ | 最大値、最小値  |
| MEDIAN | 　 | 　 | 中間値 |
| NTILE |  | ✔  | \<N>分割した番号 |
| RANK | 　 | ✔  | `ORDER`順位 (欠番あり) |
| DENSE_RANK | 　 | ✔  | `ORDER`順位 (欠番なし) |
| ROW_NUMBER |  | ✔  | 行番号 |
| SUM<BR>TOTAL | ✔ | ✔ | 合計 |
| GROUP_CONCAT | ✔ | ✔ | 値の連結リスト |