[Feature] Similar to support request for collect_set function in Hive

[FreeOnePlus]

Search before asking

• I had searched in the issues and found no similar issues.

Description

Currently in Apache Doris, there is no such function to support this scenario. I hope the developers can do a Dorisization of this function, thank you~

Use case

Find the person who logged in for the first time in a certain day in a data table in hive; you can use collect_set to process SQL:

select count(a.id) from (select id,collect_set(time) as t from t_action_login where time<='20150906' group by id) as a where size(a.t)=1 and a.t[0]='20150906';

in the above

select id,collect_set(time) as t from t_action_login where time<='20150906' group by id

It will be grouped by id, because an id may correspond to one day or many days, and corresponding to multiple days means that there are multiple days to log in, so one id will correspond to multiple dates and times, and the date corresponding to each id will be constructed through collect_set Returns as a comma-separated array. The above SQL returns:

123@163.com | ["20150620","20150619"] | | abc@163.com | ["20150816"] | | cde@qq.com | ["20150606","20150608","20150607","20150609","20150613","20150610","20150616","20150615"] | | 789@sohu.com | ["20150827","20150623","20150627","20150820","20150823","20150612","20150717"] | | 987@163.com | ["20150701","20150829","20150626","20150625","20150726","20150722","20150629","20150824","20150716","20150 | | ddsf@163.com | ["20150804","20150803","20150801","20150809","20150807","20150806","20150905","20150904","20150730","20150 | | 182@163.com |["20150803","20150801","20150809","20150808","20150805","20150806","20150906","20150904","20150730","20150 | | 22225@163.com | ["20150604","20150609","20150622","20150827","20150625","20150620","20150613","20150610","20150614","20150 | | 18697@qq.com | ["20150902"] | | 1905@qq.com | ["20150709"]

So we can make a fuss according to the returned array, which is：
where size(a.t)=1 and a.t[0]='20150906';

Indicates that the array length corresponding to an id is 1 and the id whose first time is 20150906 is the first login on that day.

Related issues

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct