[Solved] How can I average table rows by timestamp?

EverSQL Database Performance Knowledge Base

How can I average table rows by timestamp?

Database type:

I have a table in PostgreSQL that is created like this:

-- Table: myTable
-- DROP TABLE myTable;
CREATE TABLE myTable
(
  "TimeStamp" double precision,
  "Temperature" double precision,
  "Pressure" double precision,
  "Milligrams" double precision,
  table_pkey serial NOT NULL,
  CONSTRAINT myTable_pkey PRIMARY KEY (table_pkey)
);

Where TimeStamp is some number of elapsed seconds since a particular day.

I see how to average some number of table rows together to produce a condensed table. For example,

SELECT AVG("TimeStamp") "Seconds", AVG("Temperature") "Temperature",
       AVG("Pressure") "Pressure", AVG("Milligrams") "Milligrams"
FROM (SELECT ROW_NUMBER() OVER(ORDER BY null) - 1 rn, * FROM myTable) t 
GROUP BY (rn/120) 
ORDER BY rn/120

would output a table condensed by a factor of 120 input rows per output row.

Instead of averaging some number of rows together, I want to average some spans of TimeStamp together. To produce, for example, a table where each row contains the average values over each hour of the day.

EDIT

This, combined with changing the timestamp column to type typestamp seems to work:

SELECT date_trunc('hour', "Acquired"), AVG("Temperature"), 
AVG("Pressure"), AVG("Milligrams")
FROM myTable
WHERE 1=1
GROUP BY date_trunc('hour', "Acquired")
ORDER BY 1

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 11): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Avoid Subqueries (query line: 7): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
The optimized query:
SELECT
        AVG(t."TimeStamp") "Seconds",
        AVG("Temperature") "Temperature",
        AVG("Pressure") "Pressure",
        AVG("Milligrams") "Milligrams" 
    FROM
        (SELECT
            ROW_NUMBER() OVER (ORDER 
        BY
            NULL) - 1 rn,
            * 
        FROM
            myTable) t 
    GROUP BY
        (rn / 120) 
    ORDER BY
        rn / 120

Related Articles



* original question posted on StackOverflow here.