sql - 连续3天查找状态相同的sql表数据的方法

Question

我有一个像下面这样的 SQL 表

Code Name    DayStatus  Date
101  John    A          20-May-2018
101  John    A          19-May-2018
101  John    A          18-May-2018
102  Karl    A          20-May-2018
102  Karl    A          19-May-2018
102  Karl    P          18-May-2018
103  Lorem   P          20-May-2018
103  Lorem   A          19-May-2018
103  Lorem   A          18-May-2018
104  Ipsum   A          20-May-2018
104  Ipsum   P          19-May-2018
104  Ipsum   A          18-May-2018
105  Milton  A          20-May-2018
105  Milton  A          19-May-2018
105  Milton  A          18-May-2018
107  Saleh   A          20-May-2018
107  Saleh   A          19-May-2018
107  Saleh   W          18-May-2018
107  Saleh   A          17-May-2018
108  Virat   A          20-May-2018
108  Virat   H          19-May-2018
108  Virat   A          18-May-2018
108  Virat   A          17-May-2018

这里“A”代表缺席，“P”代表现在，“H”代表假期，“W”代表弱客。

从表格中，我需要找出连续缺勤3天的员工，只有当员工今天的天数状态为A(缺勤)时才可以计算。

virat 和 saleh 也算连续缺席。但如果 P(present) 出现在连续的天数之间，则不算连续缺席。

预期的输出应该是---

Code Name    
101  John          
105  Milton          
107  Saleh             
108  Virat       

Answer 1

尝试从这个 demo 查询

SELECT code, name, count(*) absent_days
FROM (
  SELECT *,
        sum( xx ) over (partition by code order by date ) ss
  FROM (
    SELECT *,
          case when DayStatus = lag(DayStatus) over(partition by code order by Date)
               then 0 else 1
          end as xx
    FROM table1
    WHERE DayStatus not in( 'W','H')
  ) x
) y
WHERE DayStatus = 'A'
GROUP BY code, name, ss
HAVING count(*) >=3
order by code

---

| code |   name |     absent_days |
|------|--------|-----------------|
|  101 |   John |               3 |
|  105 | Milton |               3 |
|  107 |  Saleh |               3 |
|  108 |  Virat |               3 |

---

这个版本给出了天数和每个时期的开始日期

    SELECT code, name, count(*) absent_days, min(date) from_date
    FROM (
      SELECT *,
            sum( xx ) over (partition by code order by date ) ss
      FROM (
        SELECT *,
              case when DayStatus = lag(DayStatus) over(partition by code order by Date)
                   then 0 else 1
              end as xx
        FROM table1
        WHERE DayStatus not in( 'W','H')
      ) x
    ) y
    WHERE DayStatus = 'A'
    GROUP BY code, name, ss
    HAVING count(*) >=3
    order by code

| code |   name | absent_days |            from_date |
|------|--------|-------------|----------------------|
|  101 |   John |           3 | 2018-05-18T00:00:00Z |
|  105 | Milton |           3 | 2018-05-18T00:00:00Z |
|  107 |  Saleh |           3 | 2018-05-17T00:00:00Z |
|  108 |  Virat |           3 | 2018-05-17T00:00:00Z |