SQLの高みへ！分析(window)関数について(Bigquery)

概要

ただ、書けるから、より書けるにステップアップ！
そのため忘れないようにメモ程度に投稿します！

そもそもwindow関数とは

何らかの条件で並んでいるデータに対して、特定の範囲のデータのみ参照できるようにする機能が用意されており、これをWindowと呼んでいます。

集合関数との違い

集合関数ではGROUP BYの同じカラム値をもつ全行を一つに集計しますが、分析関数では集計対象となる行の範囲を任意で指定できます。関数に続くOVER句でこの範囲指定を行います。集合関数と分析関数は基本同じ名前なので、よく使う一般的な集合関数は後ろにOVER句をつけれれば、分析関数になる

OVER句について

◇指定可能な３種類
partition by
order By
window

分析関数

SELECT id, item, COUNT(*) OVER (PARTITION BY item)
FROM test_orders ORDER BY id;

ID  item  COUNT(*)OVER(PARTITIONBYITEM)
-------------------- -----------------------------
A  Apple    4
B  Banana   2
C  Banana   2
D  Apple    4

PARTITION BY

列単位で複数の集計をする関数
「カラム１」と「カラム１＆カラム２」の在庫を1つの表に抽出可能

SELECT DISTINCT
 CODE	AS 'コード'
,name	AS '名前'
,SUM(カラム3) OVER(PARTITION BY CODE,name)	AS "総在庫_name"
,SUM(カラム3) OVER(PARTITION BY CODE)	AS "総在庫_code"
 FROM テーブル

LAG関数，LEAD関数

指定したカラムの行の前後のデータが得られます。

 number,
  LAG (number, 1) OVER (ORDER BY date) AS lag_data,
  --LEAD()内で，参照するカラム名とずらす行数を指定
  LEAD (number, 1) OVER (ORDER BY date) AS lead_data 

number, lagdata, lead_data
----------------------------
100,     null,      200,
200,     100,       300,
300,     200,       400,

以上です。
ありがとうございました。