33

I have a hive table,

name    age     sal
A       45      1222
B       50      4555
c       44      8888
D       78      1222
E       12      7888
F       23      4555

I want to calculate median of age column.

Below is my approach 

select min(age) as HMIN,max(age) as HMAX,count(age) as HCount,
IF(count(age)%2=0,'even','Odd') as PCOUNT 
from v_act_subjects_bh;

Appreciate any query suggestion

Amaresh
  • 3,231
  • 7
  • 37
  • 60

3 Answers3

90

You can use the percentile function to compute the median. Try this:

select percentile(cast(age as BIGINT), 0.5) from table_name
Konrad
  • 17,740
  • 16
  • 106
  • 167
Amar
  • 3,825
  • 1
  • 23
  • 26
0

Accepted answer works if you have INT values. If your data contains values between 0-1 such as scores of a model, you may use below formula;

select (percentile(cast(age as BIGINT), 0.5))/100 from table_name
ibozkurt79
  • 225
  • 4
  • 7
-3
double median = 0;
double term = 0;
double term1 = 0;
if (size % 2 == 1)
{
    term = (size + 1 - 1) / 2;
    median = term;
}
else if (size % 2 == 0)

{
    
    term1 = (size - 1) / 2;
    term1 = term1 + ((size - 1) / 2) + 1;
    term1 = term1 / 2;
    median = term1;
}
cout << "Median of array: " << median << endl;
success
  • 1
  • 1
    this is not hive – DPColombotto Mar 29 '21 at 13:44
  • Hi @Success, try to answer [C++ questions](https://stackoverflow.com/search?q=%5Bcpp%5D&searchOn=3), this here is one only for [Hive](https://stackoverflow.com/questions/tagged/hive). On Stackoverflow is very important the [TAG of the question](https://stackoverflow.com/tags). You can DELETE your answer here to avoid more down-votes... Seems that you was not looking for Hive, but for C++ or other. – Peter Krauss May 06 '21 at 13:20