How about using with recursive
test view:
create view v as
select *
from ( values ('foo', 2),
('foo', 3),
('foo', 4),
('foo', 10),
('foo', 11),
('foo', 13),
('bar', 1),
('bar', 2),
('bar', 3)
) as baz ("name", "int");
query:
with recursive t("name", "int") as ( select "name", "int", 1 as span from v
union all
select "name", v."int", t.span+1 as span
from v join t using ("name")
where v."int"=t."int"+1 )
select "name", "start", "start"+span-1 as "end", span
from( select "name", ("int"-span+1) as "start", max(span) as span
from ( select "name", "int", max(span) as span
from t
group by "name", "int" ) z
group by "name", ("int"-span+1) ) z;
result:
name | start | end | span
------+-------+-----+------
foo | 2 | 4 | 3
foo | 13 | 13 | 1
bar | 1 | 3 | 3
foo | 10 | 11 | 2
(4 rows)
I'd be interested to know how that performs on your billion row table.
select industry_id
, sum(case when current_date <= date then clicks end) as today
, sum(case when current_date-1 <= date and
date < current_date then clicks end) as yesterday
, sum(case when current_date-4 <= date and
date < current_date-1 then clicks end) as last3days
from phone_clicks
group by
industry_id
See it in your SQLFiddle.
Best Answer
Assuming you want to apply a condition on a column in the table you can use:
date_trunc('month', current_timestamp) - interval '1 month'
gives you the start of the previous month. In March this would be 2021-02-01date_trunc('month', current_timestamp)
gives you the start of "this month" so in March this would be2021-03-1
as the comparison for the upper limit is done using<
it will include everything on the last day of February (including23:59:59.9999999
which your desired condition would not include).