PostgreSQL 聚合或窗口函数仅返回最后一个值

我在 PostgreSQL 9.1 中使用带有 OVER 子句的聚合函数，并且我想仅返回每个窗口的最后一行。这last_value()窗口函数听起来好像它可以做我想要的事情 - 但事实并非如此。它为窗口中的每一行返回一行，而我只想每个窗口一行

一个简化的例子：

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 http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-DISTINCT加窗函数

Add a DISTINCT clause:

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：“按分钟”运行查询的行数 https://stackoverflow.com/questions/8193688/postgresql-running-count-of-rows-for-a-query-by-minute/8194088#8194088

更简单、更快DISTINCT ON http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-DISTINCT

PostgreSQL 也有 SQL 标准的扩展：

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以及可能更快的替代方案：

• 选择每个 GROUP BY 组中的第一行？ https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564

具有普通骨料的简单情况

If您的情况实际上与您的演示一样简单（并且您不需要最后一行中的其他列），简单的聚合函数会更简单：

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