一覧DB-10-1 · 発展SQLと別のデータモデル

ウィンドウ関数による順位と累計

二店舗の七明細を使い、店舗合計、順位と同順位、前行との差、累積額を順に求めます。CTEで計算結果を次の検索へ渡し、上位二件と上位二順位の違い、同じ日付の行をどこまで計算範囲へ含めるかを確認します。

説明

GROUP BYの集計は複数の明細を一行へまとめます。ウィンドウ関数は明細の行を残したまま、同じ店舗の合計・順位・前の行の値などを付ける機能です。計算する範囲と順序をOVERの中で指定します。

二店舗の七明細を使い、店舗合計、順位と同順位、前行との差、累積額を順に求めます。CTEで計算結果を次の検索へ渡し、上位二件と上位二順位の違い、同じ日付の行をどこまで計算範囲へ含めるかを確認します。

1同じ日付と同じ金額を含む明細

IDで一行を識別し、店舗・日付・金額を保存します。

CREATE TABLE sales(id INTEGER PRIMARY KEY明細を一意に識別,shop TEXT,day TEXT,amount INTEGER) STRICT; INSERT INTO sales VALUES(1,'A','11-01',100),(2,'A','11-02',200),(3,'A','11-02',50),(4,'B','11-01',300),(5,'B','11-03',100),(6,'B','11-03',100)同日・同額の別明細,(7,'B','11-04',50); SELECT * FROM sales ORDER BY id;

A店は三明細で350円、B店は四明細で550円です。Aの11-02には二行、Bの11-03には同額100円の二行があります。日付や金額だけでは順序が一意にならない例を用意しています。

2通常の集約と明細を残す集約

GROUP BYとSUM OVERを順に実行します。

SELECT shop,SUM(amount) AS shop_total FROM sales GROUP BY shop一店舗一行へまとめる ORDER BY shop; SELECT id,shop,amount,SUM(amount) OVER (PARTITION BY shop)明細を残して店舗別に計算 AS shop_total FROM sales ORDER BY id;

GROUP BYは二行、OVER付きは七行です。PARTITION BY shopは計算する集合を店舗ごとに分けますが、明細自体をまとめません。各行へ同じ店舗合計が付いています。

3金額の大きい順に連番を付ける

ROW_NUMBERの順序に金額とIDを指定します。

SELECT id,shop,amount,ROW_NUMBER()各行に連番 OVER (PARTITION BY shop ORDER BY amount DESC,id同額ならID順で番号を決める) AS rn FROM sales ORDER BY shop,rn;

店舗ごとに1から連番が付きます。B店の同額100円はID 5が2番、6が3番です。ROW_NUMBERは同額でも別番号になり、IDを第2キーにしたことで順序が確定します。

4同順位を認めるRANKとDENSE_RANK

順位の条件を金額だけにします。

SELECT id,shop,amount,RANK()同順位の後の番号を飛ばす OVER (PARTITION BY shop ORDER BY amount DESC) AS rnk,DENSE_RANK()同順位の後も連続した順位 OVER (PARTITION BY shop ORDER BY amount DESC) AS dense FROM sales ORDER BY shop,amount DESC,id;

B店はRANKで1・2・2・4、DENSE_RANKで1・2・2・3です。RANKは同順位に含まれた行数を次の順位へ反映し、DENSE_RANKは順位の数字を飛ばしません。同順位を決めたいので、このOVERにはIDを加えていません。

5計算した番号を同じWHEREでは絞れない

ROW_NUMBERの別名rnを同じSELECTのWHEREへ置きます。

SELECT id,ROW_NUMBER() OVER (PARTITION BY shop ORDER BY amount DESC,id) AS rn計算列の名前 FROM sales WHERE rn<=2同じSELECTでの使用は不可;

misuse of aliased window function rnというエラーです。WHEREで明細を絞る段階と、ウィンドウ関数を計算する段階が異なります。計算済みのrnを条件にするには、CTEや部分問合せを外側のSELECTへ渡します。

6CTEで番号を付けてから上位二件を取る

WITHで計算結果にrankedという名前を付けます。

WITH ranked ASこの文内の名前付き結果 (SELECT id,shop,amount,ROW_NUMBER() OVER (PARTITION BY shop ORDER BY amount DESC,id) AS rn FROM sales) SELECT * FROM ranked WHERE rn<=2計算後の番号で絞る ORDER BY shop,rn;

A店のID 2・1、B店のID 4・5の計四行です。CTEはこのSQL文の中だけで参照する名前付きの検索結果です。外側ではrnが既にある列として条件を書けます。

7上位二順位なら同額行も残る

ROW_NUMBERをRANKへ替え、IDは順位条件から外します。

WITH ranked AS (SELECT id,shop,amount,RANK()同額を同順位にする OVER (PARTITION BY shop ORDER BY amount DESC) AS rnk FROM sales) SELECT * FROM ranked WHERE rnk<=2二位以内の全行 ORDER BY shop,rnk,id;

