sql - 用户最后值(value)的总和

Question

我的 postgresql 数据库中有这个表:

purchase

userid |    date    | price
---------------------------
     1 | 2016-01-06 |    10
     1 | 2016-01-05 |     5
     2 | 2016-01-06 |    12
     2 | 2016-01-05 |    15

我想要所有用户上次购买价格的总和。对于用户 1，最后一次购买是在 2016-01-06，价格是 10。对于用户 2，最后一次购买是在 2016-01-06，价格是 12。所以 SQL 查询的结果应该是 22。

我如何在 SQL 中执行此操作？

Answer 1

您可以使用窗口函数获取排名数字，然后使用带有 SUM 的普通聚合:

WITH cte AS
(
   SELECT *, RANK() OVER(PARTITION BY userid ORDER BY "date" DESC) AS r
   FROM purchase
)
SELECT SUM(price) AS total
FROM cte
WHERE r = 1;

SqlFiddleDemo

请记住，此解决方案计算的是平局。要让每个用户只购买一次，您需要每个组都有一个不同的列(如 datetime)。但仍然有可能建立关系。

编辑:

处理领带:

CREATE TABLE purchase(
   userid INTEGER  NOT NULL 
  ,date   timestamp  NOT NULL
  ,price  INTEGER  NOT NULL
);
INSERT INTO purchase(userid,date,price) VALUES 
(1, timestamp'2016-01-06 12:00:00',10),
(1,timestamp'2016-01-05',5),
(2,timestamp'2016-01-06 13:00:00',12),
(2,timestamp'2016-01-05',15),
(2,timestamp'2016-01-06 13:00:00',1000)'

注意 RANK() 与 ROW_NUMBER 的区别:

SqlFiddleDemo_RANK SqlFiddleDemo_ROW_NUMBER SqlFiddleDemo_ROW_NUMBER_2

输出:

╔════════╦══════════════╦══════════════╗
║ RANK() ║ ROW_NUMBER() ║ ROW_NUMBER() ║
╠════════╬══════════════╬══════════════╣
║   1022 ║           22 ║         1010 ║
╚════════╩══════════════╩══════════════╝

如果 userid/date 上没有 UNIQUE 索引，则总是有可能(可能很小)出现平局。任何基于 ORDER BY 的解决方案都必须以稳定的方式工作。