從 3 個表中按月獲取合併的行數

使用 Postgres 10+，我有三個具有相似結構的表，我試圖獲取按月分組的行數。我有一個適用於兩個表的功能查詢，如下所示：

CREATE TABLE classroom_observations (id SERIAL, created_at TIMESTAMPTZ);
CREATE TABLE training_modules       (id SERIAL, created_at TIMESTAMPTZ);
CREATE TABLE teachers_workshops     (id SERIAL, created_at TIMESTAMPTZ);

INSERT INTO classroom_observations VALUES
(1, '2019-04-20 10:36:06+02')
,(2, '2019-05-22 15:22:33+02')
,(3, '2019-05-23 15:22:33+02')
,(4, '2019-05-24 15:22:33+02');
INSERT INTO training_modules VALUES
(1, '2019-03-20 10:36:06+02')
,(2, '2019-04-22 15:22:33+02')
,(3, '2019-04-23 15:22:33+02')
,(4, '2019-05-24 15:22:33+02');
INSERT INTO teachers_workshops VALUES
(1, '2019-03-20 10:36:06+02');

SELECT * FROM
 (
     SELECT to_char(co.created_at, 'YYYY-MM') AS month
          , COUNT(co.id) AS co_count
          , COUNT(tm.id) AS tm_count
       FROM classroom_observations co
      LEFT JOIN training_modules tm
         ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
      GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM')
     UNION
     SELECT to_char(tm.created_at, 'YYYY-MM') AS month
          , COUNT(co.id) AS co_count
          , COUNT(tm.id) AS tm_count
          FROM classroom_observations co
          RIGHT JOIN training_modules tm
         ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
      GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM')
     ) tbl
 ORDER BY Month;

這會產生預期的結果：

 month  | co_count | tm_count 
---------+----------+----------
2019-03 |        0 |        1
2019-04 |        1 |        2 
2019-05 |        3 |        1

當我嘗試添加第三個表teachers_workshops時，計數為tw.idastw_count時，我得到了錯誤的結果：

SELECT * FROM
 (
     SELECT to_char(co.created_at, 'YYYY-MM') AS month
          , COUNT(co.id) AS co_count
          , COUNT(tm.id) AS tm_count
          , COUNT(tw.id) AS tw_count
       FROM classroom_observations co
      LEFT JOIN training_modules tm
         ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
      LEFT JOIN teachers_workshops tw
         ON to_char(co.created_at, 'YYYY-MM') = to_char(tw.created_at, 'YYYY-MM')
      GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM') , to_char(tw.created_at, 'YYYY-MM')
     UNION
     SELECT to_char(tm.created_at, 'YYYY-MM') AS month
          , COUNT(co.id) AS co_count
          , COUNT(tm.id) AS tm_count
          , COUNT(tw.id) AS tw_count
          FROM classroom_observations co
          RIGHT JOIN training_modules tm
         ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
          RIGHT JOIN teachers_workshops tw
         ON to_char(co.created_at, 'YYYY-MM') = to_char(tw.created_at, 'YYYY-MM')
      GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM'), to_char(tw.created_at, 'YYYY-MM')
     UNION
     SELECT to_char(tm.created_at, 'YYYY-MM') AS month
          , COUNT(co.id) AS co_count
          , COUNT(tm.id) AS tm_count
          , COUNT(tw.id) AS tw_count
          FROM classroom_observations co
          RIGHT JOIN training_modules tm
         ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
          RIGHT JOIN teachers_workshops tw
         ON to_char(co.created_at, 'YYYY-MM') = to_char(tw.created_at, 'YYYY-MM')
      GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM'), to_char(tw.created_at, 'YYYY-MM')
     ) tbl
 ORDER BY Month;

壞結果：

 month  | co_count | tm_count | tw_count 
---------+----------+----------+----------
2019-04 |        1 |        0 |        0
2019-05 |        6 |        6 |        6

應該只有 1 條記錄tw_count，並且應該包含另一個月份2019-03。我顯然沒有正確地解決這個問題，所以任何幫助都將不勝感激。

我正在嘗試按月分組的行數

您顯示的查詢可能會做一些完全不同的事情。為了實現您宣布的目標：

SELECT to_char(mon, 'YYYY-MM') AS month
    , COALESCE(co.ct, 0) AS co_count
    , COALESCE(tm.ct, 0) AS tm_count
    , COALESCE(tw.ct, 0) AS tw_count
FROM  (
  SELECT date_trunc('month', created_at) AS mon, count(*) AS ct
  FROM   classroom_observations
  GROUP  BY mon
  ) co
FULL JOIN (
  SELECT date_trunc('month', created_at) AS mon, count(*) AS ct
  FROM   training_modules
  GROUP  BY mon
  ) tm USING (mon)
FULL JOIN (
  SELECT date_trunc('month', created_at) AS mon, count(*) AS ct
  FROM   teachers_workshops
  GROUP  BY mon
  ) tw USING (mon)
ORDER  BY mon;

結果：

 month  | co_count | tm_count | tw_count 
---------+----------+----------+----------
2019-03 |        0 |        1 |        1
2019-04 |        1 |        2 |        0
2019-05 |        3 |        1 |        0

db<>在這裡擺弄

• FULL [OUTER] JOIN保留所有行，即使另一側沒有匹配。
• 結果是沒有找到行的值 - 我將NULL其替換為用作可選添加。0COALESCE
• date_trunc()比 更快、更可靠to_char()。在截斷的時間戳上聚合和連接，並且只to_char()在外部格式化一次SELECT。

引用自：https://dba.stackexchange.com/questions/238908