Thanks Robert! I thought this could be nice functionality available out of the box but yea, sure, that would work for me. Can I make custom aggregate function that accepts WITHIN GROUP syntax? Which language would that need to be implemented in? Would you have examples (url?)
Thank you, Kind Regards ~Maciek On Thu, 26 Jul 2018 at 16:22, Robert Haas <robertmh...@gmail.com> wrote: > On Tue, Jul 24, 2018 at 4:16 PM, matshyeq <matsh...@gmail.com> wrote: > >> I'd like to throw here an enhancement proposal to discuss/consider. >> The FIRST/LAST_value() >> <https://www.postgresql.org/docs/devel/static/functions-window.html#id-1.5.8.26.6.2.2.9.1.1> >> functions offer powerful lookup capabilities, eg. >> here >> 1) >> https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=0f13c8541191c3018703d2a97aa90bf9 >> >> 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 >> 1 d 1 d b >> 1 a 2 d b >> 1 b 3 d b >> 2 x 7 x v >> 2 z 8 x v >> 2 y 9 x v >> 2 v 9 x v >> >> >> 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? >> > > Why not just define a custom aggregate function that does whatever you > need? I don't think it would be too hard. e.g. for something like > LAST_VALUE() just make the transition type equal to the output type and > save the last value you've seen thus far as the transition value. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
