连续几天在SQL

一尘不染

连续几天在SQL

sql

我发现连续几天都有很多 QnA。答案仍然太短，以至于我无法理解正在发生的事情。

为了具体起见，我将组成一个模型（或一个表）（如果有区别，我将使用postgresql。）

CREATE TABLE work (
    id integer NOT NULL,
    user_id integer NOT NULL,
    arrived_at timestamp with time zone NOT NULL
);


insert into work(user_id, arrived_at) values(1, '01/03/2011');
insert into work(user_id, arrived_at) values(1, '01/04/2011');

（以最简单的形式）对于给定的用户，我想查找最后一个连续的日期范围。
（我的最终目标）对于给定的用户，我想找到他连续的工作日。
如果他昨天来上班，他（截至今天）仍然有机会连续工作几天。因此，我向他展示了直到昨天为止的连续几天。
但是，如果他昨天错过了比赛，那么根据他今天是否来，他连续的工作日为0或1。

说今天是第八天。

3 * 5 6 7 * = 3 days (5 to 7)
3 * 5 6 7 8 = 4 days (5 to 8)
3 4 5 * 7 * = 1 day (7 to 7)
3 * * * * * = 0 day 
3 * * * * 8 = 1 day (8 to 8)

阅读 125

2021-05-23

共1个答案

一尘不染

这是我使用以下方法解决此问题的方法 CTE

WITH RECURSIVE CTE(attendanceDate)
AS
(
   SELECT * FROM 
   (
      SELECT attendanceDate FROM attendance WHERE attendanceDate = current_date 
      OR attendanceDate = current_date - INTERVAL '1 day' 
      ORDER BY attendanceDate DESC
      LIMIT 1
   ) tab
   UNION ALL

   SELECT a.attendanceDate  FROM attendance a
   INNER JOIN CTE c
   ON a.attendanceDate = c.attendanceDate - INTERVAL '1 day'
) 
SELECT COUNT(*) FROM CTE;

检查SQL
Fiddle上的代码

这是查询的工作方式：

它从attendance表中选择今天的记录。如果没有今天的记录，那么它将选择昨天的记录
然后它会以递归方式添加最短日期前一天的递归记录

如果您想选择最近的连续日期范围，而不考虑用户最近的出勤时间（今天，昨天或前x天），则CTE的初始化部分必须替换为以下代码段：

SELECT MAX(attendanceDate) FROM attendance

[编辑]这是SQL Fiddle上的查询，可解决您的问题＃1：SQLFiddle

2021-05-23