条件に合致する件数、例えば「男性ユーザー数」などのSQLでの書き方です。
複数書き方はありますが、基本的に**count(case式)**を使用することをおすすめします。
応用として、条件付きの重複を除いたデータ件数の書き方も紹介します。
サンプルテーブル
サンプルコードに使用するテーブルは以下の通りとします。
users
| id | name | gender | hobby |
|---|---|---|---|
| 1 | 佐藤 | 男性 | 野球 |
| 2 | 田中 | 女性 | ショッピング |
| 3 | 斎藤 | 男性 | テニス |
| 4 | 上田 | 男性 | ドライブ |
| 5 | 石川 | 女性 | テニス |
| 6 | 金沢 | 男性 | ショッピング |
条件に合致する件数のSQL
サンプルテーブルより、 gender = 男性 であるデータの件数を取得するSQL
SELECT
count(case when gender = '男性' then gender else null end) as count_man
FROM users
解説
まずcount関数の引数内のcase式に着目します。
case when gender = '男性' then gender else null end
このcase式により、下のcase列が生成されると考えればよいです。
| gender | case |
|---|---|
| 男性 | 男性 |
| 女性 | null |
| 男性 | 男性 |
| 男性 | 男性 |
| 女性 | null |
| 男性 | 男性 |
続いてcount関数に着目します。
count関数は引数内の列または式について、nullではない値の個数を数え上げます。
よって、意図通り、男性のデータ件数を抽出することができます。
補足
count関数はnullではない値であれば同じ挙動をするので、case式には自由に記述して良い部分があります。
すなわち、下のxxxxの部分はnull以外であれば何でも構いません。例えば1としてもよいですし、falseでも大丈夫です。ただし、さすがにfalseを書くのは不誠実ですので避けてください。
case when gender = '男性' then xxxx else null end
条件付きの重複を除いたデータ件数のSQL
サンプルテーブルより、「男性ユーザーの趣味の種類は何種類あるか」を取得するSQL
SELECT
count(distinct case when gender = '男性' then hobby else null end) as distinct_man_hobby
FROM users
解説
case式により、下の「case」列が生成されると考えればよいです。
case式にてthen hobbyとしていることに注意してください。
| id | name | gender | hobby | case |
|---|---|---|---|---|
| 1 | 佐藤 | 男性 | 野球 | 野球 |
| 2 | 田中 | 女性 | ショッピング | NULL |
| 3 | 斎藤 | 男性 | テニス | テニス |
| 4 | 上田 | 男性 | ドライブ | ドライブ |
| 5 | 石川 | 女性 | テニス | NULL |
| 6 | 金沢 | 男性 | ショッピング | ショッピング |
このcase式に対してdistinctのcount関数を用いることで、重複しないデータ件数を取得できます。
おすすめしない書き方
かなり個人的な意見で恐縮ですが、検索時に上位にヒットする下記の書き方は、特にSQL初学者にはおすすめしません。
SELECT
count(gender = '男性' or null) as count_man
FROM users
非常にシンプルかつ頭の良い美しい書き方です。しかし、これは以下の点がデメリットになります。
-
条件式 or nullの挙動が理解しづらい - 応用がきかない
条件式 or null の挙動
表題について解説します。
結論から言うと、条件式 or null は**trueもしくはnullを返す式になります**。
まず、条件式の部分である gender = '男性' によって下のような「条件式」列が生成されると考えてよいです。
| gender | 条件式 |
|---|---|
| 男性 | true |
| 女性 | false |
| 男性 | true |
| 男性 | true |
| 女性 | false |
| 男性 | true |
次に、この点が最も重要な部分なのですが、
true or null は true
false or nullはnull
を返します。これはor演算子の挙動です。
よって、下の 「条件式 or null」列が生成されると考えて良いです。
| gender | 条件式 | 条件式 or null |
|---|---|---|
| 男性 | true | true |
| 女性 | false | null |
| 男性 | true | true |
| 男性 | true | true |
| 女性 | false | null |
| 男性 | true | true |
この部分がSQL独特の仕様であり、挙動が理解しづらいと主張する点です。
以降はすでに解説したcount関数の仕様によって、nullではない値の個数が数え上げられるので、trueの件数、元をたどると「男性の件数」を数え上げることができます。
応用がきかない
「条件に合致するデータのうち、重複しないデータの件数のSQL」に書いたような応用がききません。
無理やりdistinctを使っても、trueかnullの列に対して重複しないデータ数を数え上げるので、trueの1件のみになります。