October 12, 2014 at 10:39 pm
Hi,
I would like to do a group by when the timestamp is different from an usual pattern,
create table #tbl ( V1 int, V2 datetime, V3 float)
insert into #tbl values (1,'2012-12-12 10:15', 12.5)
insert into #tbl values (1,'2012-12-12 10:35', 2.5)
insert into #tbl values (1,'2012-12-12 10:45', 1.5)
insert into #tbl values (1,'2012-12-12 11:15', 10.5)
insert into #tbl values (1,'2012-12-12 11:30', 11.5)
select sum(v3) [value], v1 from #tbl group by v1
drop table #tbl
i would like to have result set as, aggregating results of every 15 minutes and when it exceeds 15 minutes then add the same in next group by. This 15 minute interval can be specified in the query itself.
value v1
16.5 1
221
Thanks in advance
Ami
October 13, 2014 at 1:16 am
"Exceeds 15 minutes" --> is this V2 or V3?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2014 at 4:26 am
it is V2. It is a timestamp.
and i want to aggregate on V3.
October 13, 2014 at 4:27 am
OK. On what decision are the first three rows grouped together and the last two rows grouped together?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2014 at 4:35 am
If the timestamp is available for next 15 minute then group it....
we have it for 10:15, 10:30 and 10:45
Next timestamp is on 11:15 and not 11:00 so falls under next group. 11:15 and 11:30 we have two records, hence group it. if we have one more by 11:45 then we can include in the second group, if not if it is 12:00 or above then group under 3rd category...
October 13, 2014 at 4:50 am
Ah ok. So basically if the gap between two timestamps is bigger than 15 minutes a new group should be started.
This is a typical gaps and island problem.
Itzik has a very good description in the book SQL Server MPV Deep Dives.
You can download the sample chapter here:
http://www.manning.com/nielsen/SampleChapter5.pdf
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2014 at 6:43 am
insert into #tbl values (1,'2012-12-12 10:35', 2.5)
So timestamp is allowed to be not 15 minutes aligned? Or is it a misprint?
October 13, 2014 at 8:10 am
It is misprint, it is 10:30 only...
i'm trying to work out the solution given by
Koen Verbeeck - but finding some difficulties in formatting the same into time intervals... and not getting the desired result...
October 13, 2014 at 10:02 am
I'm trying like something below
create table #tbl ( V1 int, V2 datetime, V3 float)
insert into #tbl values (1,'2012-12-12 10:15', 12.5)
insert into #tbl values (1,'2012-12-12 10:30', 2.5)
insert into #tbl values (1,'2012-12-12 10:45', 1.5)
insert into #tbl values (1,'2012-12-12 11:15', 10.5)
insert into #tbl values (1,'2012-12-12 11:30', 11.5)
--select sum(v3) [value], v1 from #tbl
--group by v1
;WITH C AS
(
SELECT V2, v3,v1, ROW_NUMBER() OVER(ORDER BY V2) AS rownum
FROM #tbl
)
SELECT sum(cur.v3) , cur.v1, cur.rownum --datediff(minute, cur.V2 , nxt.V2) --dateadd(minute,15,Cur.V2) AS start_range , dateadd(minute, -15,Nxt.V2) AS end_range
FROM C AS Cur
JOIN C AS Nxt
ON Nxt.rownum = Cur.rownum + 1
WHERE datediff(minute, cur.V2 , nxt.V2) > 15
group by cur.v1, cur.rownum;
drop table #tbl
but not getting the exact result...
October 14, 2014 at 12:47 am
Provided 15 min is a fixed step
create table #tbl ( V1 int, V2 datetime, V3 float);
insert into #tbl values (1,'2012-12-12 10:15', 12.5);
insert into #tbl values (1,'2012-12-12 10:30', 2.5);
insert into #tbl values (1,'2012-12-12 10:45', 1.5);
insert into #tbl values (1,'2012-12-12 11:15', 10.5);
insert into #tbl values (1,'2012-12-12 11:30', 11.5);
insert into #tbl values (2,'2012-12-12 10:15', 12.5);
insert into #tbl values (2,'2012-12-12 10:30', 2.5);
insert into #tbl values (2,'2012-12-12 10:45', 1.5);
insert into #tbl values (2,'2012-12-12 11:15', 10.5);
insert into #tbl values (2,'2012-12-12 11:30', 11.5);
WITH C AS
(
SELECT V2, V3, V1, datediff(minute,0,V2)/15 - ROW_NUMBER() OVER(PARTITION BY V1 ORDER BY V2) AS grp
FROM #tbl
)
SELECT V1, sum(V3), min(V2), max(V2)
FROM C
GROUP BY V1, grp
ORDER BY V1, min(V2);
drop table #tbl;
October 14, 2014 at 7:31 am
i've done something like this,
create table #tbl ( V1 int, V2 datetime, V3 float)
insert into #tbl values (1,'2012-12-12 10:15', 12.5)
insert into #tbl values (1,'2012-12-12 10:30', 2.5)
insert into #tbl values (1,'2012-12-12 10:45', 1.5)
insert into #tbl values (1,'2012-12-12 11:15', 10.5)
insert into #tbl values (1,'2012-12-12 11:30', 11.5)
SELECT sum(v3), MIN(V2) AS start_range, MAX(V2) AS end_range
FROM (SELECT V3, V2, DATEADD(minute, -15 * ROW_NUMBER() OVER(ORDER BY v2), v2) AS grp
FROM #tbl) AS D
GROUP BY grp ORDER BY start_range ;
DROP TABLE #tbl
October 15, 2014 at 12:07 am
Don't you need group by / partition by V1 ?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply