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);
gid
v1
v2
fv
lv
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.
