ウィンドウ関数

ウィンドウ関数は、現在の行に関連する行の集合にわたって計算を行います。行のグループに対して単一の結果を返す集約関数とは異なり、ウィンドウ関数は関連する行の「ウィンドウ」を考慮しながら、行ごとに値を返します。

関連項目： [SELECT] [WITH (CTEs)] [ ウィンドウ関数リファレンス ]

---

構文

ウィンドウ関数は、OVER 句を使用してウィンドウ指定を定義します。

SELECT <windowFunction> OVER ( <windowSpec> ) FROM <multipartIdentifier>;

windowFunctionCall::=

ウィンドウ関数

ウィンドウ関数は、ウィンドウ指定で定義された行の集合にわたって計算を行う特殊な関数です。専用のウィンドウ関数（ROW_NUMBER や LAG など）と、OVER 句と組み合わせて使用する集約関数（SUM や COUNT など）のどちらも使用できます。

{ <aggregateFunction> | ROW_NUMBER() | RANK() | DENSE_RANK() | PERCENT_RANK() 
  | CUME_DIST() | NTILE(<int>) | LAG(<expr> [,<offset> [,<default>]]) 
  | LEAD(<expr> [,<offset> [,<default>]]) | FIRST_VALUE(<expr>) 
  | LAST_VALUE(<expr>) | NTH_VALUE(<expr>, <int>) }

windowFunction::=

ウィンドウ指定

ウィンドウ指定は、ウィンドウ関数の計算のために行をどのようにパーティション分割し、並べ替え、フレーム化するかを定義します。PARTITION BY は行をグループに分割し、ORDER BY は各パーティション内の順序を決定します。省略可能なフレーム句は、現在の行を基準として、どの行を計算に含めるかを指定します。

[ <windowName> ]
[ PARTITION BY <fieldList> ]
[ ORDER BY <fieldList> [ ASC | DESC ] ]
[ { ROWS | RANGE | GROUPS } 
    { UNBOUNDED PRECEDING | <int> PRECEDING | CURRENT ROW }
  | { ROWS | RANGE | GROUPS } BETWEEN 
    { UNBOUNDED PRECEDING | <int> PRECEDING | CURRENT ROW | <int> FOLLOWING }
    AND
    { <int> PRECEDING | CURRENT ROW | <int> FOLLOWING | UNBOUNDED FOLLOWING } ]

windowSpec::=

利用可能なウィンドウ関数

カテゴリ	関数
順位付け	ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
オフセット	LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()
分布	PERCENT_RANK(), CUME_DIST()
集約	SUM(), COUNT(), AVG(), MIN(), MAX()（OVER 句と組み合わせて使用）

---

例

ROW_NUMBER、RANK、DENSE_RANK による順位付け

順位付け関数を使用して、列の値に基づいて行に順位を割り当てます。

-- Rank contributors by contribution count
SELECT
    login,
    contributions,
    ROW_NUMBER() OVER (ORDER BY contributions DESC) as row_num,
    RANK() OVER (ORDER BY contributions DESC) as rank,
    DENSE_RANK() OVER (ORDER BY contributions DESC) as dense_rank
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

累計と割合

SUM や COUNT などの集約関数をウィンドウ関数として使用し、累計や割合を計算できます。

-- Running total and percentage of contributions
SELECT
    login,
    contributions,
    SUM(contributions) OVER (ORDER BY contributions DESC) as running_total,
    SUM(contributions) OVER () as total_contributions,
    ROUND(100.0 * contributions / SUM(contributions) OVER (), 2) as pct_of_total
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

LAG と LEAD による行の比較

LAG と LEAD を使用して、前の行または後続の行の値にアクセスします。

-- Compare each release to previous release
SELECT
    tag_name,
    name,
    published_at,
    LAG(tag_name, 1) OVER (ORDER BY published_at) as previous_release,
    LEAD(tag_name, 1) OVER (ORDER BY published_at) as next_release,
    julianday(published_at) - julianday(LAG(published_at, 1) OVER (ORDER BY published_at)) as days_since_last_release
FROM github.repos.releases
WHERE owner = 'stackql'
  AND repo = 'stackql'
ORDER BY published_at;

NTILE によるグループの作成

NTILE を使用して、行を指定した数のグループに分割します。

-- Group contributors into quartiles
SELECT
    login,
    contributions,
    NTILE(4) OVER (ORDER BY contributions DESC) as quartile,
    CASE NTILE(4) OVER (ORDER BY contributions DESC)
        WHEN 1 THEN 'Top Contributors'
        WHEN 2 THEN 'Active Contributors'
        WHEN 3 THEN 'Moderate Contributors'
        WHEN 4 THEN 'Occasional Contributors'
    END as tier
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

PARTITION BY を使用したグループ別の分析

PARTITION BY を使用して、グループ内でウィンドウ計算を行います。

-- Track cumulative issues by state
SELECT
    number,
    title,
    state,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as issue_sequence,
    COUNT(*) OVER (ORDER BY created_at) as cumulative_issues,
    COUNT(*) OVER (PARTITION BY state ORDER BY created_at) as cumulative_by_state
FROM github.issues.issues
WHERE owner = 'stackql'
  AND repo = 'stackql'
ORDER BY created_at;

ウィンドウフレームの使用

フレーム句を指定して、ウィンドウ計算に含める行を制御します。

-- Calculate moving average of commit activity
WITH weekly_totals AS (
    SELECT
        week,
        SUM(json_each.value) as commits_this_week
    FROM github.repos.stats_commit_activity, JSON_EACH(days)
    WHERE owner = 'stackql'
      AND repo = 'stackql'
    GROUP BY week
)
SELECT
    week,
    commits_this_week,
    ROUND(AVG(commits_this_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1) as four_week_moving_avg,
    SUM(commits_this_week) OVER (ORDER BY week) as cumulative_commits
FROM weekly_totals
ORDER BY week;

各ウィンドウ関数の詳しいドキュメントについては、ウィンドウ関数リファレンスを参照してください。

詳細については、https://sqlite.org/windowfunctions.html を参照してください。