How to make this query aggregate the time-grouping correct?
Independent
I try to perform a grouping based on rows based on a string and a eventtime. The identical strings must be grouped with eventtime occurrances within period of 30 minutes. If there are more then 30 minutes between eventtimes, there is a new groupset.
So far I found the following attempt (by many similiar, but this is simpliest). With the comments below, i also note that i missunderstood what it does. It does a 30 minutes rounding, which won't fill my need. Perhaps "grouping based on 'within daterange'" is more accurace?.
SELECT min(eventtime) as mintime,
max(eventtime) as maxtime,
[stringfield],
count([stringfield]) as qty
FROM Searches
GROUP BY datediff(mi, '19900101', eventtime) / 30,
[stringfield]
ORDER BY min(eventtime)
Resulting in this table 
See the red and blue arrows. Those two lines should be aggregated. Red arrows says 'identical strings'. The blue arrow says that 'the maxtime are within 30 min range'.
I have also tried 31 minutes without success, in case of >= issue. Can someone see by this T-SQL why it go wrong? And how I can fix it?
[Edit]
The expected output is the two rows with arrows grouped. Which means i expect five rows in the output. The 5th row should not exist (aggregated into first). Which results in Qty 3 in the first row, the maxtime will be '2013-06-01 08:55'.
2013-06-01 08:00 | 2013-06-01 08:55 | 0x2BBF4........26BD38 | 3
ypercubeᵀᴹ
In the 2012 version of SQL-Server, you can use the analytic LAG() and LEAD() functions for this:
; WITH cte AS
( SELECT
stringfield
, eventtime
, rn = ROW_NUMBER() OVER ( PARTITION BY stringfield
ORDER BY eventtime )
, leadtime = LEAD(eventtime) OVER ( PARTITION BY stringfield
ORDER BY eventtime )
, firsttime= MIN(eventtime) OVER ( PARTITION BY stringfield )
, diff = CASE WHEN DATEADD(minute, 30, eventtime)
>= LEAD(eventtime)
OVER ( PARTITION BY stringfield
ORDER BY eventtime )
THEN 0 ELSE 1
END
FROM
Searches
)
SELECT
stringfield
, mintime = COALESCE(LAG(leadtime) OVER ( PARTITION BY stringfield
ORDER BY eventtime )
,firsttime)
, maxtime = eventtime
, qty = rn - COALESCE(LAG(rn) OVER ( PARTITION BY stringfield
ORDER BY eventtime )
,0)
FROM
cte
WHERE
diff = 1
ORDER BY
stringfield,
maxtime ;
Tested at SQL-Fiddle.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Change Key for a SortedList
- Next: Custom view isn't being restored after adding it to a layout from AsyncTask's onPostExecute()
Related
Related Related
- 1
Aggregate query grouping
- 2
How to make a correct time calculation?
- 3
How to make a correct time calculation?
- 4
Aggregate query on time in MongoDB
- 5
Grouping a sum within a sql query to aggregate the rows
- 6
How to make query with grouping by foreign keys with ordering by sum of field
- 7
MongoDB execution time of Aggregate Query
- 8
SQL getting the correct dataset using aggregate query
- 9
Mongodb aggregate query help - grouping with multiple fields and converting to an array
- 10
SQL Query: grouping datetime as time slot
- 11
form does not make the correct query
- 12
How to make a double grouping in MongoDB?
- 13
How To: Specific complex grouping query
- 14
how to query the percentage of aggregate in vertica
- 15
how to query the percentage of aggregate in vertica
- 16
How do I make aggregate query return empty set instead of NULL row?
- 17
Detect and correct grouping variables with more than one repetition of a time sequence
- 18
how to make animatorInflater correct?
- 19
how to make animatorInflater correct?
- 20
How to correct the following query
- 21
How to make a query for showing records where time is not more then 30 mins
- 22
How to make a query for getting the specific rows with the latest time column value
- 23
ssrs grouping on aggregate
- 24
LINQ grouping with aggregate
- 25
LINQ grouping with aggregate
- 26
Mongoose aggregate with counting and grouping
- 27
How to make Content Grouping work in Google Analytics?
- 28
How to make a grouping dimension using mdx
- 29
How to make a pivot table by grouping the data
Comments