I have a query that does a basic moving average using the FOLLOWING
/ PRECEDING
syntax of PostgreSQL 9.0. To my horror I discovered our pg server runs on 8.4 and there is no scope to get an upgrade in the near future.
I am therefore looking for the simplest way to make a backwards compatible query of the following:
SELECT time_series,
avg_price AS daily_price,
CASE WHEN row_number() OVER (ORDER BY time_series) > 7
THEN avg(avg_price) OVER (ORDER BY time_series DESC ROWS BETWEEN 0 FOLLOWING
AND 6 FOLLOWING)
ELSE NULL
END AS avg_price
FROM (
SELECT to_char(closing_date, 'YYYY/MM/DD') AS time_series,
SUM(price) / COUNT(itemname) AS avg_price
FROM auction_prices
WHERE itemname = 'iphone6_16gb' AND price < 1000
GROUP BY time_series
) sub
It is a basic 7-day moving average for a table containing price and timestamp columns:
closing_date timestamp
price numeric
itemname text
The requirement for basic is due to my basic knowledge of SQL.
Aucun commentaire:
Enregistrer un commentaire