[ https://issues.apache.org/jira/browse/PHOENIX-3773?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16021409#comment-16021409 ]
James Taylor commented on PHOENIX-3773: --------------------------------------- Thanks for the patch, [~singamteja]. Here's some feedback: - Make sure your code base has been rebased and that the patch applies. There are directions here: https://phoenix.apache.org/contributing.html#Generate_a_patch - Have FIRST_VALUES return an ARRAY type so that you can return all values in a single row. It's not going to work to change the semantics of SQL (it's pretty well established). A function can't return multiple rows like that. - Implement this in a base class so that you can support both FIRST_VALUES and LAST_VALUES. It's a trivial matter of multiplying the result of the comparator by -1 for LAST_VALUES. > Implement FIRST_VALUES aggregate function > ----------------------------------------- > > Key: PHOENIX-3773 > URL: https://issues.apache.org/jira/browse/PHOENIX-3773 > Project: Phoenix > Issue Type: New Feature > Reporter: James Taylor > Assignee: Loknath Priyatham Teja Singamsetty > Labels: SFDC > Fix For: 4.11.0 > > Attachments: PHOENIX-3773.patch, PHOENIX-3773.v2.patch > > > Similar to FIRST_VALUE, but would allow the user to specify how many values > to keep. This could use a MinMaxPriorityQueue under the covers and be much > more efficient than using multiple NTH_VALUE calls to do the same like this: > {code} > SELECT entity_id, > NTH_VALUE(user_id,1) WITHIN GROUP (ORDER BY last_read_date DESC) as > nth1_user_id, > NTH_VALUE(user_id,2) WITHIN GROUP (ORDER BY last_read_date DESC) as > nth2_user_id, > NTH_VALUE(user_id,3) WITHIN GROUP (ORDER BY last_read_date DESC) as > nth3_user_id, > count(*) > FROM MY_TABLE > WHERE tenant_id='00Dx0000000XXXX' > AND entity_id in ('0D5x000000ABCD','0D5x000000ABCE') > GROUP BY entity_id; > {code} -- This message was sent by Atlassian JIRA (v6.3.15#6346)