【SQL】COUNTの挙動を理解する
うみうし3号はじめに
SQLでCOUNTを使う機会は結構ある。その中でも、以下の挙動について理解がふんわりしていたので調べる。
- COUNT(*)とCOUNT({列名})
- COUNT(expr or null)
- COUNT(DISTINCT expr)
うみうし3号環境
PostgreSQL
うみうし3号集約関数
COUNTは集約関数に分類される。
集約関数は、複数の入力行から1つの結果を出力する。種類としては、以下のようなものが存在する
- count
- sum
- avg
- max
- min
集約式
集約関数を選択した行に対して適応することを集約式という。集約式の形式としては、以下の4パターンのどれかとなる。
- aggregate_name (expression)
- aggregate_name (ALL expression)
- aggregate_name (DISTINCT expression)
- aggregate_name ( * )
集約関数自体は、ユーザー定義することも可能？ちょっと複雑そうなので後で追う。
aggregate_name (expression) / aggregate_name (ALL expression)
ALLはデフォルトとなるため、省略しても変わらない。
この式では、指定された式が非NULLを返す入力行すべてに集約を適応する。
aggregate_name (DISTINCT expression)
この式では、指定された式が非NULLを返す重複しない入力行すべてに集約を適応する。
aggregate_name ( * )
この式では、NULLか非NULLかに関わらず、すべての入力行に対して1回ずつ集約を適応する。
入力値が指定されていない(columnが指定されてないと読み替えていい？)ため、一般的にはCOUNTでのみ役に立つらしい。
うみうし3号応用例
集約式の挙動を踏まえ、COUNTの例を考える。
テーブル例として、以下のようなテーブルで考える。
table_name=work
| id | name |
|---|---|
| 1 | taro |
| 2 | jiro |
| 3 | null |
| 4 | taro |
COUNT({column_name})
一般的な列名を指定するパターン。この場合、対象列のNULLでない値を数える。
select count(name) from work
COUNT(DISTINCT {column_name})
DISTINCTを指定するパターン。対象列の重複でなく、NULLでない値を数える。
select count(distinct name) from work
COUNT(*)
行数を数える。すべてのブロックを数えるため、データ量やindexの関係で時間が膨らむことも多いらしい。
not null制約をつけた行があれば、そちれで代替も検討したい。
select * from work
COUNT(expression or null)
ちょっとした応用。集約関数内で評価式を適応することで、指定した値の数をgroup byなどしなくても数えられる。
select count(name = 'taro' or null)
上記では、nameカラムがtaroの行を数えることができる。
or nullの部分については、SQLの論理演算子の特性を活かしている。
name = 'taro'となる行については、orの左側がtrueとなる。name != 'taro'となる行については、orの左側がfalseとなる。
そのため、以下となり、結果としては評価式がtrueとなるものの数をカウントできる。
- true or null = true
- false or null = null
この部分をよくわからず、count(name = 'taro')とか書いていた。
この場合、式結果がfalseでもCOUNT対象となるため、結果としてはNULL以外のすべての行数をカウントすることなる。count(name)と同じ結果。
うみうし3号まとめ
- COUNTは入力値のNULL以外の値をカウントする
- COUNT(*)はNULL/not NULL問わずすべての行数をカウントする。けどコスト高いよ。
- 良い子は
count(name = 'taro')とか書いてはいけない。式結果がfalseとなるものを対象外にしたいならor nullを入れよう。