如何计算SQL中的最长连胜? [英] How to calculate longest streak in SQL?

问题描述

我有

  TABLE EMPLOYEE - ID,DATE,IsPresent

我想计算员工在职的最长连续记录.对于他没有来的日子，Present 位将是假的..所以我想计算他连续上班的最长天数..我有日期列字段是唯一的...所以我尝试了这种方式 -

I want to calculate longest streak for a employee presence.The Present bit will be false for days he didnt come..So I want to calculate the longest number of days he came to office for consecutive dates..I have the Date column field is unique...So I tried this way -

Select Id,Count(*) from Employee where IsPresent=1

但是上面的方法不起作用......谁能指导我如何计算连续性?..我相信人们已经遇到过这个......我尝试在线搜索但......不明白它好吧...请帮帮我...

But the above doesnt work...Can anyone guide me towards how I can calculate streak for this?....I am sure people have come across this...I tried searching online but...didnt understand it well...please help me out..

推荐答案

groupby 丢失.

groupby is missing.

选择整个办公室的总人天数(每个人)出勤.

To select total man-days (for everyone) attendance of the whole office.

Select Id,Count(*) from Employee where IsPresent=1

选择每位员工的人日出勤率.

To select man-days attendance per employee.

Select Id,Count(*)
from Employee
where IsPresent=1
group by id;

但这仍然不好，因为它计算出勤的总天数，而不是连续出勤的时间.

But that is still not good because it counts the total days of attendance and NOT the length of continuous attendance.

您需要做的是构造一个带有另一个日期列 date2 的临时表.date2 设置为今天.该表是员工缺勤的所有天数的列表.

What you need to do is construct a temp table with another date column date2. date2 is set to today. The table is the list of all days an employee is absent.

create tmpdb.absentdates as
Select id, date, today as date2
from EMPLOYEE
where IsPresent=0
order by id, date;

所以诀窍是计算两个缺席日之间的日期差，以找出连续存在的日子的长度.现在，用每个员工的下一个缺席日期填写 date2.每个员工的最新记录不会被更新，而是保留为今天的值，因为数据库中没有比今天大的记录.

So the trick is to calculate the date difference between two absent days to find the length of continuously present days. Now, fill in date2 with the next absent date per employee. The most recent record per employee will not be updated but left with value of today because there is no record with greater date than today in the database.

update tmpdb.absentdates
set date2 = 
  select min(a2.date)
  from
   tmpdb.absentdates a1,
   tmpdb.absentdates a2
  where a1.id = a2.id
    and a1.date < a2.date

上述查询通过对自身执行连接来更新自身，可能会导致死锁查询，因此最好创建临时表的两个副本.

The above query updates itself by performing a join on itself and may cause deadlock query so it is better to create two copies of the temp table.

create tmpdb.absentdatesX as
Select id, date
from EMPLOYEE
where IsPresent=0
order by id, date;

create tmpdb.absentdates as
select *, today as date2
from tmpdb.absentdatesX;

您需要插入招聘日期，假设数据库中每位员工的最早日期是招聘日期.

You need to insert the hiring date, presuming the earliest date per employee in the database is the hiring date.

insert into tmpdb.absentdates a
select a.id, min(e.date), today
from EMPLOYEE e
where a.id = e.id

现在用下一个较晚的缺席日期更新 date2，以便能够执行 date2 - date.

Now update date2 with the next later absent date to be able to perform date2 - date.

update tmpdb.absentdates
set date2 = 
  select min(x.date)
  from
   tmpdb.absentdates a,
   tmpdb.absentdatesX x
  where a.id = x.id
    and a.date < x.date

这将列出 emp 持续存在的天数:

This will list the length of days an emp is continuously present:

select id, datediff(date2, date) as continuousPresence
from tmpdb.absentdates
group by id, continuousPresence
order by id, continuousPresence

但你只想保持最长的连胜纪录:

But you only want to longest streak:

select id, max(datediff(date2, date) as continuousPresence)
from tmpdb.absentdates
group by id
order by id

不过上面还是有问题，因为datediff没有考虑假期和周末.

However, the above is still problematic because datediff does not take into account holidays and weekends.

因此我们将记录数作为合法工作日.

So we depend on the count of records as the legitimate working days.

create tmpdb.absentCount as
Select a.id, a.date, a.date2, count(*) as continuousPresence
from EMPLOYEE e, tmpdb.absentdates a
where e.id = a.id
  and e.date >= a.date
  and e.date < a.date2
group by a.id, a.date
order by a.id, a.date;

请记住，每次使用像 count、ave 这样的聚合器时您需要对所选项目列表进行分组，因为您必须按常识进行聚合.

Remember, every time you use an aggregator like count, ave yo need to groupby the selected item list because it is common sense that you have to aggregate by them.

现在选择最大连胜

select id, max(continuousPresence)
from tmpdb.absentCount
group by id

要列出连续上垒的日期:

To list the dates of streak:

select id, date, date2, continuousPresence
from tmpdb.absentCount
group by id
having continuousPresence = max(continuousPresence);

上面可能有一些错误(sql server tsql)，但这是大体思路.

There may be some mistakes (sql server tsql) above but this is the general idea.

这篇关于如何计算SQL中的最长连胜?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！