First and last value aggregate functions in postgresql that work correctly with NULL values

Question

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

You've linked to a page that shows how you can implement the function yourself, if you wanted to, and points out how NULLs are being ignored (by referring to STRICT). What more do you want? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Dec 3, 2013 at 8:15
Yes thats how far my postgresql knowledge goes - i know how to copy-paste functions from wiki to phpgadmin :P. That is exactly what i did. If you look the query, that i posted, you'll see me using those functions there. What i do not know, though, is what they actually do/mean and how to make them work like i need to. This is why i use stackoverflow :P. What i want is that someone told me how to change those functions so they would do what i need. So what do i need to do with this STRICT to fix my situation? — Odif Yltsaeb
– Odif Yltsaeb, Commented Dec 3, 2013 at 8:19
I guess i should thank you Damien :P. I switched STRICT for CALLED ON NULL INPUT in those aggregate functions and got them working. — Odif Yltsaeb
– Odif Yltsaeb, Commented Dec 3, 2013 at 9:42

roman · Accepted Answer · 2013-12-03 08:23:30Z

6

You could use window functions lead() and lag() to check first and last record, for example:

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

sql fiddle demo

answered Dec 3, 2013 at 8:23

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Odif Yltsaeb Over a year ago

Thanks. This works in the case i presented - in case of one car. But my problem is, that i'm actually joining one other table there, so the results of inner query actually contain several vehicles. I'll update the original post. Perhaps you can come up with better answer - or perhaps you can offer insight as how to modify those first and last aggregate functions.

Odif Yltsaeb Over a year ago

Modified the original post. If i use your query as subquery, then all the values for other vehicles install and remove dates are NULL.

Odif Yltsaeb · Accepted Answer · 2013-12-03 09:52:16Z

2

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.

answered Dec 3, 2013 at 9:52

Odif Yltsaeb

5,68614 gold badges55 silver badges82 bronze badges

1 Comment

Jan Frederik Over a year ago

Alas, this fix only works for "last", not for "first". Because when performing the aggregation, postgresql calls the function first_agg for the first time with arguments (NULL, value_of_the_first_record), so first_agg always returns NULL, also for all subsequent records. Possible workaround: use "last" anyway, with descending order: select last(my_column order by order_column desc), which is logically equivalent to select first(my_column order by order_column)

Collectives™ on Stack Overflow

First and last value aggregate functions in postgresql that work correctly with NULL values

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related