sql - 聚合函数按顺序获取两行的差异或比率

Question

我有一张包含价格、项目和日期的表格。这方面的一个例子是:

AA, 1/2/3024, 1.22
AA, 1/3/3024, 1.23
BB, 1/2/3024, 4.22
BB, 1/3/3024, 4.23

在数据中，每个价格只有两行，并且按日期排序。我如何将这个数据集压缩到一个产品行中，以显示上次价格与之前价格的差异？ [这也适用于比率，因此 AA 会产生 1.23/1.22]。

结果应该是这样的

AA, todays price-yesterdays price

尽管是求和函数，但列表上没有减法函数。

我正在使用 postgres 9.1。

Answer 1

由于每个价格只有两行，这可以更简单和更快:

SELECT n.item, n.price - o.price AS diff, n.price / o.price AS ratio
FROM   price n                 -- "new"
JOIN   price o USING (item)    -- "old"
WHERE  n.day > o.day;

->SQLfiddle

此表单具有额外的好处，您可以直接使用两行中的所有列。

---

对于更复杂的场景(对此不是必需的)，您可以使用已经指出的窗口函数。这是一个比建议的方法更简单的方法:

SELECT DISTINCT ON (item)
       item
      ,price - lead(price) OVER (PARTITION BY item ORDER BY day DESC) AS diff
FROM   price
ORDER  BY item, day DESC;

这里只需要一个窗口函数。和一个查询级别，因为 DISTINCT ON在窗口函数之后应用。窗口中的排序顺序与整体排序顺序一致，有助于提高性能。