在SQLServer中查找值不增加超過1分鐘的開始和結束時間的記錄-有解無憂

我在sql server上遇到了一個問題，如果值沒有增加超過1分鐘，我需要找到開始時間，而結束時間是值增加并且我的資料如下所示

Name  Timestamp     Value
--------------------------
M1  2022-05-04T00:00:00 580
M1  2022-05-04T00:01:00 581
M1  2022-05-04T00:02:00 582
M1  2022-05-04T00:03:00 583
M1  2022-05-04T00:04:00 584
M1  2022-05-04T00:05:00 584
M1  2022-05-04T00:06:00 584
M1  2022-05-04T00:07:00 584
M1  2022-05-04T00:08:00 585
M1  2022-05-04T00:09:00 585
M1  2022-05-04T00:10:00 586
M1  2022-05-04T00:11:00 586
M1  2022-05-04T00:12:00 586
M1  2022-05-04T00:13:00 587
M1  2022-05-04T00:14:00 588

結果應該看起來像

Start time             End time         Value
---------------------------------------------
2022-05-04T00:04:00 2022-05-04T00:07:00 584
2022-05-04T00:10:00 2022-05-04T00:12:00 586

我嘗試了如下查詢，但它不起作用


    SELECT *
    FROM (SELECT *, lag(a.Value) OVER (partition by a.Name ORDER by timestamp) pr
    FROM table a
    ORDER BY timestamp) a
    WHERE a.Value = a.pr

請幫忙！

uj5u.com熱心網友回復：

不知道你所說的混淆是什么意思，對于mysql

DROP TABLE IF EXISTS T;
CREATE table T(
   Name      VARCHAR(26) NOT NULL 
  ,Timestamp VARCHAR(19)
  ,Value     INTEGER 
);
INSERT INTO T
(Name,Timestamp,Value) 
VALUES 
('M1','2022-05-04T00:00:00',580),
('M1','2022-05-04T00:01:00',581),
('M1','2022-05-04T00:02:00',582),
('M1','2022-05-04T00:03:00',583),
('M1','2022-05-04T00:04:00',584),
('M1','2022-05-04T00:05:00',584),
('M1','2022-05-04T00:06:00',584),
('M1','2022-05-04T00:07:00',584), 
('M1','2022-05-04T00:08:00',585),
('M1','2022-05-04T00:09:00',585), 
('M1','2022-05-04T00:10:00',586), 
('M1','2022-05-04T00:11:00',586),
('M1','2022-05-04T00:12:00',586),
('M1','2022-05-04T00:13:00',587),
('M1','2022-05-04T00:14:00',588);

SELECT NAME,MIN(TIMESTAMP) MINTS,MAX(TIMESTAMP) MAXTS,value
FROM T
GROUP BY NAME,VALUE HAVING TIMESTAMPDIFF(MINUTE,MINTS,MAXTS) > 1;




NAME, MINTS,                 MAXTS,                value
'M1', '2022-05-04T00:04:00', '2022-05-04T00:07:00', '584'
'M1', '2022-05-04T00:10:00', '2022-05-04T00:12:00', '586'

uj5u.com熱心網友回復：

您使用 LAG 在正確的線路上，但您還需要通過值獲取先前的時間戳和磁區，例如

SELECT [name], startTime, endTime, [Value]
from
(
    SELECT [name],min([timestamp]) as startTime, max([timestamp]) as endTime,[value]
    FROM 
    (
        SELECT *, lag([Value]) OVER (partition by [Name],[value] ORDER by [value],[timestamp]) prevValue
                , lag([timestamp]) OVER (partition by [name],[value] ORDER by [value],[timestamp]) prevTime
        FROM table
    ) a
    GROUP BY [name],[value]
) b
WHERE Datediff(minute, starttime, endtime) > 1

它可能與一些整理有關

uj5u.com熱心網友回復：

您的資料（在 SQL Server 中）

Declare @a table(
   Name      VARCHAR(100) NOT NULL 
  ,Timestamp DATETIME
  ,Value     INTEGER 
);
INSERT INTO @a
(Name,Timestamp,Value) 
VALUES 
('M1','2022-05-04T00:00:00',580),
('M1','2022-05-04T00:01:00',581),
('M1','2022-05-04T00:02:00',582),
('M1','2022-05-04T00:03:00',583),
('M1','2022-05-04T00:04:00',584),
('M1','2022-05-04T00:05:00',584),
('M1','2022-05-04T00:06:00',584),
('M1','2022-05-04T00:07:00',584), 
('M1','2022-05-04T00:08:00',585),
('M1','2022-05-04T00:09:00',585), 
('M1','2022-05-04T00:10:00',586), 
('M1','2022-05-04T00:11:00',586),
('M1','2022-05-04T00:12:00',586),
('M1','2022-05-04T00:13:00',587),
('M1','2022-05-04T00:14:00',588);

得到你想要的結果，使用Lag，，，如下SubqueryAggreagate function

SELECT Min(timestamp2) 'Start time',
       Max(timestamp)  'End time',
       value
FROM  (SELECT NAME,
              timestamp,
              value,
              Lag(a.timestamp)
                OVER (
                  partition BY a.NAME
                  ORDER BY timestamp)         Timestamp2,
              value - Lag(a.value)
                        OVER (
                          partition BY a.NAME
                          ORDER BY timestamp) flag
       FROM   @a a1) a2
WHERE  flag = 0
GROUP  BY value,
          flag

提供的結果

開始時間	時間結束	價值
2022-05-04T00:04:00	2022-05-04T00:07:00	584
2022-05-04T00:08:00	2022-05-04T00:09:00	585
2022-05-04T00:10:00	2022-05-04T00:12:00	586

uj5u.com熱心網友回復：

可以使用MIN()and輕松完成MAX()，按 Value 分組（以及完整的 name 列），并帶有一個HAVING子句來過濾分組結果：

--Using SQL Server, Your DDL
CREATE TABLE #temp (Name varchar(100), Timestamp varchar(100), Value int)
INSERT INTO #temp VALUES
('M1','2022-05-04T00:00:00', 580)
,('M1','2022-05-04T00:01:00', 581)
,('M1','2022-05-04T00:02:00', 582)
,('M1','2022-05-04T00:03:00', 583)
,('M1','2022-05-04T00:04:00', 584)
,('M1','2022-05-04T00:05:00', 584)
,('M1','2022-05-04T00:06:00', 584)
,('M1','2022-05-04T00:07:00', 584)
,('M1','2022-05-04T00:08:00', 585)
,('M1','2022-05-04T00:09:00', 585)
,('M1','2022-05-04T00:10:00', 586)
,('M1','2022-05-04T00:11:00', 586)
,('M1','2022-05-04T00:12:00', 586)
,('M1','2022-05-04T00:13:00', 587)
,('M1','2022-05-04T00:14:00', 588)

SELECT MIN(Timestamp) AS [Start time], Max(Timestamp) AS [End time]
      ,[Value]
  FROM #temp 
GROUP BY [Name], [Value]
HAVING DATEDIFF(MINUTE, MIN(Timestamp), MAX(Timestamp)) > 1

創建相同的輸出：

/*
Start time            End time              Value
2022-05-04T00:04:00   2022-05-04T00:07:00   584
2022-05-04T00:10:00   2022-05-04T00:12:00   586
*/

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/472622.html

標籤：mysql sql sql服务器约会时间 apache-spark-sql

上一篇：從“日期時間”創建“時間”以比較不同日期的事件時間

下一篇：在沒有手動檢查的情況下格式化時差時省略零部分的好方法是什么？