SQLのたてもちよこもち。たてもちからよこもちへ

SQLを触っているとよくでてきますが。そのたびにGoogle先生に問い合わせをしているような
気がしています。ということで定着させるためにも一回まとめることにしました。

とりあえず検索

SQLもテーブル構造も出ていてとてもよくまとまっているので、このURLを覚えておくだけで事足りるのでは・・・
dev.classmethod.jp

定着が目的なので、自分でもやってみます。

検証環境

AWS RDS(SQL Server Express Edition)
SSMS v18.4
を使いました。
AWSでSQL Serverをたてて、SSMSで接続するシンプルな形です。

テーブル

こんなノリのやつを用意しました。
gyazo.com
ステータス事の値を横に持ちたい。ということはあるでしょうから、こんな感じでしょう。

とりあえずくっつけてみる。

WINDOW関数もなく、そのまま「えいや！」でくっつけるとこんな形のSQLになるかとおもいます。

select
 emp.employee_id,
 st1.qualification_id,
 st1.value as status_1,
 st2.value as status_2
from
 employee_qualification emp
 left join qualification as st1
 on emp.qualification_id = st1.qualification_id and st1.status = 1
 left join qualification as st2
 on emp.qualification_id = st2.qualification_id and st2.status = 2

実行結果は
gyazo.com

こんな感じで。性能がどうのこうのなければ、このままでもいいんでないかと思います。
せっかくなので、プロファイル情報も取得します。
「SETSTATISTICS PROFILE ON」で取得を有効にすると
gyazo.com

こんな形で取得できます。TotalCostは「0.0104844」
実行計画も以下の通りで。
gyazo.com

JOINをしてるので当然かもしれないですけど、やたらとNastedLoopsがでてきます。
ただただ横持ちを取りたいだけなのに、なんだかこれはいただけない感じがします。

いまはステータスが２つだけなのでいいですが。４つ５つとなった時にこれは無駄なことをしている感じが大きいです。

ということでいい感じにしていく。

今回はRow_Number等でとる必要がなさそうです。Statusわかっているから
まずは横持ちのテーブルぶぶん

select
	st.qualification_id,
	max(case st.status when 1 then st.value else '' end) as value_st1,
	max(case st.status when 2 then st.value else '' end) as value_st2
from 
	(
		select
			qualification_id
			,status
			,value
		from qualification
	) st
group by
	st.qualification_id

gyazo.com
実行計画もいい感じに落ち着いてます。これをそのまま先ほどのクエリに結合していきます。

こんな感じかと。

SET STATISTICS PROFILE ON
select
 emp.employee_id,
 st.qualification_id,
 st.status_1 as status_1,
 st.status_2 as status_2
from
 employee_qualification emp
 left join
 (
	select
		st.qualification_id,
		max(case st.status when 1 then st.value else null end) as status_1,
		max(case st.status when 2 then st.value else null end) as status_2
	from 
		(
			select
				qualification_id
				,status
				,value
			from qualification
		) st
	group by
		st.qualification_id
 ) st
 on emp.qualification_id = st.qualification_id

実行結果は同じようです。ここがずれていたら意味ないですからね。
gyazo.com
コストの方もよいほうに収まりました（0.00686062)
gyazo.com
実行計画も見ておきましょう。
gyazo.com
コンピューティングスカラーは気になりますが・・・気にしていたループの本数が減りました。やったね。
このままでも大分いい感じですが、今後のメンテナンス（出したいステータスが増えたときに）
そこそこ厄介なので、以下のようにしておきます。

select
 emp.employee_id,
 st.*
from
 employee_qualification emp
 left join
 (
	select
		st.qualification_id,
		max(case st.status when 1 then st.value else null end) as status_1,
		max(case st.status when 2 then st.value else null end) as status_2
	from 
		(
			select
				qualification_id
				,status
				,value
			from qualification
		) st
	group by
		st.qualification_id
 ) st
 on emp.qualification_id = st.qualification_id