Calculating median of 3 columns in a BigQuery table

Question 1

I am trying to build a query to calculate median of 3 column values. My table looks like below,

Item	Column 1	Column 2	Column 3
A	10	12	4
B	5	14	20
C	15	5	4

I want to be able to output,

Item	Column 1	Column 2	Column 3	Median
A	10	12	4	10
B	5	14	20	14
C	15	5	4	5

I have tried percentile_cont() but that seems to be only for values in a single column. How do i achieve this?

Question 2

Consider below approach

select *, 
  ( select distinct percentile_disc(col, 0.5) over() 
    from unnest([Column1, Column2, Column3]) as col
  ) AS Median
from your_table

if applied to sample data in your question - output is

Question 3

Have you tried this:

select Col1, Col2, Col3, 
       PERCENTILE_CONT([Col1, Col2, Col3], 0.5) OVER() AS Median
from   tableName

Mikhail Berlyant · Answer 1 · 2021-11-23T22:50:39

Consider below approach

select *, 
  ( select distinct percentile_disc(col, 0.5) over() 
    from unnest([Column1, Column2, Column3]) as col
  ) AS Median
from your_table

if applied to sample data in your question - output is

Abhishek Pandey · Answer 2 · 2021-11-23T18:06:09

0

Have you tried this:

select Col1, Col2, Col3, 
       PERCENTILE_CONT([Col1, Col2, Col3], 0.5) OVER() AS Median
from   tableName

Abhishek Pandey

2021-11-23 18:06:09

Yes, but that errors out as No matching signature for analytic function PERCENTILE_CONT for argument types: ARRAY<FLOAT64>, FLOAT64. Supported signatures: PERCENTILE_CONT(FLOAT64, FLOAT64); PERCENTILE_CONT(NUMERIC, NUMERIC); PERCENTILE_CONT(BIGNUMERIC, BIGNUMERIC) at [5:3]

pear_geepee

Calculating median of 3 columns in a BigQuery table

In other languages

This page is in other languages

Popular in the category