一覧DB-03-2 · 集約と条件集計

条件付き集計とグループ別集計

七件の取引を用意し、まず各明細に分類を付けます。その分類から店舗・日付別の集計とweb/store別の集計を作り、件数の数え方、条件の重なり、比率の分母を確認します。最後に集計対象の明細へ戻って合計を照合します。

説明

店舗別の売上だけでなく、販売経路別の金額や高額取引の件数を並べたい場合は、条件に合う行だけを集約します。CASEは条件に応じた値を返す式で、SUMやCOUNTと組み合わせられます。

七件の取引を用意し、まず各明細に分類を付けます。その分類から店舗・日付別の集計とweb/store別の集計を作り、件数の数え方、条件の重なり、比率の分母を確認します。最後に集計対象の明細へ戻って合計を照合します。

1分類する取引の一覧

金額は円単位、NULLは未確定、0は確定した0円です。

CREATE TABLE sales取引明細を保存(id INTEGER PRIMARY KEY,shop TEXT,day TEXT,amount INTEGER,channel TEXT) STRICT; INSERT INTO sales VALUES七件の固定データ(1,'A','11-01',100,'web'),(2,'A','11-01',200,'store'),(3,'A','11-02',NULL,'web'),(4,'B','11-01',50,'web'),(5,'B','11-02',150,'web'),(6,'B','11-02',0,'store'),(7,'C','11-02',NULL,'store'); SELECT * FROM sales ORDER BY idID順の一覧;

A店に三件、B店に三件、C店に一件あります。webとstoreは販売経路です。この一行一取引の表へ条件を付け、どの行がどの集計へ入るかを確かめます。

2一行ずつ金額帯を付ける

CASEで未確定・150円以上・150円未満を分類します。

SELECT id,amount,CASE WHEN amount IS NULLまず未確定かを判定 THEN '未確定' WHEN amount>=150 THEN '150円以上'境界150円を含む ELSE '150円未満' END残った確定額の分類 AS band FROM sales ORDER BY id;

id 2・5は150円以上、id 3・7は未確定、残りは150円未満です。CASEは最初に真となったWHENの値を返します。NULLを先に判定したため、未確定がELSEへ流れ込むことを防いでいます。

3店舗と日付の組でまとめる

GROUP BYへ二列を指定します。

SELECT shop,day,COUNT(*) AS rows,SUM(amount) AS total FROM sales GROUP BY shop,day店舗と日付の組ごと ORDER BY shop,day同じ二列で表示順を決める;

A店11-01は二件300円、11-02は一件で金額未確定です。B店11-01は50円、11-02は二件150円、C店11-02は未確定です。一店舗一行ではなく、店舗と日付の組が一行の単位になります。

4販売経路ごとの合計を横に並べる

条件に合う金額だけをSUMへ渡します。

SELECT shop,SUM(CASE WHEN channel='web' THEN amount END)webの金額だけを加算 AS web_total,SUM(CASE WHEN channel='store' THEN amount END)storeの金額だけを加算 AS store_total FROM sales GROUP BY shop ORDER BY shop;

A店はweb100円・store200円、B店はweb200円・store0円です。C店はwebの取引がなく、storeは金額未確定なので両方NULLです。ELSEを省いたCASEは不一致時にNULLを返し、SUMはその値を集計から外します。

5対象件数も並べてNULLの理由を調べる

販売経路ごとの行数と、確定金額の件数を取得します。

SELECT shop,COUNT(CASE WHEN channel='web' THEN 1 END)webの対象行数 AS web_rows,COUNT(CASE WHEN channel='store' THEN 1 END) AS store_rows,COUNT(CASE WHEN channel='store' THEN amount END)storeの確定金額件数 AS store_priced FROM sales GROUP BY shop ORDER BY shop;

C店はweb行数0、store行数1、storeの確定金額件数0です。前のNULLには「取引がない」と「取引はあるが金額未確定」という別の理由がありました。行数と値の件数を添えることで、金額だけでは見えなかった違いを説明できます。

6条件付き件数の誤りを比べる

COUNTのELSEを0にした場合と、省いた場合を並べます。

SELECT COUNT(CASE WHEN amount>=150 THEN 1 ELSE 0 END)0も数えるため全行になる AS all_nonnull,COUNT(CASE WHEN amount>=150 THEN 1 END)不一致のNULLを除外 AS matched,SUM(CASE WHEN amount>=150 THEN 1 ELSE 0 END)一致行の1だけを加算 AS matched_sum FROM sales;

最初のCOUNTは7、後の二つは2です。COUNTは0も非NULLとして数えるため、ELSE 0を入れると全行が対象になります。SUMなら真を1、その他を0にして加算でき、COUNTなら不一致をNULLにして除外できます。

