How to group data within a time span

Question

I have a group of rows sorted by date as below:

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

DDL & DML for those willing to help:

CREATE TABLE foo ( DateCol date );
INSERT INTO foo ( DateCol )
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' )
);

I am trying to group the rows and add an additional column to mark the group. The criterion is that any two rows should be in one group if the time gap between them is less than 3 days, something like below:

2017-01-01  A
2017-01-01  A
2017-01-02  A
2017-01-03  A
2017-01-04  A
2017-01-17  B
2017-01-18  B
2017-01-18  B
2017-01-18  B
2017-01-19  B
2017-01-19  B
2017-01-26  C
2017-01-27  C
2017-01-27  C

How can this be achieved?

Answer 1

Window Function Method

SELECT date,
  chr(
    65 + CAST(count(reset) OVER (ORDER BY date) AS int)
  ) AS dategrp
FROM (
  SELECT date, CASE WHEN date - lag(date) OVER () > 3 THEN 1 END AS reset
  FROM foo
  ORDER BY date
) AS t

First we generate resets with a window function

SELECT date, CASE WHEN date - lag(date) OVER () > 3 THEN 1 END AS reset
FROM foo
ORDER BY date
    date    | reset 
------------+-------
 2017-01-01 |      
 2017-01-01 |      
 2017-01-02 |      
 2017-01-03 |      
 2017-01-04 |      
 2017-01-17 |     1
 2017-01-18 |      
 2017-01-18 |      
 2017-01-18 |      
 2017-01-19 |      
 2017-01-19 |      
 2017-01-26 |     1
 2017-01-27 |      
 2017-01-27 |      

Then we generate numeric groups

SELECT date, count(reset) OVER (ORDER BY date)
FROM (
  SELECT date, CASE WHEN date - lag(date) OVER () > 3 THEN 1 END AS reset
  FROM foo
  ORDER BY date
) AS t
    date    | count 
------------+-------
 2017-01-01 |     0
 2017-01-01 |     0
 2017-01-02 |     0
 2017-01-03 |     0
 2017-01-04 |     0
 2017-01-17 |     1
 2017-01-18 |     1
 2017-01-18 |     1
 2017-01-18 |     1
 2017-01-19 |     1
 2017-01-19 |     1
 2017-01-26 |     2
 2017-01-27 |     2
 2017-01-27 |     2

Then we use chr(65+) to get the alpha group names you want. Original query at the top..

    date    | dategrp 
------------+---------
 2017-01-01 | A
 2017-01-01 | A
 2017-01-02 | A
 2017-01-03 | A
 2017-01-04 | A
 2017-01-17 | B
 2017-01-18 | B
 2017-01-18 | B
 2017-01-18 | B
 2017-01-19 | B
 2017-01-19 | B
 2017-01-26 | C
 2017-01-27 | C
 2017-01-27 | C
(14 rows)

Answer 2

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).

Answer 3

I suggest breaking the problem down, First give the rows and ordered id and link to the following date, then find the difference, then find when the difference is greater then 3 days with a case and set the row to 1 or 0. Then you should be able to do a cumulative sum for that column to set a group.

CREATE TABLE #groupedDates(
[date] datetime,
[group] nvarchar(max))

INSERT INTO #groupedDates ([date], [group])
SELECT '2017-01-01', ''
UNION ALL
SELECT '2017-01-01', ''
UNION ALL
SELECT '2017-01-02', ''
UNION ALL
SELECT '2017-01-03', ''
UNION ALL
SELECT '2017-01-04', ''
UNION ALL
SELECT '2017-01-17', ''
UNION ALL
SELECT '2017-01-18', ''
UNION ALL
SELECT '2017-01-18', ''
UNION ALL
SELECT '2017-01-18', ''
UNION ALL
SELECT '2017-01-19', ''
UNION ALL
SELECT '2017-01-19', ''
UNION ALL
SELECT '2017-01-26', ''
UNION ALL
SELECT '2017-01-27', ''
UNION ALL
SELECT '2017-01-27', ''

SELECT [id],[startDate],[endDate], CASE WHEN [dif] <=3 THEN 0 ELSE 1 END [changeGroup]
INTO #tempA
FROM (
SELECT [start].[s] AS [id], [start].[date] AS [startDate], ISNULL([end].[date],[start].[date]) AS [endDate], 
ISNULL(DATEDIFF(dd,[start].[date],[end].[date]),0) AS [dif]
FROM (
SELECT [date] , ROW_NUMBER() OVER (ORDER BY [date]) AS [s]
FROM #groupedDates) AS [start]
LEFT OUTER JOIN (
SELECT [date] , ROW_NUMBER() OVER (ORDER BY [date]) - 1 AS [e]
FROM #groupedDates) AS [end]
ON [start].[s] = [end].[e]
) AS temp

SELECT [ta].[id], [ta].[startDate] AS [date], temp.[group]
FROM #tempA AS [ta]
INNER JOIN (
SELECT [t1].[id], SUM([t2].[changeGroup]) as [group]
FROM #tempA AS [t1]
INNER JOIN #tempA AS [t2] ON [t1].[id] >= [t2].[id]
GROUP BY [t1].[id], [t1].[changeGroup]) AS temp
ON [ta].[id] = temp.[id] + 1
ORDER BY [ta].[id]

There would probably be better ways to do this but I have used this approach before