sql - 列值更改时 SQL 重置 row_number

Question

我一直在尝试解决这个问题，但没有这样做。我需要获取服务器不可用的持续时间。这是以下数据

Date  |  Time  |  Address  |  Status
11-14 |  6:32  |  1.1.1.1  |  Down --- Count Start
11-14 |  6:34  |  1.1.1.1  |  Down
11-14 |  6:54  |  1.1.1.1  |  UP
11-14 |  7:20  |  1.1.1.1  |  Down --- Reset Count to 1
11-14 |  7:25  |  1.1.1.1  |  Down
11-14 |  7:30  |  1.1.1.1  |  Up
11-14 |  7:40  |  1.1.1.1  |  Down --- Reset Count to 1
11-14 |  6:35  |  2.2.2.2  |  Down --- Now this is a different counter cause of different IP

我有这个查询

SELECT [date]
  ,[time]
  ,[address]
  ,[ms]
  ,[bytes]
  ,[ttl]
  ,[Status]
  ,COALESCE(lag([Status]) over(order by [time]),'--') [Row]
  INTO #temp
FROM
(
SELECT [date]
  ,[time]
  ,[address]
  ,[ms]
  ,[bytes]
  ,[ttl]
  ,CASE WHEN [status] = 'Success' THEN 'UP' ELSE 'DOWN' END [Status]
 FROM [ESPS].[dbo].[Ping History] p
 INNER JOIN [SuperDashboard].[dbo].[IP_Mapping] i ON i.[IP] = p.address
 WHERE [Date] = CONVERT(Date,GETDATE())
) a

SELECT [date]
  ,[time]
  ,[address]
  ,[ms]
  ,[bytes]
  ,[ttl]
  ,[Status]
FROM
(
SELECT [date]
  ,[time]
  ,[address]
  ,[ms]
  ,[bytes]
  ,[ttl]
  ,[Status]
  ,CASE WHEN [Status] != [Row] THEN 1 ELSE 0 END [row]
FROM #temp
) a WHERE [row] = 1
DROP TABLE #temp

但这仅适用于只有一个地址，因为当我尝试添加2.2.2.2. 理想的输出是每次遇到停机时获取服务器停机的持续时间。我希望有人可以帮助我或至少为我指出正确的方向。

编辑 1：预期输出应该是

Date  |  Start DownTime  |  End DownTime  | Address
11-14 |      6:32        |     6:54       | 1.1.1.1
11-14 |      7:20        |     7:30       | 1.1.1.1
11-14 |      7:40        |                | 1.1.1.1
11-14 |      6:35        |                | 2.2.2.2

标签： sqlsql-server-2017

Answer 1

使用 lag() 函数

演示

select *,case when stat='Down' and (prevval='Up' or prevval is null) then 1 else 0 end as val from
(
 select *,lag(stat) over(partition by address order by address) as prevval
 from #temp
)A

输出：

address stat    prevval val
1.1.1.1 Down            1
1.1.1.1 Up      Down    0
1.1.1.1 Down    Up      1
1.1.1.1 Down    Down    0
1.1.1.1 Up      Down    0
1.1.1.1 Down    Up      1
1.1.1.1 Down    Down    0
2.2.2.2 Down            1