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

Question 1

수행하려는 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

Question 2

이것은 모든 간격에서 작동합니다.

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

Question 3

나는 같은 문제를 만났습니다.

나는 어떤 분 간격으로 그룹화하기 쉽다는 것은 단지 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 분 간격의 시리즈가 모두 포함됩니다.

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’를 사용 하지만 원하는 간격 이 될 수 있습니다 .

Question 4

GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 300round (../ 300) 대신 사용 하는 것이 좋습니다 . 반올림 때문에 일부 레코드가 두 개의 그룹화 된 결과 집합으로 계산된다는 것을 알았습니다.

Question 5

들어 포스트 그레스 , 나는 쉽게하고를 사용하는 것이 더 정확 발견

date_trunc

기능 :

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에 ‘분’, ‘시’, ‘일’등과 같은 다양한 해상도를 제공 할 수 있습니다.

Question 6

쿼리는 다음과 같습니다.

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

Question 7

타임 스탬프를 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'))

… 그런 다음 그룹화

Question 8

여전히 필요한지 확실하지 않습니다.

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] 시간 범위 내에서 5 분 간격으로 그룹화

답변

답변

답변

답변

답변

답변

답변