PostgreSQL – How to Find Members with Consecutive Entries

datepostgresqlpostgresql-8.2

I need to find all members in the database with data on 7 or more consecutive days. The table is set up as member_id and data_date. There are duplicate member_ids for each day that there is data.

I found some answers using MySQL that involved datediff or dateadd, but I am not sure how to do this in PostgreSQL 8.2.15. Below is an example of what the table looks like, but with many more rows.

+------------+------------+
| member_id  |  data_date |
+------------+------------+
|    0000001 | 2018-04-10 |
|    0000005 | 2018-04-16 |
|    0000001 | 2018-04-11 |
|    0000002 | 2018-04-12 |
|    0000003 | 2018-04-13 |
|    0000004 | 2018-04-12 |
|    0000005 | 2018-04-15 |
|    0000003 | 2018-04-19 |
|    0000002 | 2018-04-17 |
|    0000001 | 2018-04-18 |
|    0000005 | 2018-04-10 |
|    0000002 | 2018-04-18 |
|    0000001 | 2018-04-08 |
|    0000002 | 2018-04-03 |
|    0000003 | 2018-04-02 |
|    0000004 | 2018-04-14 |
|    0000005 | 2018-04-15 |
|    0000003 | 2018-04-16 |
|    0000002 | 2018-04-19 |
|    0000001 | 2018-04-14 |
+------------+------------+

(member_id, data_date) is defined UNIQUE.

Best Answer

This simple query should work in your outdated version:

SELECT DISTINCT member_id
FROM   tbl t1
JOIN   tbl t2 USING (member_id)
JOIN   tbl t3 USING (member_id)
JOIN   tbl t4 USING (member_id)
JOIN   tbl t5 USING (member_id)
JOIN   tbl t6 USING (member_id)
JOIN   tbl t7 USING (member_id)
WHERE  t2.data_date = t1.data_date + 1
AND    t3.data_date = t1.data_date + 2
AND    t4.data_date = t1.data_date + 3
AND    t5.data_date = t1.data_date + 4
AND    t6.data_date = t1.data_date + 5
AND    t7.data_date = t1.data_date + 6;

In Postgres, you can just add integer to a date to get the next day.

And probably fast, too - as long as you don't have much longer streaks of entries producing many dupes in the first step.

Or:

SELECT DISTINCT member_id
FROM   tbl t1
JOIN   tbl t2 USING (member_id)
WHERE  t2.data_date BETWEEN t1.data_date + 1
                        AND t1.data_date + 6
GROUP  BY t1.member_id, t1.date
HAVING count(*) = 6;

Update to a current version of Postgres at the earliest opportunity.

Related Solutions

PostgreSQL 9.3 – Performance Issue: Counting table entries slower with jdbc postgres driver

There are too many different topics in one question, and even after many updates not all is clear. I'll just pick the elephant in th room and ignore the rest:

Query 1

->  Index Scan using r_pkey_index on relationships r (cost=0.43..57.53 rows=13 width=0)
                                                     (actual rows=1 loops=1)
         Index Cond: (id = 947367::bigint)
         Filter: ((date >= '2010-02-09'::date) AND (date <= '2010-11-24'::date))

Query 2

Filter: ((date >= '2010-02-09'::date) AND (date <= '2010-11-24'::date))
   Rows Removed by Filter: 44104
      ->  Bitmap Index Scan on e_pkey_index (cost=0.00..4652.34 rows=251720 width=0)
                                            (actual rows=263818 loops=1)
          Index Cond: (id = 879::bigint)

Obviously, a single row matches your criteria in the first query, while there are more than 200.000 in the second. Obviously, the first can be much faster.
Leaving aside that we are comparing two queries on two different tables.

How to Get Dates That Wrap Around New Year from Non-Datetime Columns in MySQL

You can use MAKEDATE(), it makes the day_of_year to a date.

Test:

mysql> SELECT * FROM cli_table;
+-------+--------------+-------------+-------+
| month | day_of_month | day_of_year | value |
+-------+--------------+-------------+-------+
|     1 |            1 |           1 | 23.00 |
|     1 |            2 |           2 | 22.95 |
|    12 |           31 |         365 | 23.12 |
+-------+--------------+-------------+-------+
3 rows in set (0.00 sec)

mysql> SELECT 
    -> MAKEDATE(YEAR(CURDATE()),day_of_year) AS Date,
    ->     value AS DayValue
    -> FROM test.cli_table;
+------------+----------+
| Date       | DayValue |
+------------+----------+
| 2015-01-01 |    23.00 |
| 2015-01-02 |    22.95 |
| 2015-12-31 |    23.12 |
+------------+----------+
3 rows in set (0.00 sec)

mysql>

You can try it in this Fiddle.

EDIT:

According of what you want, you can make it with pure SQL:

mysql> SELECT * FROM test.cli_table;
+-------+--------------+-------------+-------+
| month | day_of_month | day_of_year | value |
+-------+--------------+-------------+-------+
|     1 |            1 |           1 |  5.00 |
|     1 |            2 |           2 |  6.00 |
|     1 |            3 |           3 |  7.00 |
|    12 |           25 |         359 | 23.00 |
|    12 |           26 |         360 | 22.95 |
|    12 |           27 |         361 |  1.00 |
|    12 |           28 |         362 |  2.00 |
|    12 |           29 |         363 |  3.00 |
|    12 |           30 |         364 |  4.00 |
|    12 |           31 |         365 | 23.12 |
+-------+--------------+-------------+-------+
10 rows in set (0.00 sec)

mysql> SELECT 
    -> v.selected_date,
    -> v.dayyear,
    -> c.value AS DayValue,
    -> c.day_of_year 
    -> FROM test.cli_table AS c
    -> JOIN (SELECT 
    -> adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date,
    -> DAYOFYEAR(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i)) AS dayyear
    -> FROM 
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) AS v
    -> ON (v.selected_date between '2015-12-25' AND '2016-01-03'
    -> AND v.dayyear=c.day_of_year);
+---------------+---------+----------+-------------+
| selected_date | dayyear | DayValue | day_of_year |
+---------------+---------+----------+-------------+
| 2015-12-25    |     359 |    23.00 |         359 |
| 2015-12-26    |     360 |    22.95 |         360 |
| 2015-12-27    |     361 |     1.00 |         361 |
| 2015-12-28    |     362 |     2.00 |         362 |
| 2015-12-29    |     363 |     3.00 |         363 |
| 2015-12-30    |     364 |     4.00 |         364 |
| 2015-12-31    |     365 |    23.12 |         365 |
| 2016-01-01    |       1 |     5.00 |           1 |
| 2016-01-02    |       2 |     6.00 |           2 |
| 2016-01-03    |       3 |     7.00 |           3 |
+---------------+---------+----------+-------------+
10 rows in set (0.00 sec)

mysql>

Query Meaning:

SELECT 
    v.selected_date,
    v.dayyear,
    c.value AS DayValue,
    c.day_of_year 
FROM test.cli_table AS c
--- > This JOIN is generating all the dates between the selected_dates ('2015-12-25' AND '2016-01-03')
JOIN (SELECT 
        adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date,
        DAYOFYEAR(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i)) AS dayyear
      FROM 
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) AS v
--- > Here I'm matching every dayyear of the generated dates with the cli_table.day_of_year.
ON (v.selected_date between '2015-12-25' AND '2016-01-03'
    AND v.dayyear=c.day_of_year);