MEDIAN

Calculates the median of the values in expr that are not NULL. If all values are NULL, NULL is returned.

Syntax

Aggregate Function

MEDIAN(expr)

Window Function

MEDIAN(expr) OVER (
                    [PARTITION BY part]
                    )

Arguments

expr: Numeric or timestamp value (INTEGER, DECIMAL, TIMESTAMP).

---

part (optional): Expression to partition by.

Usage Notes

If expr is not numeric, expr is implicitly converted to DECIMAL. If the conversion is not possible, an error is returned.

Example 1

select  median(col2) 
              from (select 'a' as ord1, 'g1' as grp1, '10' as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, '40' as col1, 40 as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, '60' as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, '50' as col1, 50 as col2);

+--------------+
| MEDIAN(COL2) |
+--------------+
| 30           |          
+--------------+

Example 2 | GROUP BY Clause

select grp1, median(col1), median(col2) 
             from  (select 'a' as ord1, 'g1' as grp1, 10 as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, 20 as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, 20 as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, 30 as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, 30 as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, 40 as col1, 40 as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, 50 as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, 50 as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, 60 as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, 50 as col1, 50 as col2)
            group by grp1
            order by grp1;

+------+--------------+--------------+
| GRP1 | MEDIAN(COL1) | MEDIAN(COL2) |
+------+--------------+--------------+
| g1   | 20           | 20           |
| g2   | 30           | 40           |
| g3   | 50           | 55           |
+------+--------------+--------------+

Example 3 | Window Function

select  grp1, median(col1) OVER(PARTITION BY grp1) as result1,
              median(col2) OVER(PARTITION BY grp1) as result2
                from  (select 'a' as ord1, 'g1' as grp1, 10 as col1, 10 as col2
                        union all 
                        select 'b' as ord1, 'g1' as grp1, 20 as col1, 20 as col2
                        union all
                        select 'c' as ord1, 'g1' as grp1, 20 as col1, 20 as col2
                        union all 
                        select 'a' as ord1, 'g2' as grp1, 30 as col1, NULL as col2 
                        union all 
                        select 'b' as ord1, 'g2' as grp1, 30 as col1, NULL as col2 
                        union all 
                        select 'c' as ord1, 'g2' as grp1, 40 as col1, 40 as col2
                        union all 
                        select 'a' as ord1, 'g3' as grp1, 50 as col1, NULL as col2
                        union all 
                        select 'b' as ord1, 'g3' as grp1, 50 as col1, NULL as col2
                        union all 
                        select 'c' as ord1, 'g3' as grp1, 60 as col1, 60 as col2
                        union all
                        select 'd' as ord1, 'g3' as grp1, 50 as col1, 50 as col2);

+------+---------+---------+
| GRP1 | RESULT1 | RESULT2 |
+------+---------+---------+
| g1   | 20      | 20      |
| g1   | 20      | 20      |
| g1   | 20      | 20      |
| g2   | 30      | 40      |
| g2   | 30      | 40      |
| g2   | 30      | 40      |
| g3   | 50      | 55      |
| g3   | 50      | 55      |
| g3   | 50      | 55      |
| g3   | 50      | 55      |
+------+---------+---------+