PostgreSQL中的自定义聚合函数 [英] Custom aggregate function in PostgreSQL

问题描述

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

Is it possible to write an aggregate function in PostgreSQL that will calculate a delta value, by substracting the initial (last value in the column) from the current(first value in column) ? It would apply on a structure like this

rankings (userId, rank, timestamp)

并且可以像

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

为userId返回最新条目的等级（按时间戳记）-最旧条目的等级（按时间戳记）

returning for an userId the rank of the newest entry (by timestamp) - rank of the oldest entry (by timestamp)

谢谢！

推荐答案

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

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

现有功能可以通过多种方式实现。
您可以使用现有的窗口函数 first_value（）和 last_value（） ，与 DISTINCT 或 DISTINCT ON 以获取没有联接和子查询的内容：

There are many ways to achieve this with existing functions. You can use the existing window functions first_value() and last_value(), combined with DISTINCT or DISTINCT ON to get it without joins and subqueries:

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);

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

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

Or you can use basic aggregate functions in a subquery and 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;

假设唯一的（用户名，等级），否则您的要求就会模棱两可。

Assuming unique (userid, rank), or your requirements would be ambiguous.

SQL Fiddle演示。

^{... 又名 7 Samurai}

注释中的每个请求，仅对每个用户ID的最后七行相同（或最多可以找到） ，如果数量更少）：

^{... a.k.a. "7 Samurai"}
Per request in the comments, the same for only the last seven rows per userid (or as many as can be found, if there are fewer):

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

Again, one of many possible ways. But I believe this to be one of the shortest:

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 中最新条目的结果。

Note the reversed sort order. The first row is the "newest" entry. I span a frame of (max.) 7 rows and pick only the results for the newest entry with DISTINCT ON.

SQL Fiddle演示。

这篇关于PostgreSQL中的自定义聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！