显示计数数据为0的日期

vecaoik1 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(364)

我有一个这样的问题

SELECT
  d.create_at :: DATE AS dates,
  count(r.id) AS usages
FROM reports r LEFT JOIN dispatches d ON r.dispatch_id = d.id
  LEFT JOIN messages m ON d.message_id = m.id
WHERE r.status = 'SUCCESS' AND r.update_at :: DATE BETWEEN DATE '2020-05-01' AND '2020-05-10' AND m.client_id = 6
GROUP BY dates
ORDER BY dates;

会产生这样的结果

因为没有2020-05-03和2020-05-08的数据，所以他们不会显示在结果上
如何添加那些没有数据的日期，并将计数数据0作为值？
抱歉，我对postgres和db都是新手
我已经从各地遵循了几种解决方案似乎还没有得到我想要的结果
我试着用生成序列，但没有结果出来

SELECT
  x.day AS dates,
  count(r.id) AS usages
FROM (
   SELECT generate_series(timestamp '2020-05-01'
                        , timestamp '2020-05-10'
                        , interval '1 day')::date
   ) x(day)
    left join dispatches d on d.create_at = x.day
    LEFT JOIN reports r ON d.id = r.dispatch_id
    LEFT JOIN messages m ON d.message_id = m.id
WHERE r.status = 'SUCCESS' AND r.update_at :: DATE BETWEEN DATE '2020-05-01' AND '2020-05-10' AND m.client_id = 6
GROUP BY dates
ORDER BY dates;

抱歉，我遗漏了一些明显的东西。任何帮助都将不胜感激谢谢！

sql postgresql left-join

来源：https://stackoverflow.com/questions/62275079/display-dates-that-have-0-count-data

2条答案

按热度按时间

niwlg2el1#

请使用下面的查询，

SELECT
  dt.date_seq :: DATE AS dates,
  count(r.id) AS usages
FROM reports r LEFT JOIN dispatches d ON r.dispatch_id = d.id
LEFT JOIN messages m ON d.message_id = m.id
FULL OUTER JOIN (select CURRENT_DATE + i as date_seq from generate_series(date '2020- 
05-01'- CURRENT_DATE, date '2020-05-10' - CURRENT_DATE ) i)
dt
ON d.create_at = dt.date_seq
WHERE r.status = 'SUCCESS' AND r.update_at :: DATE BETWEEN DATE '2020-05-01' AND 
'2020-05-10' AND m.client_id = 6
GROUP BY dates
ORDER BY dates;

赞(0）回复(0）举报 2021-08-09

wmomyfyw2#

很接近，但where子句将外部连接变回内部连接。您需要将该条件移到联接条件中。

SELECT x.day AS dates,
       count(r.id) AS usages
FROM (
   SELECT generate_series(timestamp '2020-05-01'
                        , timestamp '2020-05-10'
                        , interval '1 day')::date
) x(day)
  left join dispatches d on d.create_at = x.day
  LEFT JOIN reports r 
         ON d.id = r.dispatch_id
        and r.status = 'SUCCESS' 
        AND r.update_at::DATE BETWEEN DATE '2020-05-01' AND '2020-05-10' 
        AND m.client_id = 6
    LEFT JOIN messages m ON d.message_id = m.id
GROUP BY dates
ORDER BY dates;

赞(0）回复(0）举报 2021-08-09

我来回答

显示计数数据为0的日期

2条答案

相关问题

热门标签

最新问答