SQL学習～WINDOW関数編～

データエンジニアになって思ったこと

業務で使うクエリってすごく難しい！！！！！！！！！！！！

前職（システムエンジニア）の時はちょこっとだけクエリを書くことはあったものの、簡単なSELECT文や、CREATE文、DELETE文しか書いてこなかった。
でもデータエンジニアになってからかなり長いクエリを見て理解しないといけなくなった。
人が書いたものを理解してそれを修正したりする業務があり、かなり深い沼だぞこれは…と思った。

特に躓いたのはWINDOW関数。
基礎的なSUM関数ならわかるけどWINDOW関数とは何ぞや⁈となった。

WINDOW関数とは

ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。 とは言っても、通常の集約関数とは異なり、ウィンドウ関数の使用は単一出力行に行をグループ化しません。 行はそれぞれ個別の身元を維持します。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることができます。

PostgreSQL 9.4.5文書

まだわからない…。
形としてはこのような感じで書かれる。

avg(salary) OVER (PARTITION BY depname)

depnameごとのsalaryの平均が出されることになるのだが、
これがどう使えるのだろう？

本を読んでみる

読んでみたのはミッケさんが書いた『SQL実践入門 高速で分かりやすいクエリの書き方』。この文でようやくなんとなくWINDOW関数の使い方が分かった！

PARTITION BY句はGROUP BY句と違って集約機能を伴わないため、元のPersonsテーブルの行がすべてそのままの形で出てくることに注目してください。言い換えると、GROUP BY句は入力の集合を集約して全く異なるレベルの出力に変換しますが、PARTITION BY句は入力に情報を付け加えるだけなので、オリジナルのテーブルの情報を完全に保存しているのです。

『SQL実践入門 高速で分かりやすいクエリの書き方』ミッケ著

例えばpersonsというテーブルがあったとして、所属部署ごとの年齢合計を出したいとする。

personsには名前・年齢・所属部署・給料が登録されている

そこで、集約関数を使うと、このように結果として出てくるテーブルが全然違った形で出てきてしまう。
nameやsalaryの内容は完全に無視されて、SELECTに入れることができない。

結果はdepName（入力の一部）とsumAge（新情報）しかない状態

しかし、ここでWINDOW関数を使うことで、このように結果として出てくるテーブルは、既存テーブルに情報を追加する形で出すことができる。

結果はname, age, depName, salary（入力のすべて）とsumAge（新情報）が並んでいる状態

つまりWINDOW関数の使いどころは、次の計算時に集計情報だけではなく元情報の何かを使用したいときだと理解できた！

WINDOW関数の個人的な集計イメージは、
窓（WINDOW）を開いて見える指定された項目（今回の場合はageとdepName）の集計結果を出した後、窓を閉めて結果テーブル完成
という感じ。
とにかく、入力元がすべて書き換わってしまうということは無く、入力元を窓から見て参照しただけであって直接手を加えていないよ～みたいなニュアンスが伝わればいいなと思う。

窓からageとdepNameだけ見えるから集計したsumAgeを付け足しとくねみたいな。

おわりに

WINDOW関数が理解できたから、業務で使うクエリがスラスラ読める…ということは無いが、「どうしてここでWINDOW関数を使っているんだろう？」という疑問が解消されたので、余計なことを考えずにクエリが読めるようになった。
あとは自分でクエリを作る時に、意図的にWINDOW関数を使えるようになれば理解は完璧だなと思う。