如何在该时间段内包括多个分组的缺失数据？

基于一些假设（问题中的歧义），我建议：

SELECt upper(trim(t.full_name)) AS teacher     , m.study_month     , r.room_pre   AS room     , count(s.room_id)         AS study_countFROM   teachers tCROSS  JOIN generate_series(date_trunc('month', now() - interval '12 month')  -- 12!    , date_trunc('month', now())    , interval '1 month') m(study_month)CROSS  JOIN rooms rLEFT   JOIN (      -- parentheses!          studies s   JOIN   teacher_contacts tc ON tc.id = s.teacher_contact_id  -- INNER JOIN!   ) ON tc.teacher_id = t.id    AND s.study_dt >= m.study_month    AND s.study_dt <  m.study_month + interval '1 month'      -- sargable!    AND s.room_id = r.idGROUP  BY t.id, m.study_month, r.id  -- id is PK of respective tablesORDER  BY t.id, m.study_month, r.id;

要点

• 用建立所有所需组合的网格

  CROSS JOIN

  。然后

  LEFT JOIN

  到现有行。有关的：
  • array_agg group by和null
  • 获取上周的创建和删除条目
  • 根据你的情况，这是一个连接几个表的，所以我用括号中

    FROM

    列表

    LEFT JOIN

    的 结果 的

    INNER JOIN

    括号内。这将是 不正确 对

    LEFT JOIN

    每个表分别，因为你将包括部分匹配安打，获得潜在的不正确计数。

• 假设 参照完整性 与PK列直接，我们并不需要包括工作

  rooms

  和

  teachers

  左侧第二次。但是我们仍然有两个表（

  studies

  和

  teacher_contacts

  ）的联接。我的角色

  teacher_contacts

  尚不清楚。通常，我期望

  studies

  和之间存在

  teachers

  直接关系。可能会进一步简化…

• 我们需要对左侧的非空列进行计数以获得所需的计数。喜欢

  count(s.room_id)

• 为了在大型表中保持快速运行，请确保您的谓词是可 保留的 。并添加匹配的 索引 。

• 该列

  teacher

  几乎（可靠）唯一。使用唯一的ID（最好是PK）（也更快，更简单）进行操作。我仍在使用

  teacher

  输出来匹配您想要的结果。包含唯一的ID可能是明智的，因为名称可以重复。

• 你要：

过去12个月（包括当月）。

因此，从

date_trunc('month', now() - interval '12month'

由于您提到的性能较低，这取决于实际的表定义和数据分布，因此先 聚合然后再加入 可能更快，例如在以下相关答案中：

• Postgres-如何为丢失的数据返回计数为0的行？

  SELECt upper(trim(t.full_name)) AS teacher
  , m.mon AS study_month
  , r.room_pre AS room
  , COALESCE(s.ct, 0) AS study_count
  
  

  FROM teachers t
  CROSS JOIN generate_series(date_trunc(‘month’, now() - interval ‘12 month’) – 12!
  , date_trunc(‘month’, now())
  , interval ‘1 month’) mon
  CROSS JOIN rooms r
  LEFT JOIN ( – parentheses!
  SELECt tc.teacher_id, date_trunc(‘month’, s.study_dt) AS mon, s.room_id, count(*) AS ct
  FROM studies s
  JOIN teacher_contacts tc ON s.teacher_contact_id = tc.id
  WHERe s.study_dt >= date_trunc(‘month’, now() - interval ‘12 month’) – sargable
  GROUP BY 1, 2, 3
  ) s ON s.teacher_id = t.id
  AND s.mon = m.mon
  AND s.room_id = r.id
  ORDER BY 1, 2, 3;

关于您的结束语：

数据集将被馈送到数据透视库…（无法直接在PG中执行此操作）

您 可以 使用的两参数形式

crosstab()

• PostgreSQL交叉表查询