tsql - 如何在时间中断的t-sql中显示时间范围
问题描述
目前我正在监控设备的使用情况。因此,设备在使用的每一分钟都会在我的 sql 服务器上创建一个日志条目。当设备不使用时,时间流会中断。有没有办法让输出显示类似
01/01/2020 10:01 - 01/01/2020 10:14
01/01/2020 13:15 - 01/01/2020 13:26
下面是我的数据现在的样子。
解决方案
您可以使用 TSQL 窗口函数LEAD()来获取下一个时间戳。
DROP TABLE IF EXISTS #dataset ;
-- USE Recursive CTE to create a Table #dataset that we can work with
WITH cte
AS (
SELECT GETUTCDATE() AS [DateTime], 1 AS counters, 'device-1' AS device_name
UNION ALL
SELECT
-- Cause a Break at 10th and 15th row
IIF(cte.counters IN ( 10, 15 ), DATEADD( MINUTE, 10, cte.[DateTime] ), DATEADD( MINUTE, 1, cte.[DateTime] ))
, 1 + cte.counters
, cte.device_name
FROM cte
WHERE cte.counters < 20
)
SELECT *
INTO #dataset
FROM cte ;
-- Now that we have a TestDataSet - Let us Query
SELECT
[DateTime]
, device_name
, LEAD( [DateTime] ) OVER (PARTITION BY device_name ORDER BY [DateTime]) AS next_DateTime
, CASE
-- LEAD( [DateTime] ) OVER (PARTITION BY device_name ORDER BY [DateTime]) is what the next time now
WHEN DATEDIFF( MINUTE, [DateTime], LEAD( [DateTime] ) OVER (PARTITION BY device_name ORDER BY [DateTime])) > 1 /*Tollerance*/ THEN
'break'
ELSE 'ok' END AS break_check_code
FROM #dataset ;
使用 LeadLEAD( DateTime ) OVER (PARTITION BY device_name ORDER BY DateTime)
我们要求下一个DateTime
( device_name
)PARTITION BY
控件,然后我们使用定义顺序ORDER BY
现在我们可以获取DateTime
设备的下一个,我们可以使用一些CASE
并DATEDIFF
检查当前时间戳和下一个时间戳之间的时间差。
这将导致以下结果
|DateTime |device_name|next_DateTime |break_check_code|
|2020-01-30 17:00:19.483|device-1 |2020-01-30 17:01:19.483|ok |
|2020-01-30 17:01:19.483|device-1 |2020-01-30 17:02:19.483|ok |
|2020-01-30 17:02:19.483|device-1 |2020-01-30 17:03:19.483|ok |
|2020-01-30 17:03:19.483|device-1 |2020-01-30 17:04:19.483|ok |
|2020-01-30 17:04:19.483|device-1 |2020-01-30 17:05:19.483|ok |
|2020-01-30 17:05:19.483|device-1 |2020-01-30 17:06:19.483|ok |
|2020-01-30 17:06:19.483|device-1 |2020-01-30 17:07:19.483|ok |
|2020-01-30 17:07:19.483|device-1 |2020-01-30 17:08:19.483|ok |
|2020-01-30 17:08:19.483|device-1 |2020-01-30 17:09:19.483|ok |
|2020-01-30 17:09:19.483|device-1 |2020-01-30 17:19:19.483|break |
|2020-01-30 17:19:19.483|device-1 |2020-01-30 17:20:19.483|ok |
|2020-01-30 17:20:19.483|device-1 |2020-01-30 17:21:19.483|ok |
|2020-01-30 17:21:19.483|device-1 |2020-01-30 17:22:19.483|ok |
|2020-01-30 17:22:19.483|device-1 |2020-01-30 17:23:19.483|ok |
|2020-01-30 17:23:19.483|device-1 |2020-01-30 17:33:19.483|break |
|2020-01-30 17:33:19.483|device-1 |2020-01-30 17:34:19.483|ok |
|2020-01-30 17:34:19.483|device-1 |2020-01-30 17:35:19.483|ok |
|2020-01-30 17:35:19.483|device-1 |2020-01-30 17:36:19.483|ok |
|2020-01-30 17:36:19.483|device-1 |2020-01-30 17:37:19.483|ok |
|2020-01-30 17:37:19.483|device-1 |NULL |ok |
请参阅SQL Fiddle以运行此示例
推荐阅读
- architecture - 使用 AWS Lambda 的应用程序架构
- mongodb - Mongodb - 投影会增加执行时间吗?
- vb.net - msg 选定项目列表框 VB.net
- node.js - mongodb find() in insertOne() via nodejs
- facebook-graph-api - 使用 Facebook API 获取未读通知计数
- c# - 将函数注册到 .NET Core 中的事件时,“此平台不支持操作”
- mercurial - 检测 mercurial 中的新行
- xamarin - 是否有任何理由混淆 Xamarin Forms、Android 应用程序?
- bash - 按名称转到祖先目录
- dart - 有人可以回答我关于 HashMap 的 Dartlang 问题吗?