teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問SQLで複数テーブルから、連日7日分の条件に合った合算データを抽出したい

編集履歴

質問編集履歴

8

ロールバック

2015/10/21 21:20

投稿

mimipachi0133
mimipachi0133

スコア9

test CHANGED
@@ -1 +1 @@
1
- SQLで複数テーブルから、登録年月日上位7日分の条件に合った合算データを抽出したい
1
+ SQLで複数テーブルから、連日7日分の条件に合った合算データを抽出したい
test CHANGED
@@ -1,28 +1,20 @@
1
+ いつもお世話になっております。
2
+
3
+ 当方、とある顧客データのピックアップ作業をSQL Server 2012 で行っているのですが、
4
+
5
+ 業務についてまだ日が浅く、なかなか思うようにデータ抽出が行えておりません。
6
+
1
- 昨日はご協力ありがとうございました。
7
+ ご協力お願いいたします。
2
-
3
- 本日、本番環境のDBで頂いた回答を参考にチャレンジした結果、私の理解が到らないばかりにうまく結果を得られませんでした。
4
-
5
- もう少しお力をお貸し頂けたらと思い、追記させていただきます。
6
-
7
- また、追加で抽出条件の注文があったので、それも反映しつつ書かせて頂きます。
8
-
9
- （本日のSQL、説明等は本番DBの環境用に書かれていますので、昨日よりも複雑になっております。）
10
8
 
11
9
 
12
10
 
13
11
  ・取得したいデータ
14
12
 
15
- ※先日は勘違いしていたのですが、「 更新年月日の登録用テーブルのトップ7日分の年月日を選択し（連続した年月日とは限りません。）取得したい」・「毎月1日のデータは当月の累計データは無いので、空で取得したい」と先方から言われましたので、希望抽出条件を修正します。
16
-
17
-
18
-
19
- 更新年月日の登録用テーブル（[T_受注残]、[T_地区在庫]）のトップ7日分の年月日を選択し（連続した年月日とは限りません。）3つの構造が同じテーブルから、条件の一致した[年月日][倉庫コード][製品コード]でグループにした本数の合算を求める。その際、
13
+ 当日から7日間の連日日付において、3つの構造が同じテーブルから、条件の一致した[年月日][倉庫コード][製品コード]でグループにした本数の合算を求める。その際、
20
14
 
21
15
  を都度合算した結果のデータ。
22
16
 
23
-
24
-
25
- （例えば、最新年月日が20150903、2015829にデータを受信していない場合、取得したいデータは
17
+ （例えば、最新年月日が20150903の場合、取得したいデータは
26
18
 
27
19
 
28
20
 
@@ -30,16 +22,16 @@
30
22
 
31
23
  20150902 → 20150901のみ（本数）
32
24
 
33
- 20150901 → 空（NULL）で出力
25
+ 20150901 → 20150801から20150831までのSUM値（本数）
34
26
 
35
27
  20150831 → 20150801から20150830までのSUM値（本数）
36
28
 
37
29
  20150830 → 20150801から20150829までのSUM値（本数）
38
30
 
31
+ 20150829 → 20150801から20150828までのSUM値（本数）
32
+
39
33
  20150828 → 20150801から20150827までのSUM値（本数）
40
34
 
41
- 20150827 → 20150801から20150826までのSUM値（本数）
42
-
43
35
 
44
36
 
45
37
  取得データにつきましては、以下のような形で取得したいと考えております。
@@ -50,342 +42,236 @@
50
42
 
51
43
  20150903,BBBBB,dsfgrhgf,59826
52
44
 
53
- 20150902,AAAAA,dfefgrth,258847
45
+ 20150902,AAAAA,dfefgrth,258847
54
-
55
- 20150901,BBBBB,fdrufehu,NULL
56
46
 
57
47
 
58
48
 
59
49
  このように、選択年月日-1日の年月日の当月前日までのデータの合算を1回のSQL実行で行いたいと考えております。
60
50
 
