source

시간 범위 내에서 5분 간격으로 그룹화

goodcode 2022. 9. 22. 00:23
반응형

시간 범위 내에서 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

이것은 모든 인터벌에서 동작합니다.

포스트그레스Ql

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

저도 같은 문제에 부딪혔어요.

분 단위로 그룹화하는 것은 에폭을 분 단위로 초 단위로 나눈 다음 반올림하거나 바닥을 사용하여 나머지를 얻는다는 것을 알게 되었습니다.따라서 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

이제 발생 빈도가 0인 인터벌 결과를 얻으려면 두 결과 세트를 모두 바깥쪽에서 결합합니다.

    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분 간격의 영상 시리즈가 모두 포함됩니다.

interval             count
-------------------  ----  
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'을 사용하지만 원하는 간격일 수 있습니다.

사용하는 것이 좋다.GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 300반올림(../300) 대신 반올림 때문에 일부 레코드는 두 개의 그룹화된 결과 세트로 카운트된다는 것을 발견했습니다.

포스트그리어의 경우, 보다 쉽고 정확하게 사용할 수 있다는 것을 알았습니다.

date_closec

다음과 같은 기능:

select name, sum(count), date_trunc('minute',timestamp) as timestamp
FROM table
WHERE xxx
GROUP BY name,date_trunc('minute',timestamp)
ORDER BY timestamp

minute, hour, day 등 다양한 해상도를 제공할 수 있습니다.date_debugc로 이동합니다.

쿼리는 다음과 같습니다.

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

아직 필요한지는 모르겠지만

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 |

타임스탬프를 ymd로 분할해야 합니다.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

...그리고 출력을 클라이언트 코드로 변환하여 원하는 대로 표시할 수 있습니다.또는 원하는 경우 별도의 열을 얻는 대신 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(unix_timestamp(timestamp) - unix_timestamp(timestamp) mod 300) as ts,  
    sum(value)
from group_interval 
group by ts 
order by ts
;

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))

이게 바로 네가 원하는 걸 할 거야.

교체하다

  • dt - 날짜
  • c - call 필드
  • astro_timeout1 - 테이블
  • 시간 간격이 증가할 때마다 300초
SELECT 
    FROM_UNIXTIME(300 * ROUND(UNIX_TIMESTAMP(r.dt) / 300)) AS 5datetime,
    (SELECT 
            r.c
        FROM
            astro_transit1 ra
        WHERE
            ra.dt = r.dt
        ORDER BY ra.dt DESC
        LIMIT 1) AS first_val
FROM
    astro_transit1 r
GROUP BY UNIX_TIMESTAMP(r.dt) DIV 300
LIMIT 0 , 30

언급URL : https://stackoverflow.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range

반응형

'source' 카테고리의 다른 글

ScopeKey가 nuxt.js 인증 모듈에서 작동하지 않음  (0) 2022.09.23
개체를 문자열로 변환  (0) 2022.09.22
10억 번째 상대 수입액  (0) 2022.09.22
오브젝트 길이를 얻는 방법  (0) 2022.09.22
JavaScript가 멀티스레딩을 지원하지 않는 이유는 무엇입니까?  (0) 2022.09.22

'source'의 다른글

  • 현재글시간 범위 내에서 5분 간격으로 그룹화

관련글

댓글

티스토리툴바