teratail
回答率
85.48%
質問するログイン新規登録
トップSQL Serverに関する質問ＳＱＬチューニング方法について 3年分の売上から各年月に振り分ける！

編集履歴

回答編集履歴

7

追記

2019/02/18 00:22

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -149,3 +149,15 @@
149
149
  ;
150
150
 
151
151
  ```
152
+
153
+ 追記
154
+
155
+ --
156
+
157
+ SQLServer2012という事で、失礼しました。
158
+
159
+ 一時テーブルなら2012でも対応可能かと思いますので、検討してみて下さい。
160
+
161
+ 店舗グループを正規化した状態の一時テーブルであれば、インデックスも作れますので。
162
+
163
+ [一時テーブル（#テーブル）を使用する](https://www.projectgroup.info/tips/SQLServer/SQL/SQL000021.html)

6

修正

2019/02/18 00:22

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -14,6 +14,8 @@
14
14
 
15
15
  , sum(case 月度 when 6 then 売上金額 else 0 end) as 売上6月
16
16
 
17
+ /*
18
+
17
19
  , sum(case 月度 when 7 then 売上金額 else 0 end) as 売上7月
18
20
 
19
21
  , sum(case 月度 when 8 then 売上金額 else 0 end) as 売上8月
@@ -25,6 +27,8 @@
25
27
  , sum(case 月度 when 11 then 売上金額 else 0 end) as 売上11月
26
28
 
27
29
  , sum(case 月度 when 12 then 売上金額 else 0 end) as 売上12月
30
+
31
+ */
28
32
 
29
33
  , sum(case 月度 when 1 then 売上金額 else 0 end) as 売上1月
30
34
 
@@ -68,33 +72,33 @@
68
72
 
69
73
  --
70
74
 
71
- |グループコード|店舗グループ|年度|4月|5月|6月|7月|8月|9月|10月|11月|12月|1月|2月|3月|
75
+ |グループコード|店舗グループ|年度|4月|5月|6月|1月|2月|3月|
72
76
 
73
- |--:|:--|--:|--:|--:|
77
+ |--:|:--|--:|--:|--:|--:|--:|--:|--:|
74
78
 
75
- 1|Ａスーパーグループ|2019|249695|2420|0|0|0|0|0|0|0|0|0|0
79
+ 1|Ａスーパーグループ|2019|249695|2420|0|0|0|0
76
80
 
77
- 1|Ａスーパーグループ|2018|0|0|0|0|0|0|0|0|0|1320|3310|293470
81
+ 1|Ａスーパーグループ|2018|0|0|0|1320|3310|293470
78
82
 
79
- 1|Ａスーパーグループ|2017|4185|1540|335575|0|0|0|0|0|0|2385|244175|1760
83
+ 1|Ａスーパーグループ|2017|4185|1540|335575|2385|244175|1760
80
84
 
81
- 2|Ｂ商店グループ|2019|0|0|0|0|0|0|0|0|0|0|0|0
85
+ 2|Ｂ商店グループ|2019|0|0|0|0|0|0
82
86
 
83
- 2|Ｂ商店グループ|2018|0|0|0|0|0|0|0|0|0|0|0|0
87
+ 2|Ｂ商店グループ|2018|0|0|0|0|0|0
84
88
 
85
- 2|Ｂ商店グループ|2017|0|0|0|0|0|0|0|0|0|0|0|0
89
+ 2|Ｂ商店グループ|2017|0|0|0|0|0|0
86
90
 
87
- 3|Ｃスーパーグループ|2019|1250|0|0|0|0|0|0|0|0|0|0|0
91
+ 3|Ｃスーパーグループ|2019|1250|0|0|0|0|0
88
92
 
89
- 3|Ｃスーパーグループ|2018|0|0|0|0|0|0|0|0|0|0|0|0
93
+ 3|Ｃスーパーグループ|2018|0|0|0|0|0|0
90
94
 
91
- 3|Ｃスーパーグループ|2017|0|0|0|0|0|0|0|0|0|0|0|0
95
+ 3|Ｃスーパーグループ|2017|0|0|0|0|0|0
92
96
 
93
- 4|Ｄスーパーグループ|2019|2420|0|0|0|0|0|0|0|0|0|0|0
97
+ 4|Ｄスーパーグループ|2019|2420|0|0|0|0|0
94
98
 
95
- 4|Ｄスーパーグループ|2018|0|0|0|0|0|0|0|0|0|0|0|0
99
+ 4|Ｄスーパーグループ|2018|0|0|0|0|0|0
96
100
 
97
- 4|Ｄスーパーグループ|2017|0|0|0|0|0|0|0|0|0|0|0|0
101
+ 4|Ｄスーパーグループ|2017|0|0|0|0|0|0
98
102
 
99
103
  ```DATA