61
-
62
-
63
- ・取得方法（前日頂いた回答を参考にしました）
64
-
65
- --ここから、WITH句です
66
-
67
- ・データ登録日時テーブル（[T_受注残]、[T_地区在庫]）からトップ年月日7日分を取得する。
68
-
69
- （テーブル[T_受注残]、[T_地区在庫]は、データ登録日時確認用のテーブルです。構成カラムは[年月日],[ファイル名称]で、[T_地区在庫]が[T_地区出荷]と[T_地区入荷]の更新情報、[T_受注残]が[T_工場出荷]の更新情報となります。）（[WK_日付範囲]）
70
-
71
- ・データ登録日時テーブル[T_受注残]と[T_地区在庫]の最新日時7日分の日時範囲で一時テーブルを作成（[WK_表示年月日計算]）
72
-
73
- ・各年月日に対応した[集計対象初日]、[集計対象最終日]を設定（[WK_取得開始日割当]）
74
-
75
- ・本数データ取得対象の[T_地区出荷]と[T_地区入荷]と[T_受注残]をUNION ALLする
76
-
77
- （[TMP_マスタテーブル] ）
78
-
79
- ・[倉庫コード],[製品コード親]でグルーピング（[TMP_コードグルーピング]）
80
-
81
- --ここまで、WITH句です。
82
-
83
- ・最後に、[TMP_マスタテーブル][WK_取得開始日割当] [TMP_コードグルーピング] を使用して、各年月日の[年月日]（ここでNUMERICに戻しています）[倉庫コード][製品コード親]についての、累計本数を抽出しようとしました。
84
-
85
-
86
-
87
- しかしこのSQLを動かしたところ、3時間以上かかっても、1日分のデータも出しきれていませんでした。
88
-
89
- 方法は良さそうなのですが、実用に足るスピードアップの方法を考えているところです。
90
-
91
-
92
-
93
- 以下に使用SQLを書きます（ややうろ覚えなので疑問点あればお願いいたします）。
94
-
95
-
96
-
97
- --このSQLで動かした結果、3時間経過しても全データ抽出ができませんでした。
98
-
99
- --今後の利用も考えて長くても20～30分くらいで取得できるようにしたいです。
100
-
101
-
102
-
103
-
104
-
105
- DECLARE
106
-
107
- @MASTER_YM AS NUMERIC
51
+ 年月日の割当については、一時テーブルで最新日7日分を選択し、結合できるようですが…（実は検索等で調べたので信憑性があやしいです、スイマセン）
52
+
53
+
54
+
55
+ 取得データにつきましては、以下のような形で取得したいと考えております。
56
+
57
+ [年月日],[倉庫コード],[製品コード],SUM[(本数)]
58
+
59
+ 20150903,AAAAA,abuodsds,156325
60
+
61
+ 20150903,BBBBB,dsfgrhgf,59826
62
+
63
+ 20150902,AAAAA,dfefgrth,258847
64
+
65
+ ・・・・
66
+
67
+
68
+
69
+ ・取得方法（チャレンジしたところまで）
70
+
71
+ ・データ登録日時テーブル[AA10]と[AA11]から最新年月日を取得し、その年月日を基準に、
72
+
73
+ 最新年月日に対する月末日、前月初日の年月日を取得
74
+
75
+ （テーブル[AA10]と[AA11]は、データ登録日時確認用のテーブルです。構成カラムは[年月日],[ファイル名称]で、[AA10]が[Ａテーブル]と[Ｂテーブル]の更新情報、[AA11]が[Cテーブル]の更新情報となります。業務上、「当日」の年月日はこのテーブルから取得しないといけません。）
76
+
77
+ ・データ登録日時テーブル[AA10]と[AA11]の最新日時7日分の日時範囲で一時テーブルを作成
78
+
79
+ ・本数データ取得対象の[Ａテーブル]と[Ｂテーブル]と[Cテーブル]をUNION ALLする
80
+
81
+
82
+
83
+ ここから、どうすればいいのかわからなくなっております。
84
+
85
+ （一応下記SQLでOVER句を使用しておりますが、意味は通っておりません。）
86
+
87
+
88
+
89
+ 一時テーブルの使用・OVER句（PARTITION BY~）による[年月日][倉庫コード][製品コード]のグループ化などを行う必要があるそうですが、イマイチ理解できておりません。
90
+
91
+
92
+
93
+ 大変わかりくにい質問となってしまいましたが、ご協力お願いいたします。
94
+
95
+
96
+
97
+ --以下、SQL文（書いたことろまで）
98
+
99
+
100
+
101
+ DECLARE
102
+
103
+ @START_DATE AS NUMERIC --開始日(前月月初日)
104
+
105
+ , @END_DATE AS NUMERIC --実績終了日(前日)
106
+
107
+ , @LAST_EOM AS NUMERIC --前月末日
108
+
109
+
110
+
111
+ SET @END_DATE = (SELECT MAX(T10.[年月日])
112
+
113
+ FROM(
114
+
115
+ SELECT MAX(T01.[年月日]) AS [年月日] FROM [BATABLE].[dbo].[AA10] T01 WHERE T01.[品種] = 'AT' AND T01.[内訳] NOT IN ('6', '9')
116
+
117
+ UNION ALL
118
+
119
+ SELECT MAX(T02.[年月日]) AS [年月日] FROM [BATABLE].[dbo].[AA11] T02 WHERE T02.[品種] = 'AT' AND T02.[内訳] NOT IN ('6', '9')
120
+
121
+ ) T10);
108
122
 
109
123
 
110
124
 
111
- /** M_物流製品親子マスタ（工場系）は過去マスタデータを蓄積するので、最新月しか持たないM_サイズ処理定義マスタ（地区系）と齟齬が起きないように使用年月を固定しています **/
125
+ SET @LAST_EOM=(SELECT CAST(CONVERT(VARCHAR,EOMONTH(DATEADD(MONTH,-1,CONVERT(DATETIME,CAST(@END_DATE AS VARCHAR),112))),112) AS NUMERIC));
112
-
126
+
113
- SET @MASTER_YM=(SELECT MAX([年月]) FROM [BI_T].[dbo].[M_サイズ処理定義マスタ]);
127
+ SET @START_DATE=(SELECT CAST(CONCAT(LEFT(@LAST_EOM,6),'01') AS NUMERIC));
128
+
129
+
114
130
 
115
131
 
116
132
 
117
133
  WITH
118
134
 
119
-
120
-
121
135
  /*********************************************************
122
136
 
123
137
  日付範囲
124
138
 
125
139
  **********************************************************/
126
140
 
