国または地域と言語

    国または地域と、言語を選んでください。

    おすすめ

      すべての国と地域

        キャラクターを選ぶ

        上部のバーとプロフィールに表示されます。いつでも変更できます。

        SQLコース

        ウィンドウ関数:ROW_NUMBER、RANK、DENSE_RANK

        SQLを開いています
        取り組み中

        SQLのウィンドウ関数:OVER、PARTITION BY、ROW_NUMBER

        SQLのウィンドウ関数(window関数、分析関数とも呼ばれます)は、複数の行にまたがって計算しながら、すべての行をそのまま返します。GROUP BYはグループを1行にまとめますが、ウィンドウ関数は行を残したまま、順位、累計、前月の値のような列を横に足します。ROW_NUMBERにPARTITION BYを組み合わせる書き方は、その代表です。

        例のテーブル

        region month revenue
        North 2026-01 120
        North 2026-02 150
        North 2026-03 150
        South 2026-01 90
        South 2026-02 80
        South 2026-03 110

        書き方

        function_name(...) OVER (PARTITION BY ... ORDER BY ...)

        OVERがあるとウィンドウ関数になります。PARTITION BYは行をグループに分け、グループごとに計算をやり直します。省くと、テーブル全体が1つのウィンドウです。OVERの中のORDER BYは、関数が行をたどる順番を決めるだけで、出力は並べ替えません。出力を並べるには、クエリの最後にORDER BYを書きます。

        ROW_NUMBER、RANK、DENSE_RANK

        SELECT region, month, revenue,
          ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS row_num,
          RANK()       OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk,
          DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rnk
        FROM sales;

        Northでは2月と3月が150で同じです。ROW_NUMBERはそれでも1と2を振り、どちらが1になるかはデータベースしだいです。RANKは両方を1にして、1月は3に飛びます。DENSE_RANKは両方を1にして、1月は2です。Southには同じ値がないので、3つとも1、2、3を返します。同じ値をどう扱いたいかで関数を選び、毎回同じ結果が必要ならORDER BYに2つ目の列を足してください。

        グループごとの上位の行

        WHEREはウィンドウが計算される前に動くので、ウィンドウ関数の結果では絞り込めません。サブクエリかWITHの中で番号を振り、その外で絞り込みます。

        WITH ranked AS (
          SELECT region, month, revenue,
            ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC, month) AS rn
          FROM sales
        )
        SELECT region, month, revenue FROM ranked WHERE rn = 1;

        結果はNorthの2月とSouthの3月です。Snowflake、BigQuery、DuckDBでは、最初のクエリの最後にQUALIFY rn = 1と書くこともできます。

        累計と移動平均

        SUM(revenue) OVER (PARTITION BY region ORDER BY month
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total

        Northの累計は120、270、420、Southは90、170、280です。フレームをROWS BETWEEN 2 PRECEDING AND CURRENT ROWに変えてAVGを使えば、3か月の移動平均になります。フレームは省かずに書きましょう。ORDER BYがあってフレームがないと、多くのデータベースはRANGEを使い、同じ値の行をまとめて足すので、累計が急に跳ねることがあります。

        LAGとLEAD

        LAG(revenue) OVER (PARTITION BY region ORDER BY month)は各行に前月の売上を、LEADは翌月の売上を返します。引き算すれば増減がわかります。Northは1月がNULL、そのあと+30、0です。Southは-10、+30です。各地域の最初の行には前月がないので、レポートでNULLと0のどちらが正しいかを決めてください。

        対応しているデータベース

        PostgreSQL、SQL Server、Oracle、MySQL 8.0以降、SQLite 3.25以降、BigQuery、Snowflake、DuckDBは、どれもウィンドウ関数に対応しています。このレッスンの練習問題は、架空のオンラインストアKestrelのテーブルに対してDuckDBで動き、返ってくる行でクエリを確認します。

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        プランを変更

        価格を確認しています

        ヘルプとフィードバック

        質問、うまく動かない点、アイデアなどをお寄せください。メールで返信します。

        どのような内容ですか?

        返信のためだけに使用します。