はじめに
この記事は、「分析関数(ウインドウ関数)をわかりやすく説明してみた (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
日付の形式による違いを比較するために記載しただけで、変更の必要なものではありません。
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 | ✔ | ✔ | 値の連結リスト |