时间戳

首页 > 解决方案 > 在 SQL Server 中为同一天的数据分配组 ID

sql - 在 SQL Server 中为同一天的数据分配组 ID

问题描述

我正在尝试检查观察结果是否来自同一天，如果它们在同一天，则分配相同的组 ID。我的数据看起来像

EmployeeID         StartDateTime       EndDateTime             ShiftType         SameDay
                 
12041     2018-07-20 13:30:00.000   2018-07-20 14:00:00.000    Shift                1
12041     2018-07-20 14:00:00.000   2018-07-20 15:00:00.000    Shift                1
12041     2018-07-20 16:00:00.000   2018-07-20 17:00:00.000    Public               2
12041     2018-07-20 17:00:00.000   2018-07-20 17:06:00.000    Shift                1
12041     2018-07-20 19:00:00.000   2018-07-20 20:00:00.000    Public               2
37821     2018-07-20 09:00:00.000   2018-07-20 10:00:00.000    Shift                3

“SameDay”列是我要创建的组 ID。基本上，如果对于每个员工（employeeID），如果条目是基于开始日期时间和结束日期时间的同一天，“只要它们具有相同的班次类型”。

我尝试使用行号/分区来执行此操作，但它不起作用。

row_number() over (partition by EmployeeID, StartDateTime, EndDateTime,  ShiftType order by EmployeeID, StartDateTime, EndDateTime) as SameDay

有人可以帮忙吗？

标签： sqlsql-server

解决方案

您可以单独处理分组，然后将该数据集与原始行连接起来。我使用了一些公用表表达式(CTE) 来隔离分组。

样本数据

create table observation
(
  EmployeeId int,
  StartDateTime datetime,
  EndDateTime datetime,
  ShiftType nvarchar(10)
);

insert into observation (EmployeeID, StartDateTime, EndDateTime, ShiftType) values
(12041, '2018-07-20 13:30:00.000', '2018-07-20 14:00:00.000', 'Shift' ),
(12041, '2018-07-20 14:00:00.000', '2018-07-20 15:00:00.000', 'Shift' ),
(12041, '2018-07-20 16:00:00.000', '2018-07-20 17:00:00.000', 'Public'),
(12041, '2018-07-20 17:00:00.000', '2018-07-20 17:06:00.000', 'Shift' ),
(12041, '2018-07-20 19:00:00.000', '2018-07-20 20:00:00.000', 'Public'),
(37821, '2018-07-20 09:00:00.000', '2018-07-20 10:00:00.000', 'Shift' );

解决方案

第一个 CTE ( cte_group) 定义分组值。然后第二个 CTE ( cte_groupnum) 为每个组分配一个 ID。对函数使用g.ShiftType descorderingrow_number()以获得与问题中完全相同的编号。

with cte_group as
(
  select o.EmployeeId,
         convert(date, o.StartDateTime) as StartDate,
         convert(date, o.EndDateTime) as EndDate,
         o.ShiftType
  from observation o
  group by o.EmployeeID,
           convert(date, o.StartDateTime),
           convert(date, o.EndDateTime),
           o.ShiftType
),
cte_groupnum as
(
  select g.*,
         row_number() over(order by g.EmployeeId, g.StartDate, g.EndDate, g.ShiftType desc) as GroupId
  from cte_group g
)
select o.*,
       gn.GroupId
from observation o
join cte_groupnum gn
  on  gn.EmployeeId = o.EmployeeId
  and gn.ShiftType = o.ShiftType
  and gn.StartDate = convert(date, o.StartDateTime)
  and gn.EndDate = convert(date, o.EndDateTime);

结果

EmployeeId StartDateTime           EndDateTime             ShiftType GroupId
---------- ----------------------- ----------------------- --------- -------
12041      2018-07-20 13:30:00.000 2018-07-20 14:00:00.000 Shift     1
12041      2018-07-20 14:00:00.000 2018-07-20 15:00:00.000 Shift     1
12041      2018-07-20 16:00:00.000 2018-07-20 17:00:00.000 Public    2
12041      2018-07-20 17:00:00.000 2018-07-20 17:06:00.000 Shift     1
12041      2018-07-20 19:00:00.000 2018-07-20 20:00:00.000 Public    2
37821      2018-07-20 09:00:00.000 2018-07-20 10:00:00.000 Shift     3

小提琴以查看它的实际效果和中间 CTE 结果。

推荐阅读