IPAQガイドラインをクエリで表現するには

　今回は泥臭い話になる．どんなプログラミングでも同じだが，一発で意図通りに動くことはまずない．試行錯誤の末，ようやくこんなものかという出来上がりである．SQL においても同じだ．クエリでは NULL の扱いが難しい．そんなところを感じ取ってもらえればと思う．

IPAQとは

　国際標準化身体活動質問票のことである．アンケート形式で成人の一週間の運動量を半定量的に計測するものであり，メッツ⋅分/週の単位で表現する．

時間のデータ型の暗黙の変換でハマる

　主に運動した日数に 1 日の平均の運動時間を分単位で乗算し，係数（メッツ）をかけて計算する．フォームは Google フォームを用い，回答形式は「時刻」ではなく「時間」を指定して間違えないようにしたのだが，それでも EXCEL の csv ファイルから SQL Server にインポートする際に暗黙の型変換が生じてエラーとなり，インポートが停止した．

結局，EXCELで整数型に変換するハメになる

　SQL Server 側のデータ型を time 型にしたのが間違いの原因だったようだ．ならばと EXCEL 側で時間に 1,440 をかけて分が整数になるようにした．改めて述べるまでもないが，ソフトウェアは 1 日経過するごとに 1 ずつ増えるシリアル値で日時を表現している．これは EXCEL でも SQL Server でも変わらない．問題は結果が無限小数となってしまい，データベース側が整数型だとインポートの際に再びオーバーフローしてしまうことである．

ROUND関数で丸めろ

　この小数を丸めて整数にするのが ROUND 関数である．引数を二つ取り，ひとつ目に該当するセルを指定し，ふたつ目に有効桁数を指定する．0 とすると直近の整数になる．例えば 1.001 を 1 としてくれるのである．注意しないといけないのだが，丸めに INT 関数を使ってはいけない．

　さらに，IF 関数をかぶせて参照するセルが空白の際に空白を返すようにしておく．データベースに NULL としてインポートさせるためである．

この際，NULLもやむを得ない

　データベースでは NULL は禁じ手であり，可能な限り許容すべきでないとされている．しかし，条件分岐型のアンケートなどではスプレッドシートに値が入らないことは普通に起こりうる．ここはぐっとこらえて，インポートの際に NULL を許容する．後述するアルゴリズムで何とか吸収しよう．

NULLの扱いでハマる

　しかし，今度はデータ抽出の際にハマることになる．NULL が一つでも計算に紛れ込んでいると，結果は全て NULL になってしまう．最初それを忘れていて，抽出結果が NULL ばかりになってしまった．

　こういう場合，単純に CASE 式で NULL を 0 に置換したくなる．しかし，ちょっと待て．NULL というものは左外部結合でも発生する．単純に NULL を 0 に置換しただけだと，存在しないデータまで 0 として作り出してしまうことになる．これはまずい．

CASE式でNULLを隠蔽しよう

　基本に立ち返ろう．計算は「メッツ×分/日×日/週」だったな？「全ての」時間が NULL の時は NULL を返し，それ以外の場合に 0 に置換してやればよい．

　ああ，ややこしい．CASE 式の入れ子になる．「全ての」だから WHEN 以下の条件式は AND で接続する．

CASE WHEN Time1 IS NULL AND Time2 IS NULL AND Time3 IS NULL THEN NULL ELSE (Procedure) END

　(Procedure)に本来の計算式を記述する．

Mets * Days1 * (CASE WHEN Time1 IS NULL THEN 0 ELSE [Time1] END) + Mets * Days2 * (CASE WHEN Time2 IS NULL THEN 0 ELSE [Time2] END) + Mets * Days3 * (CASE WHEN Time3 IS NULL THEN 0 ELSE [Time3] END)

　こんな具合になる．仮にある変数に NULL が入っていても，他の二つの計算を生かすためにすべての項を CASE 式でくるんで NULL を隠蔽する．

ガイドラインの足切りルールをクエリに盛り込もう

　国際標準化身体活動質問票のデータ処理および解析に関するガイドラインでも述べたが，「10 分以上継続する活動時間のみを集計の対象とする．10 分未満の回答は 0 と置換する」という足切りルールがある．

　これも CASE 式で表現できる．カッコで括った [Time1], [Time2], [Time3] は分単位で表現した時間の値だが，これらを次の CASE 式でそれぞれ置き換える．

CASE WHEN Time1 < 10 THEN 0 ELSE Time1 END

CASE WHEN Time2 < 10 THEN 0 ELSE Time2 END

CASE WHEN Time3 < 10 THEN 0 ELSE Time3 END

ガイドラインの時間の最大値の上限設定をクエリに盛り込もう

