连续几天在SQL [英] consecutive days in sql

问题描述

我发现连续几天都有很多stackoverflow QnA。

答案仍然太短，无法理解发生了什么。

I found many stackoverflow QnAs about consecutive days.
Still answers are too short for me to understand what's going on.

为具体起见，我将组成模型（或表）

（如果有区别，我将使用postgresql。）

For concreteness, I'll make up a model (or a table)
(I'm using postgresql if it makes a difference.)

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');

1. （以最简单的形式）对于给定的用户，我想找到最后一个连续的日期范围。

1. (In simplest form) For a given user, I want to find the last-consecutive date range.

（我的最终目标）对于给定的用户，我想找到他的连续工作日。

如果他昨天上班，他（截至今天）仍然有机会连续工作几天。因此，我向他显示了直到昨天为止的连续几天。

但是，如果他昨天错过了比赛，那么根据他是否今天来，他的连续天数可以是0或1。

(My ultimate goal) For a given user, I want to find his consecutive working days.
If he came to work yesterday, he still(as of today) has chance of working consecutive days. So I show him consecutive days upto yesterday.
But if he missed yesterday, his consecutive days is either 0 or 1 depending on whether he came today or not.

今天说是第8天。

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)

推荐答案

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

Here is my solution to this problem using 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提琴

这是查询的工作方式：

1. 它从出勤表中选择今天的记录。如果今天的记录不可用，那么它将选择昨天的记录


2. 然后它会以递归方式添加最短日期前一天的记录

1. It selects today's record from attendance table. If today's record is not available then it selects yesterday's record
2. It then keeps adding recursively record a day before the least date

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

If you want to select latest consecutive date range irrespective of when was user's latest attendance(today, yesterday or x days before), then the initialization part of CTE must be replaced by below snippet:

SELECT MAX(attendanceDate) FROM attendance

$中选择MAX（attendanceDate） b $ b

这是SQL Fiddle上的查询，它可以解决您的问题＃1： SQL小提琴

这篇关于连续几天在SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！