PostgreSQL – Counting 3 Consecutive Unsynced Dates

gaps-and-islandspostgresqlwindow functions

I have a table set out like so:

user_id  the_date    minutes_asleep         
----------------------------
1       2015-01-01   480
1       2015-01-02   0
1       2015-01-03   0
1       2015-01-04   0
1       2015-01-05   321
1       2015-01-06   0
1       2015-01-07   0
1       2015-01-08   0
2       2015-01-01   567
2       2015-01-02   0
2       2015-01-03   285
2       2015-01-04   0
2       2015-01-05   577
2       2015-01-06   0
2       2015-01-07   0
2       2015-01-08   0

I need to find the count of groups of 3 consecutive dates where minutes asleep is 0. So in the example above user_id 1 would have a count of 2, user_id 2 would have a count of 1. The total count would be 3. So the returning result would be:

total_count
-----------
3

In the case that there are 5 consecutive dates, the count should only be 1. If there are 6 consecutive, the count should be 2 etc.

A user will only ever have one record per date.

Test table:

CREATE TABLE fitness_data (
    id serial NOT NULL,
    the_date date,
    minutes_asleep integer,
    user_id integer
);

INSERT INTO fitness_data (the_date, minutes_asleep,user_id) VALUES
('2015-01-01',480,1),('2015-01-02',0,1),('2015-01-03',0,1),('2015-01-04',0,1),('2015-01-05',321,1),('2015-01-06',0,1),('2015-01-07',0,1),('2015-01-08',0,1),('2015-01-01',567,2),('2015-01-02',0,2),('2015-01-03',285,2),('2015-01-04',0,2),('2015-01-05',577,2),('2015-01-06',0,2),('2015-01-07',0,2),('2015-01-08',0,2)

Best Answer

This counts ranges of at least 3 days with minutes_asleep = 0.
A continuous range of 5 days still counts as 1.
A continuous range of 6 days counts as 2. Etc.
Ignoring all other entries where minutes_asleep is different.

SELECT sum(ct) AS total_count
FROM  (
   SELECT (count(*)/3)::int AS ct  -- integer division truncates as desired + see below
   FROM (
      SELECT user_id
           , the_date - (row_number() OVER (PARTITION BY user_id
                                            ORDER BY the_date))::int AS grp
      FROM   fitness_data
      WHERE  minutes_asleep = 0
      ) sub1
   GROUP  BY user_id, grp
   HAVING count(*) >= 3
   ) sub2
UNION ALL  -- see below
SELECT 0
LIMIT 1;

UNION ALL ... is just to return 0 instead of "no row" where no qualifying ranges are found. You could also use COALESCE in another subselect, but I find this more elegant.

Why (count(*)/3)::int? count() returns bigint. sum(bigint) returns numeric. I am pretty positive we don't need to go that far. Casting back to int, so we get a bigint result. Should be faster, too.

Related Solutions

PostgreSQL – How to Find Consecutive Free Numbers Using Window Functions

This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
)
SELECT
  id_set,
  number
FROM counted
WHERE cnt >= 3
;

Here's a SQL Fiddle demo^* link for this query: http://sqlfiddle.com/#!1/a2633/1.

UPDATE

To return only one set, you could add in one more round of ranking:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
),
ranked AS (
  SELECT
    *,
    RANK() OVER (ORDER BY id_set, grp) AS rnk
  FROM counted
  WHERE cnt >= 3
)
SELECT
  id_set,
  number
FROM ranked
WHERE rnk = 1
;

Here's a demo for this one too: http://sqlfiddle.com/#!1/a2633/2.

If you ever need to make it one set per id_set, change the RANK() call like this:

RANK() OVER (PARTITION BY id_set ORDER BY grp) AS rnk

Additionally, you could make the query return the smallest matching set (i.e. first try to return the first set of exactly three consecutive numbers if it exists, otherwise four, five etc.), like this:

RANK() OVER (ORDER BY cnt, id_set, grp) AS rnk

or like this (one per id_set):

RANK() OVER (PARTITION BY id_set ORDER BY cnt, grp) AS rnk

_{* The SQL Fiddle demos linked in this answer use the 9.1.8 instance as the 9.2.1 one doesn't appear to be working at the moment.}

How to Merge Similar Records with Different Validity Dates in SQL Server

If this is a table of back-to-back ranges only, your case can be treated as a classic "gaps and islands" problem, where you just need to isolate islands of consecutive ranges and then "condense" them by taking the minimum [from] and the maximum [to] per island.

There is an established method of solving this using two ROW_NUMBER calls:

WITH islands AS
(
  SELECT
    id,
    data,
    [from],
    [to],
    island = ROW_NUMBER() OVER (PARTITION BY id       ORDER BY [from])
           - ROW_NUMBER() OVER (PARTITION BY id, data ORDER BY [from])
  FROM
    #mergeTest
)
SELECT
  id,
  data,
  [from] = MIN([from]),
  [to]   = MAX([to])
FROM
  islands
GROUP BY
  id,
  data,
  island
;

This query will work in as low version as SQL Server 2005.

Best Answer

Related Solutions

PostgreSQL – How to Find Consecutive Free Numbers Using Window Functions

How to Merge Similar Records with Different Validity Dates in SQL Server

Related Question