sql - 为什么这个 LEFT JOIN 到日历表会返回 0 个结果？

Question

我有一个日历表 cal 设置，从一个月的第一天到一个月的最后一天，间隔为一个小时，如下所示:

   bucket_start     |     bucket_end      
--------------------+---------------------
2013-09-01 00:00:00 | 2013-09-01 00:59:59
2013-09-01 01:00:00 | 2013-09-01 01:59:59
2013-09-01 02:00:00 | 2013-09-01 02:59:59
. . .
2013-09-30 21:00:00 | 2013-09-30 21:59:59
2013-09-30 22:00:00 | 2013-09-30 22:59:59
2013-09-30 23:00:00 | 2013-09-30 23:59:59

我有另一个表 t 包含这样的数据

    id    |        when         |  fk  |
----------+-----------------------------
181207367 | 2013-09-16 17:02:50 |  211 |
181207368 | 2013-09-16 17:04:50 |  211 |
181207369 | 2013-09-16 17:06:50 |  211 |
181207370 | 2013-09-16 17:08:50 |  211 |
181207371 | 2013-09-16 17:10:50 |  211 |
181207372 | 2013-09-16 17:12:50 |  211 |
181207373 | 2013-09-16 17:14:50 |  211 |
181207374 | 2013-09-16 17:16:50 |  211 |
181207375 | 2013-09-16 17:18:50 |  211 |
181207376 | 2013-09-16 17:20:50 |  211 |
. . .

我想要实现的结果是在 cal 的给定时间内，我在 t 中有多少行，如下所示:

   bucket_start     |     bucket_end      | cnt |
--------------------+----------------------------
2013-09-01 00:00:00 | 2013-09-01 00:59:59 |  5  |
2013-09-01 01:00:00 | 2013-09-01 01:59:59 | 12  |
2013-09-01 02:00:00 | 2013-09-01 02:59:59 |  0  |
. . .
2013-09-30 21:00:00 | 2013-09-30 21:59:59 |  9  |
2013-09-30 22:00:00 | 2013-09-30 22:59:59 |  5  |
2013-09-30 23:00:00 | 2013-09-30 23:59:59 |  0  |

所以我写了下面的查询:

  SELECT cal.bucket_start, cal.bucket_end, COUNT(t.id) cnt
    FROM cal LEFT JOIN t on t.when BETWEEN cal.bucket_start AND cal.bucket_end
   WHERE t.fk = 211
GROUP BY cal.bucket_start, cal.bucket_end;

如果 t 中至少有一个数据行与桶跨度相匹配，这基本上是有效的。但是，如果没有，结果行将被省略，因为我在这里使用 LEFT JOIN ，所以我没有得到。我错过了什么？

Answer 1

This basically works if there is at least one data row in t to match the bucket span. However if there isn't the result row gets omitted, which I don't get since I'm using LEFT JOIN here.

不，这只是工作。关键在于你发表的评论。 . .

In addition I restrict the data that gets fetched from t with a WHERE clause on a foreign key, which I didn't mention. Can this be of any influence?

当然，这就是问题所在。在 SQL 中，WHERE 子句在 之后被评估，dbms 从 FROM 子句中的所有表构造函数(包括连接)构建一个工作表。

您可能通过将现在位于 WHERE 子句中的条件移动到连接条件中来获得所需的结果集。例如，此查询将返回所有 720 行，但 . . .

with cal as (
select 
  generate_series(timestamp '2013-09-01 00:00:00', timestamp '2013-09-30 23:59:59', interval '1 hour') bucket_start,
  generate_series(timestamp '2013-09-01 00:59:59', timestamp '2013-09-30 23:59:59', interval '1 hour') bucket_end
), 
t as (
select 181207367 as id, timestamp '2013-09-16 17:02:50' as when union all
select 181207368, '2013-09-16 17:04:50' union all
select 181207369, '2013-09-16 17:06:50' union all
select 181207370, '2013-09-16 17:08:50' union all
select 181207371, '2013-09-16 17:10:50' union all
select 181207372, '2013-09-16 17:12:50' union all
select 181207373, '2013-09-16 17:14:50' union all
select 181207374, '2013-09-16 17:16:50' union all
select 181207375, '2013-09-16 17:18:50' union all
select 181207376, '2013-09-16 17:20:50'
)
SELECT cal.bucket_start, cal.bucket_end, COUNT(t.id) cnt
FROM cal 
LEFT JOIN t on t.when 
  BETWEEN cal.bucket_start AND cal.bucket_end and t.id < 100
GROUP BY cal.bucket_start, cal.bucket_end
order by cal.bucket_start;

查看 17:00 时段的计数。为零。这是更改连接条件的影响之一。

注意事项

在大多数(如果不是全部)SQL 数据库管理系统中，23:59:59 并不是一个桶的尽头。它们都(？)支持微秒级的时间戳精度，而且我很确定更高的精度是它们的默认值。因此，您的查询将从计数中忽略时间为 23:59:59.6675 的所有行。 PostgreSQL 的默认值为六位数字，如 23:59:59.123456 这样的值。

我认为您最好使用这样的 cal 表(尽管为了经济起见，我将其实现为公用表表达式)和实现半开区间的连接条件。

with cal as (
select 
  generate_series(timestamp '2013-09-01 00:00:00', timestamp '2013-09-30 23:00:00', interval '1 hour') bucket_start,
  generate_series(timestamp '2013-09-01 01:00:00', timestamp '2013-09-30 24:00:00', interval '1 hour') bucket_end
  --                                          Buckets end on the hour    ^^^^^^^^
)
, t as (
select 181207367 as id, timestamp '2013-09-16 17:02:50' as when union all
select 181207368, '2013-09-16 17:04:50' union all
select 181207369, '2013-09-16 17:06:50' union all
select 181207370, '2013-09-16 17:08:50' union all
select 181207371, '2013-09-16 17:10:50' union all
select 181207372, '2013-09-16 17:12:50' union all
select 181207373, '2013-09-16 17:14:50' union all
select 181207374, '2013-09-16 17:16:50' union all
select 181207375, '2013-09-16 17:18:50' union all
select 181207376, '2013-09-16 17:20:50' union all
select 181207377, '2013-09-16 17:59:59.66745'
)
SELECT cal.bucket_start, cal.bucket_end, COUNT(t.id) cnt
FROM cal 
LEFT JOIN t 
  ON t.when >= cal.bucket_start AND t.when < cal.bucket_end
  --       ^^^^                           ^^^
  -- Join condition is a half open interval, closed on the left,
  -- and open on the right.
GROUP BY cal.bucket_start, cal.bucket_end
ORDER BY cal.bucket_start;