ウィンドウ関数は、行間の計算に使用されます。 一般的な SQL 集計関数は、1 つの行のみの結果を計算するか、すべての行を 1 つの行に集約して計算します。 ウィンドウ関数は行間計算をサポートし、各行に計算結果を入力します。

ウィンドウ関数の構文： 
SELECT key1, key2, value,
       rank() OVER (PARTITION BY key2
                    ORDER BY value DESC) AS rnk
FROM orders
ORDER BY key1,rnk
コア部分は次のとおりです。 
rank() OVER (PARTITION BY KEY1 ORDER BY KEY2 DESC)

rank() は集合関数です。 分析構文や本書に記載されている関数内の関数を使用することができます。 PARTITION BY は、どの値が計算されるかに基づいてバケットを示します。

Windows で使われる特別な集計関数

関数名 説明
rank() ウィンドウ内の特定の列に基づいてデータをソートし、ウィンドウ内のシリアル番号を返します。
row_number() ウィンドウ内の行番号を返します。
first_value(x) ウィンドウ内の最初の値を返します。 一般的に、値がウィンドウ内でソートされた後に最大値を取得するために使用されます。
last_value(x) first_value の反対です。
nth_value(x, offset) ウィンドウ内の x 番目の列の No. オフセット行の値。
lead(x,offset,defaut_value) ウィンドウの x 番目の列の特定の行の後にあるオフセット行の値。 その行が存在しない場合は、default_value を使用します。
lag(x,offset,defaut_value) ウィンドウ内の x 番目の列の特定の行の前のオフセット行の値。 その行が存在しない場合は、default_value を使用します。

例

  • それぞれの部門の従業員の給与をランク付けする 
    * | select department, persionId, sallary , rank() over(PARTITION BY department order by sallary desc) as sallary_rank order by department,sallary_rank

    応答結果：

    部門 個人 ID 給料 給料_ランク
    dev john 9000 1
    dev Smith 8000 2
    dev Snow 7000 3
    dev Achilles 6000 4
    Marketing Blan Stark 9000 1
    Marketing Rob Stark 8000 2
    Marketing Sansa Stark 7000 3
  • 従業員の給与を各部門のパーセンテージとして計算する 
    * | select department, persionId, sallary *1.0 / sum(sallary) over(PARTITION BY department ) as sallary_percentage

    応答結果：

    department persionId sallary sallary_percentage
    dev john 9000 0.3
    dev Smith 8000 0.26
    dev Snow 7000 0.23
    dev Achilles 6000 0.2
    Marketing Blan Stark 9000 0.375
    Marketing Rob Stark 8000 0.333
    Marketing Sansa Stark 7000 0.29
  • 前日より日次 UV 増加を計算する 
    * | select day ,uv, uv *1.0 /(lag(uv,1,0) over() ) as diff_percentage from

select approx_distinct(ip) as uv, date_trunc('day',__time__) as day from log group by day order by day asc

    応答結果：

    day uv diff_percentage
    2017-12-01 00:00:00 100 null
    2017-12-02 00:00:00 125 1.25
    2017-12-03 00:00:00 150 1.2
    2017-12-04 00:00:00 175 1.16
    2017-12-05 00:00:00 200 1.14
    2017-12-06 00:00:00 225 1.125
    2017-12-07 00:00:00 250 1.11