Find records with start and end time when value doesn't increase for more than 1 minute in SQL-CodePudding

I am stuck with a problem on sql server, where I need to find start time if the value does not increase for more than 1 minute while the end time is when the value increases and my data looks like below

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

The result should look like

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

I tried with queries like below but it is not working


    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

Please Help!

CodePudding user response：

Don't know what you mean by confusing , for 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'

CodePudding user response：

You are on the right lines using LAG but you also need to grab the previous timestamp and partition by the values e.g.

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

It could probably do with some tidying up

CodePudding user response：

your data (In 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);

to get your desired result, Use Lag, Subquery, and Aggreagate function as follows

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

provided result

Start time	End time	Value
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