7FILTERで集約ごとに条件を書く

同じ条件をFILTERで表します。

SELECT COUNT(*) FILTER (WHERE amount>=150)この件数集計だけに掛かる条件 AS high_rows,SUM(amount) FILTER (WHERE channel='web')この金額集計だけに掛かる条件 AS web_total FROM sales;

150円以上は2件、webの確定金額合計は300円です。FILTERはこの集約関数へ渡す行だけを絞ります。SELECT全体のWHEREと違って、列ごとに別の条件で集計できます。

8重なるWHENは先に一致した側へ入る

100円以上を先に書いたCASEと、150円以上を先にしたCASEを並べます。

SELECT id,amount,CASE WHEN amount>=100 THEN '中' WHEN amount>=150広い条件が先に成立 THEN '高' ELSE '低' END AS wrong_order,CASE WHEN amount>=150 THEN '高' WHEN amount>=100高い金額帯を先に判定 THEN '中' ELSE '低' END AS intended FROM sales WHERE amount IS NOT NULL ORDER BY id;

200円と150円は左では中、右では高になります。先のamount>=100が成立した時点でCASEの結果が決まり、後のWHENは採用されません。範囲が重なる分類は、狭い条件を先に置くか、各範囲を重ならない式にします。

9割合の分母を明示する

150円以上の2件を、全取引と確定済み取引のそれぞれで割ります。

SELECT COUNT(*) FILTER (WHERE amount>=150)*100.0/COUNT(*)全取引数を分母にする AS percent_of_all,COUNT(*) FILTER (WHERE amount>=150)*100.0/COUNT(amount)確定済み件数を分母にする AS percent_of_priced FROM sales;

全7件に対して約28.57%、確定5件に対して40%です。どちらも計算として成立しますが、表している割合が違います。100.0を掛けて小数を残し、見出しにも分母の違いを示しています。

10集計対象の明細へ戻る

webの取引を一覧にし、その金額を集計します。

SELECT id,shop,amount FROM sales WHERE channel='web'集計と同じ条件の明細 ORDER BY id; SELECT COUNT(*) AS rows未確定を含む件数,COUNT(amount) AS priced確定した金額の件数,SUM(amount) AS total確定金額の合計 FROM sales WHERE channel='web';

id 1・3・4・5の四行で、確定した三行の100+50+150が300円です。未確定のid 3も取引件数には入ります。条件付き集計を確認するときは、同じ条件の明細を取り出すと、件数と金額の対象を追えます。

まとめ

CASEは最初に成立した条件の値を返すため、NULLと条件の重なりを明示します。

条件付きCOUNTでは不一致をNULLにし、条件付きSUMでは加算する値を指定します。

集計の単位・対象行・比率の分母を決め、同じ条件の明細と照合します。

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

SQLite 3.54.0

