使用PostgreSQL如何查询包含在created_at字段日期范围内的记录？

Question

在我的PostgreSQL数据库中，我有一个包含以下字段的invitations表：

Invitations: id, created_at, completed_at (timestamp)

我正在编写一个PostgreSQL查询，返回在创建日期的2-7天内完成的记录数。

这是我到目前为止所知道的：

SELECT round(count(i.completed_at <= i.created_at + interval '7 day' and i.completed_at > i.created_at + interval '1 day')::decimal / count(DISTINCT i.id), 2) * 100 AS "CR in D2-D7"
FROM invitations i

select语句未返回正确的值。我做错了什么？

Answer 1

您提供给第一个COUNT的表达式会产生一个布尔值，而不是NULL (除非它的输入是NULL)。但是COUNT对其所有非空输入进行计数，因此无论表达式返回true还是false，计数都会递增。有很多方法可以解决这个问题，一个简单的方法(但可能不是最好的--与您已有的输入差异最小)就是使用nullif在第一个计数中将false转换为NULL。

但即便如此，这是正确的吗？一个计数有DISTINCT，而另一个没有，这似乎很奇怪。

因此，一个更完整的解决方案可能类似于：

SELECT 
  round(
    count(distinct i.id) filter (where i.completed_at <= i.created_at + interval '7 day' and i.completed_at > i.created_at + interval '1 day')::decimal 
    / 
    count(DISTINCT i.id)
  ,2) * 100 AS "CR in D2-D7"
FROM invitations i

Answer 2

只需执行以下操作：

SELECT * from
invitations i
where 
 i.completed_at <= i.created_at + interval '7 day' 
and i.completed_at > i.created_at + interval '1 day' 

Answer 3

下面是另一种方法：

SELECT COUNT(*)
FROM invitations i
WHERE completed_at BETWEEN (created_at + '2 days'::interval) AND (created_at + '7 days'::interval);