100
104
 

5

修正

2019/02/16 13:59

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -8,9 +8,29 @@
8
8
 
9
9
  select グループコード, 店舗グループ, t1.年度
10
10
 
11
- , sum(case when 売上日 BETWEEN '2019/04/01' AND '2019/04/30' then 売上金額 else 0 end) as 当年4月売上金額
11
+ , sum(case 月度 when 4 then 売上金額 else 0 end) as 売上4月
12
12
 
13
- , sum(case when 売上日 BETWEEN '2019/05/01' AND '2019/05/31' then 売上金額 else 0 end) as 当年5月売上金額
13
+ , sum(case 月度 when 5 then 売上金額 else 0 end) as 売上5月
14
+
15
+ , sum(case 月度 when 6 then 売上金額 else 0 end) as 売上6月
16
+
17
+ , sum(case 月度 when 7 then 売上金額 else 0 end) as 売上7月
18
+
19
+ , sum(case 月度 when 8 then 売上金額 else 0 end) as 売上8月
20
+
21
+ , sum(case 月度 when 9 then 売上金額 else 0 end) as 売上9月
22
+
23
+ , sum(case 月度 when 10 then 売上金額 else 0 end) as 売上10月
24
+
25
+ , sum(case 月度 when 11 then 売上金額 else 0 end) as 売上11月
26
+
27
+ , sum(case 月度 when 12 then 売上金額 else 0 end) as 売上12月
28
+
29
+ , sum(case 月度 when 1 then 売上金額 else 0 end) as 売上1月
30
+
31
+ , sum(case 月度 when 2 then 売上金額 else 0 end) as 売上2月
32
+
33
+ , sum(case 月度 when 3 then 売上金額 else 0 end) as 売上3月
14
34
 
15
35
  from (
16
36
 
@@ -18,17 +38,19 @@
18
38
 
19
39
  from 店舗グループ CROSS APPLY STRING_SPLIT(店舗コード, ',')
20
40
 
21
- cross join (
41
+ cross join (
22
42
 
23
- select cast(value as int) as 年度 from STRING_SPLIT('2017,2018,2019',',')
43
+ select cast(value as int) as 年度 from STRING_SPLIT('2017,2018,2019',',')
24
44
 
25
- ) 対象年度
45
+ ) 対象年度
46
+
47
+
26
48
 
27
49
  ) as t1 left join (
28
50
 
29
51
  select *, year(dateadd(month,-3,売上日)) as 年度 , month(売上日) as 月度 from 売上
30
52
 
31
- -- where 売上日 between [3年前] and [現在]
53
+ -- where 売上日 between '2017/04/01' and '2019/03/31'
32
54
 
33
55
  ) t2
34
56
 
@@ -38,39 +60,41 @@
38
60
 
39
61
  order by グループコード, t1.年度 desc
40
62
 
63
+
64
+
41
65
  ```
42
66
 
43
67
  結果
44
68
 
45
69
  --
46
70
 
47
- |グループコード|店舗グループ|年度|当年4月売上金額|当年5月売上金額|
71
+ |グループコード|店舗グループ|年度|4月|5月|6月|7月|8月|9月|10月|11月|12月|1月|2月|3月|
48
72
 
49
73
  |--:|:--|--:|--:|--:|
50
74
 
51
- 1|Ａスーパーグループ|2019|249695|2420
75
+ 1|Ａスーパーグループ|2019|249695|2420|0|0|0|0|0|0|0|0|0|0
52
76
 
53
- 1|Ａスーパーグループ|2018|0|0
77
+ 1|Ａスーパーグループ|2018|0|0|0|0|0|0|0|0|0|1320|3310|293470
54
78
 
55
- 1|Ａスーパーグループ|2017|0|0
79
+ 1|Ａスーパーグループ|2017|4185|1540|335575|0|0|0|0|0|0|2385|244175|1760
56
80
 
57
- 2|Ｂ商店グループ|2019|0|0
81
+ 2|Ｂ商店グループ|2019|0|0|0|0|0|0|0|0|0|0|0|0
58
82
 
59
- 2|Ｂ商店グループ|2018|0|0
83
+ 2|Ｂ商店グループ|2018|0|0|0|0|0|0|0|0|0|0|0|0
60
84
 
61
- 2|Ｂ商店グループ|2017|0|0
85
+ 2|Ｂ商店グループ|2017|0|0|0|0|0|0|0|0|0|0|0|0
62
86
 
63
- 3|Ｃスーパーグループ|2019|1250|0
87
+ 3|Ｃスーパーグループ|2019|1250|0|0|0|0|0|0|0|0|0|0|0
64
88
 
65
- 3|Ｃスーパーグループ|2018|0|0
89
+ 3|Ｃスーパーグループ|2018|0|0|0|0|0|0|0|0|0|0|0|0
66
90
 
67
- 3|Ｃスーパーグループ|2017|0|0
91
+ 3|Ｃスーパーグループ|2017|0|0|0|0|0|0|0|0|0|0|0|0
68
92
 
69
- 4|Ｄスーパーグループ|2019|2420|0
93
+ 4|Ｄスーパーグループ|2019|2420|0|0|0|0|0|0|0|0|0|0|0
70
94
 
71
- 4|Ｄスーパーグループ|2018|0|0
95
+ 4|Ｄスーパーグループ|2018|0|0|0|0|0|0|0|0|0|0|0|0
72
96
 
73
- 4|Ｄスーパーグループ|2017|0|0
97
+ 4|Ｄスーパーグループ|2017|0|0|0|0|0|0|0|0|0|0|0|0
74
98
 
75
99
  ```DATA
