Thank you all for the explanation and help.
Best regards, Yuan Email: yuan....@esgyn.cn<mailto:yuan....@esgyn.cn> Cellphone: (+86) 13671935540 From: Hans Zeller [mailto:hans.zel...@esgyn.com] Sent: Tuesday, May 09, 2017 12:54 AM To: user@trafodion.incubator.apache.org Subject: RE: [Question]Does Trafodion support median() function as Oracle? Hi, The method by Itzik Ben-Gan mentioned in the blog might not work exactly as written, because it assumes that the result of a division by two is an integer, which is not the case in Trafodion. You might need to add a cast to integer or largeint to make it work. Here is a query using WITH clauses that does the same as the Ben-Gan method: with q as (select x val from (values (1), (2), (7), (9), (null)) T(x)), rn as (select row_number() over(order by val) rownum, val from q), c as (select count(val) as cnt from q) select cast(avg(val) as numeric(10,2)) from rn, c where (mod(cnt, 2) = 1 and rownum = (cnt + 1) / 2) or (mod(cnt, 2) = 0 and (rownum = cnt/2 or rownum = cnt/2 + 1)); Replace the query defined as "q" with your own query, the rest of the SQL can stay the same. Hans From: Eric Owhadi [mailto:eric.owh...@esgyn.com] Sent: Monday, May 8, 2017 9:42 AM To: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org> Subject: RE: [Question]Does Trafodion support median() function as Oracle? Some ways to compute median without support for dedicated aggregate function: http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx Hope this helps, Eric From: Dave Birdsall [mailto:dave.birds...@esgyn.com] Sent: Monday, May 8, 2017 11:25 AM To: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org> Subject: RE: [Question]Does Trafodion support median() function as Oracle? I don't see any support for MEDIAN in the Trafodion SQL parser. From: Sean Broeder [mailto:sean.broe...@esgyn.com] Sent: Monday, May 8, 2017 9:15 AM To: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org> Subject: RE: [Question]Does Trafodion support median() function as Oracle? There is a getMedian() aggregator function in our endpoint coprocessor implementation and also in the TransactionalAggregationClient, so I would think we have support for this function. I haven't tested it personally. Regards, Sean From: Liu, Yuan (Yuan) [mailto:yuan....@esgyn.cn] Sent: Monday, May 8, 2017 3:17 AM To: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org> Subject: [Question]Does Trafodion support median() function as Oracle? Hi trafodioners, Can anyone please tell me does Trafodion support median function or similar window function as Oracle has? If not, do we have another way to realize this? Select median(fee_sum_before), avg(fee_sum_before),max(...), min(...) from ta_user_property; Best regards, Yuan Email: yuan....@esgyn.cn<mailto:yuan....@esgyn.cn> Cellphone: (+86) 13671935540
