我在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加窗功能
添加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: running count of rows for a query ‘by minute’
使用DISTINCT ON更简单,更快捷
SELECT DISTINCT ON (a)
a,b
FROM (
VALUES
(1,b)
ORDER BY a,b DESC;
有关DISTINCT ON和可能更快的替代方案的更多信息：
> Select first row in each GROUP BY group?
普通聚合的简单案例
如果您的案例实际上与您的演示一样简单(并且您不需要来自该最后一行的其他列),则简单聚合函数将更简单：
SELECT a,max(b)
FROM (
VALUES
(1,b)
GROUP BY a;
