postgresql 中的第一个和最后一个值聚合函数可以正确处理 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20345859/
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
First and last value aggregate functions in postgresql that work correctly with NULL values
提问by Odif Yltsaeb
I know there are aggregate functions for getting last and first value of rows in postgresql
我知道在postgresql 中有用于获取行的最后一个和第一个值的聚合函数
My problem is, that they do not work as i need. And i could use the help of one postgresql wizard. I'm using postgresql 9.2 - in case the version makes offering solution easyer.
我的问题是，它们不能按我的需要工作。我可以使用一位 postgresql 向导的帮助。我正在使用 postgresql 9.2 - 以防该版本使提供解决方案更容易。
Query
询问
select v.id, v.active, v.reg_no, p.install_date, p.remove_date
from vehicle v
left join period p on (v.id = p.car_id)
where v.id = 1
order by v.id, p.install_date asc
Returns 6 rows:
返回 6 行：
id, active, reg_no, install_date, remove_date
1, TRUE, something, 2008-08-02 11:13:39, 2009-02-09 10:32:32
....
1, TRUE, something, 2010-08-15 21:16:40, 2012-08-25 07:44:30
1, TRUE, something, 2012-09-10 17:05:12, NULL
But when i use aggregating query:
但是当我使用聚合查询时：
select max(id) as id, last(active) as active, first(install_date) as install_date, last(remove_date) as remove_date
from (
select v.id, v.active, v.reg_no, p.install_date, p.remove_date
from vehicle v
left join period p on (v.id = p.car_id)
where v.id = 1
order by v.id, p.install_date asc
) as bar
group by id
Then i get
然后我得到
id, active, install_date, remove_date
1, TRUE, 2008-08-02 11:13:39, 2012-08-25 07:44:30
not
不是
id, active, install_date, remove_date
1, TRUE, 2008-08-02 11:13:39, NULL
as i expected
正如我所料
Is it possible to change the aggregate functions somehow to yield NULL if the value of last row is null, not last existing value?
如果最后一行的值为空，而不是最后一个现有值，是否可以以某种方式更改聚合函数以产生 NULL？
EDIT1
编辑1
Roman Pekaroffered alternative solutionto my problem, but that does not fit my needs. The reason is - i simplified the original query. But the query i run is more complex. I realise that there might be alternative solutions to my problem - this why is update the post to include the original, more complex, query. Which is:
Roman Pekar为我的问题提供了替代解决方案，但这不符合我的需求。原因是 - 我简化了原始查询。但是我运行的查询更复杂。我意识到我的问题可能有其他解决方案 - 这就是为什么更新帖子以包含原始的、更复杂的查询。这是：
select partner_id, sum(active) as active, sum(installed) as installed, sum(removed) as removed
from (
select
pc.partner_id as partner_id,
v.id,
CASE WHEN v.active = TRUE THEN 1 ELSE 0 END as active,
CASE WHEN first(p.install_date) BETWEEN '2013-12-01' AND '2014-01-01' THEN 1 ELSE 0 END as installed,
CASE WHEN last(p.remove_date) BETWEEN '2013-12-01' AND '2014-01-01' THEN 1 ELSE 0 END as removed
from vehicle v
left join period p on (v.id = p.car_id)
left join partner_clients pc on (pc.account_id = v.client_id)
group by pc.partner_id, v.id, v.active
) as foo group by partner_id
As you can see, i actually need to get first and last value of several vehicles not one and in the end aggregate the counts of those vehicles by the owners of those vehicles.
如您所见，我实际上需要获得几辆车的第一个和最后一个值，而不是一个，最后汇总这些车辆的所有者对这些车辆的计数。
/EDIT1
/编辑1
采纳答案by Odif Yltsaeb
Thanks to Damien i went reading postgresql documentation about creating functions (source) and fiddled with the function changing it from:
感谢 Damien，我阅读了有关创建函数（源代码）的postgresql 文档，并修改了将其从以下位置更改的函数：
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT ;
$$;
CREATE AGGREGATE public.last (
sfunc = public.last_agg,
basetype = anyelement,
stype = anyelement
);
to:
到：
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE CALLED ON NULL INPUT AS $$
SELECT ;
$$;
CREATE AGGREGATE public.last (
sfunc = public.last_agg,
basetype = anyelement,
stype = anyelement
);
and it seems to have fixed my troubles.
它似乎解决了我的烦恼。
Thanks for reading.
谢谢阅读。
回答by Roman Pekar
You could use window functions lead()and lag()to check first and last record, for example:
您可以使用窗口函数lead()并lag()检查第一条和最后一条记录，例如：
select
max(a.id) as id,
max(a.first) as first,
max(a.last) as last
from (
select
v.id,
case when lag(v.id) over(order by v.id, p.install_date) is null then p.install_date end as first,
case when lead(v.id) over(order by v.id, p.install_date) is null then p.remove_date end as last
from vehicle v
left join period p on (v.id = p.car_id)
where v.id = 1
) as a
