PostgreSQL组由包括零

Question

我有一个 SQL 查询 (postgresql)，看起来像这样：

SELECT
    my_timestamp::timestamp::date as the_date,
    count(*) as count
FROM my_table
WHERE ...
GROUP BY the_date
ORDER BY the_date

结果是一个由 YYYY-MM-DD, count 对组成的表。

现在我被要求用零填写空日期。因此，如果我之前提供了

2022-03-15    3
2022-03-17    1

我现在想要返回

2022-03-15    3
2022-03-16    0
2022-03-17    1

现在我可以轻松地执行此客户端（相对于数据库）并让我的程序根据 postgres 的原始列表计算并将零增强列表返回给其客户端。但如果我可以告诉 postgresql 包含零，也许会更好。

我怀疑这根本不容易，因为 postgres 没有明显的方法知道我在做什么。但为了更多地了解 postgres 和 SQL，我想我应该尝试一下。到目前为止，这个尝试还不太有希望......

在我得出结论认为我把这个留给我的（postgres客户端）程序是正确的之前，有什么指示吗？

更新

这是一个有趣的案例，我对问题的简化导致了一个对我不起作用的正确答案。对于后来的人，我认为值得记录接下来的内容，因为通过构建 SQL 查询需要一些有趣的曲折。

@a_horse_with_no_name 回复了一个查询，如果我简化自己的查询以进行匹配，我已经验证该查询是否有效。不幸的是，我的查询有一些我认为不相关的额外包袱，因此在发布原始问题时已被删除。

这是我真实的（原始）查询，保留了所有名称（如果缩短）：

-- current query
SELECT
    LEAST(time1, time2, time3, time4)::timestamp::date as the_date,
    count(*) as count
FROM reading_group_reader rgr
INNER JOIN (  SELECT group_id, group_type  ::group_type_name
FROM (VALUES (31198, 'excerpt')) as T(group_id, group_type)) TT
ON TT.group_id = rgr.group_id
  AND TT.group_type = rgr.group_type
WHERE LEAST(time1, time2, time3, time4) >  current_date - 30
GROUP BY the_date
ORDER BY the_date;

但是，如果我将其直接转换为建议的解决方案，则 reading_group_reader 和临时表 TT< 之间的内部联接/code> 导致左连接变为内部（我认为）并且日期序列再次删除其零。 Fwiw，表 TT 是一个表，因为有时它实际上是一个子选择。

所以我将我的查询转换为这样：

SELECT
    g.dt::date as the_date,
    count(*) as count
FROM generate_series(date '2022-03-06', date '2022-04-06', interval '1 day') as g(dt)
LEFT JOIN (
    SELECT
        LEAST(rgr.time1, rgr.time2, rgr.time3, rgr.time4)::timestamp::date as the_date
    FROM reading_group_reader rgr
    INNER JOIN (
        SELECT group_id, group_type  ::group_type_name
        FROM (VALUES (31198, 'excerpt')) as T(group_id, group_type)) TT
    ON TT.group_id = rgr.group_id
      AND TT.group_type = rgr.group_type
) rgrt
ON rgrt.the_date = g.dt::date
GROUP BY g.dt
ORDER BY the_date;

但这在应该为 0 的地方输出 1，而不是 0。

然而，原因是因为我现在选择了每个日期，所以，当然，每个日期都有一个。我需要包含一个附加字段（将为 NULL）并对其进行计数。

所以这个查询最终达到了我想要的效果：

SELECT
    g.dt::date as the_date,
    count(rgrt.device_id) as count
FROM generate_series(date '2022-03-06', date '2022-04-06', interval '1 day') as g(dt)
LEFT JOIN (
    SELECT
        LEAST(rgr.time1, rgr.time2, rgr.time3, rgr.time4)::timestamp::date as the_date,
        rgr.device_id
    FROM reading_group_reader rgr
    INNER JOIN (
        SELECT group_id, group_type  ::group_type_name
        FROM (VALUES (31198, 'excerpt')) as T(group_id, group_type)
    ) TT
    ON TT.group_id = rgr.group_id
      AND TT.group_type = rgr.group_type
) rgrt(the_date)
ON rgrt.the_date = g.dt::date
GROUP BY g.dt
ORDER BY g.dt;

当然，在重新阅读已接受的答案时，我最终发现他确实计算了一个不相关的字段，而我在前几个字段中只是错过了它读数。

Answer 1

您需要加入日期列表。例如，这可以使用generate_series() 来完成。

SELECT g.dt::date as the_date,
       count(t.my_timestamp) as count
FROM generate_series(date '2022-03-01', 
                     date '2022-03-31', 
                     interval '1 day') as g(dt)
  LEFT JOIN my_table as t 
        ON t.my_timestamp::date = g.dt::date
       AND ... -- the original WHERE clause goes here!
GROUP BY the_date
ORDER BY the_date;

请注意，原始 WHERE 条件需要进入LEFT JOIN 的连接条件。您不能将它们放入 WHERE 子句中，因为这会将外连接变回内连接（这意味着不会返回缺失的日期）。

Answer 2

施放带有零以文本的整数字段对我解决了类似的问题。