【SQL】複数の条件のcountを1回のクエリでおこなう

Tweet

Category MySQL 2012/05/31

問題

こんなテーブル a があります。

create table a (id int, flag int);

こんなふうにデータを入れて、

insert into a (id, flag) values (1, 1), (2, 1), (3, 0), (4, 0), (5, 1);

こんなふうになっているとします。

select * from a;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |    0 |
|  4 |    0 |
|  5 |    1 |
+----+------+

なるべく単純な1つのSQLで、すべてのレコード数と、flag=1のレコード数と、flag=0のレコード数を取得せよ。

なお、サブクエリは使わないこと。

ヒント

集計を3つしたいので、こうなる？

select count(????), count(????), count(????) from a;

????には何が入る？

答え

ポイントは2つ

１、count(*) は件数を数えるが、count(何々)は非NULL値を数える。
　　→ countで数えたいものを非NULL、数えたくないものをNULLにしてしまえばよい。

２、3値論理
　　→ TRUE, FALSE, NULLを使って、数えたいものを非NULL、数えたくないものをNULLにするには？

SQLらしい解答はこんな感じかなと思います。

select count(*), count(flag = 1 or null), count(flag = 0 or null) from a;

+----------+-------------------------+-------------------------+
| count(*) | count(flag = 1 or null) | count(flag = 0 or null) |
+----------+-------------------------+-------------------------+
|        5 |                       3 |                       2 |
+----------+-------------------------+-------------------------+
1 row in set (0.00 sec)

解説

true, false, null の 三値の and, or 演算の結果を確認。

select
    true and true /* true */
    ,true and null /* null */
    ,true and false /* false */
    ,false and null /* false */
    ,false and false /* false */
    ,null and null /* null */
    ,true or true /* true */
    ,true or null /* true */
    ,true or false /* true */
    ,false or null /* null */
    ,false or false /* false */
    ,null or null /* null */

((flag = 1) or null) は、
flag = 1 のとき → true or null → true
flag = 0 のとき → false or null → null

なので、count(flag = 1 or null) で flag = 1 の件数がカウントできる。

この方法は他の条件にも応用が利く。

((何か複雑な条件) or null) → 条件がtrueの件数
((何か複雑な条件) and null) → 条件がfalseの件数

例えばこんな感じに。

select count((予算 between 3000円 and 5000円) or null) from 居酒屋;
/* → 予算が3000円から5000円の居酒屋の件数 */

その他の答え

/* ifでnullか非nullにしてcountする */
select count(*), count(if(flag = 1, 1, null)), count(if(flag = 0, 1, null)) from a;

/* ビット演算子、使ってみました */
SELECT count(*), sum(flag & 1), sum(~flag & 1) FROM a ;

/* ifで0か1にして足す */
select count(*), sum(if(flag=1,1,0)), sum(if(flag=0,1,0)) from a;

スピード対決

問題のテーブルに800万件ほどデータを用意しました。flag の 0/1 はランダムです。

select * from a;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |    0 |
…………
…………（800万件）
…………

エントリーNo.1

select count(*), count(if(flag = 1, 1, null)), count(if(flag = 0, 1, null)) from a;
/* → 約1.45秒 */

エントリーNo.2

SELECT count(*), sum(flag & 1), sum(~flag & 1) FROM a ;
/* → 約2.15秒 */

エントリーNo.3

select count(*), sum(if(flag=1,1,0)), sum(if(flag=0,1,0)) from a;
/* → 約2.60秒 */

エントリーNo.4

select count(*), count(flag = 1 or null), count(flag = 0 or null) from a;
/* → 約1.70秒 */

MySQLでは if の実装が優れているのか、3値論理における論理演算のコストが高いのか、if の方が若干速そう。

演算自体はビット演算がたぶん最速。

足すの（sum）と、数えるの（count）は、数える方が速いみたい。

★ しつこいですが、count(非NULL)の形にするのがポイントで、そのために ～ or null となっています。