시간 범위 내에서 5 분 간격으로 그룹화
원하는 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
GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 300round (../ 300) 대신 사용 하는 것이 좋습니다 . 반올림 때문에 일부 레코드가 두 개의 그룹화 된 결과 집합으로 계산된다는 것을 알았습니다.
들어 포스트 그레스 , 나는 쉽게하고를 사용하는 것이 더 정확 발견
기능 :
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에 '분', '시간', '일'등과 같은 다양한 해상도를 제공 할 수 있습니다.
나는 같은 문제를 만났다.
나는 어떤 분 간격으로 그룹화하기 쉽다는 것은 단지 epoch 를 초 단위로 분으로 나눈 다음 반올림하거나 바닥을 사용하여 나머지를 타는 것입니다. 따라서 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 cnt ------------------- ---- 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
이렇게하면 선택한 분 간격으로 데이터가 올바르게 그룹화됩니다. 그러나 데이터를 포함하지 않는 간격은 반환하지 않습니다. 빈 간격을 얻기 위해 generate_series 함수를 사용할 수 있습니다 .
SELECT generate_series(MIN(date_trunc('hour',timestamp_column)),
max(date_trunc('minute',timestamp_column)),'5m') as interval_alias FROM
TABLE_NAME
결과:
interval_alias ------------------- 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
on series.minute = cnt.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의 마지막 매개 변수를 조정하여 쉽게 변경할 수 있습니다. 우리의 경우 '5m'를 사용 하지만 원하는 간격 이 될 수 있습니다 .
쿼리는 다음과 같습니다.
SELECT
DATE_FORMAT(
MIN(timestamp),
'%d/%m/%Y %H:%i:00'
) AS tmstamp,
name,
COUNT(id) AS cnt
FROM
table
GROUP BY ROUND(UNIX_TIMESTAMP(timestamp) / 300), name
You're probably going to have to break up your timestamp into ymd:HM and use DIV 5 to split the minutes up into 5-minute bins -- something like
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
...and then futz the output in client code to appear the way you like it. Or, you can build up the whole date string using the sql concat operatorinstead of getting separate columns, if you like.
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'))
...and then group on that
How about this one:
select
from_unixtime(unix_timestamp(timestamp) - unix_timestamp(timestamp) mod 300) as ts,
sum(value)
from group_interval
group by ts
order by ts
;
Not sure if you still need it.
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 |
I found out that with MySQL probably the correct query is the following:
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
Let me know what you think.
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))
참고URL : https://stackoverflow.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range
'program story' 카테고리의 다른 글
| 루비 최대 정수 (0) | 2020.10.12 |
|---|---|
| 열거 형 유형을 프로그래밍 방식으로 열거하는 방법은 무엇입니까? (0) | 2020.10.11 |
| DateTime을 웹 UTC 형식으로 어떻게 포맷 할 수 있습니까? (0) | 2020.10.11 |
| "유형 'T'가 참조 유형이어야 할 때"메소드를 제네릭으로 만드는 방법은 무엇입니까? (0) | 2020.10.11 |
| Android : 프로그래밍 방식으로 Java 코드에서 Spinner 채우기 (0) | 2020.10.11 |