SQLで複数テーブルから、登録年月日上位7日分の条件に合った合算データを抽出したい

昨日はご協力ありがとうございました。
本日、本番環境のDBで頂いた回答を参考にチャレンジした結果、私の理解が到らないばかりにうまく結果を得られませんでした。
もう少しお力をお貸し頂けたらと思い、再度質問させていただきます。
また、追加で抽出条件の注文があったので、それも反映しつつ書かせて頂きます。
（本日のSQL、説明等は本番DBの環境用に書かれていますので、昨日よりも複雑になっております。）

・取得したいデータ
※先日は勘違いしていたのですが、「更新年月日の登録用テーブルのトップ7日分の年月日を選択し（連続した年月日とは限りません。）取得したい」・「毎月1日のデータは当月の累計データは無いので、空で取得したい」と先方から言われましたので、希望抽出条件を修正します。

更新年月日の登録用テーブル（[T_受注残]、[T_地区在庫]）のトップ7日分の年月日を選択し（連続した年月日とは限りません。）3つの構造が同じテーブルから、条件の一致した[年月日][倉庫コード][製品コード]でグループにした本数の合算を求める。その際、
を都度合算した結果のデータ。

（例えば、最新年月日が20150903、2015829にデータを受信していない場合、取得したいデータは

         20150903  → 20150901から20150902までのSUM値（本数）
         20150902  → 20150901のみ（本数）
         20150901  → 空（NULL）で出力
         20150831  → 20150801から20150830までのSUM値（本数）
         20150830  → 20150801から20150829までのSUM値（本数）
         20150828  → 20150801から20150827までのSUM値（本数）
         20150827  → 20150801から20150826までのSUM値（本数）

取得データにつきましては、以下のような形で取得したいと考えております。
[年月日],[倉庫コード],[製品コード親],SUM[(本数)]
20150903,AAAAA,abuodsds,156325
20150903,BBBBB,dsfgrhgf,59826
20150902,AAAAA,dfefgrth,258847
20150901,BBBBB,fdrufehu,NULL

このように、選択年月日-1日の年月日の当月前日までのデータの合算を1回のSQL実行で行いたいと考えております。

・取得方法（前日頂いた回答を参考にしました）
--ここから、WITH句です
・データ登録日時テーブル（[T_受注残]、[T_地区在庫]）からトップ年月日7日分を取得する。
（テーブル[T_受注残]、[T_地区在庫]は、データ登録日時確認用のテーブルです。構成カラムは[年月日],[ファイル名称]で、[T_地区在庫]が[T_地区出荷]と[T_地区入荷]の更新情報、[T_受注残]が[T_工場出荷]の更新情報となります。）（[WK_日付範囲]）
・データ登録日時テーブル[T_受注残]と[T_地区在庫]の最新日時7日分の日時範囲で一時テーブルを作成（[WK_表示年月日計算]）
・各年月日に対応した[集計対象初日]、[集計対象最終日]を設定（[WK_取得開始日割当]）
・本数データ取得対象の[T_地区出荷]と[T_地区入荷]と[T_受注残]をUNION ALLする
（[TMP_マスタテーブル] ）
・[倉庫コード],[製品コード親]でグルーピング（[TMP_コードグルーピング]）
--ここまで、WITH句です。
・最後に、[TMP_マスタテーブル][WK_取得開始日割当] [TMP_コードグルーピング] を使用して、各年月日の[年月日]（ここでNUMERICに戻しています）[倉庫コード][製品コード親]についての、累計本数を抽出しようとしました。

しかしこのSQLを動かしたところ、3時間以上かかっても、1日分のデータも出しきれていませんでした。
方法は良さそうなのですが、実用に足るスピードアップの方法を考えているところです。

yunn

2015/10/22 01:27

素朴な疑問なんですが、8/31のデータが出力されることはない、んですか？

kaputaros

2015/10/22 02:15 編集

テーブル構成を１つずつ書いてほしいです。サンプルデータも。あと、SQLServerについてのみの回答が必要でしたら、MySQL・PostgreSQL・DB2のタグを外してほしいです。

行動規範の内容に同意します

回答3件

中々難しいＳＱＬですね。
確かに一回のＳＱＬで取得するのは効率がよさそうですが、
union や when 等を使用する事で、ＳＱＬ自体が遅くなる場合もあります。
（この当たりはプライマリーキーや、インデックスで補う事は出来るとは思いますが・・・）
また、あまりＳＱＬが複雑になると、後から分からなくなる可能性があります。

