BigQuery Tips メモ

積算和の計算の仕方

SELECT order_id, SUM(price)
  OVER (
    ORDER BY prices
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_prices
FROM orders

中央値（近似値）の出し方

SELECT APPROX_QUANTILES(x, 2)[OFFSET(1)] FROM UNNEST([2.2, 1, 11.5, 6.6, 8, 14, 250 , 1.5, 0.15]) x

APPROX_QUANTILES を使う。number は作成する変位値の数を表します。この関数は、number +1 個の要素からなる配列を返します。最初の要素は近似最小値であり、最後の要素は近似最大値です。 ということなので、2を指定して返却された配列の2つ目の要素が中央値の近似値となる。

全体件数に対するパーセンテージの出し方

SELECT
  fruit,
  COUNT(1) AS cnt,
  SUM(COUNT(1)) OVER() AS total_cnt,
  COUNT(1) / SUM(COUNT(1)) OVER() * 100 AS percentage
FROM
  UNNEST(['Apple', 'Grape', 'Apple', 'Orange', 'Banana', 'Apple', 'Orange', 'Orange', 'Orage', 'Apple', 'Grape']) AS fruit
GROUP BY
  fruit

ビンの作り方

範囲が決まっているとき

RANGE_BUCKETを使う方法

SELECT
  num,
  (RANGE_BUCKET(num,
      GENERATE_ARRAY(0,100,10) ) -1) * 10 AS bins
FROM
  UNNEST([83, 9, 19, 39, 10, 45, 111, -1, -100, 0, 1]) AS num
ORDER BY
  num

ここでは、以下の条件でビンを作った場合

• ビンの幅は10
• 最小は、0。最大は、90
• RANGE_BUCKETの性質上、下の数値が0より小さい場合、-10になり、100より大きい場合100になるため、それらを範囲外として処理などする必要あり

範囲が決まっていないとき

FLOORで計算する方法

SELECT
  num,
  CAST(FLOOR(num / 10) * 10 AS INT64) as bins
FROM
  UNNEST([83, 9, 19, 39, 10, 45, 111, -1, -100, 0, 1]) AS num
ORDER BY
  num

ビンの幅を10とした場合

指定開始日から一定期間の日付連続値を作る

2000/1/1からスタートして、100日分作る場合。日によってデータが抜けるレコードがある場合などの日付のテンプレートに使ったり。

SELECT
  DATE_ADD("2000-01-01",INTERVAL i DAY)
FROM
  UNNEST(GENERATE_ARRAY(1,100,1)) AS i

クエリのフォーマットを素早く行う

BigQueryのコンソール上でクエリを書いていると見づらくなって、ちょくちょく「フォーマット」して整えたくなるけど、ボタンからクリックするのはちょっと面倒。

ショートカットで　command + shift + f で行える。(Macのば場合)

ちょっとしたサンプルデータの作り方

CREATE TABLE `tmp.users` AS 
SELECT * 
FROM UNNEST(ARRAY<STRUCT<id INT64, name STRING, age INT64>>
[(1, 'John', 35),
 (2,'Mike', 27),
 (3, 'Brian', 23),
 (4, 'Jack', 45),
 (5, 'Nick', 33)]) AS users