(*)前回の不適切なSQL文を用いてPIVOTは遅いと結論付けてしまいました。改めて試行すると、遅くなかったので報告します。
誤った情報で迷惑をお掛けしました。ごめんなさい。
さて、下記のSQL文で処理したのですが、額の表示で SUM(額)かMax(額)にすべきかのコメントを貰ったのですが、結果が異なりました。
★PIVOT処理
select Top n 社員CODE ,[1Y] 予定1月,[1J] 実績1月,[2Y] 予定2月,[2J] 実績2月,[3Y] 予定3月,[3J] 実績3月,[4Y] 予定4月,[4J] 実績4月
,[5Y] 予定5月,[5J] 実績5月,[6Y] 予定6月,[6J] 実績6月,[7Y] 予定7月,[7J] 実績7月,[8Y] 予定4月,[8J] 実績8月
,[9Y] 予定9月,[9J] 実績9月,[10Y] 予定10月,[10J] 実績10月,[11Y] 予定11月,[11J] 実績11月,[12Y] 予定12月,[12J] 実績12月
From
(
select 社員CODE,cast(月 as varchar)+予実 区分,額 from 縦持ち
) T
PIVOT
(
sum(額) for 区分 in ([1Y] ,[1J],[2Y] ,[2J],[3Y] ,[3J],[4Y] ,[4J],
[5Y] ,[5J],[6Y] ,[6J],[7Y] ,[7J],[8Y] ,[8J],
[9Y] ,[9J],[10Y] ,[10J],[11Y] ,[11J],[12Y] ,[12J]
)
)
as PIVOT_TABLE
order by 社員CODE
■SUM(額)で実行
1人分:00.0156
10人分:00.0312
100人分:00.1872
1000人分:01.1388
10000人分:11.1944
100000人分:81.2126
■MAX(額)で実行
1人分:00.0312000
10人分:00.0468000
100人分:00.1248000
1000人分:00.5772000
10000人分:05.7096000
100000人分:41.2776000
■MAXMIN(額)で実行
1人分:00.0312000
10人分:00.0468000
100人分:00.0780000
1000人分:00.6084000
10000人分:05.6472000
100000人分:41.1800000
■AVG(額)で実行
1人分:00.0624000
10人分:00.0780000
100人分:00.1404000
1000人分:01.1232000
10000人分:11.2008000
100000人分:80.4960000
SUM()とAVG() はMAX(),MIN()の倍、要してますね。「内部ロジックなので差は少ない」と思い込んでいたので少しショック。
★LeftJoinで縦横展開..のコメントで参照先を教えて貰いました。すこし強引な力業で、遅いだろうなという印象を持ったのですが。
select top n T.社員CODE
, y1.額 月1予算 ,j1.額 月1実績
, y2.額 月2予算 ,j2.額 月2実績
, y3.額 月3予算 ,j3.額 月3実績
, y4.額 月4予算 ,j4.額 月4実績
, y5.額 月5予算 ,j5.額 月5実績
, y6.額 月6予算 ,j6.額 月6実績
, y7.額 月7予算 ,j7.額 月7実績
, y8.額 月8予算 ,j8.額 月8実績
, y9.額 月9予算 ,j9.額 月9実績
, y10.額 月10予算 ,j10.額 月10実績
, y11.額 月11予算 ,j11.額 月11実績
, y12.額 月12予算 ,j12.額 月12実績
from 縦持ち T
left join 縦持ち as y1 on T.社員CODE = y1.社員CODE and y1.月=1 and y1.予実='Y'
left join 縦持ち as j1 on T.社員CODE = j1.社員CODE and j1.月=1 and j1.予実='J'
left join 縦持ち as y2 on T.社員CODE = y2.社員CODE and y2.月=2 and y2.予実='Y'
left join 縦持ち as j2 on T.社員CODE = j2.社員CODE and j2.月=2 and j2.予実='J'
left join 縦持ち as y3 on T.社員CODE = y3.社員CODE and y3.月=3 and y3.予実='Y'
left join 縦持ち as j3 on T.社員CODE = j3.社員CODE and j3.月=3 and j3.予実='J'
left join 縦持ち as y4 on T.社員CODE = y4.社員CODE and y4.月=4 and y4.予実='Y'
left join 縦持ち as j4 on T.社員CODE = j4.社員CODE and j4.月=4 and j4.予実='J'
left join 縦持ち as y5 on T.社員CODE = y5.社員CODE and y5.月=5 and y5.予実='Y'
left join 縦持ち as j5 on T.社員CODE = j5.社員CODE and j5.月=5 and j5.予実='J'
left join 縦持ち as y6 on T.社員CODE = y6.社員CODE and y6.月=6 and y6.予実='Y'
left join 縦持ち as j6 on T.社員CODE = j6.社員CODE and j6.月=6 and j6.予実='J'
left join 縦持ち as y7 on T.社員CODE = y7.社員CODE and y7.月=7 and y7.予実='Y'
left join 縦持ち as j7 on T.社員CODE = j7.社員CODE and j7.月=7 and j7.予実='J'
left join 縦持ち as y8 on T.社員CODE = y8.社員CODE and y8.月=8 and y8.予実='Y'
left join 縦持ち as j8 on T.社員CODE = j8.社員CODE and j8.月=8 and j8.予実='J'
left join 縦持ち as y9 on T.社員CODE = y9.社員CODE and y9.月=9 and y9.予実='Y'
left join 縦持ち as j9 on T.社員CODE = j9.社員CODE and j9.月=9 and j9.予実='J'
left join 縦持ち as y10 on T.社員CODE = y10.社員CODE and y10.月=10 and y10.予実='Y'
left join 縦持ち as j10 on T.社員CODE = j10.社員CODE and j10.月=10 and j10.予実='J'
left join 縦持ち as y11 on T.社員CODE = y11.社員CODE and y11.月=11 and y11.予実='Y'
left join 縦持ち as j11 on T.社員CODE = j11.社員CODE and j11.月=11 and j11.予実='J'
left join 縦持ち as y12 on T.社員CODE = y12.社員CODE and y12.月=12 and y12.予実='Y'
left join 縦持ち as j12 on T.社員CODE = j12.社員CODE and j12.月=12 and j12.予実='J'
order by T.社員CODE
■Left Join文
1人分:00.0468000
10人分:00.1716000
100人分:01.0452000
1000人分:09.1104000
10000人分:48.4224000
100000人分: SQL server機で Outof memory発生: 大量のメモリーを食うようです。
なんと、Pivot のMax(額)に近い数値... LeftJoinを見直しました。
横持ちと縦持ち_その後3 -で報告した値(下記)の倍くらいでしょうか。
横持ち 縦横変換
1件 00.014 00.004
10件 00.015 00.008
100件 00.017 00.042
1000件 00.048 00.322
10000件 01.913 21.913
今シリーズは、速度比だけを重視した報告になり、実務に適していない面があります。
倍程度の速度差なら、どちらを使っても差違がなく、アプリケーション・ソースの作りやすいほうが良いと思います。
縦横は優劣の問題でなく、向き不向きを吟味する必要があります。
とはいうものの、私は、縦持ち派ですが。