1
CREATE TABLE sales(id INTEGER PRIMARY KEY,shop TEXT,day TEXT,amount INTEGER,channel TEXT) STRICT; INSERT INTO sales VALUES(1,'A','11-01',100,'web'),(2,'A','11-01',200,'store'),(3,'A','11-02',NULL,'web'),(4,'B','11-01',50,'web'),(5,'B','11-02',150,'web'),(6,'B','11-02',0,'store'),(7,'C','11-02',NULL,'store'); SELECT * FROM sales ORDER BY id;
╭────┬──────┬───────┬────────┬─────────╮
│ id │ shop │  day  │ amount │ channel │
╞════╪══════╪═══════╪════════╪═════════╡
│  1 │ A    │ 11-01 │    100 │ web     │
│  2 │ A    │ 11-01 │    200 │ store   │
│  3 │ A    │ 11-02 │ NULL   │ web     │
│  4 │ B    │ 11-01 │     50 │ web     │
│  5 │ B    │ 11-02 │    150 │ web     │
│  6 │ B    │ 11-02 │      0 │ store   │
│  7 │ C    │ 11-02 │ NULL   │ store   │
╰────┴──────┴───────┴────────┴─────────╯
2
SELECT id,amount,CASE WHEN amount IS NULL THEN '未確定' WHEN amount>=150 THEN '150円以上' ELSE '150円未満' END AS band FROM sales ORDER BY id;
╭────┬────────┬───────────╮
│ id │ amount │   band    │
╞════╪════════╪═══════════╡
│  1 │    100 │ 150円未満 │
│  2 │    200 │ 150円以上 │
│  3 │ NULL   │ 未確定    │
│  4 │     50 │ 150円未満 │
│  5 │    150 │ 150円以上 │
│  6 │      0 │ 150円未満 │
│  7 │ NULL   │ 未確定    │
╰────┴────────┴───────────╯
3
SELECT shop,day,COUNT(*) AS rows,SUM(amount) AS total FROM sales GROUP BY shop,day ORDER BY shop,day;
╭──────┬───────┬──────┬───────╮
│ shop │  day  │ rows │ total │
╞══════╪═══════╪══════╪═══════╡
│ A    │ 11-01 │    2 │   300 │
│ A    │ 11-02 │    1 │ NULL  │
│ B    │ 11-01 │    1 │    50 │
│ B    │ 11-02 │    2 │   150 │
│ C    │ 11-02 │    1 │ NULL  │
╰──────┴───────┴──────┴───────╯
4
SELECT shop,SUM(CASE WHEN channel='web' THEN amount END) AS web_total,SUM(CASE WHEN channel='store' THEN amount END) AS store_total FROM sales GROUP BY shop ORDER BY shop;
╭──────┬───────────┬─────────────╮
│ shop │ web_total │ store_total │
╞══════╪═══════════╪═════════════╡
│ A    │       100 │         200 │
│ B    │       200 │           0 │
│ C    │ NULL      │ NULL        │
╰──────┴───────────┴─────────────╯
5
SELECT shop,COUNT(CASE WHEN channel='web' THEN 1 END) AS web_rows,COUNT(CASE WHEN channel='store' THEN 1 END) AS store_rows,COUNT(CASE WHEN channel='store' THEN amount END) AS store_priced FROM sales GROUP BY shop ORDER BY shop;
╭──────┬──────────┬────────────┬──────────────╮
│ shop │ web_rows │ store_rows │ store_priced │
╞══════╪══════════╪════════════╪══════════════╡
│ A    │        2 │          1 │            1 │
│ B    │        2 │          1 │            1 │
│ C    │        0 │          1 │            0 │
╰──────┴──────────┴────────────┴──────────────╯
6
SELECT COUNT(CASE WHEN amount>=150 THEN 1 ELSE 0 END) AS all_nonnull,COUNT(CASE WHEN amount>=150 THEN 1 END) AS matched,SUM(CASE WHEN amount>=150 THEN 1 ELSE 0 END) AS matched_sum FROM sales;
╭─────────────┬─────────┬─────────────╮
│ all_nonnull │ matched │ matched_sum │
╞═════════════╪═════════╪═════════════╡
│           7 │       2 │           2 │
╰─────────────┴─────────┴─────────────╯
7
SELECT COUNT(*) FILTER (WHERE amount>=150) AS high_rows,SUM(amount) FILTER (WHERE channel='web') AS web_total FROM sales;
╭───────────┬───────────╮
│ high_rows │ web_total │
╞═══════════╪═══════════╡
│         2 │       300 │
╰───────────┴───────────╯
8
SELECT id,amount,CASE WHEN amount>=100 THEN '中' WHEN amount>=150 THEN '高' ELSE '低' END AS wrong_order,CASE WHEN amount>=150 THEN '高' WHEN amount>=100 THEN '中' ELSE '低' END AS intended FROM sales WHERE amount IS NOT NULL ORDER BY id;
╭────┬────────┬─────────────┬──────────╮
│ id │ amount │ wrong_order │ intended │
╞════╪════════╪═════════════╪══════════╡
│  1 │    100 │ 中          │ 中       │
│  2 │    200 │ 中          │ 高       │
│  4 │     50 │ 低          │ 低       │
│  5 │    150 │ 中          │ 高       │
│  6 │      0 │ 低          │ 低       │
╰────┴────────┴─────────────┴──────────╯
9
SELECT COUNT(*) FILTER (WHERE amount>=150)*100.0/COUNT(*) AS percent_of_all,COUNT(*) FILTER (WHERE amount>=150)*100.0/COUNT(amount) AS percent_of_priced FROM sales;
╭────────────────────┬───────────────────╮
│   percent_of_all   │ percent_of_priced │
╞════════════════════╪═══════════════════╡
│ 28.571428571428573 │              40.0 │
╰────────────────────┴───────────────────╯
10
SELECT id,shop,amount FROM sales WHERE channel='web' ORDER BY id; SELECT COUNT(*) AS rows,COUNT(amount) AS priced,SUM(amount) AS total FROM sales WHERE channel='web';
╭────┬──────┬────────╮
│ id │ shop │ amount │
╞════╪══════╪════════╡
│  1 │ A    │    100 │
│  3 │ A    │ NULL   │
│  4 │ B    │     50 │
│  5 │ B    │    150 │
╰────┴──────┴────────╯
╭──────┬────────┬───────╮
│ rows │ priced │ total │
╞══════╪════════╪═══════╡
│    4 │      3 │   300 │
╰──────┴────────┴───────╯

演習完了