PostgreSQL 聚合或窗口函数只返回最后一个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8320569/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL aggregate or window function to return just the last value
提问by EMP
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
我在 PostgreSQL 9.1 中使用带有 OVER 子句的聚合函数，我只想返回每个窗口的最后一行。该last_value()窗口的功能听起来像它可能做我想做的-但事实并非如此。它为窗口中的每一行返回一行，而我只需要每个窗口一行
A simplified example:
一个简化的例子：
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
I want this to return one row:
我希望这返回一行：
1, 'just want this'
回答by Erwin Brandstetter
DISTINCTplus window function
DISTINCT加窗函数
Add a DISTINCTclause:
添加一个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);
More about DISTINCT:
更多关于DISTINCT：
Simpler and faster with DISTINCT ON
使用更简单、更快 DISTINCT ON
PostgreSQL also has this extension of the SQL standard:
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;
More about DISTINCT ONand possibly faster alternatives:
更多关于DISTINCT ON和可能更快的替代品：
Simple case with plain aggregate
带有普通骨料的简单案例
Ifyour 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;
