PostgreSQL聚合或窗口函数仅返回最后一个值 [英] PostgreSQL aggregate or window function to return just the last value

问题描述

我正在PostgreSQL 9.1中使用带有OVER子句的聚合函数，并且只想返回每个窗口的最后一行。 last_value（）窗口函数听起来像它可以完成我想要的操作，但是没有。它为窗口中的每一行返回一行，而我只希望每个窗口一行

I'm using an aggregate function with the OVER clause in PostgreSQL 9.1 and I want to return just the last row for each window. The last_value() window function sounds like it might do what I want - but it doesn't. It returns a row for each row in the window, whereas I want just one row per window

一个简化的示例：

SELECT a, some_func_like_last_value(b) OVER (PARTITION BY a ORDER BY b)
FROM
(
    SELECT 1 AS a, 'do not want this' AS b
    UNION SELECT 1, 'just want this'
) sub

我希望它返回一行：

1, 'just want this'

推荐答案

DISTINCT 加上窗口函数

添加 DISTINCT 子句：

SELECT DISTINCT a
     , last_value(b) OVER (PARTITION BY a ORDER BY b
                           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM  (
   VALUES
     (1, 'do not want this')
    ,(1, 'just want this')
   ) sub(a, b);

有关 DISTINCT 的更多信息：

• PostgreSQL：按分钟查询的运行行数

• PostgreSQL: running count of rows for a query 'by minute'

PostgreSQL也具有SQL标准的扩展：

PostgreSQL also has this extension of the SQL standard:

SELECT DISTINCT ON (a)
       a, b
FROM  (
   VALUES
     (1, 'do not want this')
   , (1, 'just want this')
   ) sub(a, b)
ORDER  BY a, b DESC;

有关 DISTINCT ON 的更多信息以及可能更快的替代方法：

More about DISTINCT ON and possibly faster alternatives:

• 在每个GROUP BY组中选择第一行？

• Select first row in each GROUP BY group?

如果，您的案例实际上和演示一样简单（并且您不需要最后一行的其他列），普通的聚合函数会更简单：

If your case is actually as simple as your demo (and you don't need additional columns from that last row), a plain aggregate function will be simpler:

SELECT a, max(b)
FROM  (
   VALUES
     (1, 'do not want this')
   , (1, 'just want this')
   ) sub(a, b)
GROUP  BY a;

这篇关于PostgreSQL聚合或窗口函数仅返回最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！