时间戳

首页 > 解决方案 > Postgresql：平均超过日期限制，分组依据

postgresql - Postgresql：平均超过日期限制，分组依据

问题描述

我有一张这样的桌子

item_id    date          number
1          2000-01-01    100
1          2003-03-08    50
1          2004-04-21    10
1          2004-12-11    10
1          2010-03-03    10
2          2000-06-29    1
2          2002-05-22    2
2          2002-07-06    3
2          2008-10-20    4

我正在尝试获取过去 3 个日期中每个 uniq Item_id 的平均值。这很困难，因为两者之间缺少日期，因此一系列硬编码日期并不总是有效。

我期望结果如下：

item_id   MyAverage
1          10
2          3

我真的不知道该怎么做。目前我设法为一个项目做到这一点，但我无法将其扩展到多个项目：

SELECT AVG(MyAverage.number) FROM ( 
  SELECT date,number 
  FROM item_list 
  where item_id = 1 
  ORDER BY date  DESC limit 3
) as MyAverage;

我的主要问题是泛化"DESC limit 3"over a group by id。

试图 ：

SELECT item_id,AVG(MyAverage.number) 
FROM ( 
  SELECT item_id,date,number 
  FROM item_list 
  ORDER BY date  DESC limit 3) as MyAverage 
GROUP BY item_id;

限制是把事情搞砸了。

我已经在日期和日期之间使用它“工作”，但它没有按我想要的方式工作，因为我需要一个限制而不是硬编码的日期..

谁能帮忙

标签： postgresql

解决方案

您可以使用为 ID 的最后日期row_number()分配1给3记录，然后对其进行过滤。

SELECT x.item_id,
       avg(x.number)
       FROM (SELECT il.item_id,
                    il.number,
                    row_number() OVER (PARTITION BY il.item_id
                                       ORDER BY il.date DESC) rn
                    FROM item_list il) x
       WHERE x.rn BETWEEN 1 AND 3
       GROUP BY x.item_id;

推荐阅读