时间戳

首页 > 解决方案 > 显示计数数据为 0 的日期

sql - 显示计数数据为 0 的日期

问题描述

我有这样的查询

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 的新手

我已经从各地遵循了几个解决方案似乎还没有得到我想要的结果

我尝试使用 generate_series 但没有结果出来

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;

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

标签： sqlpostgresqlleft-join

解决方案

您很接近，但是您的 WHERE 子句将外部联接重新转换为内部联接。您需要将该条件移动到 JOIN 条件中。

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;

推荐阅读