PostgreSQL - Query data from a range and bring a boundary of more spaced lines

Navigation
0

Good morning guys, how are you?

I have a performance problem on a system and need to solve customizing a SQL.

Let's imagine the following scenario: - A table with millions of records, each record has an ID (PK) and a TIMESTAMP.

  • I currently check the data for the TIMESTAMP range, for example last week's records.

  • We expect 1,000,000 (one million) records in that one-week period. I currently redeem these values to create a chart.

Let's agree that this is costly and somewhat stupid, since from one record to the other there is minimal change of values and I need to create a line chart where the value of X is TIMESTAMP.

DOUBT: Can you tell the bank to refer to this range but bring me up to 1,000 records only? But these records would have to be spaced out so they could understand the whole period, it would be a kind of sampling. Attempting to process this in system programming is sendable.

From now on I thank you so that you can help me, Gustavo Ferreira.

    
asked by anonymous 31.03.2016 / 15:39

2 answers

1

You can use Window Functions to pick up one line for every 100 rows or you can use some type of aggregation in the database, such as transforming TIMESTAMP into a date and grouping the values by day or by hour using a mean aggregation ( AVG )

    
31.03.2016 / 15:55
0

I have decided as follows:

AVG (t.h24 :: NUMERIC) AS value FROM telemetry t WHERE t.date > = (CURRENT_TIMESTAMP - INTERVAL '168 HOUR') GROUP BY date ORDER BY date DESC

In this case I am searching for data 7 days ago. The data is grouped by date and time because there are many records with the same time.

When I need a longer period than 7 days I group by day, taking the daily average of the value I need.

Thanks for the contribution Renato.

    
01.04.2016 / 16:23
Loop problem with $ stateChangeStart zoom button in / out of a div