mysql - SQL select 3 hour avg -

i got table:

mariadb [table]>  select insert_time, host_id, tx  host_daily  host_id = 2  order 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 | +---------------------+---------+------------+ 

it's not obvious, it's possible hours missing. e.g. between 23 , 2 o'clock. if possible i'd fill missing hours 0. want select 3 hour averages last week, don't seem query right. trying , modifying query while:

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

resulting in:

+----+---------+-----------------+---------------------+---------------------+ | id | host_id | avg(tx)         |                |                  | +----+---------+-----------------+---------------------+---------------------+ |  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 | +----+---------+-----------------+---------------------+---------------------+ 

would kind point me in right direction?

edit: maintaining example integrity, editing query results expected result, returning 24 hour view.

you need round each time down 3 hour period. assuming have @ least 1 record each 3 hour period, can use rounding:

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

this sort of assumes want missing values treated 0. if not, use avg(tx) instead.

if still missing rows, can add them in whatever value want periods when all data missing. question vague include in case.