SQL server UNION ALL 簡潔な結合の方法

Question

実現したいこと

SQL文をより簡潔化し、上平均、下平均、平均を容易に確認できるviewを作成したいです。

---

実行日から過去半年間を明細として表示
それらの平均値が下平均
実行日から７～１２カ月前が上平均
１２カ月前からのものが平均

それぞれ別々のSQLで作成し、UNION ALLで結合しました。

発生している問題・分からないこと

SQL初心者です。おかしい質問や必要な情報が足りない等あるかと存じます。
申し訳ございませんがその際はご指導いただけますと幸いです。

<マスタ項目については、トランザクションのunion allの結果に対して結合するときれいです。>

上記のご指導を頂きました。この内容が理解できず、恐れ入りますが教えていただけますと幸いです。

該当のソースコード

  SELECT TZ.[組織コード]
    , TZ.[倉庫コード]
    , SKM.[倉庫名]
    , TZ.[品目コード]
	, HM.[品目名１]
	, HM.[品目名２]
    , TZ.[対象年月]
	, TZ.[売上数]
FROM [yoshimei].[dbo].[TT_月別在庫] TZ
INNER JOIN [yoshimei].[dbo].[TM_品目マスタ] HM
	ON TZ.[品目コード] = HM.[品目コード]
LEFT JOIN [yoshimei].[dbo].[TM_倉庫マスタ] SKM
	ON TZ.[倉庫コード] = SKM.[倉庫コード]
	WHERE TZ.[対象年月]BETWEEN
	 [dbo].[fn_会計年月](dateadd(m,-6,GETDATE())) AND [dbo].[fn_会計年月](GETDATE())
UNION ALL
SELECT TZ.[組織コード]
      ,TZ.[倉庫コード]
	  , SKM.[倉庫名]
	  , TZ.[品目コード]
	  , HM.[品目名１]
	  , HM.[品目名２]
	  ,'上平均'
      , AVG(TZ.[売上数])
FROM [yoshimei].[dbo].[TT_月別在庫] TZ
INNER JOIN [yoshimei].[dbo].[TM_品目マスタ] HM
	ON TZ.[品目コード] = HM.[品目コード]
LEFT JOIN [yoshimei].[dbo].[TM_倉庫マスタ] SKM
	ON TZ.[倉庫コード] = SKM.[倉庫コード]
	WHERE TZ.[対象年月]BETWEEN
	 [dbo].[fn_会計年月](dateadd(m,-12,GETDATE())) AND
	 [dbo].[fn_会計年月](dateadd(m,-7,GETDATE()))
	 GROUP BY TZ.[組織コード],TZ.[倉庫コード], SKM.[倉庫名], TZ.[品目コード], HM.[品目名１] , HM.[品目名２]
UNION ALL 
  SELECT TZ.[組織コード]
    , TZ.[倉庫コード]
    , SKM.[倉庫名]
    , TZ.[品目コード]
	, HM.[品目名１]
	, HM.[品目名２]
	  ,'下平均'
      , AVG(TZ.[売上数])
FROM [yoshimei].[dbo].[TT_月別在庫] TZ
INNER JOIN [yoshimei].[dbo].[TM_品目マスタ] HM
	ON TZ.[品目コード] = UKM.[品目コード]
LEFT JOIN [yoshimei].[dbo].[TM_倉庫マスタ] SKM
	ON TZ.[倉庫コード] = SKM.[倉庫コード]
	WHERE TZ.[対象年月]BETWEEN
	 [dbo].[fn_会計年月](dateadd(m,-6,GETDATE())) AND [dbo].[fn_会計年月](GETDATE())
	 GROUP BY TZ.[組織コード],TZ.[倉庫コード], SKM.[倉庫名], TZ.[品目コード], HM.[品目名１] , HM.[品目名２]
UNION ALL
  SELECT TZ.[組織コード]
    , TZ.[倉庫コード]
    , SKM.[倉庫名]
    , TZ.[品目コード]
	, HM.[品目名１]
	, HM.[品目名２]
	  ,'平均'
      , AVG(TZ.[売上数])
FROM [yoshimei].[dbo].[TT_月別在庫] TZ
INNER JOIN [yoshimei].[dbo].[TM_品目マスタ] HM
	ON TZ.[品目コード] = HM.[品目コード]
LEFT JOIN [yoshimei].[dbo].[TM_倉庫マスタ] SKM
	ON TZ.[倉庫コード] = SKM.[倉庫コード]
	WHERE TZ.[対象年月]BETWEEN
	 [dbo].[fn_会計年月](dateadd(m,-12,GETDATE()))AND [dbo].[fn_会計年月](GETDATE())
	 GROUP BY TZ.[組織コード],TZ.[倉庫コード], SKM.[倉庫名], TZ.[品目コード], HM.[品目名１] , HM.[品目名２]

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

調べたのですが、
トランザクションデータ（売上数など）を「union all」で結合します。その結果を基に、マスタデータ（組織コード、倉庫コード、品目コードなど）を結合すると、データがきれいに整理され、処理も効率的になります。
上記回答では私の頭では理解できず。。。申し訳ございませんが猿でもわかるように教えていただけますと幸いです。

補足

特になし

Comments

[can110]

> SQL初心者です。おかしい質問や必要な情報が足りない等あるかと存じます。
> 申し訳ございませんがその際はご指導いただけますと幸いです。

https://teratail.com/help/question-tips#questionTips2
https://teratail.com/help/question-tips
は読みましたか？
読んだ結果を本文に反映ください。解決のヒントを探れるかもしれません。

[s_hotate_s]

ご回答、ご指導ありがとうございます。
わかりづらい質問文となっており申し訳ございません。

Answer 1

業務の詳細やテーブル定義などが不明なため、憶測が多分に交じりますが、SQLを見る限りでは、「上平均、下平均、平均」はTT_月別在庫テーブル単体から導出可能であるようです (提示していただいたSQLでは「上平均、下平均、平均」を求めるのに、TM_品目マスタテーブルとTM_倉庫マスタを結合している)

指摘者の意図については完全にはわかりかねますが、以下のようなSQLのつくりにしてほしかったのではないでしょうか?

1. TT_月別在庫テーブル から「上平均、下平均、平均」を導出する。導出結果をXとする
2. XにTM_品目マスタテーブルとTM_倉庫マスタを結合する

SELECT 
    X.[組織コード]
  , X.[倉庫コード]
  , SKM.[倉庫名]
  , X.[品目コード]
  , HM.[品目名１]
  , HM.[品目名２]
  , X.[対象年月]
  , X.[売上数]
FROM (
    SELECT TZ.[組織コード], TZ.[倉庫コード], TZ.[品目コード], TZ.[対象年月], TZ.[売上数]
    FROM [yoshimei].[dbo].[TT_月別在庫] TZ
    WHERE TZ.[対象年月] BETWEEN [dbo].[fn_会計年月](dateadd(m,-6,GETDATE())) AND [dbo].[fn_会計年月](GETDATE())
  UNION ALL 
    SELECT TZ.[組織コード], TZ.[倉庫コード], TZ.[品目コード], '上平均', AVG(TZ.[売上数])
    WHERE TZ.[対象年月] BETWEEN [dbo].[fn_会計年月](dateadd(m,-12,GETDATE())) AND [dbo].[fn_会計年月](dateadd(m,-7,GETDATE()))
    GROUP BY TZ.[組織コード], TZ.[倉庫コード], TZ.[品目コード]
  UNION ALL
    SELECT TZ.[組織コード], TZ.[倉庫コード], TZ.[品目コード], '下平均', AVG(TZ.[売上数])
    WHERE TZ.[対象年月] BETWEEN [dbo].[fn_会計年月](dateadd(m,-6,GETDATE())) AND [dbo].[fn_会計年月](GETDATE())
    GROUP BY TZ.[組織コード], TZ.[倉庫コード], TZ.[品目コード]
  UNION ALL
    SELECT TZ.[組織コード], TZ.[倉庫コード], TZ.[品目コード], '平均', AVG(TZ.[売上数])
    WHERE [dbo].[fn_会計年月](dateadd(m,-12,GETDATE()))AND [dbo].[fn_会計年月](GETDATE())
    GROUP BY TZ.[組織コード], TZ.[倉庫コード], TZ.[品目コード]
) AS X
JOIN INNER JOIN [yoshimei].[dbo].[TM_品目マスタ] HM ON X.[品目コード] = HM.[品目コード]
LEFT JOIN [yoshimei].[dbo].[TM_倉庫マスタ] SKM ON X.[倉庫コード] = SKM.[倉庫コード]

Comments

[s_hotate_s]

回答ありがとうございます。
質問文がわかりづらい中、ご丁寧にありがとうございます。
指示を出された際、サブクエリを使用するということをすぐに導き出せない能力の低さ、まだまだ学習が足りていないと感じました。
自分にしっかりと落とし込みフィードバックさせていただきます。
本当にありがとうございます。
ベストアンサーに選ばせていただきました。