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

Question

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

我查看了其他几篇相关文章，但未获得所需的输出：

• Postgres-如何为丢失的数据返回计数为0的行？
• 缺少值的PostgreSQL组月份明智
• 在Rails + Postgres中按任意时间间隔对记录进行计数的最佳方法

这是查询：

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...

Answer 1

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

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
  • 获取上周的创建和删除条目
  • 根据你的情况，这是一个连接几个表的，所以我用括号中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 '12 month'（而不是13）开始。这已经使开始更圆了，并且可以满足您的要求-比原始查询更准确。

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

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

  SELECT upper(trim(t.full_name)) AS teacher
  , m.mon AS study_month
  , r.room_code 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交叉表查询