FreeOnePlus opened a new issue, #9956: URL: https://github.com/apache/incubator-doris/issues/9956
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) 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: ` [email protected] | ["20150620","20150619"] | | [email protected] | ["20150816"] | | [email protected] | ["20150606","20150608","20150607","20150609","20150613","20150610","20150616","20150615"] | | [email protected] | ["20150827","20150623","20150627","20150820","20150823","20150612","20150717"] | | [email protected] | ["20150701","20150829","20150626","20150625","20150726","20150722","20150629","20150824","20150716","20150 | | [email protected] | ["20150804","20150803","20150801","20150809","20150807","20150806","20150905","20150904","20150730","20150 | | [email protected] |["20150803","20150801","20150809","20150808","20150805","20150806","20150906","20150904","20150730","20150 | | [email protected] | ["20150604","20150609","20150622","20150827","20150625","20150620","20150613","20150610","20150614","20150 | | [email protected] | ["20150902"] | | [email protected] | ["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 - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