このような場合は、ストアドプロシージャを使用して、別テーブルに結果のみを作成し
その別テーブルにできた結果をＳｅｌｅｃｔする方シンプルになり
段階的にデータを集計する事で、後から見ても分かりやすくなると思います。

・ストアドプロシージャの流れ
１．年月日・倉庫コード・製品コードの抽出
２．１．を別テーブルに追加
３．１．をループして、条件にあうデータをサマリー
４．３．の結果を別テーブルに更新

こんな流れでしょうか？（あまり詳しく見ていないのでごめんなさい）

以上参考になれば幸いです。

投稿2015/10/23 07:59

編集2015/10/23 08:08

trick

総合スコア366

こんな感じですか?

SQL
1WITH last7days AS (
2	SELECT TOP 7 ISNULL([T_受注残].[年月日], [T_地区在庫].[年月日]) [年月日]
3	FROM [T_受注残]
4	FULL JOIN [T_地区在庫]
5	ON [T_受注残].[年月日] = [T_地区在庫].[年月日]
6	ORDER BY ISNULL([T_受注残].[年月日], [T_地区在庫].[年月日]) DESC
7)
8SELECT
9	  last7days.[年月日]
10	, total.[倉庫コード]
11	, total.[製品コード親]
12	, SUM(CASE
13		WHEN total.[年月日] / 100 = last7days.[年月日] / 100
14		AND total.[年月日] % 100 < last7days.[年月日] % 100 THEN total.[本数]
15		ELSE NULL
16	  END) 本数
17FROM (
18		SELECT
19			  [年月日]
20			, [倉庫コード]
21			, [製品コード親]
22		FROM [同じ構造のテーブルその1]
23		UNION ALL
24		SELECT
25			  [年月日]
26			, [倉庫コード]
27			, [製品コード親]
28		FROM [同じ構造のテーブルその2]
29		UNION ALL
30		SELECT
31			  [年月日]
32			, [倉庫コード]
33			, [製品コード親]
34		FROM [同じ構造のテーブルその3]
35) total
36WHERE (倉庫コードと製品コード親の条件)
37GROUP BY
38	  last7days.[年月日]
39	, total.[倉庫コード]
40	, total.[製品コード親]

// 前提

年月日は数値型
倉庫コード、製品コード親は3つの同じ構造のテーブルに含まれている

[T_受注残]と[T_地区在庫]から対象の年月日を抜き出して
同じ構造の3つのテーブルをまとめたところから
同じ年月(/100)のうち日付(%100)が若いデータの本数の合計を取っています

投稿2015/10/22 04:26

編集2015/10/23 08:47

kutsulog

総合スコア985

kutsulog様、trick様ご回答ありがとうございます。
昨日は多忙で返信を行うことができませんでした。
当方側で一応解決にこぎつけた（詳しい方に助力した）ので、対応SQLなどを報告させていただきます。
やはりまた自分が勘違いしていた仕様が多かったことで、後だし的な説明になってしまうことをご了承下さい。

正直まだ全容を理解しきれていないので、解析などにお力を貸して頂けると幸いです。

--本SQLで、日付にもよりますが、約3分～4分で全データの抽出が完了しました。

DECLARE @EXEC_DATE AS NUMERIC
, @EXEC_DATETIME AS DATETIME
, @LAST_EOM AS NUMERIC
, @LAST_MONTH AS NUMERIC
, @START AS NUMERIC
, @MASTER_YM AS NUMERIC

SET @EXEC_DATE = (SELECT MAX(T10.[年月日])
FROM(
SELECT MAX(T01.[年月日]) AS [年月日] FROM [T].[DB].[地区在] T01 WHERE T01.[品種] = 'AT' AND T01.[内訳] NOT IN ('6', '9')
UNION ALL
SELECT MAX(T02.[年月日]) AS [年月日] FROM [T].[DB].[受注残] T02 WHERE T02.[品種] = 'AT' AND T02.[内訳] NOT IN ('6', '9')
) T10);

SET @EXEC_DATETIME = (SELECT (CONVERT(DATETIME,CAST(@EXEC_DATE AS VARCHAR),112)));

SET @LAST_MONTH = (SELECT LEFT(CAST(CONVERT(VARCHAR,EOMONTH(DATEADD(MONTH,-1,CONVERT(DATETIME,CAST(@EXEC_DATE AS VARCHAR),112))),112) AS NUMERIC),6));

SET @START = (SELECT CAST(CONCAT(@LAST_MONTH,'01') AS NUMERIC(8,0)));

/** 前月末日 **/
SET @LAST_EOM=(SELECT CAST(CONVERT(VARCHAR,EOMONTH(DATEADD(MONTH,-1,CONVERT(DATETIME,CAST(@EXEC_DATE AS VARCHAR),112))),112) AS NUMERIC));

/** （コードの親子関係などはデータ作成時の決まりごとなので、「下記で外部結合しているテーブルあるけど」「NULLかどうか判断してるけど」というあたりは、「万一NULLだったら補完してるよ」ぐらいに考えてください。
/** 物流_製品_親子マスタ参照用年月 // 物流_製品_親子マスタ（工場系）は過去マスタデータを蓄積するので、最新月しか持たないサイズ_処理_定義マスタ（地区系）と齟齬が起きないように使用年月を合わせています **/
SET @MASTER_YM=(SELECT MAX([年月]) FROM [T].[DB].[サイズ_処理_定義マスタ]);

WITH
/*********************************************************
日付範囲
**********************************************************/

[一時_日付範囲_前日] AS
(SELECT TOP 7 CAST(CONVERT(VARCHAR,DATEADD(DAY,-1,CONVERT(DATETIME,CAST([年月日] AS VARCHAR),112)),112) AS NUMERIC(8,0)) AS [年月日] FROM
(SELECT TOP 7 [年月日] FROM [T].[DB].[受注残] GROUP BY [年月日] ORDER BY [年月日] DESC
UNION
SELECT TOP 7 [年月日] FROM [T].[DB].[地区在] GROUP BY [年月日] ORDER BY [年月日] DESC
) X
ORDER BY [年月日] DESC
),

W_日付(Val,年月日,年月) AS(
SELECT CAST(DATEADD(DAY,-1,@EXEC_DATETIME ) AS DATE), CAST(CONVERT(NVARCHAR(10), DATEADD(DAY,-1,@EXEC_DATETIME ), 112) AS NUMERIC(8,0)), CAST(CONCAT(SUBSTRING(CONVERT(NVARCHAR(10), DATEADD(DAY,-1,@EXEC_DATETIME ), 112), 1, 6),'00') AS NUMERIC(8,0))
UNION ALL
SELECT DateAdd(day,-1,Val),CAST(CONVERT(NVARCHAR(10), DateAdd(day,-1,Val), 112) AS NUMERIC(8,0)), CAST(CONCAT(SUBSTRING(CONVERT(NVARCHAR(10), DateAdd(day,-1,Val), 112), 1, 6),'00') AS NUMERIC(8,0))
FROM W_日付
WHERE 年月日 > @START)

SELECT
T1.[年月日]
, T1.[倉庫コード]
, T1.[製品コード親]
, SUM(T1.[本数累計]) AS [本数]
FROM