76
100
 

4

修正

2019/02/16 13:56

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -28,7 +28,7 @@
28
28
 
29
29
  select *, year(dateadd(month,-3,売上日)) as 年度 , month(売上日) as 月度 from 売上
30
30
 
31
- where 売上日 between [3年前] and [現在]
31
+ -- where 売上日 between [3年前] and [現在]
32
32
 
33
33
  ) t2
34
34
 
@@ -52,7 +52,7 @@
52
52
 
53
53
  1|Ａスーパーグループ|2018|0|0
54
54
 
55
- 1|Ａスーパーグループ|2017|4185|1540
55
+ 1|Ａスーパーグループ|2017|0|0
56
56
 
57
57
  2|Ｂ商店グループ|2019|0|0
58
58
 

3

修正

2019/02/16 13:47

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -36,7 +36,7 @@
36
36
 
37
37
  group by グループコード, 店舗グループ, t1.年度
38
38
 
39
- order by グループコード, t1.年度
39
+ order by グループコード, t1.年度 desc
40
40
 
41
41
  ```
42
42
 
@@ -48,29 +48,29 @@
48
48
 
49
49
  |--:|:--|--:|--:|--:|
50
50
 
51
- 1|Ａスーパーグループ|2017|4185|1540
51
+ 1|Ａスーパーグループ|2019|249695|2420
52
52
 
53
53
  1|Ａスーパーグループ|2018|0|0
54
54
 
55
- 1|Ａスーパーグループ|2019|249695|2420
55
+ 1|Ａスーパーグループ|2017|4185|1540
56
+
57
+ 2|Ｂ商店グループ|2019|0|0
58
+
59
+ 2|Ｂ商店グループ|2018|0|0
56
60
 
57
61
  2|Ｂ商店グループ|2017|0|0
58
62
 
59
- 2|Ｂ商店グループ|2018|0|0
63
+ 3|Ｃスーパーグループ|2019|1250|0
60
64
 
61
- 2|Ｂ商店グループ|2019|0|0
65
+ 3|Ｃスーパーグループ|2018|0|0
62
66
 
63
67
  3|Ｃスーパーグループ|2017|0|0
64
68
 
65
- 3|Ｃスーパーグループ|2018|0|0
66
-
67
- 3|Ｃスーパーグループ|2019|1250|0
68
-
69
- 4|Ｄスーパーグループ|2017|0|0
69
+ 4|Ｄスーパーグループ|2019|2420|0
70
70
 
71
71
  4|Ｄスーパーグループ|2018|0|0
72
72
 
73
- 4|Ｄスーパーグループ|2019|2420|0
73
+ 4|Ｄスーパーグループ|2017|0|0
74
74
 
75
75
  ```DATA
76
76
 

2

追記

2019/02/16 13:39

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -2,9 +2,11 @@
2
2
 
3
3
  クロス集計部分はUNIONにしなくてもsum()内でcase文判定すれば簡潔にできそうです。
4
4
 
