case-kの備忘録

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

BigQueryのウィンドウ処理

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

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

f:id:casekblog:20191029214402p:plain:w300

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)

f:id:casekblog:20191029215409p:plain

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

f:id:casekblog:20191029220828p:plain:w300

参考
qiita.com