0

I have a table like this

id timestamps value
1 2022-01-01 00:00:00 2
2 2022-01-01 00:10:00 2
3 2022-01-01 00:20:00 1
4 2022-01-01 00:30:00 7
5 2022-01-01 00:40:00 4
6 2022-01-01 00:50:00 1
7 2022-01-01 01:00:00 3
8 2022-01-01 01:10:00 9
9 2022-01-01 01:20:00 5
10 2022-01-01 01:30:00 8
11 2022-01-01 01:40:00 0
12 2022-01-01 01:50:00 1

the data is being inserted every 10 minutes. now I want to select averge data every 1 hour in a 3 days range. currently, combined with PHP there is 72 queries (3 x 24hr) that being run every http request is coming from the client. the queries took about 6s, which is not a good idea. so is there any other approach that can decrease the execution time down to several ms?

2
  • Use generate_series and generate 1-hour calendar within needed 3-days range. Use it as a base, join your data to it, group by it and get needed average value.
    – Akina
    Jun 24 at 4:33
  • @Akina thanks for the direction/clue. hopefully I can achieve what I need with this approach Jun 24 at 5:25

1 Answer 1

1

To resolve your issue, I did the following (all of the code below is available on the fiddle here):

Firstly, I created a table as follows:

CREATE TABLE reading
(
  r_id INT         NOT NULL GENERATED ALWAYS AS IDENTITY, 
  r_ts TIMESTAMPTZ NOT NULL,                              -- always tz!
  r_val INT        -- NULL             -- may be NULL - missed readings?
);

I try to give my fields meaningful names - i.e. r_id instead of just id and r_ts (I use the _ts suffixes for TIMESTAMP(TZ)s - also, I always use TIMESTAMPTZ unless there's a good reason not to (which is the exception rather than the rule).

You may wish to consider the possibility of missing readings (NULL) for r_val. At the bottom of the fiddle, I've included the 3 possible behaviours of the AVG() function (all 3 values present - simple average, 1 out of a set of 3 values missing - average over the two valid readings, 3 missing out of 3, average is NULL).

I used your data to populate the table (see fiddle).

Then, we create a calendar tables - you may or may not wish to have this as part of your permanent schema (here's how to create one for SQL Server). It won't take up very much room and may tidy your SQL?

WITH calendar AS
(
  SELECT 
    t
  FROM
    GENERATE_SERIES
    (
      '2021-12-31 21:00:00'::TIMESTAMPTZ,
      '2022-01-01 02:00:00',   
      '1 HOUR'
    ) AS tab(t)
)
SELECT * FROM calendar;

I've only included 3 hours before midnight (when your data starts) and 1 hour after your data finishes - you may have data for timestamps before and after.

Result:

t
2021-12-31 21:00:00+00
2021-12-31 22:00:00+00
2021-12-31 23:00:00+00
2022-01-01 00:00:00+00
2022-01-01 01:00:00+00
2022-01-01 02:00:00+00
6 rows

So, we have every hour between 2021-12-31 21:00:00 and 2022-01-01 02:00:00 - inclusive.

Then, we can do the following:

WITH calendar AS
(
  SELECT 
    t
  FROM
    GENERATE_SERIES
    (
      '2021-12-31 21:00:00'::TIMESTAMPTZ,
      '2022-01-01 02:00:00',   
      '1 HOUR'
    ) AS tab(t)
)
SELECT
  DATE(c.t) AS dt_cal,
  EXTRACT(HOUR FROM c.t) AS hr_cal,
  ROUND(AVG(r_val), 2)
FROM
  calendar c
LEFT JOIN reading r
  ON   
    EXTRACT(HOUR FROM c.t) = EXTRACT(HOUR FROM r.r_ts)
GROUP BY   dt_cal, EXTRACT(HOUR FROM c.t)
ORDER BY dt_cal, hr_cal;

Result:

dt_cal     hr_cal  round
2021-12-31     21   NULL
2021-12-31     22   NULL
2021-12-31     23   NULL
2022-01-01      0   2.83
2022-01-01      1   4.33
2022-01-01      2   NULL
6 rows

So, we have our averages over the hours for which data is available.

If you arrive into work and want to check the last 3 days, you can do the following:

WITH calendar AS
(
  SELECT 
    t
  FROM
    GENERATE_SERIES
    (
      NOW()::DATE - INTERVAL '3 DAYS',
      NOW()::DATE - INTERVAL '1 HOUR',
      '1 HOUR'
    ) AS tab(t)
)
SELECT 
  t  -- whatever you want here - i.e. the query from above!
FROM calendar;

Result - see fiddle - 72 hour slots = 3 days x 24 hours (= 72).

So, you have every 1 HOUR TIMESTAMP from 3 days ago at 00:00:00 up to 23:00:00 last night.

I also played around with using AVG() as a window function - see the fiddle - can also obtain cumulative averages. These may even be more performant - again, see the EXPLAIN (ANALYZE, BUFFERS, VERBOSE) in the fiddle, but this depends on your own data and on what indexes you want to use.

2
  • What is the reason to generate 10-minute intervals whereas OP needs in grouping by 1-hour intervals?
    – Akina
    Jun 24 at 6:43
  • No reason - that's an error on my part - thanks for pointing it out! Am correcting now! Jun 24 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.