Hi All,
I'd like to throw here an enhancement proposal to discuss/consider.
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);
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?
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;
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;
Any chances of implementing that?
Thank you,
Kind Regards
~Maciek
