case-kの備忘録

日々の備忘録です。最近はGCPやデータ分析系のことを呟きます

BigQueryのStandard SQLで四分位計算

GBQのStandard SQLでの四分位計算方法を備忘録として残しておきます。
これまで中央値などの計算はGBQのレガシーSQLを使っていたのですが、制限等何かと不便でしたので調べてみました。

  SELECT
    day ,
    min,
    percentiles[offset(25)] AS per_25,
    percentiles[offset(50)] AS per_50,
    percentiles[offset(75)] AS per_75,
    max
  FROM (
    SELECT
      day ,# 集計単位
      MIN( [column name]) AS min,
      MAX([column name]) AS max,
      APPROX_QUANTILES([column name],100) percentiles
    FROM
      [table name]
    GROUP BY
      day ))

念の為レガシーSQLで検算。



  SELECT
    day,
    min([column name]),
    NTH(501, QUANTILES([column name], 1001))  AS per_50,
    max([columns name])
  FROM
    [table name]
  GROUP BY
     day ))