我需要编写一个查询,它将过滤掉相同停机事件的多个。这些记录是在同一时间用多个不同的时间窃取器创建的,我不需要这些。此外,如果一个停机事件有多个时间窃取程序,我需要将时间窃取程序设置为'NULL'。
示例表:
<表类>
Id
TimeStealer
开始结束Is_Downtime Downtime_Event tbody><<tr>1 机器1 2022-01-01 01:00:00 2022-01-01 01:01:00 1 故障 2机器2 2022-01-01 01:00:00 2022-01-01 01:01:00 1 故障 3 空 2022-01-01 00:01:00 2022-01-01 00:59:59 0 操作 表类>
似乎这是每个组的前1行,但是当有多行时,添加了使列NULL
的逻辑。您还可以通过使用有窗口的COUNT
来实现这一点,然后在外部SELECT
中使用CASE
表达式,仅在存在1个事件时返回TimeStealer
的值:
WITH CTE AS(
SELECT V.Id,
V.TimeStealer,
V.Start,
V.[End],
V.Is_Downtime,
V.Downtime_Event,
ROW_NUMBER() OVER (PARTITION BY V.Start, V.[End], V.Is_Downtime,V.Downtime_Event ORDER BY ID) AS RN,
COUNT(V.ID) OVER (PARTITION BY V.Start, V.[End], V.Is_Downtime,V.Downtime_Event) AS Events
FROM(VALUES('1','Machine 1',CONVERT(datetime2(0),'2022-01-01 01:00:00'),CONVERT(datetime2(0),'2022-01-01 01:01:00'),'1','Malfunction'),
('2','Machine 2',CONVERT(datetime2(0),'2022-01-01 01:00:00'),CONVERT(datetime2(0),'2022-01-01 01:01:00'),'1','Malfunction'),
('3','NULL',CONVERT(datetime2(0),'2022-01-01 00:01:00'),CONVERT(datetime2(0),'2022-01-01 00:59:59'),'0','Operating'))V(Id,TimeStealer,[Start],[End],Is_Downtime,Downtime_Event))
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ID,
CASE WHEN C.Events = 1 THEN C.TimeStealer END AS TimeStealer,
C.Start,
C.[End],
C.Is_Downtime,
C.Downtime_Event
FROM CTE C
WHERE C.RN = 1;