postgrePostgres SQL 允许按日期过滤时 COUNT(*) 为零

Question

此查询将获取每个图书馆内的书籍计数。书籍通过书架桌连接到图书馆。

查询在没有日期筛选的情况下工作正常。它向我显示了所有计数，包括相关计数为零的库 ID。

一旦我引入created_date_time过滤，它将不再包含零计数。

我想知道过去 30 天内创建的书籍计数 按图书馆分组，但也显示图书馆的计数为 0

SELECT l.id as "library_id", COUNT(b.id) AS "book_count"
FROM shelf s 
  LEFT JOIN book b ON s.id = b.shelf_id
  LEFT JOIN library l ON l.id = s.library_id
      WHERE b.created_date_time >= current_date - interval '30' day
AND b.created_date_time < current_date
GROUP BY l.id

Answer 1

如果你想

按library.id分组，不要在library上使用LEFT JOIN：这不会带来任何好处。

最简单的方法是将谓词添加到连接条件中，而不是WHERE：

SELECT    l.id as "library_id", COUNT(b.id) AS "book_count"
FROM      library l
LEFT JOIN shelf s ON l.id = s.library_id
LEFT JOIN book b ON s.id = b.shelf_id
AND       b.created_date_time >= current_date - interval '30 days'
AND       b.created_date_time <  current_date
GROUP BY  l.id

您还可以使用条件聚合：

SELECT    l.id as "library_id",
          COUNT(b.id)
            FILTER (WHERE b.created_date_time >= current_date - interval '30 days'
                    AND   b.created_date_time <  current_date)
            AS "book_count"
FROM      library l
LEFT JOIN shelf s ON l.id = s.library_id
LEFT JOIN book b ON s.id = b.shelf_id
GROUP BY  l.id

注意：当没有行时，SUM返回NULL(而不是零，这是COUNT在这些情况下返回的内容(。

Answer 2

正如 Horse 所提到的，为了快速修复，您可以将日期限制从 WHERE 子句移动到 ON 子句：

SELECT l.id as "library_id", COUNT(b.id) AS "book_count"
FROM shelf s 
LEFT JOIN book b
    ON s.id = b.shelf_id
LEFT JOIN library l
    ON l.id = s.library_id AND
       b.created_date_time >= current_date - interval '30' day AND
       b.created_date_time < current_date
GROUP BY l.id

请注意，处理此问题的另一种方法是从 WHERE 子句中删除日期条件，并在统计书籍计数时使用条件聚合：

SELECT l.id as "library_id",
       SUM(CASE WHEN b.created_date_time >= current_date - interval '30' day AND
                     b.created_date_time < current_date
                THEN 1 ELSE 0 END) AS "book_count"
FROM shelf s 
LEFT JOIN book b
    ON s.id = b.shelf_id
LEFT JOIN library l
    ON l.id = s.library_id
GROUP BY l.id