将事务配对到日期范围行中
将事务配对到日期范围行中
提问于 2018-12-19 23:47:26
我有一个结构类似于下面的表，该表显示了何时将员工作为特定角色添加(operation=I)或从帐户中删除(operation=D
Account | Employee | Role | Operation | OperationTimestamp
ABC | 1 | Rep | I | 1/1/2018
DEF | 1 | Mgr | I | 1/1/2018
ABC | 1 | Rep | D | 3/31/2018
ABC | 1 | Rep | I | 7/1/2018
ABC | 1 | Rep | D | 12/31/2018
ABC | 2 | Mgr | I | 1/1/2018
DEF | 2 | Exc | I | 1/1/2018
ABC | 2 | Mgr | D | 3/31/2018
ABC | 2 | Mgr | I | 6/1/2018
ABC | 2 | Mgr | D | 10/31/2018(I =插入，D=删除)
我需要开发一个查询，它将返回该员工在该帐户上的帐户、员工、角色和日期范围，如下所示：
Account | Employee | Role | StartingDate | EndingDate
ABC | 1 | Rep | 1/1/2018 | 3/31/2018
DEF | 1 | Mgr | 1/1/2018 | NULL
ABC | 1 | Rep | 7/1/2018 | 12/31/2018
ABC | 2 | Mgr | 1/1/2018 | 3/31/2018
DEF | 2 | Exc | 1/1/2018 | NULL
ABC | 2 | Mgr | 6/1/2018 | 10/31/2018因此，正如您从结果集中看到的那样，如果一个员工被添加到一个帐户，但没有被删除，那么EndingDate应该为NULL。
我正在努力的地方是，您可以多次在帐户中添加/删除同一员工和/或在多个角色中添加/删除相同的员工。我的直觉告诉我，我需要按account>employee>role>date对事务进行排序，并以某种方式将每2行组合在一起(因为它应该始终是一个I操作，后面跟着一个D操作)，但我不确定如何处理“丢失”的删除(如果它们仍然在一个帐户上)。
回答 3
Stack Overflow用户
发布于 2018-12-19 23:58:52
假设：对于相同的组合(帐户、雇员、角色)，I操作之后永远不会有另一个I；如果有下一行(可能不是针对该组合)，它始终是一个D。
数据：
create table my_table (
Account varchar(3),
Employee int,
role varchar(3),
Operation varchar(1),
OperationTimestamp datetime
);
insert into my_table values
('ABC',1,'Rep','I','20180101')
,('DEF',1,'Mgr','I','20180101')
,('ABC',1,'Rep','D','20180331')
,('ABC',1,'Rep','I','20180701')
,('ABC',1,'Rep','D','20181231')
,('ABC',2,'Mgr','I','20180101')
,('DEF',2,'Exc','I','20180101')
,('ABC',2,'Mgr','D','20180331')
,('ABC',2,'Mgr','I','20180601')
,('ABC',2,'Mgr','D','20181031');如果上述情况属实，那么我将使用以下查询：
with
x as (
select
account, employee, role, operationtimestamp, operation,
lead(operation)
over(partition by account, employee, role
order by account, employee, role, operationtimestamp)
as next_op,
lead(operationtimestamp)
over(partition by account, employee, role
order by account, employee, role, operationtimestamp)
as next_ts
from my_table
),
y as(
select
account, employee, role,
operationtimestamp as startingdate,
next_ts as endingdate
from x
where operation = 'I'
)
select *
from y
order by employee, startingdate结果：
account employee role startingdate endingdate
------- -------- ---- --------------------- ---------------------
ABC 1 Rep 2018-01-01 00:00:00.0 2018-03-31 00:00:00.0
DEF 1 Mgr 2018-01-01 00:00:00.0 <null>
ABC 1 Rep 2018-07-01 00:00:00.0 2018-12-31 00:00:00.0
ABC 2 Mgr 2018-01-01 00:00:00.0 2018-03-31 00:00:00.0
DEF 2 Exc 2018-01-01 00:00:00.0 <null>
ABC 2 Mgr 2018-06-01 00:00:00.0 2018-10-31 00:00:00.0Stack Overflow用户
发布于 2018-12-20 00:01:54
对于row_number和self join，这是相当简单的：
declare @t table(Account varchar(3), Employee int, EmpRole varchar(3), Operation varchar(1), OperationTimestamp datetime);
insert into @t values
('ABC',1,'Rep','I','20180101')
,('DEF',1,'Mgr','I','20180101')
,('ABC',1,'Rep','D','20180331')
,('ABC',1,'Rep','I','20180701')
,('ABC',1,'Rep','D','20181231')
,('ABC',2,'Mgr','I','20180101')
,('DEF',2,'Exc','I','20180101')
,('ABC',2,'Mgr','D','20180331')
,('ABC',2,'Mgr','I','20180601')
,('ABC',2,'Mgr','D','20181031');
with d as
(
select Account
,Employee
,EmpRole
,Operation
,OperationTimestamp
,row_number() over (partition by Account, Employee, EmpRole order by OperationTimestamp) as ord
from @t
)
select s.Account
,s.Employee
,s.EmpRole
,s.OperationTimestamp as OperationTimestampStart
,e.OperationTimestamp as OperationTimestampEnd
from d as s
left join d as e
on s.Account = e.Account
and s.Employee = e.Employee
and s.EmpRole = e.EmpRole
and s.ord = e.ord-1
where s.Operation = 'I';输出
+---------+----------+---------+-------------------------+-----------------------+
| Account | Employee | EmpRole | OperationTimestampStart | OperationTimestampEnd |
+---------+----------+---------+-------------------------+-----------------------+
| ABC | 1 | Rep | 2018-01-01 | 2018-03-31 |
| ABC | 1 | Rep | 2018-07-01 | 2018-12-31 |
| ABC | 2 | Mgr | 2018-01-01 | 2018-03-31 |
| ABC | 2 | Mgr | 2018-06-01 | 2018-10-31 |
| DEF | 1 | Mgr | 2018-01-01 | NULL |
| DEF | 2 | Exc | 2018-01-01 | NULL |
+---------+----------+---------+-------------------------+-----------------------+Stack Overflow用户
发布于 2018-12-20 01:16:03
我认为你只需要一个lead()或者一个累积的min()。我的意思是：
select account, employee, role, OperationTimestamp, EndingDate
from (select t.*,
min(case when operation = 'D' then OperationTimestamp end) over
(partition by account, employee, role
order by OperationTimestamp desc
) as EndingDate
from t
) t
where operation = 'I';页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53854717
复制相关文章
相似问题
腾讯云开发者
