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. — user330315
– user330315, Commented Jan 21, 2014 at 20:36
@user973238 Any column can be set to null (if you do not set a NOT NULL constraint). — Ihor Romanchenko
– Ihor Romanchenko, Commented Jan 21, 2014 at 20:48
As an alternative to window functions, you could try these first() and last() aggregate functions, e.g. SELECT last(price ORDER BY date) — IMSoP
– IMSoP, Commented Jan 21, 2014 at 20:55
@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
– IMSoP, Commented Jan 21, 2014 at 21:49

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-01-21 23:54:05Z

2

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;

edited Jan 21, 2014 at 23:54

answered Jan 21, 2014 at 21:26

Mike Sherrill 'Cat Recall'

96.6k20 gold badges132 silver badges195 bronze badges

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

6 Comments

Wine Too Over a year ago

I understand now, but OK, I changed data to datetime now since I can get real time with real data. Since and date and time is written like text we could use function TO_TIMESTAMP(mydate || mytime, 'DD.MM.YYYYHH24:MI:SS'). How would then query look like?

Mike Sherrill 'Cat Recall' Over a year ago

You seem to have misunderstood me. I was suggesting you either change your existing "mydate" column to type timestamp, or add an additional column of type timestamp. Timestamp; not text or char(n) or varchar(n).

Wine Too Over a year ago

I gave example most similar to concrete situation, easy-to-use table and best description of problem I know. Everything above would be some other situation than one which I asked to help solving.

Wine Too Over a year ago

Well, I wasn't aware that this is so complex problem which require that complex query. It work as expected on my data, thank you for solving a problem.

Mike Sherrill 'Cat Recall' Over a year ago

If you have a table of codes, you could rewrite this without the first common table expression (CTE). If you had sensible data types, you could eliminate the second CTE. What's left after those two changes is pretty straightforward.

|

Collectives™ on Stack Overflow

PostgreSQL, Custom aggregate

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related