sql - 一段时间内的平均值非常慢

Question

我正在尝试使用 postgresql 计算一段时间内的多个平均值(每个 id 一个)。

我有一个有效的查询，但它非常非常慢。 (在我的笔记本电脑上 3 分钟，在服务器上 30 秒..)

我想做的是计算过去 X 天的平均值。可能存在日期差距(对于没有数据的星期六和星期日)，但我仍然需要最后一个 X。因此，例如 1 个月将是 20 天，等等。

为了做到这一点，我一直在使用 row_number() OVER (PARTITION BY item_id ORDER BY tdate DESC) 并仅选择 BETWEEN 0 AND X(X 是我需要的最大日期数)

我的完整查询是:

SELECT x.item_id AS id,avg(x.value) AS result FROM 

(SELECT il.item_id, il.value,  row_number() OVER (PARTITION BY 
il.item_id  ORDER BY il.tdate DESC) rn 

FROM item_prices il) x

WHERE x.rn BETWEEN 0 AND 50 GROUP BY x.item_id order by x.item_id ASC;

正如我所说，我的问题是它非常慢。我怀疑 PSQL 正在为每个 id 重新计算 SELECT il.item_id, il.value, row_number() OVER (PARTITION BY il.item_id ORDER BY il.tdate DESC，这就是它这么慢的原因。

我一直在阅读有关平均值的信息并尝试了一些方法 ( this ) 但没有成功。

有人知道如何使查询更快吗？

我的表是这样的:

ID,item_id,value,tdate

解释:

GroupAggregate  (cost=7707688.82..8934895.66 rows=36453 width=36)
  Group Key: x.item_id
   ->  Subquery Scan on x  (cost=7707688.82..8933564.38 rows=175125 width=14)
    Filter: ((x.rn >= 1) AND (x.rn <= 50))
    ->  WindowAgg  (cost=7707688.82..8408189.14 rows=35025016 width=26)
          ->  Sort  (cost=7707688.82..7795251.36 rows=35025016 width=18)
                Sort Key: il.item_id, il.tdate DESC
                ->  Seq Scan on item_prices il  (cost=0.00..1163862.16 rows=35025016 width=18)

Answer 1

What i'm trying to do is compute the average over the last X days.

这表明:

SELECT ip.item_id AS id, avg(x.value) AS result
FROM item_prices ip
WHERE ip.tdate <= current_date AND
      ip.tdate > current_date - X * interval '1 day'
GROUP BY ip.item_id;

不过，我看不出您的实际查询与您提出的问题有什么关系。