127
- [WK_日付範囲] AS (
141
+ [NT_日付範囲] AS
142
+
128
-
143
+ (SELECT TOP 7 [年月日] FROM
144
+
145
+ (SELECT TOP 7 [年月日] FROM [BATABLE].[dbo].[AA10] GROUP BY [年月日] WHERE [年月日] ORDER BY [年月日] DESC
146
+
147
+ UNION
148
+
129
- SELECT TOP 7 CONVERT(DATE, CONVERT(CHAR(8), MAX([年月日])), 112) AS [対象年月日]
149
+ SELECT TOP 7 [年月日] FROM [BATABLE].[dbo].[AA11] GROUP BY [年月日] WHERE [年月日] ORDER BY [年月日] DESC
150
+
130
-
151
+ ) X
152
+
153
+ ORDER BY [年月日] DESC
154
+
155
+ )
156
+
157
+
158
+
159
+ SELECT
160
+
161
+ [年月日]
162
+
163
+ , [倉庫コード]
164
+
165
+ , [製品コード]
166
+
167
+ , SUM([本数]) AS [本数]
168
+
131
- FROM
169
+ FROM
132
-
133
- (SELECT TOP 7 年月日 FROM [BI_T].[dbo].[T_受注残] GROUP BY 年月日 ORDER BY 年月日 DESC
170
+
134
-
135
- UNION
136
-
137
- SELECT TOP 7 年月日 FROM [BI_T].[dbo].[T_地区在庫] GROUP BY 年月日 ORDER BY 年月日 DESC
138
-
139
- )W
140
-
141
- GROUP BY [年月日]
142
-
143
- ORDER BY [年月日] DESC
144
-
145
- ),
146
-
147
-
148
-
149
- /*********************************************************
150
-
151
- 年月日計算
152
-
153
- **********************************************************/
154
-
155
- [WK_表示年月日計算] AS (
156
-
157
- SELECT [基準日], DATEADD(d, -1, [基準日]) AS [集計対象最終日]
158
-
159
- FROM
160
-
161
- (
162
-
163
- SELECT [対象年月日] AS [基準日] FROM [WK_日付範囲]
164
-
165
- )Y
166
-
167
- ),
168
-
169
- [WK_取得開始日割当] AS (
170
-
171
- SELECT [基準日],[集計対象最終日],
172
-
173
- DATEFROMPARTS(YEAR([集計対象最終日]), MONTH([集計対象最終日]), 1) AS [集計対象初日]
174
-
175
- FROM [WK_表示年月日計算]
176
-
177
- ),
178
-
179
-
180
-
181
- /*********************************************************
182
-
183
- マスタテーブル --WHERE条件等は、区分の決まりごとなのできにしないでください
184
-
185
- **********************************************************/
186
-
187
- [TMP_マスタテーブル] AS (
188
-
189
- SELECT CONVERT(DATE, CONVERT(CHAR(8), MAX([年月日])), 112) AS [日付]
190
-
191
- , [倉庫コード]
192
-
193
- , [製品コード親]
194
-
195
- , [本数]
196
-
197
- FROM
198
-
199
- (SELECT
171
+ (SELECT
200
172
 
201
173
  T0.[年月日]
202
174
 
203
175
  , T0.[出荷先コード] AS [倉庫コード]
204
176
 
205
- , CASE
206
-
207
- WHEN M0.[製品コード親] IS NULL THEN T0.[製品コード]
208
-
209
- ELSE M0.[製品コード親]
210
-
211
- END AS [製品コード親]
177
+ , T0.[製品コード]
178
+
212
-
179
+ , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数]
180
+
213
- , T0.[出荷本数] AS [本数]
181
+ , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数]
214
182
 
215
183
  FROM
216
184
 
217
- [BI_T].[dbo].[T_工場出荷] T0
185
+ [BATABLE].[dbo].[Ａテーブル] T0
218
-
219
- LEFT OUTER JOIN
220
-
221
- [BI_T].[dbo].[M_物流製品親子マスタ] M0
222
-
223
- ON
224
-
225
- T0.[製品コード] = M0.[製品コード]
226
-
227
- AND @MASTER_YM = M0.[年月]
228
186
 
229
187
  WHERE
230
188
 
231
- T0.[品種] = 'AT'
232
-
233
- AND T0.[内訳] NOT IN ('6','9')
189
+ T0.[年月日] BETWEEN @START_DATE AND @END_DATE
190
+
234
-
191
+ GROUP BY
192
+
193
+ T0.[年月日]
194
+
235
- AND T0.[需要先]='1'
195
+ , T0.[出荷先コード]
236
-
237
- AND SUBSTRING(ISNULL(M0.[製品コード親], T0.[製品コード]), 4, 1) != 'T'
196
+
238
-
239
- AND LEN(T0.[出荷先コード]) > 7 --直送は不要
197
+ , T0.[製品コード]
240
-
241
-
242
-
198
+
199
+
200
+
201
+
202
+
243
- UNION ALL
203
+ UNION ALL
244
-
245
-
246
-
204
+
205
+
206
+
247
- SELECT
207
+ SELECT
248
208
 
249
209
  T0.[年月日]
250
210
 
251
211
  , T0.[出荷先コード] AS [倉庫コード]
252
212
 
253
- , CASE
254
-
255
- WHEN M0.[製品コード親] IS NULL THEN T0.[DOT製品コード親]
256
-
257
- ELSE M0.[製品コード親]
258
-
259
- END AS [製品コード親]
213
+ , T0.[製品コード]
214
+
260
-
215
+ , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数]
216
+
261
- , T0.[出荷本数] AS [本数]
217
+ , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数]
262
218
 
263
219
  FROM
264
220
 
265
- [BI_T].[dbo].[T_地区出荷] T0
221
+ [BATABLE].[dbo].[Ｂテーブル] T0
266
-
267
- LEFT OUTER JOIN
268
-
269
- [BI_T].[dbo].[M_サイズ処理定義マスタ] M0
270
-
271
- ON
272
-
273
- T0.[DOT製品コード親] = M0.[DOT製品コード親]
274
222
 
275
223
  WHERE
276
224
 
277
- T0.[品種] = 'AT'
278
-
279
- AND T0.[内訳] NOT IN ('6','9')
225
+ T0.[年月日] BETWEEN @START_DATE AND @END_DATE
226
+
280
-
227
+ GROUP BY
228
+
281
- AND SUBSTRING(ISNULL(M0.[製品コード親], T0.[DOT製品コード親]), 4, 1) != 'T'
229
+ T0.[年月日]
282
-
230
+
283
- AND T0.[受払区分] IN ('C3','G3')
231
+ , T0.[出荷先コード]
232
+
284
-
233
+ , T0.[製品コード]
285
-
286
-
234
+
235
+
236
+
287
- UNION ALL
237
+ UNION ALL
288
-
289
-
290
-
238
+
239
+
240
+
291
- SELECT
241
+ SELECT
292
242
 
293
243
  T0.[年月日]
294
244
 
295
245
  , T0.[出荷先コード] AS [倉庫コード]
296
246
 
