如何包含时间跨度内多个分组的缺失数据？

我在下面引用了查询，其中按教师、学习年月和过去 12 个月（包括当月）的房间对学习计数进行分组。我得到的结果是正确的，但是，我想在数据丢失时包含计数为零的行。

我查看了其他几个相关的帖子，但无法获得所需的输出：

• Postgres - 如何返回缺失数据计数为 0 的行？ https://stackoverflow.com/questions/346132/postgres-how-to-return-rows-with-0-count-for-missing-data/15733103#15733103
• Postgresql 组月明智地缺失值 https://stackoverflow.com/questions/24156202/postgresql-group-month-wise-with-missing-values/24165871#24165871
• Rails+Postgres 中按任意时间间隔计算记录的最佳方法 https://stackoverflow.com/questions/15576794/best-way-to-count-records-by-arbitrary-time-intervals-in-railspostgres/15577413#15577413

这是查询：

SELECT
    upper(trim(t.full_name))               AS teacher
  , date_trunc('month', s.study_dt)::date  AS study_month
  , r.room_code                            AS room
  , COUNT(1)                               AS study_count
FROM
  studies                           AS s
  LEFT OUTER JOIN rooms             AS r   ON r.id = s.room_id
  LEFT OUTER JOIN teacher_contacts  AS tc  ON tc.id = s.teacher_contact_id
  LEFT OUTER JOIN teachers          AS t   ON t.id = tc.teacher_id 
WHERE
  s.study_dt BETWEEN now() - interval '13 month' AND now()
  AND s.study_dt IS NOT NULL
GROUP BY
    teacher
  , study_month
  , room
ORDER BY 
    teacher  
  , study_month
  , room;

我得到的输出：

"teacher","study_month","room","study_count"
"DOE, JOHN","2015-07-01","A1",1
"DOE, JOHN","2015-12-01","A2",1
"DOE, JOHN","2016-01-01","B1",1
"SIMPSON, HOMER","2016-05-01","B2",3
"MOUSE, MICKEY","2015-08-01","A2",1
"MOUSE, MICKEY","2015-11-01","B1",1
"MOUSE, MICKEY","2015-11-01","B2",2

但我希望为所有缺失的年月和房间组合显示 0 计数。例如（仅第一排，共有 4 个房间：A1, A2, B1, B2):

"teacher","study_month","room","study_count"
"DOE, JOHN","2015-07-01","A1",1
"DOE, JOHN","2015-07-01","A2",0
"DOE, JOHN","2015-07-01","B1",0
"DOE, JOHN","2015-07-01","B2",0
...
"DOE, JOHN","2015-12-01","A1",1
"DOE, JOHN","2015-12-01","A2",0
"DOE, JOHN","2015-12-01","B1",0
"DOE, JOHN","2015-12-01","B2",0
...

为了获得丢失的年月，我尝试使用时间序列进行左外连接并加入time_range.year_month = study_month，但没有成功。

SELECT date_trunc('month', time_range)::date AS year_month
FROM generate_series(now() - interval '13 month', now() ,'1 month') AS time_range 

所以，我想知道如何“填补空白”

a) 年月和房间，并且作为奖励： b) 只是一年一个月。

这样做的原因是数据集将被输入到一个数据透视库中，我们可以得到类似于以下的输出（无法直接在 PG 中执行此操作）：

teacher,room,2015-07,...,2015-12,...,2016-07,total
"DOE, JOHN",A1,1,...,1,...,0,2
"DOE, JOHN",A2,0,...,0,...,0,0
...and so on...

基于一些假设（问题中的含糊之处）我建议：

SELECT upper(trim(t.full_name)) AS teacher
     , m.study_month
     , r.room_code              AS room
     , count(s.room_id)         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') m(study_month)
CROSS  JOIN rooms r
LEFT   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.id
GROUP  BY t.id, m.study_month, r.id  -- id is PK of respective tables
ORDER  BY t.id, m.study_month, r.id;

主要观点

• 构建所有所需组合的网格CROSS JOIN。进而LEFT JOIN到现有行。有关的：

  • array_agg group by 和 null https://stackoverflow.com/questions/22443882/array-agg-group-by-and-null/22480876#22480876
  • 获取上周创建和删除的条目 https://stackoverflow.com/questions/29371305/get-created-as-well-as-deleted-entries-of-last-week/29375290#29375290

• 在你的例子中，它是几个表的联接，所以我在FROM列出至LEFT JOIN to the result of INNER JOIN括号内。 这将是不正确 to LEFT JOIN分别到每个表，因为您将包括部分匹配的命中并获得可能不正确的计数。

• 假设参照完整性直接使用 PK 列，我们不需要包括rooms and teachers第二次在左侧。但我们仍然有两个表的联接（studies and teacher_contacts）。的作用teacher_contacts我不清楚。通常情况下，我期望之间存在关系studies and teachers直接地。可能会进一步简化...

• 我们需要计算左侧的非空列以获得所需的计数。喜欢count(s.room_id)

• 为了让大表保持快速，请确保您的谓词是sargable https://en.wiktionary.org/wiki/sargable。并添加匹配indexes.

• 专栏teacher很难（可靠）独特。使用唯一的 ID 进行操作，最好是 PK（也更快、更简单）。我还在用teacher使输出符合您想要的结果。包含唯一 ID 可能是明智之举，因为名称可能重复。

• 你要：

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

  所以从date_trunc('month', now() - interval '12 month'（不是 13）。这已经四舍五入了开始并执行您想要的操作 - 比您的原始查询更准确。

由于您提到性能缓慢，根据实际的表定义和数据分布，可能会更快先聚合后加入，就像这个相关的答案一样：

• Postgres - 如何返回缺失数据计数为 0 的行？ https://stackoverflow.com/questions/346132/postgres-how-to-return-rows-with-0-count-for-missing-data/15733103#comment64041705_15733103