untitled: SQL文を考えた

友人がSQLで悩んでいるとのことで一緒に考えてみた。
続くかわからないけど記事としては第一回。
至極小ネタなので読むには値しないと思う。

今回はMySQL限定である。

.

※注意

MySQLは単純クエリのスピードがすばらしいが、複雑なSQL文においては最適なパフォーマンスを出すのが難しい。
そのため、単純なSQLで別々に値を取得し、アプリ側で結合したほうが圧倒的に早くなる場合も多い。
今回はそういう前提を無視したお話であることを理解いただきたい。

■問題

もとのテーブルはひとつで、以下のような列とレコードを持つ。
（実際の業務のテーブルとは構造が違うが導出するSQLを考えるには十分）

phoneテーブル
id (ユーザーID)	carrier (キャリア)	type (種別)	start (契約日)	terminate (解約日)
1	docomo	smart	2010/02/01	2011/02/01
2	au	feature	2010/04/01	null

求めたい結果のテーブルは以下のような集計

2011年2月の集計結果
day (日付)	docomo_smart_start (docomo&&smartの契約数)	docomo_smart_terminate (docomo&&smartの解約数)	docomo_feature_start (docomo&&featureの契約数)	・・・	au_smart_start (au&&smartの契約数)	・・・
2011/02/01	67	12	0	・・・	78	・・・
2011/02/02	56	23	5	・・・	50	・・・
2011/02/03	0	2	0	・・・	0	・・・

・結果の列は固定
・月別で集計する
・その日に契約した数と解約した数を横に並べる

■組み立て方

ポイント

・MySQLなので高度な集約関数はほぼないに等しい。
・契約日、解約日いずれかまたは両方に該当月が含まれるレコードを集計する必要がある。
・列ごとに違う集計なのでCASE式を使う。

基本構成

契約日列と解約日列で別の集計を行って結合する。

左辺を考える

（ここは友人の条件指定には含まれなかったので必要だったかどうかは不明）
MySQLではFULL OUTER JOINができないので、結合する左辺にはすべての日付が含まれなければならない。
だが、契約日列、解約日列どちらかにすべての日付が含まれるとは限らない。
よって、契約日と解約日どちらかに含まれる日付の一覧を作って左辺とする。

基本構成その２

すべての日付を左辺とし、契約日列で集計した結果と解約日列で集計した結果をLEFT JOINする。

■SQL文を作る

2011年2月の集計をするとして。

契約日と解約日どちらかに含まれる日付の一覧を作るSQL

SELECT US.start AS day FROM phone AS US
 WHERE US.start BETWEEN '2011-02-01' AND '2011-02-28'
 UNION
SELECT UT.terminate AS day FROM phone AS UT
 WHERE UT.terminate BETWEEN '2011-02-01' AND '2011-02-28';

このままでは日付が重複して抽出されるので、あとで使うときに重複をなくす。
ただし、UNIONなんかしてしまっているので、かなりパフォーマンスは悪いだろうと思う。

契約日で集計をするSQL

SELECT
       MAX(s_table.start) AS day
      ,SUM(CASE WHEN s_table.carrier='docomo'   AND s_table.type='smart'   THEN 1 ELSE 0 END) AS `d_s`
      ,SUM(CASE WHEN s_table.carrier='docomo'   AND s_table.type='feature' THEN 1 ELSE 0 END) AS `d_f`
      ,SUM(CASE WHEN s_table.carrier='au'       AND s_table.type='smart'   THEN 1 ELSE 0 END) AS `a_s`
      ,SUM(CASE WHEN s_table.carrier='au'       AND s_table.type='feature' THEN 1 ELSE 0 END) AS `a_f`
      ,SUM(CASE WHEN s_table.carrier='softbank' AND s_table.type='smart'   THEN 1 ELSE 0 END) AS `s_s`
      ,SUM(CASE WHEN s_table.carrier='softbank' AND s_table.type='feature' THEN 1 ELSE 0 END) AS `s_f`
  FROM phone AS s_table
 WHERE s_table.start BETWEEN '2011-02-01' AND '2011-02-28'
 GROUP BY s_table.start;

MySQLの場合、SELECT句のstart列をMAX()で囲む必要はないのだが、習慣づけとして。
CASE式を使うことで複数の項目の集計を一回のSQLで実行している。
念のために解説すると、

CASE WHEN s_table.carrier='docomo' AND s_table.type='smart' THEN 1 ELSE 0 END

このCASE式ではレコードでcarrierがdocomoかつtypeがsmartなら1、そうでなければ0を返す指定がされている。
これをSUM()で囲っているので該当行の集計が行われる。

CASE WHEN s_table.carrier='docomo' AND s_table.type='smart' THEN 1 END

とELSEを省くとnullを返してしまい、集計を誤る可能性があるので必ずELSEは書く。

解約日で集計をするSQL

SELECT
       MAX(t_table.terminate) AS day
      ,SUM(CASE WHEN t_table.carrier='docomo'   AND t_table.type='smart'   THEN 1 ELSE 0 END) AS `d_s`
      ,SUM(CASE WHEN t_table.carrier='docomo'   AND t_table.type='feature' THEN 1 ELSE 0 END) AS `d_f`
      ,SUM(CASE WHEN t_table.carrier='au'       AND t_table.type='smart'   THEN 1 ELSE 0 END) AS `a_s`
      ,SUM(CASE WHEN t_table.carrier='au'       AND t_table.type='feature' THEN 1 ELSE 0 END) AS `a_f`
      ,SUM(CASE WHEN t_table.carrier='softbank' AND t_table.type='smart'   THEN 1 ELSE 0 END) AS `s_s`
      ,SUM(CASE WHEN t_table.carrier='softbank' AND t_table.type='feature' THEN 1 ELSE 0 END) AS `s_f`
  FROM phone AS t_table
 WHERE t_table.terminate BETWEEN '2011-02-01' AND '2011-02-28'
 GROUP BY t_table.terminate;

