This can be accomplished by means of a RECURSIVE Common Table Expression, and some preprocessing with the lag window function
.
First, let's assume this is our data:
CREATE TABLE ds
(
d date NOT NULL
) ;
INSERT INTO
ds(d)
VALUES
('2017-01-01'),
('2017-01-01'),
('2017-01-02'),
('2017-01-03'),
('2017-01-04'),
('2017-01-17'),
('2017-01-18'),
('2017-01-18'),
('2017-01-18'),
('2017-01-19'),
('2017-01-19'),
('2017-01-26'),
('2017-01-27'),
('2017-01-27') ;
With this data, we can use the lag
window function to "look into the previous row", and decide if is less than 3 days away. [In your spec. you talk about the next date, but your example groups by the previous date].
SELECT
d,
coalesce(d - lag(d) over(order by d) >= 3, true) AS start_new_group,
row_number() over (order by d) AS rn
FROM
ds
ORDER BY
d ;
This is the (intermediate) result we would get at this point:
| d | start_new_group | rn |
|------------|-----------------|----|
| 2017-01-01 | true | 1 |
| 2017-01-01 | false | 2 |
| 2017-01-02 | false | 3 |
| 2017-01-03 | false | 4 |
| 2017-01-04 | false | 5 |
| 2017-01-17 | true | 6 |
| 2017-01-18 | false | 7 |
| 2017-01-18 | false | 8 |
| 2017-01-18 | false | 9 |
| 2017-01-19 | false | 10 |
| 2017-01-19 | false | 11 |
| 2017-01-26 | true | 12 |
| 2017-01-27 | false | 13 |
| 2017-01-27 | false | 14 |
Now that we know when to start a next group of dates, we can use a RECURSIVE CTE
, that also includes the previous query (that we call d_start
)
WITH RECURSIVE
d_start AS
(SELECT
d,
-- Here is where we decide when to start a new group
coalesce(d - lag(d) over(order by d) >= 3, true) AS start_new_group,
row_number() over (order by d) AS rn -- So that we can order by and join
FROM
ds
),
d_group AS
(
SELECT
d, rn,
0 AS group_nr
FROM
d_start
WHERE
rn = 1
UNION ALL
SELECT
d_start.d, rn+1 AS rn,
CASE WHEN d_start.start_new_group
THEN group_nr+1
ELSE group_nr
END AS group_nr
FROM
d_group -- We recurse at this point
JOIN d_start USING(rn) -- and join the info of d_start
)
SELECT
d, group_nr
FROM
d_group
WHERE
rn > 1 -- We need to ignore the first row, because we have it twice
ORDER BY
rn ;
The result you get is:
| d | group_nr |
|------------|----------|
| 2017-01-01 | 1 |
| 2017-01-01 | 1 |
| 2017-01-02 | 1 |
| 2017-01-03 | 1 |
| 2017-01-04 | 1 |
| 2017-01-17 | 2 |
| 2017-01-18 | 2 |
| 2017-01-18 | 2 |
| 2017-01-18 | 2 |
| 2017-01-19 | 2 |
| 2017-01-19 | 2 |
| 2017-01-26 | 3 |
| 2017-01-27 | 3 |
| 2017-01-27 | 3 |
I've used 1, 2, 3 as group numbers (because you can have as many as you want, which is not the case for single letters). Converting from numbers to letters shouldn't be difficult.
You can check this query at SQLFiddle. It has been tested with PostgreSQL. With very few modifications (I guess just taking out the RECURSIVE
word), it will also work with MS SQL Server and Oracle (at least).