CODEWITHSUNDEEP
databasepostgresqlmoving-averagetemporal
A Kntu
A Kntu

Reputation: 689

Moving average in Temporal database in PostgreSQL

How can I apply the moving average in temporal database. My data includes temperature and I want to apply moving average for every 15 records.

Upvotes: 3

Views: 543

Answers (2)

A.Amidi
A.Amidi

Reputation: 2522

 WITH moving_avrag AS (
  SELECT 0 AS [lag] UNION ALL
  SELECT 1 AS [lag] UNION ALL
  SELECT 2 AS [lag] UNION ALL
  SELECT 3 AS [lag] --ETC
)
SELECT
  DATEADD(day,[lag],[date]) AS [reference_date],
  [otherkey1],[otherkey2],[otherkey3],
  AVG([value1]) AS [avg_value1],
  AVG([value2]) AS [avg_value2]
FROM [data_table]
CROSS JOIN moving_avg
GROUP BY [otherkey1],[otherkey2],[otherkey3],DATEADD(day,[lag],[date])
ORDER BY [otherkey1],[otherkey2],[otherkey3],[reference_date];

Upvotes: 2

Bhavik Ambani
Bhavik Ambani

Reputation: 6657

You can fire query as below

marc=# SELECT entity, name, salary, start_date,
        avg(salary) OVER (ORDER BY entity, start_date
                          ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
        FROM salary;

 entity     | name      | salary  | start_date    |         avg         
 -----------+-----------+---------+---------------+----------------------
 Accounting | millicent |  850.00 | 2006-01-01    | 825.0000000000000000
 Accounting | jack      |  800.00 | 2010-05-01    | 916.6666666666666667
 R&D        | tom       | 1100.00 | 2005-01-01    | 966.6666666666666667
 R&D        | john      | 1000.00 | 2008-07-01    | 933.3333333333333333
 R&D        | maria     |  700.00 | 2009-01-01    | 733.3333333333333333
 R&D        | kevin     |  500.00 | 2009-05-01    | 633.3333333333333333
 R&D        | marc      |  700.00 | 2010-02-15    | 600.0000000000000000

Upvotes: 3

Related Questions