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
