Пользовательская функция агрегации в PostgreSQL

Можно ли написать агрегатную функцию в PostgreSQL, которая будет вычислять значение дельта, вычитая начальное (последнее значение в столбце) из текущего (первое значение в столбце)? Это применимо к такой структуре, как это

rankings (userId, rank, timestamp)

И может использоваться как

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

возврат для пользователя. Ранг последней записи (по метке времени) - ранняя старая запись (по метке времени)

Спасибо!

Ответ 1

ранг самой новой записи (по метке времени) - ранг самой старой записи (по метке времени)

Существует много способов добиться этого с помощью существующих функций. Вы можете использовать существующие функции окна first_value() и 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);

Обратите внимание на пользовательские фреймы для функций окна!

Или вы можете использовать основные агрегированные функции в подзапросе и 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.

Шичинин без самураев

^{... a.k.a. "7 Самурай"}
По запросу в комментариях то же самое только для последних семи строк для userid (или столько, сколько их можно найти, если их меньше):

Опять один из многих возможных способов. Но я считаю, что это один из самых коротких:

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.

Ответ 2

Вы можете сделать это с помощью JOIN и DISTINCT ON в Postgres. Запрос GRP дает вам последние значения rank для каждого userID поэтому просто присоедините его к rankings по user_id и значениям user_id.

SELECT rankings.userId, 
       rankings.rank-GRP.rank as delta,
       rankings.timestamp
FROM rankings
JOIN
(
    SELECT DISTINCT ON (userId)  userId, rank, timestamp
    FROM rankings
    ORDER BY userId, timestamp DESC
) as GRP ON rankings.userId=GRP.userId

SQLFiddle demo