0

I have a table containing range table like this.

+---------+---------+
| user_id | view_dt |
+---------+---------+
| A       |     1/1 |
+---------+---------+
| A       |    1/10 |
+---------+---------+
| A       |    1/14 |
+---------+---------+
| A       |    1/22 |
+---------+---------+
| A       |    1/23 |
+---------+---------+
| A       |    1/30 |
+---------+---------+

I want this data to be grouped based on a 14 day interval. That is, the groups would be:

GROUP 1: 1/1, 1/10, 1/14

GROUP 2: 1/22, 1/23, 1/30

Basically I want to count the orders by interval. So Output should be:

user_id: A          count of series = 2

Note that I the 1/30 date should belong to GROUP 2 since 1/30 should be compared to the first date of GROUP 2 (1/22) instead of (1/1).

I think it can be achieved by creating a recursive query because my grouping condition is 14 days from the first date of the group. Just curious if there's an alternative solution to the recursive CTE method?

1
  • Please always disclose your version of Postgres and a basic table definition - a CREATE TABLE statement showing data types and constraints. Is this for a single user_id or is the sample misleading? Commented Apr 7, 2022 at 0:05

2 Answers 2

2
WITH RECURSIVE 
cte1 AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt) rn,
           COUNT(*) OVER (PARTITION BY id ORDER BY dt 
                          RANGE BETWEEN CURRENT ROW AND INTERVAL '15 day' FOLLOWING) cnt
    FROM test
    ORDER BY 1,2
),
cte2 AS (
    SELECT *
    FROM cte1 
    WHERE rn = 1
    UNION ALL
    SELECT cte1.*
    FROM cte1
    JOIN cte2 ON cte1.id = cte2.id
             AND cte1.rn = cte2.rn + cte2.cnt
)
SELECT id, COUNT(*)
FROM cte2
GROUP BY id;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=874ecc698183349114594e6f01ece3fd

1
  • 1
    I like the clever trick to count rows within time range, and skip ahead in the recursive part. Should be AND INTERVAL '14 day' FOLLOWING, though. Commented Apr 7, 2022 at 0:19
1

This is one of the rare cases where a procedural solution might be faster because it can make do with a single scan over the table:

CREATE OR REPLACE FUNCTION island_count(_days int = 14)
  RETURNS TABLE (user_id text, islands int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   this_id text;  -- using user_id for "last_id" directly
   this_dt date;
   last_dt date;
BEGIN   
   FOR this_id, this_dt IN
      SELECT t.user_id, t.view_dt
      FROM   tbl t
      ORDER  BY 1, 2
   LOOP
      IF this_id = user_id THEN
         IF this_dt > last_dt + _days THEN
            islands := islands + 1;
            last_dt := this_dt;  -- restart frame
            -- RAISE NOTICE 'New island: user: % date: %', this_id, this_dt;  -- to debug
         END IF;
      ELSE
         IF user_id IS NULL THEN
            -- nothing to return yet
         ELSE
            RETURN NEXT;
         END IF;
         user_id := this_id;
         islands := 1;           -- restart count
         last_dt := this_dt;     -- restart frame
      END IF;
      -- else do nothing
   END LOOP;

   IF FOUND THEN                 -- if any rows at all ...
      RETURN NEXT;               -- ... terminate final loop
   END IF;
END
$func$;

Call:

SELECT * FROM island_count();

db<>fiddle here

An index over (user_id, t.view_dt) will help performance a lot for big tables.

If you don't want to persist the function in the db, consider a "temporary function". See:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.