Query to get average items per second for the last 12 hours, in Impala/Hive SQL? (I have partial queries already)

Question

We have a database being continuously filled with more data, and for a dashboard I need to show average items (being added) per second (given by a COUNT of msg_num in my queries below), for each hour for the last 12 hours. I have two more basic queries running successfully, so need to kind of merge them somehow:

Items per hour, for last 12 hours:

SELECT DATE_TRUNC('HOUR', concat(replace(timestamp,'"',''), "Z")) AS hours, COUNT(msg_num)
FROM mydb.mytable
WHERE unix_timestamp(timestamp) > (unix_timestamp(now()) - 43200)
GROUP BY 1
ORDER BY 1 ASC
LIMIT 12

Items per second, over the last hour:

SELECT DATE_TRUNC('SECOND', concat(replace(timestamp,'"',''), "Z")) AS seconds, COUNT(msg_num)
FROM mydb.mytable
WHERE unix_timestamp(timestamp) > (unix_timestamp(now()) - 3600)
GROUP BY 1
ORDER BY 1 ASC
LIMIT 3600

So what query would give me average items per second, for each hour for the last 12 hours?

Thanks

Answer 1

I think the average items per second over an hour is the total items per hour divided by 3600 (60 * 60):

SELECT DATE_TRUNC('HOUR', concat(replace(timestamp,'"',''), "Z")) AS hours,
       COUNT(msg_num) / 3600 as avg_items_per_second
FROM mydb.mytable
WHERE unix_timestamp(timestamp) > (unix_timestamp(now()) - 43200)
GROUP BY 1
ORDER BY 1 ASC

LIMIT 12