PostgreSQL, Custom aggregate

Question

Is here way to get function like custom aggregate when MAX and SUM is not enough to get result? Here is my table:

DROP TABLE IF EXISTS temp1;
CREATE TABLE temp1(mydate text, code int, price decimal);
INSERT INTO temp1 (mydate, code, price) VALUES 
('01.01.2014 14:32:11', 1,   9.75), 
(                   '', 1,   9.99), 
(                   '', 2,  40.13), 
('01.01.2014 09:12:04', 2,  40.59), 
(                   '', 3,  18.10), 
('01.01.2014 04:13:59', 3,  18.20), 
(                   '', 4,  10.59), 
('01.01.2014 15:44:32', 4,  10.48), 
(                   '', 5,   8.19), 
(                   '', 5,   8.24), 
(                   '', 6,  11.11), 
('04.01.2014 10:22:35', 6,  11.09), 
('01.01.2014 11:48:15', 6,  11.07), 
('01.01.2014 22:18:33', 7,  22.58), 
('03.01.2014 13:15:40', 7,  21.99), 
(                   '', 7,  22.60);

Here is query for getting result:

SELECT code, 
       ROUND(AVG(price), 2), 
       MAX(price) 
  FROM temp1 
 GROUP BY code 
 ORDER BY code;

In short: I have to get LAST price by date (written as text) for every grouped code if date exists otherwise (if date isn't written) price should be 0. In column LAST is wanted result and result of AVG and MAX for illustration:

  CODE    LAST     AVG     MAX  
 ------------------------------
     1    9.75    9.87    9.99
     2   40.59   40.36   40.59
     3   18.20   18.15   18.20
     4   10.48   10.54   10.59
     5    0.00    8.22    8.24
     6   11.09   11.09   11.11
     7   21.99   22.39   22.60

How would I get wanted result? How that query would look like?

EDITED
I simply have to try 'IMSoP's advices to update and use custom aggregate functions first/last.

    SELECT code, 
       CASE WHEN MAX(mydate)<>'' THEN
           (SELECT last(price ORDER BY TO_TIMESTAMP(mydate, 'DD.MM.YYYY HH24:MI:SS')))
            ELSE
            0
            END AS "LAST",
       ROUND(AVG(price), 2) AS "AVG", 
       MAX(price) AS "MAX"
  FROM temp1 
 GROUP BY code 
 ORDER BY code;

With this simple query I get same results as with Mike's complex query.
And more, those one better consumes double (same) entries in mydate column, and is faster.
Is this possible? It look's similar to 'SELECT * FROM magic()' :)

Did you check out the last_value() function? Btw: never, ever store dates as varchar. That is begging for trouble. — a_horse_with_no_name
@user973238 Any column can be set to null (if you do not set a NOT NULL constraint). — Ihor Romanchenko
As an alternative to window functions, you could try these first() and last() aggregate functions, e.g. SELECT last(price ORDER BY date) — IMSoP
@user973238 Why did you title your question "custom aggregate", if copy and pasting a custom aggregate definition is too complicated? Once you've created them, that's a lot simpler than any other approach I can conceive of. — IMSoP

Mike Sherrill 'Cat Recall' Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-01-21T21:26:25

You said in comments that one code can have two rows with the same date. So this is sane data.

01.01.2014  1   3.50
01.01.2014  1  17.25
01.01.2014  1  99.34

There's no deterministic way to tell which of those rows is the "last" one, even if you sort by code and "date". (In the relational model--a model based on mathematical sets--the order of columns is irrelevant, and the order of rows is irrelevant.) The query optimizer is free to return rows is the way it thinks best, so this query

select *
from temp1
order by mydate, code

might return this on one run,

01.01.2014  1   3.50
01.01.2014  1  17.25
01.01.2014  1  99.34

and this on another.

01.01.2014  1   3.50
01.01.2014  1  99.34
01.01.2014  1  17.25

Unless you store some value that makes the meaning of last obvious, what you're trying to do isn't possible. When people need to make last obvious, they usually use a timestamp.

After your changes, this query seems to return what you're looking for.

with distinct_codes as (
  select distinct code 
  from temp1
),
corrected_table as (
select 
  case when mydate <> '' then TO_TIMESTAMP(mydate, 'DD.MM.YYYY HH24:MI:SS')
       else null
  end as mydate, 
  code, 
  price
from temp1
),
max_dates as (
  select code, max(mydate) max_date
  from corrected_table
  group by code
)
select c1.mydate, d1.code, coalesce(c1.price, 0)
from corrected_table c1
inner join max_dates m1
        on m1.code = c1.code
       and m1.max_date = c1.mydate
right join distinct_codes d1
        on d1.code = c1.code
order by code;

PostgreSQL, Custom aggregate

1 Answers