sql - PostgreSQL 中的自定义聚合函数

Question

是否可以在 PostgreSQL 中编写一个聚合函数，通过从当前(列中的第一个值)中减去初始值(列中的最后一个值)来计算增量值？它将应用于这样的结构

rankings (userId, rank, timestamp)

可以像这样使用

SELECT userId, custum_agg(rank) OVER w 
FROM rankings
WINDOWS w AS (PARTITION BY userId ORDER BY timstamp desc)

为 userId 返回最新条目的排名(按时间戳)- 最旧条目的排名(按时间戳)

谢谢!

Answer 1

the rank of the newest entry (by timestamp) - rank of the oldest entry (by timestamp)

有很多方法可以利用现有函数实现这一点。您可以使用现有的 window functions first_value() and last_value() , 与 DISTINCT 或 DISTINCT ON 相结合以获得它而无需连接和子查询:

SELECT DISTINCT ON (userid)
       userid
     , last_value(rank) OVER w  
     - first_value(rank) OVER w AS rank_delta
FROM   rankings
WINDOW w AS (PARTITION BY userid ORDER BY ts
             ROWS BETWEEN UNBOUNDED PRECEDING
             AND  UNBOUNDED FOLLOWING);

注意自定义 frames for the window functions !

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

SELECT userid, r2.rank - r1.rank AS rank_delta
FROM  (
  SELECT userid
       , min(ts) AS first_ts
       , max(ts) AS last_ts
   FROM  rankings
   GROUP BY 1
   ) sub
JOIN   rankings r1 USING (userid)
JOIN   rankings r2 USING (userid)
WHERE  r1.ts = first_ts
AND    r2.ts = last_ts;

假设 (userid, rank) 是唯一的，否则您的要求会不明确。

SQL Fiddle demo.

七忍之武士

^{... a.k.a. "7 Samurai"}
根据评论中的请求，仅每个用户标识的最后七行相同(或者尽可能多，如果更少的话):

同样，这是许多可能的方法之一。但我相信这是最短的之一:

SELECT DISTINCT ON (userid)
       userid
     , first_value(rank) OVER w  
     - last_value(rank)  OVER w AS rank_delta
FROM   rankings
WINDOW 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 demo.