0
435

Written by

Eduard Lebedyuk
Senior Cloud Architect at InterSystems
STAFF
Follow
Question Eduard Lebedyuk · Aug 2, 2021

Median in SQL

#InterSystems IRIS #Analytics #Beginner #SQL

Does InterSystems IRIS has a MEDIAN (or percentile) calculation in SQL?

Product version: IRIS 2021.1
Discussion (4)0
Log in or sign up to continue

Comments

Alexander Koblov · Aug 18, 2021

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

Doc: https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=RSQL_windowfunctions

Also, it's possible to create a user-defined aggregate function. See https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_createudaf

0
Muhammad Waseem · Aug 10, 2021

Yes IS do have MEDIAN function. Try this one
SELECT MEDIAN(birthd.decade.MEMBERS, MEASURES.[%COUNT]) ON 0 FROM patients

0
Evgeny Shvarov  Aug 10, 2021 to Muhammad Waseem

Looks like IRIS BI MDX syntax

0
Randy Shaw · Aug 17, 2021

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
0