在BigQuery标准SQL中，如何获取行的子集合的中值

Question

我有类似的数据

day
Sun		太阳		太阳		太阳		太阳		周一		Mon		周一

您可以使用percentile_cont():

select distinct day,
percentile_cont(price, 0.5) over (partition by day) as median_overall,
percentile_cont((case when produce = 'apple' then price end), 0.5 ignore nulls) over (partition by day) as median_apple
from t;

我找到了另一种方法，它实际上更灵活(但不提供线性插值(

SELECT
day,
APPROX_QUANTILES(price, 2)[OFFSET  (1)] AS median_all,
APPROX_QUANTILES(case when product = 'apples' then price end, 2)[OFFSET  (1)] AS median_apples,
FROM t group by day