vendredi 8 mai 2015

SQL workaround to substitute FOLLOWING / PRECEEDING in PostgreSQL 8.4

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