Unnest join need support WITH ORDINALITY

[ImTangYun]

Spark support lateral view posexplode to explode array and get an index number of data in array, for example:

select item.a,t.r from tmp.lateral_test1 lateral view posexplode(c3) t as r,item

My table created like this:

CREATE EXTERNAL TABLE tmp.lateral_test(
c3 array<struct<a:string>> COMMENT 'from deserializer')

I know lateral view explode can be transformed as join lateral unnest in starrocks, but how to tansform lateral view posexplode?
Also, in Trino/Prestodb Sql can be transformed as:

SELECT "t1"."item"."a" AS "a", "t1"."r" AS "r"
FROM "tmp"."lateral_test1" AS "$cor0"
CROSS JOIN UNNEST("$cor0"."c3") WITH ORDINALITY AS "t1" ("item", "r")

There need a way to support this feature, Thans!

[wangsimo0]

window function can be used in this scenario
row_number() over(partition by c3)-1 as r

[ImTangYun]

window function can be used in this scenario row_number() over(partition by c3)-1 as r

I tryed and found it not possiable to use 'row_number() over(partition by c3)-1 as r' to support the same function

The biggest problem is no way to make sure the r to be the index of item in array

[github-actions]

We have marked this issue as stale because it has been inactive for 6 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to StarRocks!