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

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

ただいまの
回答率

90.32%

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

受付中

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 2,387
いつもお世話になっております。
当方、とある顧客データのピックアップ作業を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]が[Aテーブル]と[Bテーブル]の更新情報、[AA11]が[Cテーブル]の更新情報となります。業務上、「当日」の年月日はこのテーブルから取得しないといけません。)
 ・データ登録日時テーブル[AA10]と[AA11]の最新日時7日分の日時範囲で一時テーブルを作成
 ・本数データ取得対象の[Aテーブル]と[Bテーブル]と[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].[Aテーブル] 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].[Bテーブル] 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].[Cテーブル] T0
          WHERE
              T0.[年月日] BETWEEN @START_DATE AND @END_DATE
          GROUP BY
              T0.[年月日]
            , T0.[入荷先コード]
            , T0.[製品コード]
      )
GROUP BY
     [年月日]
   , [倉庫コード]  
   , [製品コード]
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • mimipachi0133

    2015/10/20 23:33 編集

    テーブル[AA10]と[AA11]は、データ登録日時確認用のテーブルです。構成カラムは、[年月日]と[ファイル名称]となります。というのも、合算したい3テーブルは、日次処理でバッチによってtxtデータを毎日決まった時間に取り込んでいるためです。[AA10]は[Aテーブル][Bテーブル]の更新情報、[AA11]は[Cテーブル]の更新情報を持っています。3テーブルは単にデータを蓄積するだけなので、ここから最新年月日を基準として日付リストを作成しております。度々情報が抜け落ちて申しわけありません。

    キャンセル

  • yuba

    2015/10/20 23:39

    AA10とAA11はテキストファイルからDBテーブルへの取り込み処理のログですね。それだけだったら目的の処理にはまったく必要なさそうに見えます。
    A,B,Cのカラム構成は[年月日],[倉庫コード],[製品コード],[本数]ですよね? そして[年月日],[倉庫コード],[製品コード]が主キーでしょうか。これさえあれば情報としては必要十分に見えます。

    それとも、「当日」というのをAA10,AA11に含まれる最新の日付のことである、と定義するとかそういうことでしょうか?

    キャンセル

  • mimipachi0133

    2015/10/20 23:47 編集

    // A,B,Cのカラム構成は[年月日],[倉庫コード],[製品コード],[本数]ですよね? そして[年月日],[倉庫コード],[製品コード]が主キーでしょうか。
    それで問題ありません。DB構成上主キーではないのですが、扱いは同義です。




    //それとも、「当日」というのをAA10,AA11に含まれる最新の日付のことである、と定義するとかそういうことでしょうか?
    それでお願いいたします。おっしゃる通り、ファイル取込日時 = 3テーブルの最新年月日なんですが、一応[AA10]と[AA11]を参照するのが決まりとなっております。年月日だけ引っ張ってくるテーブルです。

    キャンセル

回答 1

+1

SQL Serverなんで一時テーブルよりはCTE(WITH構文)ですかね。
手元に実行環境ないのでデバッグしていませんがだいたいこんな感じで動くかと。
WITH 
  latest AS (
    SELECT CONVERT(DATE, CONVERT(CHAR(8), MAX([年月日])), 112) AS [最新年月日]
    FROM (SELECT [年月日] FROM AA10 UNION SELECT [年月日] FROM AA11)
  ),
  last1week AS (
    SELECT [基準日], DATEADD(d, -1, [基準日]) AS [集計対象最終日] FROM (
      SELECT DATEADD(d, -6, [最新年月日]) AS [基準日] FROM latest
      UNION
      SELECT DATEADD(d, -5, [最新年月日]) AS [基準日] FROM latest
      UNION
      SELECT DATEADD(d, -4, [最新年月日]) AS [基準日] FROM latest
      UNION
      SELECT DATEADD(d, -3, [最新年月日]) AS [基準日] FROM latest
      UNION
      SELECT DATEADD(d, -2, [最新年月日]) AS [基準日] FROM latest
      UNION
      SELECT DATEADD(d, -1, [最新年月日]) AS [基準日] FROM latest
      UNION
      SELECT [最新年月日] AS [基準日] FROM latest
    )
  ),
  target_range AS (
    SELECT [基準日],[集計対象最終日],
      DATEFROMPARTS(YEAR([集計対象最終日]), MONTH([集計対象最終日]), 1) AS [集計対象初日]
      FROM last1week
  ),
  master_table AS (
    SELECT CONVERT(DATE, CONVERT(CHAR(8), MAX([年月日])), 112) AS [日付]
      , [倉庫コード],[製品コード],[本数] FROM (
      SELECT  [年月日],[倉庫コード],[製品コード],[本数] FROM [Aテーブル]
      UNION ALL
      SELECT  [年月日],[倉庫コード],[製品コード],[本数] FROM [Bテーブル]
      UNION ALL
      SELECT  [年月日],[倉庫コード],[製品コード],[本数] FROM [Cテーブル]
    )
  ),
  target_grouping AS (
    SELECT DISTINCT [倉庫コード],[製品コード] FROM master_table
  )
SELECT 
  [基準日],[倉庫コード],[製品コード],
  (SELECT SUM([本数]) FROM master_table m 
    WHERE m.[倉庫コード]=g.[倉庫コード] AND m.[製品コード]=g.[製品コード]
    AND m.[日付] BETWEEN r.[集計対象初日] AND r.[集計対象最終日]) AS [本数総計]
FROM target_range r, target_grouping g
倉庫コードと製品コードの一覧をA,B,Cテーブルからかき集めているところが美しくありませんが、倉庫マスタテーブル、製品マスタテーブルなんてのにアクセス可能であれば改善できます。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/10/21 00:42

    早速の回答ありがとうございます。 
    なるほど、各種マスタテーブルがあればそれで紐づければ良いのですね、調べてみます。

    キャンセル

  • 2015/10/21 01:40

    これだけやっといてなんですが、これどう考えてもアプリ側で集計すべきロジックです。

    キャンセル

  • 2016/05/24 21:25

    運用業務ではSQLだけで乗り切ろ という風潮がどこでもあるんでしょうかね・・・
    以前つとめていた会社の運用部門の人も、後から誰もメンテできないような壮大なSQLを書かれていました。

    キャンセル

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

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

同じタグがついた質問を見る