PostgreSQL，自定义聚合

Question

当 MAX 和 SUM 不足以获得结果时，是否可以通过这种方式获得自定义聚合之类的功能？这是我的表格:

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); 

这里是获取结果的查询:

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

简而言之:如果日期存在，我必须按日期(写为文本)获取每个分组代码的最后价格，否则(如果未写入日期)价格应为 0。LAST 列中是想要的结果和 AVG 和 MAX 的结果以供说明:

  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

我怎样才能得到想要的结果？该查询会是什么样子？

已编辑
我只需要尝试“IMSoP”的建议来首先/最后更新和使用自定义聚合函数。

    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; 

通过这个简单的查询，我得到了与 Mike 的复杂查询相同的结果。
而且，那些更好地消耗 mydate 列中的双(相同)条目，并且速度更快。
这可能吗？它看起来类似于“SELECT * FROM magic()”:)

Answer 1

您在评论中说过，一个代码可以有两行具有相同的日期。所以这是合理的数据。

01.01.2014  1   3.50
01.01.2014  1  17.25
01.01.2014  1  99.34

即使您按代码和“日期”排序，也无法确定这些行中的哪一行是“最后”行。 (在关系模型——基于数学集合的模型——列的顺序是无关紧要的，行的顺序也是无关紧要的。)查询优化器自由地返回行是它认为最好的方式，所以这个查询

select *
from temp1
order by mydate, code

可能会在一次运行中返回它，

01.01.2014  1   3.50
01.01.2014  1  17.25
01.01.2014  1  99.34

还有这个。

01.01.2014  1   3.50
01.01.2014  1  99.34
01.01.2014  1  17.25

除非您存储一些值使 last 的含义变得明显，否则您无法完成您想要做的事情。当人们需要使last 明显时，他们通常使用时间戳。

---

在您进行更改后，此查询似乎会返回您要查找的内容。

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;