BigQueryの名前つきウィンドウでウィンドウ指定のあるクエリを読みやすくする

Window Frame句

SQL中にWINDOW ~~~と書くことでPARTITION BY ~~~やORDER BY ~~~などの指定に名前をつけることができOVER句で参照できる

分析関数のコンセプト  |  BigQuery  |  Google Cloud

cloud.google.com

今までこの存在を知らず1つずつウィンドウ指定を書いていたけど社内の勉強会で同僚に教えてもらった

都度書いていた同じウィンドウ指定をまとめられる!しかも名前を付けることで意味付けできる！

よいことしかなさそうなので実際に個人で使っているSQLを変えてみる

サンプルデータはTogglの作業記録からとってきているデータを使う

サンプルのデータ

こんな感じのデータ

SQLは表現を簡単にするため、単にLAGで対象行から前月分(last_month_hours)、前々月分(last_2_month_hours)の合計時間を出すようにした

実際に使う場合はここから前月比出したりとかそういう計算を行う想定

before

SELECT
month,
project,
ROUND(SUM(hour), 1) AS current_hours,
ROUND(LAG(SUM(hour),1) OVER(PARTITION BY project ORDER BY month), 1) AS last_month_hours,
ROUND(LAG(SUM(hour),2) OVER(PARTITION BY project ORDER BY month), 1) AS last_2_month_hours,
FROM sample_table
WHERE workspace = 'work'
GROUP BY month, project
ORDER BY month

after

SELECT
month,
project,
ROUND(SUM(hour), 1) AS current_hours,
ROUND(LAG(SUM(hour),1) OVER(p_project__o_month), 1) AS last_month_hours,
ROUND(LAG(SUM(hour),2) OVER(p_project__o_month), 1) AS last_2_month_hours,
FROM sample_table
WHERE workspace = 'work'
GROUP BY month, project
WINDOW p_project__o_month AS (PARTITION BY project ORDER BY month)
ORDER BY month

• 結果

サンプルだけだとそんなに変わらないなーという感じ

しかし、項目が増えて複雑なウィンドウ指定を都度書く必要がでてきたりした場合は有効かなと感じる

他

ドキュメント見たときにどんな単位にでも展開してくれるのか！ と思って試してみたがそこまでよしなにはやってくれない模様

ORDER BY, PARTITION BYなどのウィンドウ指定の単位で定義する必要があるのと一度名前を付け足ウィンドウ指定を2つ並べて含めるなどもできなかった

ORDER BY aとORDER BY a DESC両方定義したい場合

WINDOW
  o_a AS (ORDER BY a),
  o_a_desc AS (o_a DESC)

といった書き方は構文エラーできない

WINDOW
  o_a AS (ORDER BY a),
  o_a_desc AS (ORDER BY a DESC)

このように記述する必要があるよう

またドキュメントからは見つけられなかったがWHERE句、GROUP BYやORDER BY句も存在する場合は

• GROUP BY
• WINDOW
• ORDER BY

の順番である必要があるっぽい(並びを変えると次のような構文エラーとなった)

Syntax error: Expected end of input but got keyword WINDOW at [19:1]

感想

指標違いで同じウィンドウ指定の列が複数個発生する場合、名前付きウィンドウを使用することでSQLの見通しをよくできそう

命名規則などをしっかりすればだいぶ可読性が上がるのでは? と感じた