I am trying to write code equivalent to below in Oracle 23
The keypoints are aggregation of array ( window functions ) and collation according to datatype.
Code below runs in Postgres 17 and DuckDB 1.1.x
with v01 as ( select x.partkey, x.val1::integer as val1, x.val2
from ( values ('partkey1', 7, 'g'),
('partkey1', 10, 'j'),
('partkey2', 7, 'g'),
('partkey2', 2, 'b'))
as x(partkey, val1, val2) ) ,
v02 as ( select v.*
, array_agg(v.val1) over (win_v02) as integer_array
, string_agg(v.val1::varchar, ',') over (win_v02) as varchar_array -- for collation comparison & visualisation
from v01 v
window win_v02 as (partition by v.partkey) ),
v03 as ( select v.*
, rank() over (order by v.integer_array, v.val2) as integer_array_rank -- orders by val1::integer
, rank() over (order by v.varchar_array, v.val2) as varchar_array_rank -- orders by val1::varchar
from v02 v )
select v.val1, v.val2, v.integer_array, v.integer_array_rank, v.varchar_array, v.varchar_array_rank from v03 v
order by v.integer_array_rank ;