B店のID 6も加わり、計五行です。「二件まで」と「二位まで、同順位を含む」は違う要件です。使う順位関数と同順位の条件を、必要な一覧に合わせて決めます。

8直前の明細との金額差

LAGで同じ店舗の前の行を参照します。

SELECT id,shop,amount,LAG(amount)一つ前の行の金額 OVER (PARTITION BY shop ORDER BY day,id前後関係を決める順序) AS previous,amount-LAG(amount)現在額から前行額を引く OVER (PARTITION BY shop ORDER BY day,id) AS difference FROM sales ORDER BY shop,day,id;

各店舗の先頭では前行がなくNULLです。A店は前行との差がNULL・100・-150、B店はNULL・-200・0・-50になります。ORDER BY day,idにより、同日の二行もIDで順序を決めています。

9同日の全行を含む累積と一行ずつの累積

既定の範囲と、ROWSを明示した範囲を並べます。

SELECT id,shop,day,amount,SUM(amount) OVER (PARTITION BY shop ORDER BY day)同じ日付を同じ範囲へ含める AS same_day_total,SUM(amount) OVER (PARTITION BY shop ORDER BY day,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW先頭から現在行までの行範囲) AS row_total FROM sales ORDER BY shop,day,id;

A店のID 2では日付単位の累積は350、一行ずつなら300です。既定のRANGEは同じORDER BY値を持つ行を一緒に含めます。ROWSは指定した順序の行単位で、先頭から現在行までを合計します。

10直前一行と現在行の平均

計算範囲を二行へ狭めます。

SELECT id,shop,amount,AVG(amount) OVER (PARTITION BY shop ORDER BY day,id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW前行と現在行だけを平均) AS moving_average FROM sales ORDER BY shop,day,id;

A店は100・150・125、B店は300・200・100・75です。店舗の先頭は前行がないため一行だけの平均です。PARTITION BY、ORDER BY、フレーム範囲の三つを合わせて、どの値を計算へ含めるかを決めます。

まとめ

ウィンドウ関数は明細を残し、集合・順序・範囲をOVERで指定します。

ROW_NUMBERとRANKでは同額時の件数が変わり、計算後の絞込みは外側へ分けます。

LAGと累積・移動平均は、同順序の行の扱いとフレームを明示します。

操作履歴と解説を振り返れます。

SQLite 3.54.0

