get median value from a group by query in postgresql 14-CodePudding

I have a query with a column as date type and the other as double precision. The date column is called trading_day and the double precision column is called stock_price. I want the median stock_price of every month in the year 2000.

My query is as follows:

select date_trunc('month',trading_day) as dt_trunc,median(stock_price) from data_price
where extract(year from trading_day) = 2000
group by dt_trunc

I'm not sure if my query is really aggregating and computing correctly and I thought also that in the newer version of PostgreSQL a median function exists.

CodePudding user response：

A standard SQL way to calculate a median is to get the continuous percentile for 50%.

select 
  date_trunc('month', trading_day) as dt_trunc
, percentile_cont(0.5) WITHIN GROUP (ORDER BY stock_price) as median
from data_price
where extract(year from trading_day) = 2000
group by dt_trunc