Postgresql中的第一个和最后一个值聚合函数可正确使用NULL值 [英] First and last value aggregate functions in postgresql that work correctly with NULL values

问题描述

我知道在 postgresql

我的问题是，它们无法按我的需要工作。我可以使用一个postgresql向导的帮助。我使用的是PostgreSQL 9.2-以防该版本使提供解决方案更加容易。

查询

 从车辆中选择v.id，v.active，v.reg_no，p.install_date，p.remove_date 
 v离开连接期p on 
（v.id = p.car_id ）
，其中v.id = 1 
由v.id订购，p.install_date asc 
  

返回6行：

  id，active，reg_no，install_date，remove_date 
 1，TRUE，正确，2008-08-02 11:13:39，2009-02-09 10:32:32 
 .... 
 1，是，有些东西，2010-08-15 21:16:40 ，2012-08-25 07:44:30 
 1，TRUE，something，2012-09-10 17:05:12，NULL 
  

但是当我使用汇总查询时：

 选择max（id）作为id ，last（active）为活动，first（install_date）为install_date，last（remove_date）为remove_date 
 from（
选择v.id，v.active，v.reg_no，p.install_date，p。从车辆中移除remove_date 
 v离开
加入期p on（vi d = p.car_id）
，其中v.id = 1 
（按v.id排序，p.install_date asc 
）为bar 
，按id 
  

然后我得到

  id，active，install_date，remove_date 
 1，TRUE，2008-08-02 11:13:39，2012-08-25 07:44:30 
  

不是

  id，活动，安装日期，删除日期
 1，TRUE，2008-08-02 11：13：39，NULL 
  

如我所料

如果最后一行的值为空，而不是最后一个存在的值，是否有可能更改聚合函数以产生NULL？

EDIT1

罗马·佩卡（Roman Pekar）提供了我的问题的替代解决方案，但这不符合我的需求。原因是-我简化了原始查询。但是我运行的查询更加复杂。我意识到我的问题可能有其他解决方案-这就是为什么要更新帖子以包含原始的，更复杂的查询。

 选择partner_id，sum（active）为活动，sum（installed）为安装，sum（removed）为移除
 from（
选择
 pc.partner_id作为partner_id，
 v.id，
 v.active = TRUE THEN 1 ELSE 0 END为活动时的情况，
首次安装（p.install_date）和'2014-01-01'之间的情况，然后1 ELSE 0 END安装，
最后一次（p.remove_date）在'2013- 12-01'和'2014-01-01'然后从车上移走
，然后删除剩余的1个0结束v 
左加入期p on（v.id = p.car_id）
左加入partner_clients pc（pc.account_id = v.client_id）
由pc.partner_id，v.id，v.active 
组）作为foo group by partner_id 
  

您实际上看到的是，我实际上需要获得几辆汽车的第一笔和最后一笔的价值，而不是一辆，最后要归还车主对这些车辆的计数

/ EDIT1

解决方案

感谢达米安（Damien），我阅读了有关创建函数的Postgresql文档（源），并修改了以下函数：

 创建或替换功能public.last_agg（anyelement，anyelement）
返回anyelement语言sql不可变字符串AS $$ 
选择$ 2; 
 $$; 
 
创建聚合public.last（
 sfunc = public.last_agg，
 basetype = anyelement，
 stype = anyelement 
）; 
  

至：

 创建或替换功能public.last_agg（anyelement，anyelement）
返回以空输入形式调用的anyelement语言sql，如$$ 
选择$ 2; 
 $$; 
 
创建聚合public.last（
 sfunc = public.last_agg，
 basetype = anyelement，
 stype = anyelement 
）; 
  

似乎已经解决了我的麻烦。

感谢阅读。

I know there are aggregate functions for getting last and first value of rows in 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.

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:

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?

EDIT1

Roman Pekar offered alternative solution to 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:

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

解决方案

Thanks to Damien i went reading postgresql documentation about creating functions (source) and fiddled with the function changing it from:

CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

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 $2;
$$;

CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

and it seems to have fixed my troubles.

Thanks for reading.

这篇关于Postgresql中的第一个和最后一个值聚合函数可正确使用NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！