　ガイドラインには時間の最大値の上限も記載されている．具体的には次のようなルールである．

1. 歩行，中等度の身体活動，強い身体活動の合計時間が 16 時間（960 分）を超えるデータは外れ値とみなして解析から除外する
2. 「日数」が「8 日以上」や「分からない」，「回答拒否」などの回答も除外する

　1. の歩行と強い身体活動および 2. の日数については問題ない．問題は中等度の身体活動である．国際標準化身体活動質問票のデータ処理および解析に関するガイドラインの表をよく見ていただきたいが，中等度の身体活動のメッツ係数には 3.0, 4.0, 5.5, 6.0 の四種類がある．これをどのような配分で適用していくのか？いや，「除外する」だから不要で良いのか，という話である．

　疑問は尽きないが，とりあえずガイドラインに従って各ドメインの時間が 16 時間を超えるデータは切り捨てる方針で行く．

　ガイドラインにはそれぞれのドメインが 16 時間とは書いていない．つまり，合計値が 16 時間と解釈できる．単純に合計すればよいだろう．

CASE WHEN Time1 + Time2 + Time3 > 960 THEN NULL ELSE (Procedure) END

　この式で(Procedure)全体を包むのだが，ちょっと楽をして，先の条件式と連結してみよう．

CASE WHEN (Time1 IS NULL AND Time2 IS NULL AND Time3 IS NULL) OR (Time1 + Time2 + Time3 > 960) THEN NULL ELSE (Procedure) END

　条件式をかっこで包んであるのは真理値の順番を明確にするためと，人の目に認識しやすいようにするためである．

　…と思ったのだが，またしても NULL の扱いに手こずることになる．データベースをよく見ると，時間の変数が 11 個もあってアルゴリズムが複雑すぎる．

もう一度，条件を整理しよう

1. 「全ての」時間が NULL の場合，結果は NULL を返す
2. それ以外の場合には，下記の処理に入る
3. 個々の値が 10 分未満なら 0 に置換する
4. 値の総計が 960 分を超えるなら NULL を返す
5. METs * 時間 * 日数を計算する

　上記の 3. と 4. のどちらを先に判定すべきだろうか？個人的には，4. を先に判定して 3. を最後に回すべきだと思う．では，順に記述していこう．11 個も変数を書いてられないので以下省略，でよろしく．

CASE WHEN (Time01 IS NULL) AND (Time02 IS NULL) AND (Time03 IS NULL) AND ... THEN NULL ELSE (Procedure 1) END

　次に (Procedure 1) の中身だ．上記処理の 4. に該当する．

CASE WHEN [Time01] + [Time02] + [Time03] + ... > 960 THEN NULL ELSE (Procedure 2) END

　ここで，各々 [Time01], [Time02], [Time03] とカッコで括ってあるのは下記の式で置換したいからである．ここでも NULL が入り込んでいると式全体が NULL となり，本来計算に入るべきデータが NULL になってしまう．NULL の扱いはかように難しい．

CASE WHEN Time01 IS NULL THEN 0 ELSE Time01 END

CASE WHEN Time02 IS NULL THEN 0 ELSE Time02 END

CASE WHEN Time03 IS NULL THEN 0 ELSE Time03 END

...

　さらに処理 5. を記述する．(Procedure 2) のことだ．

METS01 * Days01 * (CASE WHEN Time01 IS NULL THEN 0 ELSE <Time01> END) + METS02 * Days02 * (CASE WHEN Time02 IS NULL THEN 0 ELSE <Time02> END) + ...

　このような三段構えの式になる．この程度で頭がこんがらかってきたら，フローチャートなんぞ書いてられない．俺もだいぶ混乱してるけどね．

　まだある．処理 3. の 10 分未満の処理を書いていない．<Time01>, <Time02>, ... の式を下記に置換する必要がある．

CASE WHEN Time01 < 10 THEN 0 ELSE Time01 END

CASE WHEN Time02 < 10 THEN 0 ELSE Time02 END

CASE WHEN Time03 < 10 THEN 0 ELSE Time03 END ...

　これでやっとガイドラインに沿った計算ができるようになった．言葉で表現した定義をアルゴリズムに落とし込む作業ってのは，ほんと難しい．要件定義書ってのがいかに重要か，分かったかな？

ガイドラインの基準が変わったら

　なお，当然ながらガイドラインの基準が変わった場合にはデータベース側のクエリを更新しなければならない．ストアドプロシージャにでも書いておいて，将来ガイドラインが変更されたらプロシージャごと切り替えて即座に対応できるよう，引き継ぎ事項に記載しておこう．当然，プロシージャにはバージョン番号を振っておくことだ．