在 PostgreSQL 8.4 中替换 FOLLOWING/PRECEEDING 的 SQL 解决方法

Question

我有一个使用 PostgreSQL 9.0 的 FOLLOWING/PRECEDING 语法执行基本移动平均线的查询。令我震惊的是，我发现我们的 pg 服务器运行在 8.4 上，并且在不久的将来没有升级的余地。

因此，我正在寻找对以下内容进行向后兼容查询的最简单方法:

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

这是包含价格和时间戳列的表的基本 7 天移动平均线:

closing_date timestamp
price        numeric
itemname     text

基础的要求是由于我对SQL的基础知识。

Answer 1

Postgres 8.4 already has CTEs.
我建议使用它，计算 CTE 中的每日平均值，然后自加入过去一周的所有天数(存在或不存在)。最后，再次汇总为每周平均值:

WITH cte AS (
   SELECT closing_date::date AS closing_day
        , sum(price)   AS day_sum
        , count(price) AS day_ct
   FROM   auction_prices
   WHERE  itemname = 'iphone6_16gb'
   AND    price <= 1000  -- including upper border
   GROUP  BY 1
   )   
SELECT d.closing_day
     , CASE WHEN d.day_ct > 1
            THEN d.day_sum / d.day_ct
            ELSE d.day_sum
       END AS avg_day         -- also avoids division-by-zero
     , CASE WHEN sum(w.day_ct) > 1
            THEN sum(w.day_sum) / sum(w.day_ct)
            ELSE sum(w.day_sum)
       END AS week_avg_proper  -- also avoids division-by-zero
FROM   cte d
JOIN   cte w ON w.closing_day BETWEEN d.closing_day - 6 AND d.closing_day
GROUP  BY d.closing_day, d.day_sum, d.day_ct
ORDER  BY 1;

SQL Fiddle. (在 Postgres 9.3 上运行，但也应该在 8.4 中运行。)

注意事项

• 我使用了不同(正确)的算法来计算每周平均值。请参阅我的 comment to the question 中的注意事项.

• 这会计算基表中每天 的平均值，包括极端情况。但是好几天都没有排。

• 可以从 date 中减去 integer:d.closing_day - 6。 (但不是来自 varchar 或 timestamp!)

• 您将 timestamp 列称为 closing_date 相当令人困惑 - 它不是 date，而是 timestamp。以及 time_series 用于具有 date 值的结果列？我改用 closing_day ...

• 请注意我如何计算价格 count(price)，不是 items COUNT(itemname) - 如果任一列可以为 NULL，这将是偷偷摸摸的错误的入口点。如果两者都可以为 NULL，count(*) 会更好。

• CASE 构造避免了被零除错误，只要您正在计算的列可以 为 NULL，这种错误就可能发生。为此，我可以使用 COALESCE，但在使用它的同时，我也将案例简化为恰好 1 个价格。