[2020年7月] Google Cloud Certified – Professional Data Engineer日本語版(プロ認定):BigQuery ウィンドウ関数まとめ

BigQueryはよく使ってるので余裕だろって思ってたら「ウィンドウ関数」なる知らない単語が模試で出たので、自分なりにかみ砕いてまとめました。

 

あ、申し遅れました。私はProfessional Data Engineerの資格を取るため勉強中のへっぽこSE、こと、すえきあおいです。ちなみに試験は7/29。あと21日です。(これは予定投稿なので、記事を書いている時と公開日時にはタイムラグがあります)

カウントダウン怖いw

 

ウィンドウ関数とは

行のグループに対して値を計算し、各行に対して 1 つの結果を返します。

これは、行のグループ全体に対して 1 つの結果を返す集計関数(GROUP BY)とは異なります。

 

???

ってなったと思うんですが、こういうのは具体的な使い方をみるとスッと理解できるので、さっさと読み進めてください。

まーあえて補足するなら、GROUP BYしたときに集計値と、その明細を並べて見たい時に使うイメージでしょうか。

明細をみて分析するから、分析関数なのかな。ウィンドウ関数、ともいいますが、グループ化した塊をそれぞれ一つの窓枠(ウィンドウ枠)に納める、というのもイメージしやすいかも。

ちなみに、グループはOVER(PARTITION BY ほにゃらら)で定義する。

 

そして、BigQueryのウィンドウ関数には3種類あります。

 

BigQuery ウィンドウ関数①:ナビゲーション関数

LAG ウィンドウ関数

先行する 1 つの行の値。

現在の行より前を範囲に指定できます。デフォルトだとオフセットが1なので、自分の直前の値が返ってきます。

この例だとLAG(name, 2)なので、ウィンドウ枠内(divisionが同じ値)に区切って見たときの2行前のnameが返ってきます。


LEADウィンドウ関数:後続の 1 つの行の値。

LAGの後ろバージョン。

PERCENTILE_CONTウィンドウ関数

Percentile Continuous(連続)の 略。

パーセンタイル(Percentile)とは、 65パーセンタイルであれば、最小値から数えて65%に位置する値を指す。

なので、PERCENTILE_CONT(x, 0.01)だったら最初うちから数えて1%に位置する値を返します。

 

PERCENTILE_DISC ウィンドウ関数

Percentile Discreet(思慮深い?w)の略。正直、CONTとの違いがわからない。わからないけど、まぁいいや。似たような感じって覚えとく。

BigQueryのウィンドウ関数②:番号付け関数

RANK ウィンドウ関数

順序付けされたパーティション内の各行の(1 から始まる)序数ランクを返します。

ふむ。OVER (ORDER BY value DESC) としているので、値が大きいのが1位なんですね。ふむふむ。

 

CUM_DIST ウィンドウ関数

Cumulative Distributionの略。累積分布。

NP/NR として定義される行の相対的ランクを返します。NP は、現在の行に先行するか、ピアになっている行数に定義されます。NR はパーティション内の行数です。

ROW_NUMBER ウィンドウ関数

順序付けされた各パーティションの各行の順次行序数(1 ベース)を返します。ORDER BY 句が指定されていない場合、結果は変動する可能性があります。

BigQueryのウィンドウ関数③:集計分析関数

MIN, MAX, SUM, AVG など、知ってるのが多かったので省略。

おそらく、ウィンドウ枠ごとに集計するんでしょうね。マニアックなやつは、時間があれば調査してまとめます。

 

勉強になったー。

普通にSQLとか使ってますが、ウィンドウ関数って聞いたことすらなかったので、ここで勉強できてよかったです。

一連のGCPプロ認定資格試験の勉強、不安半分、めんどくさい半分で始めたんですが、新たな気づきがたくさんあっていいですね。やってみてよかったです。

Data Enginnere取れたら別のやつも挑戦してみたいと思ってます。

 

それでは!

末岐 碧衣
  • 末岐 碧衣
  • フリーランス のシステムエンジニア。独立後、一度も営業せずに月収 96 万円を達成。1986年大阪生まれ。早稲田大学理工学部卒。システムエンジニア歴 12年。
    2009年、ITコンサルティング企業に入社。3年目でコミュ障が爆発し人間関係が崩壊。うつにより休職するも、復帰後はコミュ障の自覚を持ち、「チームプレイ」を徹底的に避け、会社組織内においても「一人でできる仕事」に専念。社内外から評価を得た。
    無理に「チームプレイ」するよりも「一人でできる仕事」に専念した方が自分も周囲も幸せにできることを確信し、2015年フリーランスとして独立。