sql - 在连续的日期流中查找每个第 n 个日期

Question

我想在给定日期范围内为每个用户插入到我的表中的连续日期流中每 4 天查找/标记

CREATE TABLE mytable (
  id INTEGER,
  myuser INTEGER,            
  day DATE NOT NULL,
  PRIMARY KEY  (id)      
);

问题是，每个用户只有连续 3 天有效，之后必须有一天的“休息”

 id  | myuser |    day     |
-----+--------+------------+
  0  |    200 | 2012-01-12 | }
  1  |    200 | 2012-01-13 | }--> 3 continuous days
  2  |    200 | 2012-01-14 | }
  3  |    200 | 2012-01-15 | <-- not ok, user 200 should get warned and delete this
  4  |    200 | 2012-01-16 | }
  5  |    200 | 2012-01-17 | }--> 3 continuous days
  6  |    200 | 2012-01-18 | }
  7  |    200 | 2012-01-19 | <-- not ok, user 200 should get warned and delete this
  8  |    201 | 2012-01-12 | }
  9  |    201 | 2012-01-13 | }--> 3 continuous days
  10 |    201 | 2012-01-14 | }
  11 |    201 | 2012-01-16 | <-- ok, there is a one day gap here
  12 |    201 | 2012-01-17 | 

主要目标是查看给定的日期范围(通常是一个月)并确定不允许的日期。此外，我还必须注意正确处理重叠日期，例如，如果我查看从 2012-02-01 到 2012-02-29 的日期范围，则 2012-02-01 可能是“休息”日，如果2012-01-29 到 2012-01-31 存在于同一用户的该表中。

Answer 1

我无法访问 PostgreSQL，但希望它能工作...

WITH
  grouped_data AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY myuser ORDER BY day) - (day - start_date) AS user_group_id,
    myuser,
    day
  FROM
    myTable
  WHERE
        day >= start_date - 3
    AND day <= end_date
)
,
  sequenced_data AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY myuser, user_group_id ORDER BY day) AS sequence_id,
    myuser,
    day
  FROM
    grouped_data
)
SELECT
  myuser,
  day,
  CASE WHEN sequence_id % 4 = 0 THEN 1 ELSE 0 END as should_be_a_break_day
FROM
  sequenced_data
WHERE
  day >= start_date

抱歉，我没有解释工作原理，我不得不参加一个 session :)

start_date = '2012-01-14' 的示例...

 id | myuser |    day     | ROW_NUMBER() | day - start_date | user_group_id
----+--------+------------+--------------+------------------+---------------
  0 |    200 | 2012-01-12 |     1        |        -2        |  1 - -2 = 3
  1 |    200 | 2012-01-13 |     2        |        -1        |  2 - -1 = 3
  2 |    200 | 2012-01-14 |     3        |         0        |  3 -  0 = 3
  3 |    200 | 2012-01-15 |     4        |         1        |  4 -  1 = 3
  4 |    200 | 2012-01-16 |     5        |         2        |  5 -  2 = 3
----+--------+------------+--------------+------------------+---------------
  5 |    201 | 2012-01-12 |     1        |        -2        |  1 - -2 = 3
  6 |    201 | 2012-01-13 |     2        |        -1        |  2 - -1 = 3
  7 |    201 | 2012-01-14 |     3        |         0        |  3 - -1 = 3
  8 |    201 | 2012-01-16 |     4        |         2        |  4 -  2 = 2

任何连续的日期都将具有相同的 user_group_id。天数中的每个“间隙”都会使 user_group_id 减少 1(请参阅第 8 行，如果记录是第 17 天，则有 2 天的间隙，id 应该是 1)。

一旦您有了 group_id，就可以轻松地使用 row_number() 来说明它是序列中的哪一天。最多 3 天与“每 4 天应该是一个间隙”相同，“x % 4 = 0”标识每 4 天。