试图获取id在不同时间段中显示的次数-Postgresql

Question

所以我想得到一个id在不同时间段中显示的次数。这是模式：

CREATE TABLE phone_clicks (
id integer NOT NULL,
date date NOT NULL,
industry_id integer NOT NULL,
clicks integer DEFAULT 0 NOT NULL
);
insert into phone_clicks(id, date, industry_id)
values
(1, '2021-12-03', 1),
(2, '2021-12-03', 2),
(3, '2021-12-02', 3),
(4, '2021-12-01', 1),
(5, '2021-12-01', 3),
(6, '2021-12-01', 4),
(7, '2021-12-02', 1),
(8, '2021-12-02', 2);

这是我现在的位置，但所有这些都是为了获得每个id的完整计数。

select  industry_id
,       count(case when current_date <= date then 1 else 0 end) as today 
,       count(case when current_date-1 <= date and
date < current_date then 1 else 0 end) as yesterday
,       count(case when current_date-4 <= date and 
date < current_date-1 then 1 else 0 end) as last3days
from    phone_clicks
group by
industry_id

这给我回了这个：

industry_id today   yesterday   last3days
4             1        1            1
1             3        3            3
3             2        2            2
2             2        2            2

这只是id每次在表中显示的次数。我想要id 4今天、昨天和过去3天出现的次数。今天和昨天的应为0

Answer 1

您可以在windows函数中使用filter (where ...)模式作为使用条件，这是一种常见且更好的case模式

演示

select
industry_id,
count(*) filter (where current_date <= date) as today,
count(*) filter (where current_date-1 <= date and date < current_date) as yesterday,
count(*) filter (where current_date-4 <= date and date < current_date-1) as last3days
from
phone_clicks
group by
industry_id;

Answer 2

当您使用COUNT时，它会计算任何非NULL的值
0不为NULL，因此会对其进行计数。

如果删除CASE WHEN中的ELSE，则只计算成功的条件。

select  industry_id
, count(case when t.date = current_date
then id end) as today 
, count(case when t.date = current_date-1
then id end) as yesterday
, count(case when t.date between current_date-4 
and current_date-2
then id end) as last3days
from    phone_clicks t
group by
industry_id