1
CREATE TABLE sales(id INTEGER PRIMARY KEY,shop TEXT,day TEXT,amount INTEGER) STRICT; INSERT INTO sales VALUES(1,'A','11-01',100),(2,'A','11-02',200),(3,'A','11-02',50),(4,'B','11-01',300),(5,'B','11-03',100),(6,'B','11-03',100),(7,'B','11-04',50); SELECT * FROM sales ORDER BY id;
╭────┬──────┬───────┬────────╮
│ id │ shop │  day  │ amount │
╞════╪══════╪═══════╪════════╡
│  1 │ A    │ 11-01 │    100 │
│  2 │ A    │ 11-02 │    200 │
│  3 │ A    │ 11-02 │     50 │
│  4 │ B    │ 11-01 │    300 │
│  5 │ B    │ 11-03 │    100 │
│  6 │ B    │ 11-03 │    100 │
│  7 │ B    │ 11-04 │     50 │
╰────┴──────┴───────┴────────╯
2
SELECT shop,SUM(amount) AS shop_total FROM sales GROUP BY shop ORDER BY shop; SELECT id,shop,amount,SUM(amount) OVER (PARTITION BY shop) AS shop_total FROM sales ORDER BY id;
╭──────┬────────────╮
│ shop │ shop_total │
╞══════╪════════════╡
│ A    │        350 │
│ B    │        550 │
╰──────┴────────────╯
╭────┬──────┬────────┬────────────╮
│ id │ shop │ amount │ shop_total │
╞════╪══════╪════════╪════════════╡
│  1 │ A    │    100 │        350 │
│  2 │ A    │    200 │        350 │
│  3 │ A    │     50 │        350 │
│  4 │ B    │    300 │        550 │
│  5 │ B    │    100 │        550 │
│  6 │ B    │    100 │        550 │
│  7 │ B    │     50 │        550 │
╰────┴──────┴────────┴────────────╯
3
SELECT id,shop,amount,ROW_NUMBER() OVER (PARTITION BY shop ORDER BY amount DESC,id) AS rn FROM sales ORDER BY shop,rn;
╭────┬──────┬────────┬────╮
│ id │ shop │ amount │ rn │
╞════╪══════╪════════╪════╡
│  2 │ A    │    200 │  1 │
│  1 │ A    │    100 │  2 │
│  3 │ A    │     50 │  3 │
│  4 │ B    │    300 │  1 │
│  5 │ B    │    100 │  2 │
│  6 │ B    │    100 │  3 │
│  7 │ B    │     50 │  4 │
╰────┴──────┴────────┴────╯
4
SELECT id,shop,amount,RANK() OVER (PARTITION BY shop ORDER BY amount DESC) AS rnk,DENSE_RANK() OVER (PARTITION BY shop ORDER BY amount DESC) AS dense FROM sales ORDER BY shop,amount DESC,id;
╭────┬──────┬────────┬─────┬───────╮
│ id │ shop │ amount │ rnk │ dense │
╞════╪══════╪════════╪═════╪═══════╡
│  2 │ A    │    200 │   1 │     1 │
│  1 │ A    │    100 │   2 │     2 │
│  3 │ A    │     50 │   3 │     3 │
│  4 │ B    │    300 │   1 │     1 │
│  5 │ B    │    100 │   2 │     2 │
│  6 │ B    │    100 │   2 │     2 │
│  7 │ B    │     50 │   4 │     3 │
╰────┴──────┴────────┴─────┴───────╯
5
SELECT id,ROW_NUMBER() OVER (PARTITION BY shop ORDER BY amount DESC,id) AS rn FROM sales WHERE rn<=2;
Parse error: misuse of aliased window function rn
6
WITH ranked AS (SELECT id,shop,amount,ROW_NUMBER() OVER (PARTITION BY shop ORDER BY amount DESC,id) AS rn FROM sales) SELECT * FROM ranked WHERE rn<=2 ORDER BY shop,rn;
╭────┬──────┬────────┬────╮
│ id │ shop │ amount │ rn │
╞════╪══════╪════════╪════╡
│  2 │ A    │    200 │  1 │
│  1 │ A    │    100 │  2 │
│  4 │ B    │    300 │  1 │
│  5 │ B    │    100 │  2 │
╰────┴──────┴────────┴────╯
7
WITH ranked AS (SELECT id,shop,amount,RANK() OVER (PARTITION BY shop ORDER BY amount DESC) AS rnk FROM sales) SELECT * FROM ranked WHERE rnk<=2 ORDER BY shop,rnk,id;
╭────┬──────┬────────┬─────╮
│ id │ shop │ amount │ rnk │
╞════╪══════╪════════╪═════╡
│  2 │ A    │    200 │   1 │
│  1 │ A    │    100 │   2 │
│  4 │ B    │    300 │   1 │
│  5 │ B    │    100 │   2 │
│  6 │ B    │    100 │   2 │
╰────┴──────┴────────┴─────╯
8
SELECT id,shop,amount,LAG(amount) OVER (PARTITION BY shop ORDER BY day,id) AS previous,amount-LAG(amount) OVER (PARTITION BY shop ORDER BY day,id) AS difference FROM sales ORDER BY shop,day,id;
╭────┬──────┬────────┬──────────┬────────────╮
│ id │ shop │ amount │ previous │ difference │
╞════╪══════╪════════╪══════════╪════════════╡
│  1 │ A    │    100 │ NULL     │ NULL       │
│  2 │ A    │    200 │      100 │        100 │
│  3 │ A    │     50 │      200 │       -150 │
│  4 │ B    │    300 │ NULL     │ NULL       │
│  5 │ B    │    100 │      300 │       -200 │
│  6 │ B    │    100 │      100 │          0 │
│  7 │ B    │     50 │      100 │        -50 │
╰────┴──────┴────────┴──────────┴────────────╯
9
SELECT id,shop,day,amount,SUM(amount) OVER (PARTITION BY shop ORDER BY day) AS same_day_total,SUM(amount) OVER (PARTITION BY shop ORDER BY day,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_total FROM sales ORDER BY shop,day,id;
╭────┬──────┬───────┬────────┬────────────────┬───────────╮
│ id │ shop │  day  │ amount │ same_day_total │ row_total │
╞════╪══════╪═══════╪════════╪════════════════╪═══════════╡
│  1 │ A    │ 11-01 │    100 │            100 │       100 │
│  2 │ A    │ 11-02 │    200 │            350 │       300 │
│  3 │ A    │ 11-02 │     50 │            350 │       350 │
│  4 │ B    │ 11-01 │    300 │            300 │       300 │
│  5 │ B    │ 11-03 │    100 │            500 │       400 │
│  6 │ B    │ 11-03 │    100 │            500 │       500 │
│  7 │ B    │ 11-04 │     50 │            550 │       550 │
╰────┴──────┴───────┴────────┴────────────────┴───────────╯
10
SELECT id,shop,amount,AVG(amount) OVER (PARTITION BY shop ORDER BY day,id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_average FROM sales ORDER BY shop,day,id;
╭────┬──────┬────────┬────────────────╮
│ id │ shop │ amount │ moving_average │
╞════╪══════╪════════╪════════════════╡
│  1 │ A    │    100 │          100.0 │
│  2 │ A    │    200 │          150.0 │
│  3 │ A    │     50 │          125.0 │
│  4 │ B    │    300 │          300.0 │
│  5 │ B    │    100 │          200.0 │
│  6 │ B    │    100 │          100.0 │
│  7 │ B    │     50 │           75.0 │
╰────┴──────┴────────┴────────────────╯

演習完了