Hello developers!

Already every day I puzzle how to make an hourly sample of the database, but with maximum and average values ​​at each hour, besides, if possible, it would be worth displaying zero values ​​at a specific hour (for beauty). The matter is aggravated by the fact that the version of PgSQL - 8.3

Therefore, the generate_series function does not work. WITH also strangely refused to work as it is in the example. The unnest function unnest similar - it does not work.

The last attempt was to combine an array and a table, for example through a JOIN , to sample the entire array without exception and find all the entries from the tables by the specified time.

My table is as follows:

id | related | info | time |

It turns out the following query can select the total amount at a specific hour:

 SELECT date_part('hour', tbl.time), COUNT( tbl.id ) FROM table AS tbl WHERE tbl.related = 'to something' tbl.info = 'info' GROUP BY date_part('hour', tbl.time) ORDER BY date_part('hour', tbl.time)

And you want to, really want to get the average at a specific hour. I suspect that everything should not be so complicated, but after a hint I climb to you.

    2 answers 2

    Suppose we have a table containing Hours h 0 ... 23 records

     SELECT h, AVG(<Ваше Поле>) AS someData FROM hours LEFT JOIN tbl ON hours.h = date_part('hour', tbl.time) GROUP BY h
    • Thanks pushed me to the right thought. The fact is that in my case, and apparently I forgot to mention this, I need to do a sample not by the average value of the field, but by the average value of the tuples for the selected hour. - Dex

    Those. I get something like this:

     SELECT h, COALESCE(AVG(tbl.c), 0) AS average FROM hours LEFT JOIN ( SELECT date_trunc('hour', tbl.time) AS st, COUNT(tbl.call_id) AS c FROM table AS tbl WHERE tbl.related = 'to something' AND tbl.info = 'info' GROUP BY date_trunc('hour', tbl.time) ORDER BY date_trunc('hour', tbl.time) ) AS tbl ON hours.h = date_part('hour', tbl.st ) GROUP BY h ORDER BY h

    At the moment I can’t try to simplify it.