Дарья Смагина's questions

有一个 postgresql 和一个带有事实标识符Fact_id及其创建日期的表，Created_at类型为：

Fact_id我需要计算过去一天、前一天和前两天创建了多少。但在结果表中，仅反映Fact_id在过去 24 小时内创建的值。

我已经受够了嵌套请求：

select fact_id, int_1, int_2, int_3
from (select fact_id,
     COUNT(case when created_at >= (now() - interval '1 day') then 1 end) as int_1,
     COUNT(case when created_at between (now() - interval '1 day') and (now() - interval '2 day') then 1 end) as int_2,
     COUNT(case when created_at between (now() - interval '2 day') and (now() - interval '3 day') then 1 end) as int_3
     from table
     where created_at >= (now() - interval '3 day')
     group by fact_id) as d
where int_1 > 0

结果，我得到一张表格：

是否有可能以某种方式优化或缩短代码以获得相同的结果？ COUNT 条件中的句点几乎是手动设置的，特别令人困惑。