5
+ ※結果の年度について不足があったので追加
6
+
5
7
  ```SQL
6
8
 
7
- select グループコード, 店舗グループ
9
+ select グループコード, 店舗グループ, t1.年度
8
10
 
9
11
  , sum(case when 売上日 BETWEEN '2019/04/01' AND '2019/04/30' then 売上金額 else 0 end) as 当年4月売上金額
10
12
 
@@ -12,18 +14,110 @@
12
14
 
13
15
  from (
14
16
 
15
- select グループコード, 店舗名 as 店舗グループ, value as 店舗コード
17
+ select グループコード, 店舗名 as 店舗グループ, value as 店舗コード, 年度
16
18
 
17
19
  from 店舗グループ CROSS APPLY STRING_SPLIT(店舗コード, ',')
18
20
 
21
+ cross join (
22
+
23
+ select cast(value as int) as 年度 from STRING_SPLIT('2017,2018,2019',',')
24
+
25
+ ) 対象年度
26
+
19
27
  ) as t1 left join (
20
28
 
29
+ select *, year(dateadd(month,-3,売上日)) as 年度 , month(売上日) as 月度 from 売上
30
+
21
- select * from 売上テーブル where 売上日 between [3年前] and [現在]
31
+ where 売上日 between [3年前] and [現在]
22
32
 
23
33
  ) t2
24
34
 
25
- on t1.店舗コード=t2.店舗コード
35
+ on t1.店舗コード=t2.店舗コード and t1.年度=t2.年度
26
36
 
27
- group by グループコード, 店舗グループ
37
+ group by グループコード, 店舗グループ, t1.年度
38
+
39
+ order by グループコード, t1.年度
28
40
 
29
41
  ```
42
+
43
+ 結果
44
+
45
+ --
46
+
47
+ |グループコード|店舗グループ|年度|当年4月売上金額|当年5月売上金額|
48
+
49
+ |--:|:--|--:|--:|--:|
50
+
51
+ 1|Ａスーパーグループ|2017|4185|1540
52
+
53
+ 1|Ａスーパーグループ|2018|0|0
54
+
55
+ 1|Ａスーパーグループ|2019|249695|2420
56
+
57
+ 2|Ｂ商店グループ|2017|0|0
58
+
59
+ 2|Ｂ商店グループ|2018|0|0
60
+
61
+ 2|Ｂ商店グループ|2019|0|0
62
+
63
+ 3|Ｃスーパーグループ|2017|0|0
64
+
65
+ 3|Ｃスーパーグループ|2018|0|0
66
+
67
+ 3|Ｃスーパーグループ|2019|1250|0
68
+
69
+ 4|Ｄスーパーグループ|2017|0|0
70
+
71
+ 4|Ｄスーパーグループ|2018|0|0
72
+
73
+ 4|Ｄスーパーグループ|2019|2420|0
74
+
75
+ ```DATA
76
+
77
+ create table 売上(売上日 date, 店舗コード varchar(4), 売上金額 int);
78
+
79
+ insert into 売上 values
80
+
81
+ ('2017/04/01', '0001', 4185 )
82
+
83
+ ,('2017/05/01', '0001', 1540 )
84
+
85
+ ,('2017/06/01', '0002', 335575 )
86
+
87
+ ,('2018/01/02', '0001', 2385 )
88
+
89
+ ,('2018/02/02', '0001', 244175 )
90
+
91
+ ,('2018/03/02', '0001', 1760 )
92
+
93
+ ,('2019/01/03', '0001', 1320 )
94
+
95
+ ,('2019/02/03', '0001', 3310 )
96
+
97
+ ,('2019/03/03', '0001', 293470 )
98
+
99
+ ,('2019/04/04', '0001', 249695 )
100
+
101
+ ,('2019/05/04', '0001', 2420 )
102
+
103
+ ,('2019/04/04', '0007', 1250 )
104
+
105
+ ,('2019/04/07', '0101', 2420 )
106
+
107
+ ;
108
+
109
+ create table 店舗グループ(グループコード integer, 店舗コード varchar(255),店舗名 varchar(255));
110
+
111
+ insert into 店舗グループ values
112
+
113
+ (1, '0001,0002,0003', 'Ａスーパーグループ' )
114
+
115
+ ,(2, '0004,0005,0008', 'Ｂ商店グループ' )
116
+
117
+ ,(3, '0006,0007,0009', 'Ｃスーパーグループ' )
118
+
119
+ ,(4, '0101,0102,0103', 'Ｄスーパーグループ' )
120
+
121
+ ;
122
+
123
+ ```

1

補正

2019/02/16 13:36

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -16,7 +16,11 @@
16
16
 
17
17
  from 店舗グループ CROSS APPLY STRING_SPLIT(店舗コード, ',')
18
18
 
19
- ) as t1 left join 売上テーブル t2
19
+ ) as t1 left join (
20
+
21
+ select * from 売上テーブル where 売上日 between [3年前] and [現在]
22
+
23
+ ) t2
20
24
 
21
25
  on t1.店舗コード=t2.店舗コード
22
26