I have the following table in the postgres database (the table name is table_test):
id dia Data_sensor_Analog
2165 2020-09-20 4585542
2165 2020-09-21 4954566
2165 2020-09-26 255
I would like to count how many consecutive days have the attribute dia
.
For this, I tried to make the following code:
WITH
groups AS (
SELECT
ROW_NUMBER() OVER (ORDER BY dia) AS rn,
dateadd(dia, -ROW_NUMBER() OVER (ORDER BY dia), dia) AS grp,
dia
FROM table_test
)
SELECT
COUNT(*) AS consecutiveDates,
MIN(dia) AS minDate,
MAX(dia) AS maxDate
FROM groups
GROUP BY grp
ORDER BY 1 DESC, 2 DESC
I would like the output to be:
consecutiveDates minDate maxDate
1 2020-09-20 2020-09-21
However, when I run the code, the following error message appears:
ERROR: function dateadd(text, bigint, text) does not exist
LINE 17: dateadd(dia, -ROW_NUMBER() OVER (ORDER BY dia), dia) A
I'm using postgres and found this sample code on the website: https://blog.jooq.org/2015/11/07/how-to-find-the-longest-consecutive-series-of-events-in-sql/
I transformed the dia
attribute to:
ALTER TABLE table_test
ALTER COLUMN dia
TYPE TIMESTAMP WITHOUT TIME ZONE
USING dia::timestamp without time zone;
Considering you have only one entry for a day in your table then try this:
select id, count(*) -1 "count", max(dia), min(dia) from (
select *,
date(dia) - row_number() over (partition by id order by date(dia)) * interval '1 day' "filter"
from table_test
) t1
group by id, filter
having count(*) -1 > 0
In case you have multiple values for same date then try below:
with cte as (
select
*,
date(dia) date_,date(dia) - dense_rank() over ( partition by id order by date(dia)) * interval '1 day' "filter"
from table_test
)
select
id, count(distinct date_) -1 "count" , max(dia),min(dia)
from cte
group by id, filter
having count(distinct date_) -1 >0