Get multiple records of median in one line

 

Keywordmedian duplicate  multiple records

There are many calculation steps. First, you need to use distinct to get the set of values, and then you need to work out where the middle position is according to its number, get the value of the middle position, and then query all records equal to the median. It is difficult to maintain these intermediate variables in SQL, and you have to implement it with multi-level nested subqueries. It is difficult to write and not easy to understand later.


 It would be much easier if you use esProc SPL language in this case. Get raw data from database:

>T=connect(”mysqlDB”).query(“select * from T”)

And then one line can include multiple calculation steps:

>s=T.id(f),m=s.sort().m((s.len()+1)\2),r=T.select(f==m)

In addition to median, there are also related calculations of maximum / minimum value. SPL further provides several variations of TopN. In addition to the clear step-by-step calculation ability, it also provides convenience. You can take the value / record / record position in the set of TopN and apply it to the grouped subsets.Refer to TopN and variants.


When the data is not in the database, it is still convenient for SPL to perform complex calculations:

=file(“d:/t.csv”).import(;,",").enum...

It's also easy to embed esProc into Java applicationsplease refer to How to Call an SPL Script in Java

For specific usage, please refer to  Getting started with esProc