Find median in spark SQL for multiple double datatype columns
<p>I have a requirement to find median for multiple double datatype columns.Request suggestion to find the correct approach.</p>

<p>Below is my sample dataset with one column. I am expecting the median value to be returned as 1 for my sample.</p>

<pre><code>  scala&gt; sqlContext.sql("select num from test").show();
+---+
|num|
+---+
|0.0|
|0.0|
|1.0|
|1.0|
|1.0|
|1.0|
+---+
</code></pre>

<p>I tried the following options</p>

<p>1) Hive UDAF percentile, it worked only for BigInt.</p>

<p>2) Hive UDAT percentile_approx, but it does not work as expected (returns 0.25 vs 1).</p>

<p>sqlContext.sql("select percentile_approx(num,0.5) from test").show();</p>

<pre><code>+----+
| _c0|
+----+
|0.25|
+----+
</code></pre>

<p>3) Spark window function percent_rank- to find median the way i see is to look for all percent_rank above 0.5 and pick the max percent_rank's corresponding num value. But it does not work in all cases, especially when i have even record counts, in such case the median is the average of the middle value in the sorted distribution.</p>

<p>Also in the percent_rank, as i have to find the median for multiple columns, i have to calculate it in different dataframes, which to me is little complex method. Please correct me, if my understanding is not right.   </p>

<pre><code>+---+-------------+
|num|percent_rank |
+---+-------------+
|0.0|0.0|
|0.0|0.0|
|1.0|0.4|
|1.0|0.4|
|1.0|0.4|
|1.0|0.4|
+---+---+
</code></pre>