Q&A
「まとめて外部結合するとのことです。」とおっしゃっていますが、つまり別の方の設計の元にクエリを組んでおられるということでしょうか。
また、実現したい要件に[倉庫コード][製品コード]はまったく出てきませんが、クエリを書くときになってどうしてこれらをグループ化するような必要が出てくるのでしょうか。
いつもお世話になっております。
当方、とある顧客データのピックアップ作業をSQL Server 2012 で行っているのですが、
業務についてまだ日が浅く、なかなか思うようにデータ抽出が行えておりません。
ご協力お願いいたします。
・取得したいデータ
当日から7日間の連日日付において、3つの構造が同じテーブルから、条件の一致した[年月日][倉庫コード][製品コード]でグループにした本数の合算を求める。その際、
を都度合算した結果のデータ。
（例えば、最新年月日が20150903の場合、取得したいデータは
20150903 → 20150901から20150902までのSUM値（本数） 20150902 → 20150901のみ（本数） 20150901 → 20150801から20150831までのSUM値（本数） 20150831 → 20150801から20150830までのSUM値（本数） 20150830 → 20150801から20150829までのSUM値（本数） 20150829 → 20150801から20150828までのSUM値（本数） 20150828 → 20150801から20150827までのSUM値（本数）
取得データにつきましては、以下のような形で取得したいと考えております。
[年月日],[倉庫コード],[製品コード親],SUM[(本数)]
20150903,AAAAA,abuodsds,156325
20150903,BBBBB,dsfgrhgf,59826
20150902,AAAAA,dfefgrth,258847
このように、選択年月日-1日の年月日の当月前日までのデータの合算を1回のSQL実行で行いたいと考えております。
年月日の割当については、一時テーブルで最新日7日分を選択し、結合できるようですが…（実は検索等で調べたので信憑性があやしいです、スイマセン）
取得データにつきましては、以下のような形で取得したいと考えております。
[年月日],[倉庫コード],[製品コード],SUM[(本数)]
20150903,AAAAA,abuodsds,156325
20150903,BBBBB,dsfgrhgf,59826
20150902,AAAAA,dfefgrth,258847
・・・・
・取得方法（チャレンジしたところまで）
・データ登録日時テーブル[AA10]と[AA11]から最新年月日を取得し、その年月日を基準に、
最新年月日に対する月末日、前月初日の年月日を取得
（テーブル[AA10]と[AA11]は、データ登録日時確認用のテーブルです。構成カラムは[年月日],[ファイル名称]で、[AA10]が[Ａテーブル]と[Ｂテーブル]の更新情報、[AA11]が[Cテーブル]の更新情報となります。業務上、「当日」の年月日はこのテーブルから取得しないといけません。）
・データ登録日時テーブル[AA10]と[AA11]の最新日時7日分の日時範囲で一時テーブルを作成
・本数データ取得対象の[Ａテーブル]と[Ｂテーブル]と[Cテーブル]をUNION ALLする
ここから、どうすればいいのかわからなくなっております。
（一応下記SQLでOVER句を使用しておりますが、意味は通っておりません。）
一時テーブルの使用・OVER句（PARTITION BY~）による[年月日][倉庫コード][製品コード]のグループ化などを行う必要があるそうですが、イマイチ理解できておりません。
大変わかりくにい質問となってしまいましたが、ご協力お願いいたします。
--以下、SQL文（書いたことろまで）
DECLARE
@START_DATE AS NUMERIC --開始日(前月月初日)
, @END_DATE AS NUMERIC --実績終了日(前日)
, @LAST_EOM AS NUMERIC --前月末日
SET @END_DATE = (SELECT MAX(T10.[年月日])
FROM(
SELECT MAX(T01.[年月日]) AS [年月日] FROM [BATABLE].[dbo].[AA10] T01 WHERE T01.[品種] = 'AT' AND T01.[内訳] NOT IN ('6', '9')
UNION ALL
SELECT MAX(T02.[年月日]) AS [年月日] FROM [BATABLE].[dbo].[AA11] T02 WHERE T02.[品種] = 'AT' AND T02.[内訳] NOT IN ('6', '9')
) T10);
SET @LAST_EOM=(SELECT CAST(CONVERT(VARCHAR,EOMONTH(DATEADD(MONTH,-1,CONVERT(DATETIME,CAST(@END_DATE AS VARCHAR),112))),112) AS NUMERIC));
SET @START_DATE=(SELECT CAST(CONCAT(LEFT(@LAST_EOM,6),'01') AS NUMERIC));
WITH
/*********************************************************
日付範囲
**********************************************************/
[NT_日付範囲] AS
(SELECT TOP 7 [年月日] FROM
(SELECT TOP 7 [年月日] FROM [BATABLE].[dbo].[AA10] GROUP BY [年月日] WHERE [年月日] ORDER BY [年月日] DESC
UNION
SELECT TOP 7 [年月日] FROM [BATABLE].[dbo].[AA11] GROUP BY [年月日] WHERE [年月日] ORDER BY [年月日] DESC
) X
ORDER BY [年月日] DESC
)
SELECT
[年月日]
, [倉庫コード]
, [製品コード]
, SUM([本数]) AS [本数]
FROM
(SELECT
T0.[年月日]
, T0.[出荷先コード] AS [倉庫コード]
, T0.[製品コード]
, --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数]
, --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数]
FROM
[BATABLE].[dbo].[Ａテーブル] T0
WHERE
T0.[年月日] BETWEEN @START_DATE AND @END_DATE
GROUP BY
T0.[年月日]
, T0.[出荷先コード]
, T0.[製品コード]
UNION ALL SELECT T0.[年月日] , T0.[出荷先コード] AS [倉庫コード] , T0.[製品コード] , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数] , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数] FROM [BATABLE].[dbo].[Ｂテーブル] T0 WHERE T0.[年月日] BETWEEN @START_DATE AND @END_DATE GROUP BY T0.[年月日] , T0.[出荷先コード] , T0.[製品コード] UNION ALL SELECT T0.[年月日] , T0.[出荷先コード] AS [倉庫コード] , T0.[製品コード] , --SUM(T0.[入荷本数]*-1) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数] , --SUM(T0.[入荷本数]*-1) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数] FROM [BATABLE].[dbo].[Ｃテーブル] T0 WHERE T0.[年月日] BETWEEN @START_DATE AND @END_DATE GROUP BY T0.[年月日] , T0.[入荷先コード] , T0.[製品コード] )
GROUP BY
[年月日]
, [倉庫コード]
, [製品コード]
修正内容、把握しました。
もうひとつ、これは今さら言っても仕方ないことなのかもしれませんが、日付はテーブル上でもやはり 20150902 などという数値として表現されているのでしょうかね。これはもう変えようがないことでしょうか。
そうですね。年月日は、テーブル上では数値型([numeric (8,0)])で入っています。日数の加減算が必要な際はSQLで一回日付型に変換して、終わったら数値型に戻しています。もちろん最終的には数値型で取得したいです。変数なんかは決まっていないので必要であれは任意で追加して頂いてもいいです。後付けですいません。
あとは、データ登録日時テーブル[AA10]と[AA11]という登場人物の出てくる目的と、どんなカラム構成のテーブルか、あたりですかね。
テーブル[AA10]と[AA11]は、データ登録日時確認用のテーブルです。構成カラムは、[年月日]と[ファイル名称]となります。というのも、合算したい3テーブルは、日次処理でバッチによってtxtデータを毎日決まった時間に取り込んでいるためです。[AA10]は[Ａテーブル][Bテーブル]の更新情報、[AA11]は[Cテーブル]の更新情報を持っています。3テーブルは単にデータを蓄積するだけなので、ここから最新年月日を基準として日付リストを作成しております。度々情報が抜け落ちて申しわけありません。
AA10とAA11はテキストファイルからDBテーブルへの取り込み処理のログですね。それだけだったら目的の処理にはまったく必要なさそうに見えます。
A,B,Cのカラム構成は[年月日],[倉庫コード],[製品コード],[本数]ですよね？ そして[年月日],[倉庫コード],[製品コード]が主キーでしょうか。これさえあれば情報としては必要十分に見えます。
それとも、「当日」というのをAA10,AA11に含まれる最新の日付のことである、と定義するとかそういうことでしょうか？
// A,B,Cのカラム構成は[年月日],[倉庫コード],[製品コード],[本数]ですよね？ そして[年月日],[倉庫コード],[製品コード]が主キーでしょうか。
それで問題ありません。DB構成上主キーではないのですが、扱いは同義です。
//それとも、「当日」というのをAA10,AA11に含まれる最新の日付のことである、と定義するとかそういうことでしょうか？
それでお願いいたします。おっしゃる通り、ファイル取込日時 = 3テーブルの最新年月日なんですが、一応[AA10]と[AA11]を参照するのが決まりとなっております。年月日だけ引っ張ってくるテーブルです。
回答1件
0
SQL Serverなんで一時テーブルよりはCTE（WITH構文）ですかね。
手元に実行環境ないのでデバッグしていませんがだいたいこんな感じで動くかと。
sql
1WITH 2 latest AS ( 3 SELECT CONVERT(DATE, CONVERT(CHAR(8), MAX([年月日])), 112) AS [最新年月日] 4 FROM (SELECT [年月日] FROM AA10 UNION SELECT [年月日] FROM AA11) 5 ), 6 last1week AS ( 7 SELECT [基準日], DATEADD(d, -1, [基準日]) AS [集計対象最終日] FROM ( 8 SELECT DATEADD(d, -6, [最新年月日]) AS [基準日] FROM latest 9 UNION 10 SELECT DATEADD(d, -5, [最新年月日]) AS [基準日] FROM latest 11 UNION 12 SELECT DATEADD(d, -4, [最新年月日]) AS [基準日] FROM latest 13 UNION 14 SELECT DATEADD(d, -3, [最新年月日]) AS [基準日] FROM latest 15 UNION 16 SELECT DATEADD(d, -2, [最新年月日]) AS [基準日] FROM latest 17 UNION 18 SELECT DATEADD(d, -1, [最新年月日]) AS [基準日] FROM latest 19 UNION 20 SELECT [最新年月日] AS [基準日] FROM latest 21 ) 22 ), 23 target_range AS ( 24 SELECT [基準日],[集計対象最終日], 25 DATEFROMPARTS(YEAR([集計対象最終日]), MONTH([集計対象最終日]), 1) AS [集計対象初日] 26 FROM last1week 27 ), 28 master_table AS ( 29 SELECT CONVERT(DATE, CONVERT(CHAR(8), MAX([年月日])), 112) AS [日付] 30 , [倉庫コード],[製品コード],[本数] FROM ( 31 SELECT [年月日],[倉庫コード],[製品コード],[本数] FROM [Aテーブル] 32 UNION ALL 33 SELECT [年月日],[倉庫コード],[製品コード],[本数] FROM [Bテーブル] 34 UNION ALL 35 SELECT [年月日],[倉庫コード],[製品コード],[本数] FROM [Cテーブル] 36 ) 37 ), 38 target_grouping AS ( 39 SELECT DISTINCT [倉庫コード],[製品コード] FROM master_table 40 ) 41SELECT 42 [基準日],[倉庫コード],[製品コード], 43 (SELECT SUM([本数]) FROM master_table m 44 WHERE m.[倉庫コード]=g.[倉庫コード] AND m.[製品コード]=g.[製品コード] 45 AND m.[日付] BETWEEN r.[集計対象初日] AND r.[集計対象最終日]) AS [本数総計] 46FROM target_range r, target_grouping g
倉庫コードと製品コードの一覧をA,B,Cテーブルからかき集めているところが美しくありませんが、倉庫マスタテーブル、製品マスタテーブルなんてのにアクセス可能であれば改善できます。
投稿2015/10/20 15:26
総合スコア5568
早速の回答ありがとうございます。
なるほど、各種マスタテーブルがあればそれで紐づければ良いのですね、調べてみます。
これだけやっといてなんですが、これどう考えてもアプリ側で集計すべきロジックです。
運用業務ではSQLだけで乗り切ろ という風潮がどこでもあるんでしょうかね・・・
以前つとめていた会社の運用部門の人も、後から誰もメンテできないような壮大なSQLを書かれていました。
あなたの回答
tips
プレビュー