(SELECT
CAST(CONVERT(VARCHAR,DATEADD(DAY,1,CONVERT(DATETIME,CAST( [年月日] AS VARCHAR),112)),112) AS NUMERIC(8,0)) AS [年月日] -- 例：20150928のデータ → 20150929の前日データ（抽出した[年月日]は、「前日データから見たら、当日データの年月日」なので、最終的に +1日している）
, [倉庫コード]
, [製品コード親]
, [本数累計]
FROM
(SELECT
[年月日]
, [倉庫コード]
, [製品コード親]
, SUM([出荷本数])
OVER(
PARTITION BY
[年月]
, [倉庫コード]
, [製品コード親]
ORDER BY
[年月日]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS [本数累計]
FROM
(SELECT
WK1.[年月日]
, WK1.[年月]
, WK2.[出荷先コード] AS [倉庫コード]
, CASE
WHEN M0.[製品コード親] IS NULL THEN WK2.[製品コード]
ELSE M0.[製品コード親]
END AS [製品コード親]
, SUM(CASE
WHEN WK1.[年月日] = WK2.[年月日] THEN WK2.[出荷本数]
ELSE NULL
END
) AS [出荷本数]
FROM
W_日付 WK1
INNER JOIN
[T].[DB].[工場出荷] WK2
ON
WK2.[年月日] BETWEEN WK1.[年月] AND WK1.[年月] + 99
LEFT OUTER JOIN
[T].[DB].[物流_製品_親子マスタ] M0
ON
WK2.[製品コード] = M0.[製品コード]
AND @MASTER_YM = M0.[年月]
WHERE
WK2.[年月日] <>@LAST_EOM --毎月1日の前日データは空白にしたい = -1日である最終日は取得しない
GROUP BY
WK1.[年月日]
, WK1.[年月]
, WK2.[出荷先コード]
, CASE
WHEN M0.[製品コード親] IS NULL THEN WK2.[製品コード]
ELSE M0.[製品コード親]
END
) X
) Y
WHERE Y.[年月日] IN (SELECT [年月日] FROM [一時_日付範囲_前日])
AND Y.[本数累計] IS NOT NULL

  UNION ALL

   SELECT
    CAST(CONVERT(VARCHAR,DATEADD(DAY,1,CONVERT(DATETIME,CAST( [年月日] AS VARCHAR),112)),112) AS NUMERIC(8,0)) AS [年月日]
  , [倉庫コード]
  , [製品コード親]
  , [本数累計]

FROM
(SELECT
[年月日]
, [倉庫コード]
, [製品コード親]
, SUM([出荷本数])
OVER(
PARTITION BY
[年月]
, [倉庫コード]
, [製品コード親]
ORDER BY
[年月日]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS [本数累計]
FROM
(SELECT
WK1.[年月日]
, WK1.[年月]
, WK2.[出荷先コード] AS [倉庫コード]
, CASE
WHEN M0.[製品コード親] IS NULL THEN WK2.[ストック製品コード親]
ELSE M0.[製品コード親]
END AS [製品コード親]
, SUM(CASE
WHEN WK1.[年月日] = WK2.[年月日] THEN (WK2.[出荷本数])
ELSE NULL
END
) AS [出荷本数]
FROM
W_日付 WK1
INNER JOIN
[T].[DB].[地区出荷] WK2
ON
WK2.[年月日] BETWEEN WK1.[年月] AND WK1.[年月] + 99

      LEFT OUTER JOIN
          [T].[DB].[サイズ_処理_定義マスタ] M0
      ON
          WK2.[ストック製品コード親] = M0.[ストック製品コード親]      
      WHERE
          WK2.[年月日] <>@LAST_EOM
     GROUP BY
        WK1.[年月日]
      , WK1.[年月]
      , WK2.[出荷先コード]  
      , CASE
          WHEN M0.[製品コード親] IS NULL THEN WK2.[ストック製品コード親]
          ELSE                                M0.[製品コード親]
        END
     ) X
  ) Y
   WHERE   Y.[年月日] IN (SELECT [年月日] FROM [一時_日付範囲_前日])
   AND Y.[本数累計] IS NOT NULL
         
  UNION ALL
  
  SELECT
    CAST(CONVERT(VARCHAR,DATEADD(DAY,1,CONVERT(DATETIME,CAST( [年月日] AS VARCHAR),112)),112) AS NUMERIC(8,0)) AS [年月日]
  , [倉庫コード]
  , [製品コード親]
  , [本数累計]

FROM
(SELECT
[年月日]
, [倉庫コード]
, [製品コード親]
, SUM([入荷本数])
OVER(
PARTITION BY
[年月]
, [倉庫コード]
, [製品コード親]
ORDER BY
[年月日]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS [本数累計]
FROM
(SELECT
WK1.[年月日]
, WK1.[年月]
, WK2.[出荷元コード] AS [倉庫コード]
, CASE
WHEN M0.[製品コード親] IS NULL THEN WK2.[ストック製品コード親]
ELSE M0.[製品コード親]
END AS [製品コード親]
, SUM(CASE
WHEN WK1.[年月日] = WK2.[年月日] THEN WK2.[入荷本数]*-1
ELSE NULL
END
) AS [入荷本数]
FROM
W_日付 WK1
INNER JOIN
[T].[DB].[地区入荷] WK2
ON
WK2.[年月日] BETWEEN WK1.[年月] AND WK1.[年月] + 99
LEFT OUTER JOIN
[T].[DB].[サイズ_処理_定義マスタ] M0
ON
WK2.[ストック製品コード親] = M0.[ストック製品コード親]
WHERE
WK2.[年月日] <>@LAST_EOM
GROUP BY
WK1.[年月日]
, WK1.[年月]
, WK2.[出荷元コード]
, CASE
WHEN M0.[製品コード親] IS NULL THEN WK2.[ストック製品コード親]
ELSE M0.[製品コード親]
END
) X
) Y
WHERE Y.[年月日] IN (SELECT [年月日] FROM [一時_日付範囲_前日])
AND Y.[本数累計] IS NOT NULL
)T1