InterSystems IRIS does not have a MEDIAN SQL function. However, it has different window functions.
For example, Percent_Rank might help you here.
create table test(c1 varchar(10), c2 integer)
insert into test(c1,c2)
select 'a',1
union all
select 'b',2
union all
select 'c',3
union all
select 'd',3
union all
select 'e',3
union all
select 'f',4
union all
select 'g',5
union all
select 'h',6
select c1, c2, Percent_Rank () OVER (
ORDER BY c2 desc
) rank_no
from test
order by c2 desc
c1 c2 rank_no
h 6 0
g 5 .1428571428571428571
f 4 .2857142857142857143
c 3 .4285714285714285714
d 3 .4285714285714285714
e 3 .4285714285714285714
b 2 .8571428571428571429
a 1 1
I happened to need Median recently and came up with this solution.
SELECT TOP 1
AVG(main.age) AS _Average,min(main.age) AS _Min,
CASE WHEN %vid = count(main.age)/2 THEN main.age else 0 END+MAX(CASE WHEN %vid = count(main.age)/2 THEN main.age else 0 END) AS _Median,
max(main.age) AS _Max
FROM
(
SELECT TOP all a.Age FROM Sample.Person a
ORDER BY a.Age
) main
InterSystems IRIS does not have a MEDIAN SQL function. However, it has different window functions.
For example, Percent_Rank might help you here.
Doc: https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=RS...
Also, it's possible to create a user-defined aggregate function. See https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...
Yes IS do have MEDIAN function. Try this one
SELECT MEDIAN(birthd.decade.MEMBERS, MEASURES.[%COUNT]) ON 0 FROM patientsLooks like IRIS BI MDX syntax
I happened to need Median recently and came up with this solution.
SELECT TOP 1 AVG(main.age) AS _Average,min(main.age) AS _Min, CASE WHEN %vid = count(main.age)/2 THEN main.age else 0 END+MAX(CASE WHEN %vid = count(main.age)/2 THEN main.age else 0 END) AS _Median, max(main.age) AS _Max FROM ( SELECT TOP all a.Age FROM Sample.Person a ORDER BY a.Age ) main