SQL 선택 3시간 평균

SQL 선택 3시간 평균

이 테이블이 있어요

MariaDB [table]> 
select insert_time, host_id, tx 
from host_daily 
where host_id = 2 
order by insert_time desc 
limit 24;

+---------------------+---------+------------+
| insert_time         | host_id | tx         |
+---------------------+---------+------------+
| 2016-02-06 14:00:00 |       2 | 9676875156 |
| 2016-02-06 13:00:00 |       2 | 9671544048 |
| 2016-02-06 12:00:00 |       2 | 9669464371 |
| 2016-02-06 11:00:00 |       2 | 9667087098 |
| 2016-02-06 10:00:00 |       2 | 9665014071 |
| 2016-02-06 09:00:00 |       2 | 9662931956 |
| 2016-02-06 08:00:00 |       2 | 9660874138 |
| 2016-02-06 07:00:00 |       2 | 9658624162 |
| 2016-02-06 06:00:00 |       2 | 9656555329 |
| 2016-02-06 05:00:00 |       2 | 9654443169 |
| 2016-02-06 04:00:00 |       2 | 9651362676 |
| 2016-02-06 03:00:00 |       2 | 9648531733 |
| 2016-02-06 02:00:00 |       2 | 9633368883 |
| 2016-02-05 23:00:00 |       2 | 9464826179 |
| 2016-02-05 22:00:00 |       2 | 9363099844 |
| 2016-02-05 21:00:00 |       2 | 9270841166 |
| 2016-02-05 20:00:00 |       2 | 9140988502 |
| 2016-02-05 19:00:00 |       2 | 9022460285 |
| 2016-02-05 18:00:00 |       2 | 8925920799 |
| 2016-02-05 17:00:00 |       2 | 8825711136 |
| 2016-02-05 16:00:00 |       2 | 8802081092 |
| 2016-02-05 15:00:00 |       2 | 8755784419 |
+---------------------+---------+------------+

확실하진 않지만, 몇 시간이 부족할 수도 있어요.예: 23시에서 2시 사이.가능하다면 이 누락된 시간을 0으로 채우고 싶습니다.이제 지난 주의 평균 3시간을 선택하고 싶은데, 조회가 제대로 되지 않는 것 같습니다.잠시 이 쿼리를 수정하려고 합니다.

select 
    n.n as id, 
    host_id,
    avg(tx),
    insert_time + interval 3 * n.n hour as 'from',
    insert_time + interval 3 * (n.n + 1) hour as 'to'
from (SELECT  0 AS n
          UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3
          UNION ALL SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6
          UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9
          UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
          UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
          UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
          UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
          UNION ALL SELECT 22 UNION ALL SELECT 23 
     ) n 
left join host_daily
     on n.n = hour(insert_time) div 3
where host_id = 2 
  and insert_time > NOW() - INTERVAL 24 HOUR group by n.n;

결과:

+----+---------+-----------------+---------------------+---------------------+
| id | host_id | avg(tx)         | from                | to                  |
+----+---------+-----------------+---------------------+---------------------+
|  4 |       2 | 9672627858.3333 | 2016-02-07 00:00:00 | 2016-02-07 03:00:00 |
|  3 |       2 | 9665011041.6667 | 2016-02-06 18:00:00 | 2016-02-06 21:00:00 |
|  2 |       2 | 9658684543.0000 | 2016-02-06 12:00:00 | 2016-02-06 15:00:00 |
|  1 |       2 | 9651445859.3333 | 2016-02-06 06:00:00 | 2016-02-06 09:00:00 |
|  0 |       2 | 9605672722.3333 | 2016-02-06 00:00:00 | 2016-02-06 03:00:00 |
|  7 |       2 | 9366255729.6667 | 2016-02-06 18:00:00 | 2016-02-06 21:00:00 |
|  6 |       2 | 9029789862.0000 | 2016-02-06 12:00:00 | 2016-02-06 15:00:00 |
|  5 |       2 | 9019970953.0000 | 2016-02-06 06:00:00 | 2016-02-06 09:00:00 |
+----+---------+-----------------+---------------------+---------------------+

누가 나를 올바른 방향으로 인도해 줄 수 있을까?

편집: 쿼리 결과를 예상 결과로 편집하여 24시간 보기에서 복귀함으로써 예제 무결성을 유지합니다.

매회 반올림을 3시간으로 줄여야 합니다.매 3시간마다 레코드가 하나 이상 있다고 가정하면 반올림을 사용할 수 있습니다.

select from_unixtime(floor(unix_timestamp(insert_time)/(60*60*3))*60*60*3) as timestart,
       host_id,
       sum(tx) / 3
from host_daily hd
group by from_unixtime(floor(unix_timestamp(insert_time)/(60*60*3))*60*60*3), hostid
order by 1, 2;

이러한 종류의 가정에서는 결측값이 0으로 처리되는 것을 전제로 합니다.사용할 수 없는 경우는, 을 사용합니다.avg(tx)대신.

그래도 행이 누락된 경우 모든 데이터가 누락된 기간에 원하는 값을 사용하여 행에 추가할 수 있습니다.당신의 질문은 그 경우에 무엇을 포함시켜야 하는지에 대해 모호합니다.

언급URL : https://stackoverflow.com/questions/35241254/sql-select-3-hour-avg