BigQueryでgroup毎に上限数(limit)をつける

背景

全体ではなく、group 毎に limit 数を付けたい。もう少しわがまま言うと、group毎にランダムサンプリングしたい。

方法

次のようなクエリのときで、

SELECT
  aaa,
  bbb
FROM
  table1
;

aaaのグループ毎に上限1000で出したいとする。

SELECT
  aaa,
  bbb
 FROM
  (SELECT
    aaa,
    bbb,
    rank() OVER (PARTITION BY aaa ORDER BY rand) rank
   FROM
     (SELECT
        aaa,
        bbb,
        rand() rand
      FROM
        table1
     ) /* 乱数を付与する */
  ) /* 乱数を元に順位をつける */
WHERE
  rank 

これでOK。
二段ほど奥まったが、OVER句の中のORDER BYにrand()が使えないことと、ウィンドウ関数のrank()がその関数で定義されるクエリのWHERE句の条件としてつかないことが理由。