我有一个用于记录事件的表。具体有两种类型:ON和OFF。
有时会有重叠的日志条目,因为可以同时记录2个设备。这不是至关重要的,因为最终报告应大致(正确)概述ON-> OFF期间。
下面是一个示例,第3列仅用于说明:不存在。
ActionTaken ID ID_of_next_OFF
Switched ON 1 3
Switched ON 2 6
Switched OFF 3
Switched ON 4 7
Switched ON 5 8
Switched OFF 6
Switched OFF 7
Switched OFF 8
Switched On 9 10
Switched OFF 10
Switched On 11 12
Switched OFF 12
给定前两列,如何计算第三列?
这不起作用:
SELECT actionTaken, Id, LEAD(Id)
OVER (PARTITION BY ActionTaken ORDER BY ID) nextConn
FROM dbo.Events
因为ID_of_Next基于下一个匹配的actionTaken值,而不是下一个替代值。
您的方法正确。所有你需要的是LEFT JOIN
对的'Switched ON'
同一部分'Switched OFF'
上等于行号的一部分。
with Events as (
select 'Switched ON' as ActionTaken, 1 as ID union all -- 3
select 'Switched ON', 2 union all -- 6
select 'Switched OFF', 3 union all
select 'Switched ON', 4 union all -- 7
select 'Switched ON', 5 union all -- 8
select 'Switched OFF', 6 union all
select 'Switched OFF', 7 union all
select 'Switched OFF', 8 union all
select 'Switched On', 9 union all -- 10
select 'Switched OFF', 10 union all
select 'Switched On', 11 union all -- 12
select 'Switched OFF', 12
), E as (
select
*, row_number() over(partition by ActionTaken order by ID) as rn
from Events
)
select
a.ActionTaken, a.ID, b.ID
from E as a
left join E as b
on a.ActionTaken = 'Switched ON' and
b.ActionTaken = 'Switched OFF' and
a.rn = b.rn
order by a.ID, a.ActionTaken;
输出:
+--------------+----+------+
| ActionTaken | ID | ID |
+--------------+----+------+
| Switched ON | 1 | 3 |
| Switched ON | 2 | 6 |
| Switched OFF | 3 | NULL |
| Switched ON | 4 | 7 |
| Switched ON | 5 | 8 |
| Switched OFF | 6 | NULL |
| Switched OFF | 7 | NULL |
| Switched OFF | 8 | NULL |
| Switched On | 9 | 10 |
| Switched OFF | 10 | NULL |
| Switched On | 11 | 12 |
| Switched OFF | 12 | NULL |
+--------------+----+------+
使用SQL Fiddle在线进行测试。
我将接受的答案移至您的答案,因为它易于实现,并且可以在排名中添加更多字段。
我很高兴能帮助您。