今日はSQLのチューニングに終始してました。
こんなテーブルがあって、
受付(受付ID,受付日時,担当者ID,ステータス),担当者マスタ(担当者ID,担当者名)
受付日時の直近5か月前から未来まで一カ月毎の各ステータスの合計を出すっていうもの。
元のSQLを1時間調べてたんだけど、解釈内容に自信が持てず、全部作り直ししました。
FROM句に副々問い合わせ、SELECT句にも副問い合わせな感じで130行OVERのSQLだったので。。。
で、作り直した最終的なSQLは、こんな感じ(他の条件を全部はしょっているのだけれど。。)。共通テーブル式を使ってて、文字列加工をした列の値を2段目のselect文でgroupbyを掛けているので、たぶん内部では一時的にテーブルを作っているはず。なのでこの部分が改良の余地がありそうだけど。。
WITH 共通テーブル式 (受付月,担当者ID,担当者名,ステータス) AS
(select
substring(convert(varchar,受付日時,111),1,7),
受付.担当者ID,
担当者名,
ステータス
from 受付 left outer join 担当者マスタ
on 受付.担当者ID=担当者マスタ.担当者ID
where 受付日時 >=convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate()),111),1,7)+'/01')
)
select
担当月,
担当者ID,
担当者名,
count(case when ステータス='1' then 担当月 end) as ステータス_1合計,
count(case when ステータス='2' then 担当月 end) as ステータス_2合計,
count(case when ステータス='3' then 担当月 end) as ステータス_3合計,
count(case when ステータス='4' then 担当月 end) as ステータス_4合計
from
共通テーブル式
group by 担当月,担当者ID, 担当者名
order by ....
ま、PIVOTは使っても使わなくても変わりなさそうだし、元は10分かかってたものが2秒になったので、これ以上はいいよね？
こんなものかな。
ついでに思い出せるだけ、昔のSQLを書いてみる。
確か、範囲内の全受付に対して、いちいち担当者毎に月のカウントを取ってdistinctをしていたはずだから、、、
select distinct
substring(convert(varchar,受付_O.受付日時,111),1,7) as 担当月,
受付_O.担当者ID,
担当者名,
(select count(受付_I.受付日時) from 受付 AS 受付_I
where 受付_I.受付日時 >=convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate(),111),1,7)+'/01') and
受付_I.担当者ID=受付_O.担当者ID and 受付_I.ステータス='1' and
受付_I.受付日時 >=convert(datetime,substring(convert(varchar,受付_O.受付日時,111),1,7)+'/01') and
受付_I.受付日時 < convert(datetime,substring(convert(varchar,dateadd(m,1,受付_O.受付日時),111),1,7)+'/01') and
受付_I.ステータス= 受付_O.ステータス
) as ステータス_1合計,
(select count(受付_I.受付日時) from 受付 AS 受付_I
where 受付_I.受付日時 >= convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate(),111),1,7)+'/01') and
受付_I.担当者ID=受付_O.担当者ID and 受付_I.ステータス='2' and
受付_I.受付日時 >=convert(datetime,substring(convert(varchar,受付_O.受付日時,111),1,7)+'/01') and
受付_I.受付日時 < convert(datetime,substring(convert(varchar,dateadd(m,1,受付_O.受付日時),111),1,7)+'/01') and
受付_I.ステータス= 受付_O.ステータス ) as ステータス_2合計,
(select count(受付_I.受付日時) from 受付 AS 受付_I
where 受付_I.受付日時 >= convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate(),111),1,7)+'/01') and
受付_I.担当者ID=受付_O.担当者ID and 受付_I.ステータス='3' and
受付_I.受付日時 >= convert(datetime,substring(convert(varchar,受付_O.受付日時,111),1,7)+'/01') and
受付_I.受付日時 < convert(datetime,substring(convert(varchar,dateadd(m,1,受付_O.受付日時),111),1,7)+'/01') and
受付_I.ステータス= 受付_O.ステータス ) as ステータス_3合計,
(select count(受付_I.受付日時) from 受付 AS 受付_I
where 受付_I.受付日時 >= convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate(),111),1,7)+'/01') and
受付_I.担当者ID=受付_O.担当者ID and 受付_I.ステータス='4' and
受付_I.受付日時 >= convert(datetime,substring(convert(varchar,受付_O.受付日時,111),1,7)+'/01') and
受付_I.受付日時 < convert(datetime,substring(convert(varchar,dateadd(m,1,受付_O.受付日時),111),1,7)+'/01') and
受付_I.ステータス= 受付_O.ステータス ) as ステータス_4合計
from 受付 as 受付_O left outer join 担当者マスタ
on 受付_O.担当者ID = 担当者マスタ.担当者ID and
受付_O.受付日時 >= convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate(),111),1,7)+'/01')
集計するつもりのない、group byとか、distinctとか、union allと動きの異なるunionって、最終手段であって、他の方法が無いかを考えなくちゃ、だね。上の場合は、その月に同じ担当者で、ステータス1のデータが1000件あったら、1000回、同じカウントを行って、出力された同一行をdistinct処理してるってことですよね。なので、残り999回のカウントは不要ってことかい、ってなるはずなんだけど。。。
