lundi 26 janvier 2015

SQLite Rolling TimeStamp Count

Hi I have asked this question before but might nit have phrased it right hope someone can help me


i'm trying to get a SQLite query to count the occurrence of an value in a Rolling TimeFrame and return a distinct count


My Data looks something like this





---------DATA----------





TimeStamp UserName
2015-01-12 06:07:00 JamesG
2015-01-12 06:07:00 JohnH
2015-01-12 06:10:00 JohnH
2015-01-12 06:10:00 James
2015-01-12 06:10:00 Dean
2015-01-12 06:10:00 Alicia
2015-01-12 06:12:00 Ann
2015-01-12 06:12:00 Dean
2015-01-12 06:12:00 JohnH


This is the result I would like




Result count
"2015-01-12 06:07:00" 2
"2015-01-12 06:10:00" 4
"2015-01-12 06:12:00" 2


With my current select I get a result but it doesn't consider Timestamp it generates 5min intervals




WITH RECURSIVE
min_time(t) AS (
SELECT datetime((strftime('%s',
(SELECT MIN(TimeStamp)
FROM ConcurrentUsers)
) / 300) * 300,
'unixepoch')
),
max_time(t) AS (
SELECT datetime((strftime('%s',
(SELECT MAX(TimeStamp)
FROM ConcurrentUsers)
) / 300) * 300,
'unixepoch')
),
intervals(t) AS (
SELECT t FROM min_time
UNION ALL
SELECT datetime(t, '+5 minutes')
FROM intervals
WHERE t <= (SELECT t FROM max_time)
)
SELECT t AS interval,
(SELECT COUNT(strftime('%M',TimeStamp))
FROM ConcurrentUsers
WHERE TimeStamp < datetime(intervals.t, '+5 minutes') AS Count
FROM intervals where interval >= date('now')
ORDER BY date(interval) DESC


Any help would be appreciated.


Aucun commentaire:

Enregistrer un commentaire