求め方は契約日で集計と同じなので説明は省く。

結果とまとめ

結合してひとつのSQL文にする

SELECT
       DISTINCT UNIONST.day
      ,COALESCE(S.`d_S`,0) AS `docomo_smart_start`
      ,COALESCE(T.`d_s`,0) AS `docomo_smart_terminate`
      ,COALESCE(S.`d_f`,0) AS `docomo_feature_start`
      ,COALESCE(T.`d_f`,0) AS `docomo_feature_terminate`
      ,COALESCE(S.`a_s`,0) AS `au_smart_start`
      ,COALESCE(T.`a_s`,0) AS `au_smart_terminate`
      ,COALESCE(S.`a_f`,0) AS `au_feature_start`
      ,COALESCE(T.`a_f`,0) AS `au_feature_terminate`
      ,COALESCE(S.`s_s`,0) AS `softbank_smart_start`
      ,COALESCE(T.`s_s`,0) AS `softbank_smart_terminate`
      ,COALESCE(S.`s_f`,0) AS `softbank_feature_start`
      ,COALESCE(T.`s_f`,0) AS `softbank_feature_terminate`

FROM

-- 全日付を抽出するクエリ
(SELECT US.start AS day FROM phone AS US
  WHERE US.start BETWEEN '2011-02-01' AND '2011-02-28'
  UNION
 SELECT UT.terminate AS day FROM phone AS UT
  WHERE UT.terminate BETWEEN '2011-02-01' AND '2011-02-28') AS UNIONST

LEFT JOIN
-- 契約日で集計する
(SELECT
        MAX(s_table.start) AS day
       ,SUM(CASE WHEN s_table.carrier='docomo'   AND s_table.type='smart'   THEN 1 ELSE 0 END) AS `d_s`
       ,SUM(CASE WHEN s_table.carrier='docomo'   AND s_table.type='feature' THEN 1 ELSE 0 END) AS `d_f`
       ,SUM(CASE WHEN s_table.carrier='au'       AND s_table.type='smart'   THEN 1 ELSE 0 END) AS `a_s`
       ,SUM(CASE WHEN s_table.carrier='au'       AND s_table.type='feature' THEN 1 ELSE 0 END) AS `a_f`
       ,SUM(CASE WHEN s_table.carrier='softbank' AND s_table.type='smart'   THEN 1 ELSE 0 END) AS `s_s`
       ,SUM(CASE WHEN s_table.carrier='softbank' AND s_table.type='feature' THEN 1 ELSE 0 END) AS `s_f`
  FROM phone AS s_table
 WHERE s_table.start BETWEEN '2011-02-01' AND '2011-02-28'
 GROUP BY s_table.start) AS S
ON S.day = UNIONST.day

LEFT JOIN
-- 解約日で集計する
(SELECT
        MAX(t_table.start) AS day
       ,SUM(CASE WHEN t_table.carrier='docomo'   AND t_table.type='smart'   THEN 1 ELSE 0 END) AS `d_s`
       ,SUM(CASE WHEN t_table.carrier='docomo'   AND t_table.type='feature' THEN 1 ELSE 0 END) AS `d_f`
       ,SUM(CASE WHEN t_table.carrier='au'       AND t_table.type='smart'   THEN 1 ELSE 0 END) AS `a_s`
       ,SUM(CASE WHEN t_table.carrier='au'       AND t_table.type='feature' THEN 1 ELSE 0 END) AS `a_f`
       ,SUM(CASE WHEN t_table.carrier='softbank' AND t_table.type='smart'   THEN 1 ELSE 0 END) AS `s_s`
       ,SUM(CASE WHEN t_table.carrier='softbank' AND t_table.type='feature' THEN 1 ELSE 0 END) AS `s_f`
  FROM phone AS t_table
 WHERE t_table.terminate BETWEEN '2011-02-01' AND '2011-02-28'
 GROUP BY t_table.terminate) AS T
ON T.day = UNIONST.day

ORDER BY UNIONST.day ASC;

SELECT句の値の列をCOALESCE(value, 0)で囲っているのはnullがあった場合の考慮である。
こうしておけば、valueにnullが来ても0を返せる。
契約日列がnullであることがなければ、インラインビューSからの値には本来不要だが、不慮のデータを考慮して念のため使っている。

まとめ

CASE式以外は非常に単純な構成になった。
FULL OUTER JOINが使えればもう少し省略できる。
一晩考えて作ったんだけど、もっと簡単な方法があるのかもしれない。
（むしろブログ書いてる時間のほうが長いかも）
何度も言うけど、総合的なパフォーマンスはまったく考慮していない。
あくまでSQL文ひとつで結果を求めることだけを目的にしている。

書籍の紹介

CASE式の異常なまでの可用性の高さはミック氏の書籍「達人に学ぶSQL徹底指南書」に教えていただいた。

untitled

2011/12/06

SQL文を考えた

※注意

■問題