November 2, 2019 at 1:34 pm
Hi there
I’m stuck with a time-intelligence query. However I think, I’m close to it 😉
Background: I want to find out if my CPU is running too hot, so I started some monitoring. Input data is a classic time stream (key, timestamp, value) for every two seconds. Now I want to find out, for how long (period) a specific temperature was present. Hence, I need to merge periods with the same values.
I think, the trick is to first convert the event stream into a time-series format (key, start, end, value)
Expected result:
Sensor “Core #0”
32C from 2019-11-01 10:32:48.2320000 to 2019-11-01 10:32:52.5200000 (-1)
31C from 2019-11-01 10:32:52.5200000 to 2019-11-01 10:32:54.6520000 (-1)
32C from 2019-11-01 10:32:54.6520000 to 2019-11-01 10:33:01.0210000 (-1)
…
Probably, I’ll have to assign “group numbers” that count the occurrence of a value, so I can distinguish them later. Like ‘the first time it hit 32C’, ‘the second time it hit 32’ instead of directly summarizing them.
Please find some code below:
drop table if exists timeStuff.timePeriods;
GO
drop schema if exists timeStuff;
GO
create schema timeStuff
GO
create table timeStuff.timePeriods
(
sensor varchar(10),
eventTS datetime2,
temperature smallint
);
insert into timeStuff.timePeriods (sensor, eventTS, temperature) values ('Core #0', '2019-11-01 10:32:48.2320000', 32);
insert into timeStuff.timePeriods (sensor, eventTS, temperature) values ('Core #0', '2019-11-01 10:32:50.3880000', 32);
insert into timeStuff.timePeriods (sensor, eventTS, temperature) values ('Core #0', '2019-11-01 10:32:52.5200000', 31);
insert into timeStuff.timePeriods (sensor, eventTS, temperature) values ('Core #0', '2019-11-01 10:32:54.6520000', 32);
insert into timeStuff.timePeriods (sensor, eventTS, temperature) values ('Core #0', '2019-11-01 10:32:58.9100000', 32);
insert into timeStuff.timePeriods (sensor, eventTS, temperature) values ('Core #0', '2019-11-01 10:33:01.0210000', 60);
insert into timeStuff.timePeriods (sensor, eventTS, temperature) values ('Core #0', '2019-11-01 10:33:03.1430000', 36);
insert into timeStuff.timePeriods (sensor, eventTS, temperature) values ('Core #0', '2019-11-01 10:33:05.2760000', 39);
insert into timeStuff.timePeriods (sensor, eventTS, temperature) values ('Core #0', '2019-11-01 10:33:07.3980000', 36);
insert into timeStuff.timePeriods (sensor, eventTS, temperature) values ('Core #0', '2019-11-01 10:33:09.5150000', 36);
-- build times series from event stream
-- (almost always 2 seconds, as this is the measure interval)
with timeSeries as (
select
ts.sensor,
ts.eventTS as startTS,
-- remove overlaps
dateadd(nanosecond, - 100, lead(ts.eventTS) over(partition by ts.sensor order by ts.eventTS)) as stopTS,
ts.temperature
from timeStuff.timePeriods ts
)
select
ts.*,
-- things get weird here :-/
row_number() over(partition by ts.sensor order by ts.startTS) -
row_number() over(partition by ts.sensor, ts.temperature order by ts.startTS)
from timeSeries ts
order by ts.startTS
November 2, 2019 at 6:21 pm
;with cte as
(
select ts.sensor,
ts.temperature,
ts.eventTS,
LAG(ts.temperature) OVER (PARTITION BY ts.sensor ORDER BY ts.eventTS) LagTemp
from timeStuff.timePeriods ts
)
,cte2 as (
select cte.*
from cte
where ISNULL(LagTemp,-32768) <> cte.temperature
)
select cte2.sensor,
CONCAT(cte2.temperature,'C from ',cte2.eventTS,' to ',ISNULL(LEAD(CONVERT(varchar,cte2.eventTS)) OVER (PARTITION BY cte2.sensor ORDER BY cte2.eventTS)+' (-1)','...')) msg
from cte2
order by cte2.sensor, cte2.eventTS
November 4, 2019 at 4:45 pm
Hi Jonathan
Thank you very much for your help, which is greatly appreciated.
However, I was probably not so concise in describing what I need. My goal is to produce new records and "merge" the periodes with the same value.
The Query should return a result set like:
sensor startTS stopTS temperature
Core #0 2019-11-01 10:32:48.2320000 2019-11-01 10:32:52.5199999 32
Core #0 2019-11-01 10:32:52.5200000 2019-11-01 10:32:54.6519999 31
Core #0 2019-11-01 10:32:54.6520000 2019-11-01 10:33:01.0209999 32
Core #0 2019-11-01 10:33:01.0210000 2019-11-01 10:33:03.1429999 60
Core #0 2019-11-01 10:33:03.1430000 2019-11-01 10:33:05.2759999 36
Core #0 2019-11-01 10:33:05.2760000 2019-11-01 10:33:07.3979999 39
Core #0 2019-11-01 10:33:07.3980000 NULL 36
As you can see, the goal is to merge equal values of the same occurrence into one record. Original Record 1 and 2 have been merged with the earliest startTS and the latest stopTS, then it's interrupted by another record wird the value 31, so the next two records with value 32 are considered a new period and merged…
November 4, 2019 at 5:25 pm
Hi Jonathan
Thank you very much for your help, which is greatly appreciated.
However, I was probably not so concise in describing what I need. My goal is to produce new records and "merge" the periodes with the same value.
The Query should return a result set like:
sensor startTS stopTS temperature
Core #0 2019-11-01 10:32:48.2320000 2019-11-01 10:32:52.5199999 32
Core #0 2019-11-01 10:32:52.5200000 2019-11-01 10:32:54.6519999 31
Core #0 2019-11-01 10:32:54.6520000 2019-11-01 10:33:01.0209999 32
Core #0 2019-11-01 10:33:01.0210000 2019-11-01 10:33:03.1429999 60
Core #0 2019-11-01 10:33:03.1430000 2019-11-01 10:33:05.2759999 36
Core #0 2019-11-01 10:33:05.2760000 2019-11-01 10:33:07.3979999 39
Core #0 2019-11-01 10:33:07.3980000 NULL 36As you can see, the goal is to merge equal values of the same occurrence into one record. Original Record 1 and 2 have been merged with the earliest startTS and the latest stopTS, then it's interrupted by another record wird the value 31, so the next two records with value 32 are considered a new period and merged…
I thought that was what the query I provided did.
Have you tried it?
November 5, 2019 at 11:54 am
I just took the columns out of the CONCAT from my previous query so they are separate columns, I think this is exactly what you have asked for:
;with cte as
(
select ts.sensor,
ts.temperature,
ts.eventTS,
LAG(ts.temperature) OVER (PARTITION BY ts.sensor ORDER BY ts.eventTS) LagTemp
from timeStuff.timePeriods ts
)
,cte2 as (
select cte.*
from cte
where cte.LagTemp <> cte.temperature
or cte.LagTemp IS NULL
)
select cte2.sensor,
cte2.eventTS startTS,
DATEADD(ns,-100,LEAD(cte2.eventTS) OVER (PARTITION BY cte2.sensor ORDER BY cte2.eventTS)) stopTS,
cte2.temperature
from cte2
order by cte2.sensor, cte2.eventTS
November 8, 2019 at 4:19 pm
Yep, that does the trick 🙂
thanks a lot.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply