0Pricing
SQL Interview Prep · レッスン

FIRST_VALUE、LAST_VALUE、フレーム端点

境界値の取得方法と、LAST_VALUEに関するフレームの落とし穴を学びます。

「FIRST_VALUE、LAST_VALUE、フレーム端点」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

境界値を取得する

面接官は、「各行にグループ内の最初と最後の値を並べて表示してください」と質問します。ユーザーごとの初回ログイン日や、パーティション内の最新価格を各明細行に並べる処理を考えてみてください。

使う関数はFIRST_VALUEとLAST_VALUEです。単純そうに見えますが、LAST_VALUEにはSQLで最も有名なウィンドウフレームの落とし穴の一つが隠れています。このレッスンでは、どちらの関数も確実に使えるようにします。

FIRST_VALUEの基本

FIRST_VALUE(col)は、ウィンドウの最初の行にあるcolの値を、すべての行に付加して返します。日付順に並べると、各行にそのパーティション内で最も早い値が付与されます。

デフォルトのフレームはパーティションの最初の行から始まるため、FIRST_VALUEは通常、期待どおりに動作します。

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS first_login
FROM logins;

デフォルトのウィンドウフレーム

ここが核心です。ウィンドウに ORDER BY を追加すると、デフォルトのフレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW になります。

つまり、各行のウィンドウはパーティションの先頭から現在の行までだけを対象とし、末尾までは対象にしません。FIRST_VALUE には影響しません（最初の行は常に範囲内に含まれるため）が、LAST_VALUE には大きな影響があります。

LAST_VALUE の落とし穴

ORDER BY だけを指定して LAST_VALUE を実行すると、多くの受験者はパーティションの最後の値が返ると考えます。しかし、フレームの末尾が現在の行になっているため、「フレーム内の最後の値」は現在の行自身の値にすぎません。

そのため、このクエリはすべての行で login_date 自体を返し、壊れているように見えます。これは、ウィンドウ関数で最も頻繁に質問される落とし穴です。

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS wrong_last_login
FROM logins;

フルフレームで LAST_VALUE を修正する

フレームをパーティション全体に広げれば解決できます。指定するのは ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING です。

これで各行のウィンドウがパーティション全体を対象とするため、LAST_VALUE は本当の最後の値を返します。面接ではこの修正方法を明示してください。単に関数名を知っているだけでなく、フレームを理解していることを示せます。

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS last_login
FROM logins;

より簡単な代替方法

多くのエンジニアは、フレーム自体を使わない方法を選びます。最後の値を取得するには、並び順を逆にして FIRST_VALUE を使います。

FIRST_VALUE(login_date) OVER (... ORDER BY login_date DESC) は、フレーム句を指定しなくても最新の日付を返します。覚えやすく、すっきりしたテクニックなので、言及するとよいでしょう。

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date DESC
  ) AS last_login
FROM logins;

フレームにおける ROWS と RANGE の違い

フレームには2種類あります。ROWS は物理的な行を数え、RANGE は同じ ORDER BY の値を持つ行（ピア）でグループ化します。

デフォルトのフレームでは RANGE が使われるため、同じ ORDER BY 値を持つ行はフレームの境界を共有します。LAST_VALUE を修正するときは、同順位の値による予期しない動作を避けるため、明示的な ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を使うのがおすすめです。

任意の位置には NTH_VALUE

最初と最後以外にも、NTH_VALUE(col, n) を使うと、フレーム内の n 番目の位置にある値を取得できます。たとえば、2番目に高い価格を取得できます。

これは LAST_VALUE と同じフレーム規則に従います。そのため、現在の行までではなくパーティション全体から n 番目の値を取得したい場合は、フルフレームと組み合わせてください。

SELECT
  product_id,
  price,
  NTH_VALUE(price, 2) OVER (
    PARTITION BY product_id
    ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS second_highest_price
FROM prices;

実践例：最初と最後の値を同時に取得する

よくあるレポートでは、各取引の横に、その顧客の最初と最後の取引金額を表示します。両方の関数を組み合わせ、LAST_VALUE には明示的なフレームが必要であることを忘れないでください。

これで各行にパーティション全体の最初と最後の値が入り、差分の計算やラベル付けにそのまま使えるようになります。

SELECT
  customer_id,
  txn_date,
  amount,
  FIRST_VALUE(amount) OVER w AS first_amt,
  LAST_VALUE(amount) OVER w AS last_amt
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

名前付きウィンドウで重複を避ける

前のクエリでは、WINDOW w AS (...) 句を使い、OVER w を2回参照していたことに注目してください。ウィンドウを一度定義しておけば、長いフレーム指定を繰り返さずに済み、2つの関数の定義がずれてしまうのも防げます。

主要なデータベースの多くは名前付きウィンドウをサポートしています。複数の列で同じウィンドウを共有する場合に使うと、面接官にも好印象を与える、すっきりした書き方です。

実践例：最初から最後までの差分

よくある追加質問は、顧客の最初の取引から最後の取引までの変化を求める方法です。各行に両端の値があるので、それらを引き算し、必要であれば顧客ごとに1行になるよう重複を排除します。

これは、フルフレームの修正と単純な算術を組み合わせたものです。面接官が見たい、最初から最後までをきれいに組み立てた回答の典型です。

SELECT DISTINCT
  customer_id,
  LAST_VALUE(amount) OVER w - FIRST_VALUE(amount) OVER w AS first_to_last_delta
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

確認問題

LAST_VALUE の典型的な落とし穴です。

まとめ

境界値関数では、フレームが重要です。

  • FIRST_VALUE はデフォルトのフレームで機能しますが、LAST_VALUE は機能しません。
  • デフォルトのフレームは現在の行で終わるため、LAST_VALUE は ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING で修正します。または、並び順を逆にして FIRST_VALUE を使います。
  • NTH_VALUE(col, n) は任意の位置の値を取得します。名前付きウィンドウを使うと、複数列の指定から重複をなくせます。

これで、LAG、LEAD、NTILE、境界値関数のツールキットは完成です。

よくある質問

「FIRST_VALUE、LAST_VALUE、フレーム端点」レッスンは無料ですか？

はい。「FIRST_VALUE、LAST_VALUE、フレーム端点」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「FIRST_VALUE、LAST_VALUE、フレーム端点」で何を学びますか？

境界値の取得方法と、LAST_VALUEに関するフレームの落とし穴を学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。

「FIRST_VALUE、LAST_VALUE、フレーム端点」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. LAGとLEADで隣接行を参照する
  2. 期間ごとの変化を求める
  3. NTILEで区分に分ける
  4. FIRST_VALUE、LAST_VALUE、フレーム端点
← SQL Interview Prepに戻る