Enhancement request: enable FIRST/LAST_value() also as a regularaggregate (not only as windowing function)

Поиск
Список
Период
Сортировка
От matshyeq
Тема Enhancement request: enable FIRST/LAST_value() also as a regularaggregate (not only as windowing function)
Дата
Msg-id CAONr5=s+8q36Wmo_LVyUgy7gjo-SCfviYJ556Uvr-T_ss_w7mg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Enhancement request: enable FIRST/LAST_value() also as a regular aggregate (not only as windowing function)
Re: Enhancement request: enable FIRST/LAST_value() also as a regularaggregate (not only as windowing function)
Список pgsql-hackers
Дерево обсуждения
Hi All,

I'd like to throw here an enhancement proposal to discuss/consider.
The FIRST/LAST_value() functions offer powerful lookup capabilities, eg.
here

SELECT t.* ,FIRST_value(v1)OVER(PARTITION BY gid ORDER BY v2) fv ,LAST_value(v1)OVER(PARTITION BY gid ORDER BY v2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv FROM( VALUES (1, 'b', 3),(1, 'd', 1),(1, 'a', 2) ,(2, 'x', 7),(2, 'y', 9),(2, 'z', 8),(2, 'v', 9)) t (gid, v1, v2);


gidv1v2fvlv


but, given those values are repeating - why can't I simply use this functions as regular aggregates?

Or can I? It doesn't seem to be possible while I find this use case actually more common than in windowing context…
Am I missing some workaround here?

Anyway, Oracle is an example where both contexts are possible and I think this could provide great reference as to what I have in mind.
2) Demonstrating those functions in both contexts there:

a) as Windowing Aggregate:

 WITH t(gid, v1, v2) AS(

SELECT 1, 'b', 3 FROM dual UNION ALL

SELECT 1, 'd', 1 FROM dual UNION ALL

SELECT 1, 'a', 2 FROM dual UNION ALL

SELECT 2, 'x', 7 FROM dual UNION ALL

SELECT 2, 'y', 9 FROM dual UNION ALL

SELECT 2, 'z', 8 FROM dual UNION ALL

SELECT 2, 'v', 9 FROM dual --UNION ALL

)

SELECT t.*

     ,MIN(v1)KEEP(DENSE_RANK FIRST ORDER BY v2) OVER(PARTITION BY gid) lkp_first

     ,MIN(v1)KEEP(DENSE_RANK LAST ORDER BY v2) OVER(PARTITION BY gid) lkp_lastMin

     ,MAX(v1)KEEP(DENSE_RANK LAST ORDER BY v2) OVER(PARTITION BY gid) lkp_lastMax

 FROM t;

GIDV1V2LKP_FIRSTLKP_LASTMINLKP_LASTMAX


b) as a regular aggregate (cf. GROUP BY and two rows only in the result)

 WITH t(gid, v1, v2) AS(

SELECT 1, 'b', 3 FROM dual UNION ALL

SELECT 1, 'd', 1 FROM dual UNION ALL

SELECT 1, 'a', 2 FROM dual UNION ALL

SELECT 2, 'x', 7 FROM dual UNION ALL

SELECT 2, 'y', 9 FROM dual UNION ALL

SELECT 2, 'z', 8 FROM dual UNION ALL

SELECT 2, 'v', 9 FROM dual --UNION ALL

)

SELECT t.gid

     ,MIN(v1)KEEP(DENSE_RANK FIRST ORDER BY v2)lkp_first

     ,MIN(v1)KEEP(DENSE_RANK LAST ORDER BY v2)lkp_lastMin

     ,MAX(v1)KEEP(DENSE_RANK LAST ORDER BY v2)lkp_lastMax

 FROM t

GROUP BY t.gid;

GIDLKP_FIRSTLKP_LASTMINLKP_LASTMAX

Any chances of implementing that?

Thank you,
Kind Regards 
~Maciek

В списке pgsql-hackers по дате отправления: