BigQueryのウィンドウ処理

BigQueryウィンドウ処理の備忘録です。

Dataset

console.cloud.google.com

RANK()

ベンダーごとの旅客数が多いレコード順にランクを付与します。

SELECT
  vendor_id,
  passenger_count,
  RANK() OVER (PARTITION BY vendor_id ORDER BY passenger_count DESC) AS rank_passenger_count
FROM
  `nyc-tlc.yellow.trips`
GROUP BY
  vendor_id,
  passenger_count
LIMIT
  1000

LAG/LEAD

レコード前後の旅客数を取得します。LAGは前のレコードで、LEADが後ろのレコードを取得します。移動平均などを計算したい場合利用します。

SELECT
  vendor_id,
  pickup_datetime,
  passenger_count,
  LAG(pickup_datetime ) OVER (ORDER BY pickup_datetime DESC ) AS lag_pickup_datetime,
  LAG(passenger_count) OVER (ORDER BY pickup_datetime DESC ) AS lag_passenger_count,
  LEAD(pickup_datetime) OVER (ORDER BY pickup_datetime DESC ) AS lead_pickup_datetime,
  LEAD(passenger_count) OVER (ORDER BY pickup_datetime DESC ) AS lead_passenger_count
FROM (
  SELECT
    *
  FROM
    `nyc-tlc.yellow.trips`
  LIMIT
    1000)

ROW_NUMBER()

ベンダーごとに新しいレコード順にIDを付与します。enteridが1のデータを取得すればベンダーごとに最新のレコードを取得することができます。

SELECT
  vendor_id,
  pickup_datetime,
  enterid
FROM (
  SELECT
    vendor_id,
    pickup_datetime,
    ROW_NUMBER() OVER (PARTITION BY vendor_id ORDER BY pickup_datetime DESC) AS enterid
  FROM (
    SELECT
      *
    FROM
      `nyc-tlc.yellow.trips`
    LIMIT
      1000))
WHERE
  enterid = 1

参考
qiita.com