2000/01/08から2005/05/31まで就労
2006/01/08から2010/05/31まで就労
2011/01/08から現在も就労
の全就労日数を求める方法を紹介します。

データ構造サンプル
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL ,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1 ;
CREATE TABLE works (
id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL ,
start_date DATE NOT NULL ,
retire_date DATE NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1 ;

データの情報としては、
users.id:1,users.name:奥進太郎
works.id:1,works.user_id:1,works.start_date:2000/01/08,works.retire_date:2005/05/31
works.id:1,works.user_id:1,works.start_date:2006/01/08,works.retire_date:2010/05/31
works.id:1,works.user_id:1,works.start_date:2011/01/08,works.retire_date:null

1回毎の就労日数を求めるVIEWを作成します。
CREATE VIEW work_diffs AS
select works.user_id AS user_id,
(case when isnull(works.retire_date) then (to_days(curdate()) – to_days(works.start_date))
else (to_days(works.retire_date) – to_days(works.start_date)) end end) AS work_diff
from `works`;

上記VIEWをgroup byで合計すると、全就労日数が取得できます。
CREATE VIEW work_sum_diffs as
select work_diffs.user_id as user_id,sum(work_diffs.work_diff) as sum_work_diff
from work_diffs group by work_diffs.user_id;

usersとwork_diffsをJOINすれば、users毎の全就労日数を取得することができます。

凄く過激なタイトルを付けてしまいました。
これはあるSQL文を見て、それを短く書き換えたときの感情です。


SELECT SUM(CASE WHEN condition > 100 THEN 0 ELSE 1 END) as positive FROM some_table


1行で書いてしまいましたが、もともとはCASE文が数層入れ子になっていて、複雑な条件式を最後にSUMで括っていた、というのが複数項目にも及んでいた感じのSQLでした。そして、CASE式で評価される結果は必ず0か1。つまるところ、CASE文をたくさん組み合わせて条件を作り、条件に合うものを数えるということをやっていたわけです。

最初に思ったのは「WHERE句で条件絞ってcountすればいいじゃん」ってことです

SELECT COUNT(condition) as positive FROM some_table　WHERE condition > 100


そうできればよかったんですが…条件に合う件数を数える項目がほかにもたくさんあり、それぞれで
毎回SQLを発行するのもこれまた頭が悪い。

SQLの返答をphp側でforeach回してオレオレカウントする…というのも考えたのですが、
カウント部分で何をやっているかを把握しようとするとCASE文のピラミッドとどっこいどっこいだなとも。

「テーブル全体を検索しつつ、複数条件それぞれにおいて条件に合うものをcountするには…」と調べていて、
このタイトルの「Say yes or die!」にたどり着いたわけです。

MySQLの論理式にはTrue、FalseのほかにNullを叩き込むことができると言う話があって…

• True AND Null = Null
• False AND Null = False
• True OR Null = True
• False OR Null = Null

つまるところ、Nullの有無にかかわらずTrue/False断言できる式は断言してもらえるという結果になるわけです。
そして、count()はNullでないものを数える。

というわけで生まれるイディオムが　count([何か条件式] OR Null) というわけです。
条件式がTrueならレコードはTrueと評価され、カウントの対象になる、
条件式がFalseまたはNullなら、レコードはFalseと評価され、カウントされない。

このイディオムを使って最初のSQLを書き直すと…

SELECT count((condition > 100) OR NULL)as positive FROM some_table


‘OR NULL’という書き方の意味さえ知っていれば、これが何かを「合計」しているわけではなく「数えている」ことが一目でわかりますし、何より、WHERE句を変えながら何度もSQL発行しなくて済みます。別のカウントをしたいときは条件を変えた検索フィールドを追加するだけですから。

「Say yes or die!」もとい「Be true or null!」使えると思います！