我正在嘗試撰寫查詢來計算行之間的時間差，但我失敗了。
問題是事件并不總是一個接一個，如果是這種情況，它應該回傳 NULL 值或完全跳過它。
示例表
| 時間 | 計算機名 | 渲染描述 | 事件顯示編號 |
|---|---|---|---|
| 2022-05-19 14:12:58.400 | COMP16 | 事件日志服務已啟動。 | 6005 |
| 2022-05-19 13:52:46.360 | COMP16 | 事件日志服務已停止。 | 6006 |
| 2022-05-19 13:15:47.417 | COMP16 | 事件日志服務已啟動。 | 6005 |
| 2022-05-08 08:26:11.777 | COMP16 | 事件日志服務已啟動。 | 6005 |
| 2022-05-08 07:30:11.030 | COMP16 | 事件日志服務已停止。 | 6006 |
| 2022-05-01 08:22:37.553 | COMP16 | 事件日志服務已啟動。 | 6005 |
| 2022-05-01 07:30:08.057 | COMP16 | 事件日志服務已停止。 | 6006 |
| 2022-04-24 08:20:55.190 | COMP16 | 事件日志服務已啟動。 | 6005 |
| 2022-04-24 07:30:07.143 | COMP16 | 事件日志服務已停止。 | 6006 |
和我想要得到的結果
| 計算機名 | 事件停止 | 活動開始 | 差異分鐘 |
|---|---|---|---|
| COMP16 | 2022-04-24 07:30:07.143 | 2022-04-24 08:20:55.190 | 50 |
| COMP16 | 2022-05-01 07:30:08.057 | 2022-05-01 08:22:37.553 | 52 |
| COMP16 | 2022-05-08 07:30:11.030 | 2022-05-08 08:26:11.777 | 56 |
| COMP16 | 空值 | 2022-05-19 13:15:47.417 | 空值 |
| COMP16 | 2022-05-19 13:52:46.360 | 2022-05-19 14:12:58.400 | 20 |
uj5u.com熱心網友回復：
這將適用于您的示例資料：
with Data as (
select *,
case when RenderedDescription like '%started%' then 'S'
when RenderedDescription like '%stopped%' then 'E' end as Code,
count(case when RenderedDescription like '%started%' then 1 end)
over (partition by ComputerName order by "Time" desc) as Grp
from EventLog
)
select
ComputerName,
max(case when Code = 'E' then "Time" end) as EventStopped,
min(case when Code = 'S' then "Time" end) as EventStarted,
datediff(second,
max(case when Code = 'E' then "Time" end),
min(case when Code = 'S' then "Time" end)
) / 60 as DifferenceMinutes
from Data
group by ComputerName, Grp
order by ComputerName, EventStarted;
如果您可以連續開始和停止，請嘗試以下操作：
with Data as (
select *,
case when RenderedDescription like '%started%' then 'S'
when RenderedDescription like '%stopped%' then 'E' end as Code,
lag(
case when RenderedDescription like '%started%' then 'S'
when RenderedDescription like '%stopped%' then 'E' end
) over (partition by ComputerName order by "Time") as LastCode
from EventLog
), Grouped as (
select *,
count(case when LastCode = 'E' and Code = 'S' then null else 1 end)
over (partition by ComputerName order by "Time") as Grp
from Data
)
select
ComputerName,
max(case when Code = 'E' then "Time" end) as EventStopped,
min(case when Code = 'S' then "Time" end) as EventStarted,
datediff(second,
max(case when Code = 'E' then "Time" end),
min(case when Code = 'S' then "Time" end)
) / 60 as DifferenceMinutes
from Grouped
group by ComputerName, Grp
order by ComputerName, EventStarted;
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d7bd556c49cbca84d025bcd1eedc9771
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/478956.html
標籤：sql服务器