У меня есть некоторые трудности с команд MySQL, что я хочу сделать.
SELECT a.timestamp, name, count(b.name)
FROM time a, id b
WHERE a.user = b.user
AND a.id = b.id
AND b.name = 'John'
AND a.timestamp BETWEEN '2010-11-16 10:30:00' AND '2010-11-16 11:00:00'
GROUP BY a.timestamp
Это мой текущий оператор вывода.
timestamp name count(b.name)
------------------- ---- -------------
2010-11-16 10:32:22 John 2
2010-11-16 10:35:12 John 7
2010-11-16 10:36:34 John 1
2010-11-16 10:37:45 John 2
2010-11-16 10:48:26 John 8
2010-11-16 10:55:00 John 9
2010-11-16 10:58:08 John 2
Как мне сгруппировать их в 5 минут интервал результаты?
Я хочу, чтобы мой выход, чтобы быть как
timestamp name count(b.name)
------------------- ---- -------------
2010-11-16 10:30:00 John 2
2010-11-16 10:35:00 John 10
2010-11-16 10:40:00 John 0
2010-11-16 10:45:00 John 8
2010-11-16 10:50:00 John 0
2010-11-16 10:55:00 John 11
Это работает с каждым интервалом.
В PostgreSQL
в
SELECT
TIMESTAMP WITH TIME ZONE 'epoch' +
INTERVAL '1 second' * round(extract('epoch' from timestamp) / 300) * 300 as timestamp,
name,
count(b.name)
FROM time a, id
WHERE …
GROUP BY
round(extract('epoch' from timestamp) / 300), name
<БР> В MySQL
SELECT
timestamp, -- not sure about that
name,
count(b.name)
FROM time a, id
WHERE …
GROUP BY
UNIX_TIMESTAMP(timestamp) DIV 300, name
Для данных, я нашел его проще и точнее использовать
функции, как:
select name, sum(count), date_trunc('minute',timestamp) as timestamp
FROM table
WHERE xxx
GROUP BY name,date_trunc('minute',timestamp)
ORDER BY timestamp
Вы можете предоставить различные решения, такие как 'минуту','час','день' и т. д.... date_trunc.
я наткнулся на тот же вопрос.
Я обнаружил, что это легко группировать по любой минутный интервал просто деля эпоха по минут в количество секунд, а затем либо округления или через пол избавлюсь от остальных. Так что если вы хотите сделать интервал в 5 минут можно использовать 300секунд. в <код>
SELECT COUNT(*) cnt,
to_timestamp(floor((extract('epoch' from timestamp_column) / 300 )) * 300)
AT TIME ZONE 'UTC' as interval_alias
FROM TABLE_NAME GROUP BY interval_alias
</код>
в <предварительно> interval_alias УНТ
2010-11-16 10:30:00 2 2010-11-16 10:35:00 10 2010-11-16 10:45:00 8 2010-11-16 10:55:00 11 </пред>
Это позволит вернуть правильно группировать данные выбранного интервала; однако, это не вернет интервалы, Дон't содержит любые данные. Для того, чтобы получить эти пустые промежутки времени, мы можем использовать функцию generate_series.
в <код>
SELECT generate_series(MIN(date_trunc('hour',timestamp_column)),
max(date_trunc('minute',timestamp_column)),'5m') as interval_alias FROM
TABLE_NAME
2010-11-16 10:30:00 2010-11-16 10:35:00 2010-11-16 10:40:00 2010-11-16 10:45:00 2010-11-16 10:50:00 2010-11-16 10:55:00 </пред>
Сейчас, чтобы получить результат с помощью интервала с нулевым случаев мы просто внешнее соединение обоих результирующих наборах.
в <код>
SELECT series.minute as interval, coalesce(cnt.amnt,0) as count from
(
SELECT count(*) amnt,
to_timestamp(floor((extract('epoch' from timestamp_column) / 300 )) * 300)
AT TIME ZONE 'UTC' as interval_alias
from TABLE_NAME group by interval_alias
) cnt
RIGHT JOIN
(
SELECT generate_series(min(date_trunc('hour',timestamp_column)),
max(date_trunc('minute',timestamp_column)),'5m') as minute from TABLE_NAME
) series
на серии.минута = УНТ.interval_alias
</код> Конечный результат будет включать в себя серии с 5-минутными интервалами, даже те, которые не имеют никакого значения. в <предварительно> интервала
2010-11-16 10:30:00 2 2010-11-16 10:35:00 10 2010-11-16 10:40:00 0 2010-11-16 10:45:00 8 2010-11-16 10:50:00 0 2010-11-16 10:55:00 11 </пред>
Интервал может быть легко изменено путем изменения последнего параметра generate_series. В нашем случае мы используем '5м' но это может быть интервал мы хотим.
Вы'повторно, вероятно, придется порвать свои метки на ГМД:HM и использовать div 5 разделить минутах в 5-ти минутах закромах-что-то вроде
select year(a.timestamp),
month(a.timestamp),
hour(a.timestamp),
minute(a.timestamp) DIV 5,
name,
count(b.name)
FROM time a, id b
WHERE a.user = b.user AND a.id = b.id AND b.name = 'John'
AND a.timestamp BETWEEN '2010-11-16 10:30:00' AND '2010-11-16 11:00:00'
GROUP BY year(a.timestamp),
month(a.timestamp),
hour(a.timestamp),
minute(a.timestamp) DIV 12
...а потом повертеть выход в клиентский код, чтобы появиться так, как вам нравится. Или, вы можете создать целую строку с датой в operatorinstead в SQL функция concat получения отдельных столбцах, если вам нравится.
select concat(year(a.timestamp), "-", month(a.timestamp), "-" ,day(a.timestamp),
" " , lpad(hour(a.timestamp),2,'0'), ":",
lpad((minute(a.timestamp) DIV 5) * 5, 2, '0'))
...а потом группы по этому
Не уверен, если она вам еще понадобится.
SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(timestamp))/300)*300) AS t,timestamp,count(1) as c from users GROUP BY t ORDER BY t;
2016-10-29 19:35:00 | 2016-10-29 19:35:50 | 4 |
2016-10-29 19:40:00 | 2016-10-29 19:40:37 | 5 |
2016-10-29 19:45:00 | 2016-10-29 19:45:09 | 6 |
2016-10-29 19:50:00 | 2016-10-29 19:51:14 | 4 |
2016-10-29 19:55:00 | 2016-10-29 19:56:17 | 1 |
Я узнал, что в MySQL, вероятно, правильным запросом является следующий:
SELECT SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300,
'%Y-%m-%d %H:%i:%S' ) , 1, 19 ) AS ts_CEILING,
SUM(value)
FROM group_interval
GROUP BY SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300,
'%Y-%m-%d %H:%i:%S' ) , 1, 19 )
ORDER BY SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300,
'%Y-%m-%d %H:%i:%S' ) , 1, 19 ) DESC
Позвольте мне знать, что вы думаете.
select
CONCAT(CAST(CREATEDATE AS DATE),' ',datepart(hour,createdate),':',ROUNd(CAST((CAST((CAST(DATEPART(MINUTE,CREATEDATE) AS DECIMAL (18,4)))/5 AS INT)) AS DECIMAL (18,4))/12*60,2)) AS '5MINDATE'
,count(something)
from TABLE
group by CONCAT(CAST(CREATEDATE AS DATE),' ',datepart(hour,createdate),':',ROUNd(CAST((CAST((CAST(DATEPART(MINUTE,CREATEDATE) AS DECIMAL (18,4)))/5 AS INT)) AS DECIMAL (18,4))/12*60,2))