297
- , CASE
298
-
299
- WHEN M0.[製品コード親] IS NULL THEN T0.[DOT製品コード親]
300
-
301
- ELSE M0.[製品コード親]
302
-
303
- END AS [製品コード親]
247
+ , T0.[製品コード]
248
+
304
-
249
+ , --SUM(T0.[入荷本数]*-1) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数]
250
+
305
- , (T0.[入荷本数]*-1) AS [本数]
251
+ , --SUM(T0.[入荷本数]*-1) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数]
306
252
 
307
253
  FROM
308
254
 
309
- [BI_T].[dbo].[T_地区入荷] T0
255
+ [BATABLE].[dbo].[Ｃテーブル] T0
310
-
311
- LEFT OUTER JOIN
312
-
313
- [BI_T].[dbo].[M_サイズ処理定義マスタ] M0
314
-
315
- ON
316
-
317
- T0.[DOT製品コード親] = M0.[DOT製品コード親]
318
256
 
319
257
  WHERE
320
258
 
321
- T0.[品種] = 'AT'
322
-
323
- AND T0.[内訳] NOT IN ('6','9')
259
+ T0.[年月日] BETWEEN @START_DATE AND @END_DATE
324
-
325
- AND SUBSTRING(ISNULL(M0.[製品コード親], T0.[DOT製品コード親]), 4, 1) != 'T'
260
+
326
-
327
- AND T0.[受払区分] = 'C5'
328
-
329
- AND LEN(T0.[出荷元コード]) > 7
330
-
331
- )Z
332
-
333
- GROUP BY
261
+ GROUP BY
262
+
334
-
263
+ T0.[年月日]
264
+
335
- [倉庫コード]
265
+ , T0.[入荷先コード]
336
-
266
+
337
- , [製品コード親]
267
+ , T0.[製品コード]
338
-
339
- , [本数]
268
+
340
-
341
- ),
342
-
343
- [TMP_コードグルーピング] AS (
344
-
345
- SELECT DISTINCT [倉庫コード],[製品コード親] FROM [TMP_マスタテーブル]
346
-
347
- )
269
+ )
270
+
348
-
271
+ GROUP BY
349
-
350
-
272
+
351
- SELECT
273
+ [年月日]
352
-
353
- CAST((CONVERT(VARCHAR,[基準日],112))AS NUMERIC) AS [年月日]
274
+
354
-
355
- , [倉庫コード]
275
+ , [倉庫コード]
356
-
276
+
357
- , [製品コード親]
277
+ , [製品コード]
358
-
359
- , (SELECT SUM([本数])
360
-
361
- FROM
362
-
363
- [TMP_マスタテーブル] M01
364
-
365
- WHERE
366
-
367
- M01.[倉庫コード]=TM.[倉庫コード] AND M01.[製品コード親]=TM.[製品コード親]
368
-
369
- AND
370
-
371
- M01.[日付] BETWEEN WK.[集計対象初日] AND WK.[集計対象最終日]) AS [本数]
372
-
373
- FROM
374
-
375
- [WK_取得開始日割当] WK, [TMP_コードグルーピング] TM
376
-
377
- WHERE
378
-
379
- (SELECT SUM([本数])
380
-
381
- FROM
382
-
383
- [TMP_マスタテーブル] M01
384
-
385
- WHERE
386
-
387
- M01.[倉庫コード]=TM.[倉庫コード] AND M01.[製品コード親]=TM.[製品コード親]
388
-
389
- AND
390
-
391
- M01.[日付] BETWEEN WK.[集計対象初日] AND WK.[集計対象最終日]) IS NOT NULL

7

回答SQL使用後の質問等

2015/10/21 21:20

投稿

mimipachi0133
mimipachi0133

スコア9

test CHANGED
@@ -1 +1 @@
1
- SQLで複数テーブルから、連日7日分の条件に合った合算データを抽出したい
1
+ SQLで複数テーブルから、登録年月日上位7日分の条件に合った合算データを抽出したい
test CHANGED
@@ -1,20 +1,28 @@
1
- いつもお世話になっております。
2
-
3
- 当方、とある顧客データのピックアップ作業をSQL Server 2012 で行っているのですが、
4
-
5
- 業務についてまだ日が浅く、なかなか思うようにデータ抽出が行えておりません。
6
-
7
- ご協力お願いいたします。
1
+ 昨日はご協力ありがとうございました。
2
+
3
+ 本日、本番環境のDBで頂いた回答を参考にチャレンジした結果、私の理解が到らないばかりにうまく結果を得られませんでした。
4
+
5
+ もう少しお力をお貸し頂けたらと思い、追記させていただきます。
6
+
7
+ また、追加で抽出条件の注文があったので、それも反映しつつ書かせて頂きます。
8
+
9
+ （本日のSQL、説明等は本番DBの環境用に書かれていますので、昨日よりも複雑になっております。）
8
10
 
9
11
 
10
12
 
11
13
  ・取得したいデータ
12
14
 
15
+ ※先日は勘違いしていたのですが、「 更新年月日の登録用テーブルのトップ7日分の年月日を選択し（連続した年月日とは限りません。）取得したい」・「毎月1日のデータは当月の累計データは無いので、空で取得したい」と先方から言われましたので、希望抽出条件を修正します。
16
+
17
+
18
+
13
- 当日から7日間の連日日付において、3つの構造が同じテーブルから、条件の一致した[年月日][倉庫コード][製品コード]でグループにした本数の合算を求める。その際、
19
+ 更新年月日の登録用テーブル（[T_受注残]、[T_地区在庫]）のトップ7日分の年月日を選択し（連続した年月日とは限りません。）3つの構造が同じテーブルから、条件の一致した[年月日][倉庫コード][製品コード]でグループにした本数の合算を求める。その際、
14
20
 
15
21
  を都度合算した結果のデータ。
16
22
 
23
+
24
+
17
- （例えば、最新年月日が20150903の場合、取得したいデータは
25
+ （例えば、最新年月日が20150903、2015829にデータを受信していない場合、取得したいデータは
18
26
 
