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

はじめに

この記事は、「分析関数（ウインドウ関数）をわかりやすく説明してみた (Qiita)」に記載されたコードをSQLite3で試すにあたり、元のままでは不都合な部分を書き替えたものです。
書き替えたコードだけを記載していますので、元の記事と併せて読んでいただくことが前提となります。
分かり易く大変有用な元記事を書かれた@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()などのテキスト化の処理が必要になる箇所があります。

テストデータ作成

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

日付の形式による違いを比較するために記載しただけで、変更の必要なものではありません。

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()を使用しています。

行間参照グループ化

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

フラグ化

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

グループ化

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 LAST_VALUE NTH_VALUE		✔	最初、最後、<N>番目の行
LAG LEAD		✔	前の行、後ろの行
MAX MIN	✔	✔	最大値、最小値
MEDIAN			中間値
NTILE		✔	<N>分割した番号
RANK		✔	ORDER順位 (欠番あり)
DENSE_RANK		✔	ORDER順位 (欠番なし)
ROW_NUMBER		✔	行番号
SUM TOTAL	✔	✔	合計
GROUP_CONCAT	✔	✔	値の連結リスト