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

集計関数による件数・合計・平均の計算

三店舗の七明細を用意し、件数と金額の集計、店舗別集計、集計前後の絞込みを順に行います。平均の分母、空の集計、NULLを0へ置き換えた場合まで、元の明細と計算結果を対応させます。

説明

集約関数は、複数行から件数・合計・平均などを求めます。全行をまとめるか、店舗ごとにまとめるかで結果の一行が表す単位が変わります。また、金額が未確定のNULLと、確定した0円は集計で異なる扱いになります。

1七件の売上明細

一行を一件の取引とし、金額未確定はNULL、確定した0円は0として保存します。

CREATE TABLE sales取引単位の明細表(id INTEGER PRIMARY KEY,shop TEXT,day TEXT,amount INTEGER円単位の金額。NULLも許す

,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;

A店は100円・200円・未確定、B店は50円・150円・0円、C店は未確定の一件です。channelはwebかstore、dayはこの固定例の月日です。同じ日や店舗でも取引ごとに行を分けています。

2行数と金額の件数

COUNT(*)とCOUNT(amount)を同時に求めます。

SELECT COUNT(*)すべての行数 AS rows,COUNT(amount)金額がNULLでない行数 AS priced FROM sales;

行数は7、金額がNULLでない行は5です。COUNT(*)は行を数え、COUNT(amount)はその列がNULLでない行を数えます。0円の取引は金額があるので後者にも含まれます。

3合計・平均・最小・最大

確定している金額を集約します。

SELECT SUM(amount)金額の合計 AS total,AVG(amount)非NULLの金額の平均 AS average,MIN(amount)最小額 AS minimum,MAX(amount)最大額 AS maximum FROM sales;

合計500円、平均100円、最小0円、最大200円です。平均は(100+200+50+150+0)/5です。NULLの二件は金額の合計にも平均の分母にも入りません。

4店舗ごとにまとめる

GROUP BY shopを指定します。

SELECT shop,COUNT(*) AS rows,COUNT(amount) AS priced,SUM(amount) AS total,ROUND(AVG(amount),2)平均を小数第2位へ丸める AS average FROM sales GROUP BY shop店舗ごとに集約 ORDER BY shop店舗順に表示;

A店は3件中2件が確定し300円・平均150円、B店は3件が確定し200円・平均約66.67円です。C店は一件あるものの金額は未確定なので、合計と平均はNULLです。GROUP BYにより結果は一店舗一行になります。

5平均の分母を計算で確かめる

合計を非NULLの件数で割った値を表示します。

SELECT shop,SUM(amount)*1.0/COUNT(amount)合計を有効な金額件数で割る AS from_parts,AVG(amount)同じ平均を集約関数で求める AS average FROM sales GROUP BY shop ORDER BY shop;

平均と同じ結果です。1.0を掛けて実数の割算にしています。C店では非NULLの件数が0で、結果はNULLです。SQLiteでは0での割算もNULLになりますが、他のDB製品ではエラーになる場合があるため、除算の条件は製品ごとに確認します。

6100円以上の取引だけを集計する

WHEREで明細を絞ってから店舗別にまとめます。

SELECT shop,COUNT(*) AS rows,SUM(amount) AS total FROM sales WHERE amount>=100集約する前の取引条件 GROUP BY shop残った取引を店舗別にまとめる ORDER BY shop;

A店の100円・200円とB店の150円だけが対象になり、A店300円、B店150円です。WHEREが取引単位で対象を減らした後に、その残った行を集約しています。C店は条件に合う明細がなく、結果行もありません。

7合計が250円以上の店舗だけを選ぶ

明細を減らさず集計し、HAVINGで合計を条件にします。

SELECT shop,COUNT(*) AS rows,SUM(amount) AS total FROM sales GROUP BY shop全明細を店舗別に集約 HAVING SUM(amount)>=250集約結果の合計を条件にする ORDER BY shop;

A店の一行だけが残り、件数は未確定を含む3、合計は300円です。HAVINGはグループに対する条件です。WHERE amount>=250なら対象取引が一件もないため、この検索と同じ意味にはなりません。

8条件に合う行が一件もない場合

1000円以上の明細を集計します。

SELECT COUNT(*) AS rows,SUM(amount) AS total,AVG(amount) AS average FROM sales WHERE amount>=1000この表では該当なし;

COUNTは0、SUMとAVGはNULLです。GROUP BYのない集約は、入力が空でも全体の集計を一行返します。「0円の取引がある」と「集計対象がない」は違う状態です。

9空の入力を店舗別に集計する

同じ条件にGROUP BYを加えます。

SELECT shop,COUNT(*) AS rows FROM sales WHERE amount>=1000該当する明細なし GROUP BY shop存在するグループごとの結果;

今回は結果が0行で、表も表示されません。対象行がなければ店舗のグループ自体が作られないためです。前の全体集計の一行と比較すると、集約単位の違いが分かります。

10NULLを0へ置き換えると平均が変わる

COALESCEで未確定金額を0にした平均も求めます。

SELECT AVG(amount)確定分だけの平均 AS known_average,ROUND(AVG(COALESCE(amount,0)NULLを0として計算),2) AS zero_filled_average FROM sales;

確定分の平均は100円ですが、0へ置き換えると500/7で約71.43円です。COALESCEは最初の非NULL値を返します。未確定を0とみなすと分母も変わるため、画面に0を表示したいだけの都合で集計の意味を変えないようにします。

11店舗数と重複を除いた金額数

COUNTへDISTINCTを付けます。

SELECT COUNT(DISTINCT shop)店舗の種類数 AS shops,COUNT(DISTINCT amount)非NULL金額の種類数 AS amounts FROM sales;

店舗はA・B・Cの3、金額は0・50・100・150・200の5種類です。COUNT(DISTINCT amount)は金額の種類数で、売上件数ではありません。NULLはこの件数に入りません。

まとめ

COUNT(*)とCOUNT(列)ではNULLの行を数えるかが異なります。

GROUP BYで結果の一行の単位を定め、WHEREは明細、HAVINGは集約後のグループへ条件を付けます。

平均の分母と空の入力の扱いを確認し、NULLを0へ置き換える意味を明示します。

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

SQLite 3.54.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 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   │
╰────┴──────┴───────┴────────┴─────────╯

SELECT COUNT(*) AS rows,COUNT(amount) AS priced FROM sales;

╭──────┬────────╮
│ rows │ priced │
╞══════╪════════╡
│    7 │      5 │
╰──────┴────────╯

SELECT SUM(amount) AS total,AVG(amount) AS average,MIN(amount) AS minimum,MAX(amount) AS maximum FROM sales;

╭───────┬─────────┬─────────┬─────────╮
│ total │ average │ minimum │ maximum │
╞═══════╪═════════╪═════════╪═════════╡
│   500 │   100.0 │       0 │     200 │
╰───────┴─────────┴─────────┴─────────╯

SELECT shop,COUNT(*) AS rows,COUNT(amount) AS priced,SUM(amount) AS total,ROUND(AVG(amount),2) AS average FROM sales GROUP BY shop ORDER BY shop;

╭──────┬──────┬────────┬───────┬─────────╮
│ shop │ rows │ priced │ total │ average │
╞══════╪══════╪════════╪═══════╪═════════╡
│ A    │    3 │      2 │   300 │   150.0 │
│ B    │    3 │      3 │   200 │   66.67 │
│ C    │    1 │      0 │ NULL  │ NULL    │
╰──────┴──────┴────────┴───────┴─────────╯

SELECT shop,SUM(amount)*1.0/COUNT(amount) AS from_parts,AVG(amount) AS average FROM sales GROUP BY shop ORDER BY shop;

╭──────┬────────────────────┬────────────────────╮
│ shop │     from_parts     │      average       │
╞══════╪════════════════════╪════════════════════╡
│ A    │              150.0 │              150.0 │
│ B    │ 66.666666666666671 │ 66.666666666666671 │
│ C    │ NULL               │ NULL               │
╰──────┴────────────────────┴────────────────────╯

SELECT shop,COUNT(*) AS rows,SUM(amount) AS total FROM sales WHERE amount>=100 GROUP BY shop ORDER BY shop;

╭──────┬──────┬───────╮
│ shop │ rows │ total │
╞══════╪══════╪═══════╡
│ A    │    2 │   300 │
│ B    │    1 │   150 │
╰──────┴──────┴───────╯

SELECT shop,COUNT(*) AS rows,SUM(amount) AS total FROM sales GROUP BY shop HAVING SUM(amount)>=250 ORDER BY shop;

╭──────┬──────┬───────╮
│ shop │ rows │ total │
╞══════╪══════╪═══════╡
│ A    │    3 │   300 │
╰──────┴──────┴───────╯

SELECT COUNT(*) AS rows,SUM(amount) AS total,AVG(amount) AS average FROM sales WHERE amount>=1000;

╭──────┬───────┬─────────╮
│ rows │ total │ average │
╞══════╪═══════╪═════════╡
│    0 │ NULL  │ NULL    │
╰──────┴───────┴─────────╯

SELECT shop,COUNT(*) AS rows FROM sales WHERE amount>=1000 GROUP BY shop;

SELECT AVG(amount) AS known_average,ROUND(AVG(COALESCE(amount,0)),2) AS zero_filled_average FROM sales;

╭───────────────┬─────────────────────╮
│ known_average │ zero_filled_average │
╞═══════════════╪═════════════════════╡
│         100.0 │               71.43 │
╰───────────────┴─────────────────────╯

SELECT COUNT(DISTINCT shop) AS shops,COUNT(DISTINCT amount) AS amounts FROM sales;

╭───────┬─────────╮
│ shops │ amounts │
╞═══════╪═════════╡
│     3 │       5 │
╰───────┴─────────╯

演習完了