19
27
 
20
28
 
@@ -22,248 +30,362 @@
22
30
 
23
31
  20150902 → 20150901のみ（本数）
24
32
 
25
- 20150901 → 20150801から20150831までのSUM値（本数）
33
+ 20150901 → 空（NULL）で出力
26
34
 
27
35
  20150831 → 20150801から20150830までのSUM値（本数）
28
36
 
29
37
  20150830 → 20150801から20150829までのSUM値（本数）
30
38
 
31
- 20150829 → 20150801から20150828までのSUM値（本数）
32
-
33
39
  20150828 → 20150801から20150827までのSUM値（本数）
34
40
 
35
-
41
+ 20150827 → 20150801から20150826までのSUM値（本数）
42
+
43
+
44
+
36
-
45
+ 取得データにつきましては、以下のような形で取得したいと考えております。
46
+
37
-
47
+ [年月日],[倉庫コード],[製品コード親],SUM[(本数)]
48
+
49
+ 20150903,AAAAA,abuodsds,156325
50
+
51
+ 20150903,BBBBB,dsfgrhgf,59826
52
+
53
+ 20150902,AAAAA,dfefgrth,258847
54
+
55
+ 20150901,BBBBB,fdrufehu,NULL
38
56
 
39
57
 
40
58
 
41
59
  このように、選択年月日-1日の年月日の当月前日までのデータの合算を1回のSQL実行で行いたいと考えております。
42
60
 
43
- 年月日の割当については、一時テーブルで最新日7日分を選択し、結合できるようですが…（実は検索等で調べたので信憑性があやしいです、スイマセン）
44
-
45
-
46
-
47
- 取得データにつきましては、以下のような形で取得したいと考えております。
48
-
49
- [年月日],[倉庫コード],[製品コード],SUM[(本数)]
50
-
51
- 20150903,AAAAA,abuodsds,156325
52
-
53
- 20150903,BBBBB,dsfgrhgf,59826
54
-
55
- 20150902,AAAAA,dfefgrth,258847
56
-
57
- ・・・・
58
-
59
-
60
-
61
- ・取得方法（チャレンジしたところまで）
62
-
63
- ・データ登録日時テーブル[AA10]と[AA11]から最新年月日を取得し、その年月日を基準に、
64
-
65
- 最新年月日に対する月末日、前月初日の年月日を取得
66
-
67
- （テーブル[AA10]と[AA11]は、データ登録日時確認用のテーブルです。構成カラムは[年月日],[ファイル名称]で、[AA10]が[Ａテーブル]と[Ｂテーブル]の更新情報、[AA11]が[Cテーブル]の更新情報となります。業務上、「当日」の年月日はこのテーブルから取得しないといけません。）
68
-
69
- ・データ登録日時テーブル[AA10]と[AA11]の最新日時7日分の日時範囲で一時テーブルを作成
70
-
71
- ・本数データ取得対象の[Ａテーブル]と[Ｂテーブル]と[Cテーブル]をUNION ALLする
72
-
73
-
74
-
75
- ここから、どうすればいいのかわからなくなっております。
76
-
77
- （一応下記SQLでOVER句を使用しておりますが、意味は通っておりません。）
78
-
79
-
80
-
81
- 一時テーブルの使用・OVER句（PARTITION BY~）による[年月日][倉庫コード][製品コード]のグループ化などを行う必要があるそうですが、イマイチ理解できておりません。
82
-
83
-
84
-
85
- 大変わかりくにい質問となってしまいましたが、ご協力お願いいたします。
86
-
87
-
88
-
89
- --以下、SQL文（書いたことろまで）
90
-
91
-
92
-
93
- DECLARE
94
-
95
- @START_DATE AS NUMERIC --開始日(前月月初日)
96
-
97
- , @END_DATE AS NUMERIC --実績終了日(前日)
98
-
99
- , @LAST_EOM AS NUMERIC --前月末日
100
-
101
-
102
-
103
- SET @END_DATE = (SELECT MAX(T10.[年月日])
104
-
105
- FROM(
106
-
107
- SELECT MAX(T01.[年月日]) AS [年月日] FROM [BATABLE].[dbo].[AA10] T01 WHERE T01.[品種] = 'AT' AND T01.[内訳] NOT IN ('6', '9')
108
-
109
- UNION ALL
110
-
111
- SELECT MAX(T02.[年月日]) AS [年月日] FROM [BATABLE].[dbo].[AA11] T02 WHERE T02.[品種] = 'AT' AND T02.[内訳] NOT IN ('6', '9')
112
-
113
- ) T10);
61
+
62
+
63
+ ・取得方法（前日頂いた回答を参考にしました）
64
+
65
+ --ここから、WITH句です
66
+
67
+ ・データ登録日時テーブル（[T_受注残]、[T_地区在庫]）からトップ年月日7日分を取得する。
68
+
69
+ （テーブル[T_受注残]、[T_地区在庫]は、データ登録日時確認用のテーブルです。構成カラムは[年月日],[ファイル名称]で、[T_地区在庫]が[T_地区出荷]と[T_地区入荷]の更新情報、[T_受注残]が[T_工場出荷]の更新情報となります。）（[WK_日付範囲]）
70
+
71
+ ・データ登録日時テーブル[T_受注残]と[T_地区在庫]の最新日時7日分の日時範囲で一時テーブルを作成（[WK_表示年月日計算]）
72
+
73
+ ・各年月日に対応した[集計対象初日]、[集計対象最終日]を設定（[WK_取得開始日割当]）
74
+
75
+ ・本数データ取得対象の[T_地区出荷]と[T_地区入荷]と[T_受注残]をUNION ALLする
76
+
77
+ （[TMP_マスタテーブル] ）
78
+
79
+ ・[倉庫コード],[製品コード親]でグルーピング（[TMP_コードグルーピング]）
80
+
81
+ --ここまで、WITH句です。
82
+
83
+ ・最後に、[TMP_マスタテーブル][WK_取得開始日割当] [TMP_コードグルーピング] を使用して、各年月日の[年月日]（ここでNUMERICに戻しています）[倉庫コード][製品コード親]についての、累計本数を抽出しようとしました。
84
+
85
+
86
+
87
+ しかしこのSQLを動かしたところ、3時間以上かかっても、1日分のデータも出しきれていませんでした。
88
+
89
+ 方法は良さそうなのですが、実用に足るスピードアップの方法を考えているところです。
90
+
91
+
92
+
93
+ 以下に使用SQLを書きます（ややうろ覚えなので疑問点あればお願いいたします）。
94
+
95
+
96
+
97
+ --このSQLで動かした結果、3時間経過しても全データ抽出ができませんでした。
98
+
99
+ --今後の利用も考えて長くても20～30分くらいで取得できるようにしたいです。
100
+
101
+
102
+
103
+
104
+
105
+ DECLARE
106
+
107
+ @MASTER_YM AS NUMERIC
114
108
 
115
109
 
116
110
 
117
- SET @LAST_EOM=(SELECT CAST(CONVERT(VARCHAR,EOMONTH(DATEADD(MONTH,-1,CONVERT(DATETIME,CAST(@END_DATE AS VARCHAR),112))),112) AS NUMERIC));
111
+ /** M_物流製品親子マスタ（工場系）は過去マスタデータを蓄積するので、最新月しか持たないM_サイズ処理定義マスタ（地区系）と齟齬が起きないように使用年月を固定しています **/
118
-
112
+
119
- SET @START_DATE=(SELECT CAST(CONCAT(LEFT(@LAST_EOM,6),'01') AS NUMERIC));
113
+ SET @MASTER_YM=(SELECT MAX([年月]) FROM [BI_T].[dbo].[M_サイズ処理定義マスタ]);
120
-
121
-
122
114
 
123
115
 
124
116
 
125
117
  WITH
126
118
 
119
+
120
+
127
121
  /*********************************************************
128
122
 
129
123
  日付範囲
130
124
 
131
125
  **********************************************************/
132
126
 
133
- [NT_日付範囲] AS
127
+ [WK_日付範囲] AS (
134
-
128
+
135
- (SELECT TOP 7 [年月日] FROM
129
+ SELECT TOP 7 CONVERT(DATE, CONVERT(CHAR(8), MAX([年月日])), 112) AS [対象年月日]
130
+
136
-
131
+ FROM
132
+
137
- (SELECT TOP 7 [年月日] FROM [BATABLE].[dbo].[AA10] GROUP BY [年月日] WHERE [年月日] ORDER BY [年月日] DESC
133
+ (SELECT TOP 7 年月日 FROM [BI_T].[dbo].[T_受注残] GROUP BY 年月日 ORDER BY 年月日 DESC
138
-
134
+
139
- UNION
135
+ UNION
140
-
136
+
141
- SELECT TOP 7 [年月日] FROM [BATABLE].[dbo].[AA11] GROUP BY [年月日] WHERE [年月日] ORDER BY [年月日] DESC
137
+ SELECT TOP 7 年月日 FROM [BI_T].[dbo].[T_地区在庫] GROUP BY 年月日 ORDER BY 年月日 DESC
142
-
138
+
143
- ) X
139
+ )W
140
+
144
-
141
+ GROUP BY [年月日]
142
+
145
- ORDER BY [年月日] DESC
143
+ ORDER BY [年月日] DESC
146
-
144
+
147
- )
145
+ ),
148
-
149
-
150
-
146
+
147
+
148
+
151
- SELECT
149
+ /*********************************************************
152
-
150
+
153
- [年月日]
151
+ 年月日計算
152
+
154
-
153
+ **********************************************************/
154
+
155
+ [WK_表示年月日計算] AS (
156
+
157
+ SELECT [基準日], DATEADD(d, -1, [基準日]) AS [集計対象最終日]
158
+
159
+ FROM
160
+
161
+ (
162
+
163
+ SELECT [対象年月日] AS [基準日] FROM [WK_日付範囲]
164
+
165
+ )Y
166
+
167
+ ),
168
+
169
+ [WK_取得開始日割当] AS (
170
+
171
+ SELECT [基準日],[集計対象最終日],
172
+
173
+ DATEFROMPARTS(YEAR([集計対象最終日]), MONTH([集計対象最終日]), 1) AS [集計対象初日]
174
+
175
+ FROM [WK_表示年月日計算]
176
+
177
+ ),
178
+
179
+
180
+
181
+ /*********************************************************
182
+
183
+ マスタテーブル --WHERE条件等は、区分の決まりごとなのできにしないでください
184
+
185
+ **********************************************************/
186
+
187
+ [TMP_マスタテーブル] AS (
188
+
189
+ SELECT CONVERT(DATE, CONVERT(CHAR(8), MAX([年月日])), 112) AS [日付]
190
+
155
- , [倉庫コード]
191
+ , [倉庫コード]
156
-
192
+
157
- , [製品コード]
193
+ , [製品コード親]
158
-
194
+
159
- , SUM([本数]) AS [本数]
195
+ , [本数]
160
-
196
+
161
- FROM
197
+ FROM
162
-
198
+
163
- (SELECT
199
+ (SELECT
164
200
 
165
201
  T0.[年月日]
166
202
 
167
203
  , T0.[出荷先コード] AS [倉庫コード]
168
204
 
205
+ , CASE
206
+
207
+ WHEN M0.[製品コード親] IS NULL THEN T0.[製品コード]
208
+
209
+ ELSE M0.[製品コード親]
210
+
169
- , T0.[製品コード]
211
+ END AS [製品コード親]
170
-
171
- , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数]
212
+
172
-
173
- , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数]
213
+ , T0.[出荷本数] AS [本数]
174
214
 
175
215
  FROM
176
216
 
177
- [BATABLE].[dbo].[Ａテーブル] T0
217
+ [BI_T].[dbo].[T_工場出荷] T0
218
+
219
+ LEFT OUTER JOIN
220
+
221
+ [BI_T].[dbo].[M_物流製品親子マスタ] M0
222
+
223
+ ON
224
+
225
+ T0.[製品コード] = M0.[製品コード]
226
+
227
+ AND @MASTER_YM = M0.[年月]
178
228
 
179
229
  WHERE
180
230
 
231
+ T0.[品種] = 'AT'
232
+
181
- T0.[年月日] BETWEEN @START_DATE AND @END_DATE
233
+ AND T0.[内訳] NOT IN ('6','9')
234
+
182
-
235
+ AND T0.[需要先]='1'
236
+
237
+ AND SUBSTRING(ISNULL(M0.[製品コード親], T0.[製品コード]), 4, 1) != 'T'
238
+
239
+ AND LEN(T0.[出荷先コード]) > 7 --直送は不要
240
+
241
+
242
+
183
- GROUP BY
243
+ UNION ALL
244
+
245
+
246
+
247
+ SELECT
184
248
 
185
249
  T0.[年月日]
186
250
 
187
- , T0.[出荷先コード]
251
+ , T0.[出荷先コード] AS [倉庫コード]
252
+
188
-
253
+ , CASE
254
+
255
+ WHEN M0.[製品コード親] IS NULL THEN T0.[DOT製品コード親]
256
+
257
+ ELSE M0.[製品コード親]
258
+
189
- , T0.[製品コード]
259
+ END AS [製品コード親]
260
+
190
-
261
+ , T0.[出荷本数] AS [本数]
262
+
191
-
263
+ FROM
264
+
192
-
265
+ [BI_T].[dbo].[T_地区出荷] T0
266
+
193
-
267
+ LEFT OUTER JOIN
268
+
194
-
269
+ [BI_T].[dbo].[M_サイズ処理定義マスタ] M0
270
+
271
+ ON
272
+
273
+ T0.[DOT製品コード親] = M0.[DOT製品コード親]
274
+
275
+ WHERE
276
+
277
+ T0.[品種] = 'AT'
278
+
279
+ AND T0.[内訳] NOT IN ('6','9')
280
+
281
+ AND SUBSTRING(ISNULL(M0.[製品コード親], T0.[DOT製品コード親]), 4, 1) != 'T'
282
+
283
+ AND T0.[受払区分] IN ('C3','G3')
284
+
285
+
286
+
195
- UNION ALL
287
+ UNION ALL
196
-
197
-
198
-
288
+
289
+
290
+
199
- SELECT
291
+ SELECT
200
292
 
201
293
  T0.[年月日]
202
294
 
203
295
  , T0.[出荷先コード] AS [倉庫コード]
204
296
 
297
+ , CASE
298
+
299
+ WHEN M0.[製品コード親] IS NULL THEN T0.[DOT製品コード親]
300
+
301
+ ELSE M0.[製品コード親]
302
+
205
- , T0.[製品コード]
303
+ END AS [製品コード親]
206
-
207
- , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数]
304
+
208
-
209
- , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数]
305
+ , (T0.[入荷本数]*-1) AS [本数]
210
306
 
