我在下面引用了以下查询,该查询按教师,学习年份- 月份和过去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个房间: 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,但是这没有用。
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到现有行。有关的:
CROSS JOIN
LEFT JOIN
FROM
INNER JOIN
假设 参照完整性 与PK列直接,我们并不需要包括工作rooms和teachers左侧第二次。但是我们仍然有两个表(studies和teacher_contacts)的联接。我的角色teacher_contacts尚不清楚。通常,我期望studies和之间存在teachers直接关系。可能会进一步简化…
rooms
teachers
studies
teacher_contacts
我们需要对左侧的非空列进行计数以获得所需的计数。喜欢count(s.room_id)
count(s.room_id)
为了在大型表中保持快速运行,请确保您的谓词是可 保留的 。并添加匹配的 索引 。
该列teacher几乎(可靠)唯一。使用唯一的ID(最好是PK)(也更快,更简单)进行操作。我仍在使用teacher输出来匹配您想要的结果。包含唯一的ID可能是明智的,因为名称可以重复。
teacher
你要:
过去12个月(包括当月)。
因此,从date_trunc('month', now() - interval '12 month'(而不是13)开始。这已经使开始更圆了,并且可以满足您的要求-比原始查询更准确。
date_trunc('month', now() - interval '12 month'
由于您提到的性能较低,这取决于实际的表定义和数据分布,因此先 聚合然后再加入 可能更快,例如在以下相关答案中:
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()直接并以出色的性能产生所需的结果,而无需首先进行上述查询。考虑:
crosstab()