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:[email protected]]
Sent: Monday, May 8, 2017 9:42 AM
To: [email protected]
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:[email protected]]
Sent: Monday, May 8, 2017 11:25 AM
To:
[email protected]<mailto:[email protected]>
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:[email protected]]
Sent: Monday, May 8, 2017 9:15 AM
To:
[email protected]<mailto:[email protected]>
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:[email protected]]
Sent: Monday, May 8, 2017 3:17 AM
To:
[email protected]<mailto:[email protected]>
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: [email protected]<mailto:[email protected]>
Cellphone: (+86) 13671935540