211
307
  FROM
212
308
 
213
- [BATABLE].[dbo].[Ｂテーブル] T0
309
+ [BI_T].[dbo].[T_地区入荷] T0
310
+
311
+ LEFT OUTER JOIN
312
+
313
+ [BI_T].[dbo].[M_サイズ処理定義マスタ] M0
314
+
315
+ ON
316
+
317
+ T0.[DOT製品コード親] = M0.[DOT製品コード親]
214
318
 
215
319
  WHERE
216
320
 
321
+ T0.[品種] = 'AT'
322
+
217
- T0.[年月日] BETWEEN @START_DATE AND @END_DATE
323
+ AND T0.[内訳] NOT IN ('6','9')
324
+
218
-
325
+ AND SUBSTRING(ISNULL(M0.[製品コード親], T0.[DOT製品コード親]), 4, 1) != 'T'
326
+
327
+ AND T0.[受払区分] = 'C5'
328
+
329
+ AND LEN(T0.[出荷元コード]) > 7
330
+
331
+ )Z
332
+
219
- GROUP BY
333
+ GROUP BY
220
-
221
- T0.[年月日]
334
+
222
-
223
- , T0.[出荷先コード]
335
+ [倉庫コード]
224
-
336
+
225
- , T0.[製品コード]
337
+ , [製品コード親]
226
-
227
-
228
-
338
+
229
- UNION ALL
339
+ , [本数]
340
+
230
-
341
+ ),
342
+
231
-
343
+ [TMP_コードグルーピング] AS (
344
+
232
-
345
+ SELECT DISTINCT [倉庫コード],[製品コード親] FROM [TMP_マスタテーブル]
346
+
347
+ )
348
+
349
+
350
+
233
- SELECT
351
+ SELECT
234
-
352
+
235
- T0.[年月日]
353
+ CAST((CONVERT(VARCHAR,[基準日],112))AS NUMERIC) AS [年月日]
236
-
354
+
237
- , T0.[出荷先コード] AS [倉庫コード]
355
+ , [倉庫コード]
238
-
356
+
239
- , T0.[製品コード]
357
+ , [製品コード親]
240
-
241
- , --SUM(T0.[入荷本数]*-1) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数]
358
+
242
-
243
- , --SUM(T0.[入荷本数]*-1) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数]
359
+ , (SELECT SUM([本数])
244
-
360
+
245
- FROM
361
+ FROM
246
-
362
+
247
- [BATABLE].[dbo].[Ｃテーブル] T0
363
+ [TMP_マスタテーブル] M01
364
+
365
+ WHERE
366
+
367
+ M01.[倉庫コード]=TM.[倉庫コード] AND M01.[製品コード親]=TM.[製品コード親]
368
+
369
+ AND
370
+
371
+ M01.[日付] BETWEEN WK.[集計対象初日] AND WK.[集計対象最終日]) AS [本数]
372
+
373
+ FROM
374
+
375
+ [WK_取得開始日割当] WK, [TMP_コードグルーピング] TM
248
376
 
249
377
  WHERE
250
378
 
379
+ (SELECT SUM([本数])
380
+
381
+ FROM
382
+
383
+ [TMP_マスタテーブル] M01
384
+
385
+ WHERE
386
+
387
+ M01.[倉庫コード]=TM.[倉庫コード] AND M01.[製品コード親]=TM.[製品コード親]
388
+
389
+ AND
390
+
251
- T0.[年月日] BETWEEN @START_DATE AND @END_DATE
391
+ M01.[日付] BETWEEN WK.[集計対象初日] AND WK.[集計対象最終日]) IS NOT NULL
252
-
253
- GROUP BY
254
-
255
- T0.[年月日]
256
-
257
- , T0.[入荷先コード]
258
-
259
- , T0.[製品コード]
260
-
261
- )
262
-
263
- GROUP BY
264
-
265
- [年月日]
266
-
267
- , [倉庫コード]
268
-
269
- , [製品コード]

6

追記

2015/10/21 12:04

投稿

mimipachi0133
mimipachi0133

スコア9

test CHANGED
File without changes
test CHANGED
@@ -64,6 +64,8 @@
64
64
 
65
65
  最新年月日に対する月末日、前月初日の年月日を取得
66
66
 
67
+ （テーブル[AA10]と[AA11]は、データ登録日時確認用のテーブルです。構成カラムは[年月日],[ファイル名称]で、[AA10]が[Ａテーブル]と[Ｂテーブル]の更新情報、[AA11]が[Cテーブル]の更新情報となります。業務上、「当日」の年月日はこのテーブルから取得しないといけません。）
68
+
67
69
  ・データ登録日時テーブル[AA10]と[AA11]の最新日時7日分の日時範囲で一時テーブルを作成
68
70
 
69
71
  ・本数データ取得対象の[Ａテーブル]と[Ｂテーブル]と[Cテーブル]をUNION ALLする

5

誤字修正

2015/10/20 15:10

投稿

mimipachi0133
mimipachi0133

スコア9

test CHANGED
File without changes
test CHANGED
@@ -40,7 +40,7 @@
40
40
 
41
41
  このように、選択年月日-1日の年月日の当月前日までのデータの合算を1回のSQL実行で行いたいと考えております。
42
42
 
43
- 年月の割当については、一時テーブルで最新日7日分を選択し、結合できるようですが…（実は検索等で調べたので信憑性があやしいです、スイマセン）
43
+ 年月日の割当については、一時テーブルで最新日7日分を選択し、結合できるようですが…（実は検索等で調べたので信憑性があやしいです、スイマセン）
44
44
 
45
45
 
46
46
 

4

誤字修正

2015/10/20 14:09

投稿

mimipachi0133
mimipachi0133

スコア9

test CHANGED
File without changes
test CHANGED
@@ -52,7 +52,7 @@
52
52
 
53
53
  20150903,BBBBB,dsfgrhgf,59826
54
54
 
55
- 2015902,AAAAA,dfefgrth,258847
55
+ 20150902,AAAAA,dfefgrth,258847
56
56
 
57
57
  ・・・・
58
58
 

3

不明慮な点を補足

2015/10/20 14:06

投稿

mimipachi0133
mimipachi0133

スコア9

test CHANGED
File without changes
test CHANGED
@@ -10,12 +10,14 @@
10
10
 
11
11
  ・取得したいデータ
12
12
 
13
- 当日から7日間の連日日付において、3つの構造が同じテーブルから、条件の一致したデータ本数
13
+ 当日から7日間の連日日付において、3つの構造が同じテーブルから、条件の一致した[年月日][倉庫コード][製品コード]でグループにした本数の合算を求める。その際、
14
14
 
15
15
  を都度合算した結果のデータ。
16
16
 
17
17
  （例えば、最新年月日が20150903の場合、取得したいデータは
18
18
 
19
+
20
+
19
21
  20150903 → 20150901から20150902までのSUM値（本数）
20
22
 
21
23
  20150902 → 20150901のみ（本数）
@@ -34,9 +36,25 @@
34
36
 
35
37
 
36
38
 
39
+
40
+
37
41
  このように、選択年月日-1日の年月日の当月前日までのデータの合算を1回のSQL実行で行いたいと考えております。
38
42
 
39
- 年月の割当については、一時テーブルで最新日7日分を選択し、まとめて外部結合するとのことです。
43
+ 年月の割当については、一時テーブルで最新日7日分を選択し、結合できるようですが…（実は検索等で調べたので信憑性があやしいです、スイマセン）
44
+
45
+
46
+
47
+ 取得データにつきましては、以下のような形で取得したいと考えております。
48
+
49
+ [年月日],[倉庫コード],[製品コード],SUM[(本数)]
50
+
51
+ 20150903,AAAAA,abuodsds,156325
52
+
53
+ 20150903,BBBBB,dsfgrhgf,59826
54
+
55
+ 2015902,AAAAA,dfefgrth,258847
56
+
57
+ ・・・・
40
58
 
41
59
 
42
60
 

2

タイトル変更

2015/10/20 14:05

投稿

mimipachi0133
mimipachi0133

スコア9

test CHANGED
@@ -1 +1 @@
1
- SQLで複数テーブルから、連日7日分の合算データを抽出したい
1
+ SQLで複数テーブルから、連日7日分の条件に合った合算データを抽出したい
test CHANGED
File without changes

1

タグの編集

2015/10/20 13:31

投稿

mimipachi0133
mimipachi0133

スコア9

test CHANGED
File without changes
test CHANGED
File without changes