小编典典

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

sql

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

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

这是查询:

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个房间: A1A2B1B2 ):

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

阅读 161

收藏
2021-03-08

共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列直接,我们并不需要包括工作roomsteachers左侧第二次。但是我们仍然有两个表(studiesteacher_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()直接并以出色的性能产生所需的结果,而无需首先进行上述查询。考虑:

2021-03-08