PostgreSQL中的自定义聚合函数

最新条目的排名（按时间戳记）-最旧条目的排名（按时间戳记）

现有功能可以通过多种方式来实现。您可以使用现有的窗口函数

first_value()

last_value()

DISTINCT

DISTINCT ON

SELECt DISTINCT ON (userid)       userid     , last_value(rank) OVER w       - first_value(rank) OVER w AS rank_deltaFROM   rankingsWINDOW w AS (PARTITION BY userid ORDER BY ts  ROWS BETWEEN UNBOUNDED PRECEDING  AND  UNBOUNDED FOLLOWING);

注意窗口功能的自定义框架！

或者，您可以在子查询和JOIN中使用基本的聚合函数：

SELECt userid, r2.rank - r1.rank AS rank_deltaFROM  (  SELECt userid       , min(ts) AS first_ts       , max(ts) AS last_ts   FROM  rankings   GROUP BY 1   ) subJOIN   rankings r1 USING (userid)JOIN   rankings r2 USING (userid)WHERe  r1.ts = first_tsAND    r2.ts = last_ts;

假设唯一

(userid, rank)

SQL Fiddle演示。

忍者武士

…也称为“ 7 Samurai”，
每个请求在注释中， 每个用户ID 仅适用于 最后七个行 （如果有更少，则可以找到尽可能多的 行 ）：

同样，许多可能的方式之一。但我认为这是最短的方法之一：

SELECt DISTINCT ON (userid)       userid     , first_value(rank) OVER w       - last_value(rank)  OVER w AS rank_deltaFROM   rankingsWINDOW w AS (PARTITION BY userid ORDER BY ts DESC  ROWS BETWEEN CURRENT ROW AND 7 FOLLOWING)ORDER  BY userid, ts DESC;

请注意相反的排序顺序。第一行是“最新”条目。我跨过一帧（最多）7行，并仅使用来选择最新条目的结果

DISTINCT ON

SQL Fiddle演示。