On Thu, Apr 16, 2020 at 2:49 PM Rob Northcott <Rob.Northcott@xxxxxxxxxxxxxx> wrote: > > From: Alex Magnum <magnum11200@xxxxxxxxx> > > What I would like to do is to create a table as shown below that displays the counts per our for the past n dates. > > > > I can do this with a function but is there an easy way to use recursive queries? > > Counts per hour for given date > > HR 2020-04-01 2020-04-02 ... 2020-04-10 > 00 38 33 36 > 01 33 26 18 > 02 26 36 17 > 03 36 18 10 > 04 18 17 3 > 05 17 10 3 > 06 10 3 6 > 07 3 3 10 > . 3 6 13 > . 6 10 22 > . 10 13 12 > 22 13 22 9 > 23 22 11 8 > > Wouldn’t it be easier to do it the other way round, with a column per hour and a row per date? That way the query just needs one sub-query per hour to select just the count for that hour, and group by date. Note that you wouldn't need subqueries for that, the FILTER clause can be used and is supported since version 9.4.
