How can I count continuous time frames

My data looks like this:

我的数据是这样的:

Id| Em_Name|Em_Reg_Date
--------------------------------
1 | John   |2010-03-30 00:00:00  
1 | John   |2010-03-31 00:00:00  
2 | Marc   |2010-10-26 00:00:00  
2 | Marc   |2010-10-27 00:00:00  
2 | Marc   |2010-10-28 00:00:00  
2 | Marc   |2010-10-29 00:00:00  
2 | Marc   |2010-12-16 00:00:00  
2 | Marc   |2010-12-17 00:00:00    
2 | Marc   |2010-12-20 00:00:00  
2 | Marc   |2010-12-21 00:00:00  
2 | Marc   |2010-12-22 00:00:00  
3 | Paul   |2010-02-25 00:00:00  
3 | Paul   |2010-02-26 00:00:00  
3 | Paul   |2010-12-13 00:00:00  
3 | Paul   |2010-12-14 00:00:00  
3 | Paul   |2010-12-15 00:00:00  
--------------------------------

A time frame is a continuous period of time.
e.g. Paul has following two (2) time frames

时间框架是一段连续的时间。保罗有以下两个时间段

 FRAME 1 FROM 2010-02-25 00:00:00  to 2010-02-26 00:00:00  
 FRAME 2 FROM 2010-12-13 00:00:00  to 2010-12-15 00:00:00

So, the result should be like this

结果应该是这样的

1 John   1  
2 Marc   3  
3 Paul   2

The question is: I need to count time frames for each Employee.

问题是:我需要为每个员工计算时间。

The problem here lies in the fact that I need to isolate the continues time frames in order to count them. I've even tried a declare cursor (works but I've to store the data in a temp table) And I want this to be in a "simple" sql statement Using max to find a start date works for only one frame. You can not find the second/third frame with max.

这里的问题在于，我需要隔离连续时间帧以计数它们。我甚至尝试过一个声明游标(工作，但我必须将数据存储在一个临时表中)，我希望这是在一个“简单”的sql语句中，使用max来找到一个开始日期只适用于一个框架。你找不到max的第二/第三帧。

Is there anyone with fresh new ideas?

有谁有新的想法吗?

3 个解决方案

#1

SQL Server 2005+

SQL Server 2005 +

select em_name, COUNT(distinct startdate)
from
(
    select *, startdate = em_reg_date - ROW_NUMBER() over (
        partition by em_name order by em_reg_date) +1
    from tbl
) X
group by Em_Name

Oracle, DB2 also support Row_Number(), but you will need some variation to calculating startdate

Oracle, DB2也支持Row_Number()，但是您需要一些变化来计算startdate。

#2

I'm not sure of the reason for both the ID and em_name fields, so I'll treat it as if the ID is sufficient to use alone.

我不确定ID和em_name字段的原因，因此我将其视为ID足以单独使用。

The logic I'm using is simply this... A group can be represented by the last entry in the group. And the last entry is simply an entry that does not have a matching entry for the following day.

我用的逻辑很简单……一个组可以由组中的最后一个条目表示。最后一个条目是一个没有匹配条目的条目。

Provided that an Index for (ID, Em_Reg_Date) exists, this should be quite fast.

如果存在(ID, Em_Reg_Date)索引，那么这应该会很快。

SELECT
  ID,
  COUNT(*)
FROM
  your_table [source]
WHERE
  NOT EXISTS (
              SELECT
                *
              FROM
                your_table
              WHERE
                Em_Reg_Date = [source].Em_Reg_Date + 1
                AND ID = [source].ID
             )
GROUP BY
  ID

EDIT

编辑

This changes the logic to look "up to the next monday" if the current record is a Friday, Saturday or Sunday.

如果当前记录是周五、周六或周日，那么这就改变了查找“下星期一”的逻辑。

SET DATEFIRST 1   -- This just ensures that Monday is counted as Day 1

SELECT
  ID,
  COUNT(*)
FROM
  your_table [source]
WHERE
  NOT EXISTS (
              SELECT
                *
              FROM
                your_table
              WHERE
                ID = [source].ID
                AND Em_Reg_Date <= [source].Em_Reg_Date + CASE WHEN DATEPART(weekday, [source].Em_Reg_Date) >= 5 THEN 8 - DATEPART(weekday, [source].Em_Reg_Date) ELSE 1 END
                AND Em_Reg_Date >  [source].Em_Reg_Date
             )
GROUP BY
  ID

#3

SELECT Id, Name, COUNT( Id )
FROM (
   SELECT Id, Name
   FROM  `<your_table_name>` 
   GROUP BY Name, MONTH( Em_Reg_Date )
   ) as X
GROUP BY Id

Tested on MySQL 5.0.7

MySQL 5.0.7测试

#1