【bigquery】四分位数の求め方

bigqqueryで四分位数を算出するコードがあまりなかったのでメモ投稿です

SELECT
　　key
 , min
　　-- 配列にしたpercent情報からoffsetで割合算出
 , percent[offset(25)] AS per_25 -- 第一四分位点
 , percent[offset(50)] AS per_50 -- 第二四分位点
 , percent[offset(75)] AS per_75 -- 第三四分位点
 , max
FROM 
(
　　-- 四分位数を抽出するため、以下のように統計量を先に出す
　　SELECT
   key 
   , MIN(ranking) AS min
   , MAX(ranking) AS max
   -- ranking情報を割合で出せるように配列にする
   , APPROX_QUANTILES(ranking, 100) AS percent  
　　FROM
  dataset.table
 GROUP BY
   key
)

上記のコードよりも効率的なコードがあったのでこちらも追記(2023/8/15)

SELECT
  APPROX_QUANTILES(price, 4)[OFFSET(1)] as q1, -- 第1四分位数
  APPROX_QUANTILES(price, 4)[OFFSET(2)] as median, -- 中央値
  APPROX_QUANTILES(price, 4)[OFFSET(3)] as q3 -- 第3四分位数
FROM
  dataset.table

上記は、APPROX_QUANTILES()関数での四分位数算出コード

この関数は、データを指定された数のバケットに分割し、各バケットの最大値を返します。

SELECT
  PERCENTILE_CONT(0.25) OVER(ORDER BY price) as q1, -- 第1四分位数
  PERCENTILE_CONT(0.5) OVER(ORDER BY price) as median, -- 中央値
  PERCENTILE_CONT(0.75) OVER(ORDER BY price) as q3 -- 第3四分位数
FROM
  dataset.table

上記は、PERCENTILE_CONT()関数での四分位数算出コード

この関数は、与えられたパーセンタイルに対応する値を返します。