Postgres查询返回0 If Empty For Date

Question

我在stackoverflow中挖掘了一些解决方案，但没有什么能与我想要的完全匹配(我不认为(。

我的目标是组合两个查询，它们将返回3列[日期，OUT，IN]作为输出。这两个查询各自返回正确的输出。

OUT的第一个SELECT查询返回[03/01，4]，[03/02，10]，[03/03，21]，[003/01，4]

IN的第二次SELECT查询返回[03/01，4]，[03/03，25]

使用WITH以两种不同的方式组合两个查询，输出是相同的。

[03/01，4，4]，[03/03，21，25]->OUT作为内部查询

[03/01，4，4]，[03/03，21，25]->IN作为内部查询

可以看出，由于3月2日和4日没有IN，因此会为这些日期返回一行空数据。我认为使用WITH查询的正确方法是将OUT作为内部查询，因此第2个和第4个是外部查询中递归的一部分。

粘贴在下面的是WITH查询的简化版本。内部查询本身会返回三月的每一天的计数。由于某种原因，当外部查询没有相关日期(第2和第4天(的计数时，它不会返回0。有人对如何解决这个问题有意见吗？我可能用错COALESCE了吗？

WITH first_query (the_date, out) AS
(
SELECT DISTINCT ON(to_char(t1.date, 'dd')) to_char(t1.out_date, 'dd') AS out_date2, count(to_char(t1.out_date, 'dd')) AS out
FROM table_out AS t1
WHERE to_char(t1.out_date,'yyyy') = to_char(date_trunc('year', CURRENT_DATE), 'YYYY')  
AND to_char(t1.out_date,'MM') = to_char(date_trunc('month', CURRENT_DATE), 'MM')  
AND t1.out_id = 14
GROUP BY out_date2
)
SELECT fq.the_date AS day_of_month, fq.out, COALESCE( ( SELECT COUNT(to_char(t1.in_date, 'DD')) ), 0) AS in
FROM first_query fq, table_in t1
WHERE to_char(t1.in_date,'yyyy') = to_char(date_trunc('year', CURRENT_DATE), 'YYYY') 
AND to_char(t1.in_date,'MM') = to_char(date_trunc('month', CURRENT_DATE), 'MM')
AND to_char(t1.in_date,'dd') = fq.the_date
GROUP BY day_of_month, fq.out
ORDER BY day_of_month ASC

按照Jeremy的建议，答案最终需要CAST和LEFT JOIN。时间戳让这件事变得更加困难。

SELECT CAST(t1.out_date AS Date) AS date2, count(DISTINCT 
t1.id) out, count(DISTINCT t2.id) AS in
FROM table_out as t1
LEFT JOIN table_in AS t2 ON CAST(t1.out_date AS Date) =         
CAST(t2.in_date AS Date)
WHERE to_char(t1.out_date,'yyyy') = 
to_char(date_trunc('year',         CURRENT_DATE), 'YYYY')
AND to_char(t1.out_date,'MM') = to_char(date_trunc('month',                         
CURRENT_DATE), 'MM')
AND t1.out_id = 14
GROUP BY out_date2

Answer 1

按照Jeremy的建议，答案最终需要CAST和LEFT JOIN。时间戳让这件事变得更加困难。

SELECT CAST(t1.out_date AS Date) AS date2, count(DISTINCT 
t1.id) out, count(DISTINCT t2.id) AS in
FROM table_out as t1
LEFT JOIN table_in AS t2 ON CAST(t1.out_date AS Date) =         
CAST(t2.in_date AS Date)
WHERE to_char(t1.out_date,'yyyy') = 
to_char(date_trunc('year',         CURRENT_DATE), 'YYYY')
AND to_char(t1.out_date,'MM') = to_char(date_trunc('month',                         
CURRENT_DATE), 'MM')
AND t1.out_id = 14
GROUP BY out_date2