[BQ] Bigqueryメモ

自分用bigqueryメモ

小ネタから備忘録まで

ウィンドウ関数はWINDOWを使って定義しよう

以下のようなウィンドウ関数を使った処理を考える．

SELECT
  AVG(expr) OVER (PARTITION BY hoge) AS avg_expr,
  COUNT(expr) OVER (PARTITION BY hoge) AS cnt_expr,
  SUM(expr) OVER (PARTITION BY fuga) AS sum_expr
FROM
  tablename

同じサブクエリー内ではウィンドウ関数を別で定義できる．
こうすると一括で処理を書き換えられる．

SELECT
  AVG(expr) OVER (hoge_window) AS avg_expr,
  COUNT(expr) OVER (hoge_window) AS cnt_expr,
  SUM(expr) OVER (fuga_window) AS sum_expr
FROM
  tablename
WINDOW
  hoge_window AS (PARTITION BY hoge),
  fuga_window AS (PARTITION BY fuga)

パーセンタイル

$y$%誤差で$x$パーセンタイルを取る．
このとき，
$a = \frac{x}{y} + 1,\ b=\frac{100}{y} + 1$
が自然数であれば以下のやり方で指定できる．

SELECT
  APPROX_QUANTILES(expr, b)[SAFE_ORDINAL(a)] AS y_percentile
FROM
  tablename

厳密解を得たい時？は以下のようにする．（上に比べて遅いし冗長）

WITH
  middle AS (
    SELECT
      PERCENTILE_CONT(expr, y/100) OVER () AS y_percentile
    FROM
      tablename
  )
SELECT
  ANY_VALUE(y_percentile) AS y_percentile
FROM
  middle