質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.52%

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

受付中

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 2,434
昨日はご協力ありがとうございました。
本日、本番環境の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日分のデータも出しきれていませんでした。
方法は良さそうなのですが、実用に足るスピードアップの方法を考えているところです。
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • yunn

    2015/10/22 10:27

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

    キャンセル

  • kaputaros

    2015/10/22 11:13 編集

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

    キャンセル

回答 3

+1

こんな感じですか?

WITH last7days AS (
    SELECT TOP 7 ISNULL([T_受注残].[年月日], [T_地区在庫].[年月日]) [年月日]
    FROM [T_受注残]
    FULL JOIN [T_地区在庫]
    ON [T_受注残].[年月日] = [T_地区在庫].[年月日]
    ORDER BY ISNULL([T_受注残].[年月日], [T_地区在庫].[年月日]) DESC
)
SELECT
      last7days.[年月日]
    , total.[倉庫コード]
    , total.[製品コード親]
    , SUM(CASE
        WHEN total.[年月日] / 100 = last7days.[年月日] / 100
        AND total.[年月日] % 100 < last7days.[年月日] % 100 THEN total.[本数]
        ELSE NULL
      END) 本数
FROM (
        SELECT
              [年月日]
            , [倉庫コード]
            , [製品コード親]
        FROM [同じ構造のテーブルその1]
        UNION ALL
        SELECT
              [年月日]
            , [倉庫コード]
            , [製品コード親]
        FROM [同じ構造のテーブルその2]
        UNION ALL
        SELECT
              [年月日]
            , [倉庫コード]
            , [製品コード親]
        FROM [同じ構造のテーブルその3]
) total
WHERE (倉庫コードと製品コード親の条件)
GROUP BY
      last7days.[年月日]
    , total.[倉庫コード]
    , total.[製品コード親]

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+1

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

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

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

こんな流れでしょうか?(あまり詳しく見ていないのでごめんなさい)

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

GROUP BY
   T1.[年月日]
 , T1.[倉庫コード]  
 , T1.[製品コード親]


--以上、よろしくお願い致します。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.52%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる