整形後の複数SELECT文を一つの表にまとめる

Question

お世話になっております。
よろしくお願いします。

やりたいこと

複数のテーブルからSELECTした結果を合わせて一つにまとめた表を出力するビューを作成したいです。

下記のような二つSQLで複数テーブルからデータを抽出した後、ID・year・monthを基準として一つの表にまとめたいと考えています。

--SQL1：テーブル"TABLE0"からID・year・monthを基準にstatus=1のレコード数をカウントする
SELECT tbl1.ID
      ,tbl1.year
      ,tbl1.month
      ,sum(CASE tbl1.status WHEN 1 THEN 1 ELSE 0 END) AS count_status
FROM (
       SELECT tbl0.ID
      ,extract(YEAR FROM tbl0.ts) AS year
      ,extract(MONTH FROM tbl0.ts) AS month
      ,tbl0.status
       FROM TABLE0 tbl0
     ) AS tbl1
WHERE tbl1.status = 1
GROUP BY tbl1.ID,tbl1.year,tbl1.month
ORDER BY tbl1.ID,tbl1.year,tbl1.month;

--テーブル"TABLE_A"からID・year・monthを基準にNumber1=1,2,3のレコード数をカウント
--テーブル"TABLE_B"からID・year・monthを基準にNumber2=12のレコード数をカウント
--上記実現のために、それぞれの条件でUNION後にカウント
SELECT tblA.ID
      ,tblA.year
      ,tblA.month
      ,sum(CASE tblA.number WHEN 1 THEN 1 ELSE 0 END) AS count_1
      ,sum(CASE tblA.number WHEN 2 THEN 1 ELSE 0 END) AS count_2
      ,sum(CASE tblA.number WHEN 3 THEN 1 ELSE 0 END) AS count_3
      ,sum(CASE tblA.number WHEN 12 THEN 1 ELSE 0 END) AS count_12
FROM (
       SELECT tbla.ID
             ,extract(YEAR FROM tbla.timestamp) AS year
             ,extract(MONTH FROM tbla.timestamp) AS month
             ,tbla.Number1 AS number
       FROM TABLE_A tbla
       WHERE tbla.Number1 in (1,2,3)
       UNION ALL 
       SELECT tblb.ID
             ,extract(YEAR FROM tblb.timestamp) AS year
             ,extract(MONTH FROM tblb.timestamp) AS month
             ,tblb.Number2
       FROM TABLE_B tblb
       WHERE tblb.Number2 = 12
     ) AS tblA
GROUP BY tblA.ID,tblA.year,tblA.month
ORDER BY tblA.ID,tblA.year,tblA.month;

試そうとしたこと

"LEFT JOIN"で一つ目のSQL結果に二つ目のSQL結果を結合しようと考えましたが、LEFT JOIN ON ○○ = ●●を書く段階で、どうやって記載すればいいかわからなくなってしまいました。
(二つ目のSQLですでに複数テーブルをUNIONしてtblAとエイリアスをつけているため)

先週質問させていただいた内容の質問からあまり時間がたっておらず、非常に力不足を実感いたします。。
ご参考となる一言でも構いませんので、ご助力いただけますと幸いです。

Answer 1

端的な説明をすると、以下のようになれば良いと思いますが。

select Q1.*
     , Q2.count_1, Q2.count_2, Q2.count_3, Q2.count_12
from (一つ目のSQL) Q1
     left join (二つ目のSQL) Q2
     on  Q1.ID = Q2.ID
     AND Q1.year = Q2.year
     AND Q1.month= Q2.month
ORDER BY Q1.ID, Q1.year, Q1.month

※それぞれのSQLでのorder byは不要です。
性能的なところでは分解した方が良いかもしれませんけど、それも実行計画次第ですね。

Comments

[inu_inu]

ご提示いただいたLEFT JOINのテーブル指定部分にSELECTを記載することで無事に出力することができました！
GROUP BYの指定で少々躓きましたので意味合いについてもっと理解を深めたいと思います。
BAについては、kunai様のご助言でピンと来て作成できたため、非常に申し訳ありませんがkunai様をBAとさせていただきました。
sazi様のご回答で答え合わせができたため、非常に参考になったことに変わりはありません。ありがとうございました。

Answer 2

on は、1つ目のTableと2つ目のTableを繋ぐ際に、どのColumnをキーにしてつなげればいいか、という事ですよ。
1つにまとめる時に「1つ目のTableのこの値が2つ目のTableのこの値と一致するものでまとめよう」と考えますよね？
その2つのColumnを書けばいいだけです。

Comments

[inu_inu]

記載漏れていましたが、一度のSQLで複数のテーブルからSELECTした結果を合わせて一つにまとめた表を出力するビューを作成したいと思っています。
kunai様のおっしゃる通り、
LEFT JOIN ON tbl1.ID = tblA.ID
AND tbl1.year = tablA.year
AND tbl1.month= tablA.month
が必要ということはわかるのですが、tblAは二つ目のSQLの結果付与したエイリアスとなるため、二つ目のSQL自体をどこに組み込めばいいのかがわかっていない状況です。。

[inu_inu]

LEFT JOIN テーブル名 ON ～
のテーブル名部分にSELECTを記載すればいいですかね、試してみたいと思います。

[inu_inu]

テーブル名部分へ記載した後、GROUP BYの指定で少々躓きましたが、無事に出力することができました！ありがとうございました。