SQL Interview Prep · レッスン

スライディングウィンドウで移動平均を求める

Q: 「スライディングウィンドウで移動平均を求める」で何を学びますか？

BETWEEN PRECEDING AND CURRENT ROWを使って、N期間の移動平均を計算します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

BETWEEN PRECEDING AND CURRENT ROWを使って、N期間の移動平均を計算します。

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

移動平均の質問

アナリストはこの質問を頻繁に受けます。「売上の7日間移動平均を計算してください。」移動平均（ローリング平均）は、各データ点を直近の前後の値とともに平均することで、日次データのノイズを滑らかにします。

面接で評価される回答は、明示的なスライディングフレームを指定したウィンドウ関数の AVG です。重要なのは、順序に沿ってウィンドウが正しく移動するようにフレームの境界を選ぶことです。

基本パターン

移動平均は AVG(value) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW) で表します。フレームは各行で移動し、その行と直前の n 行を対象にします。

日次の行に対して7日間のウィンドウを作る場合は、現在の行に加えて6行前までを対象にするため、合計7行になります。ここでのオフバイワンは、面接官が最もよく見つける間違いです。

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

ウィンドウサイズを正しく数える

ウィンドウサイズは preceding + 1 です（+1は現在の行です）。したがって、次のようになります。

3行のウィンドウ：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
7行のウィンドウ：ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
30行のウィンドウ：ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

面接ではこの計算を声に出して説明すると、推測ではなく意図的に選んでいることを示せます。

中央寄せと後方ウィンドウ

上の例は後方移動平均です。過去の値だけを見るため因果的であり、予測用ダッシュボードにも安全に使えます。

中央寄せの移動平均は前後の両方を参照します。たとえば、7行の中央寄せウィンドウには ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING を使います。中央寄せのウィンドウはより対称的に平滑化できますが、リアルタイムでは最新の行に対して計算できません。どちらが用途に必要かを説明してください。

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS centered_avg_7
FROM daily_sales;

ウィンドウ端の影響

データの先頭では、完全なウィンドウがまだ存在しません。7日間の後方移動平均の1行目では、利用できる行は1行だけなので、AVG はその1つの値だけを平均します。

そのため、先頭の行では少ない行数による「ウォームアップ」平均が表示されます。面接では、この扱いについて質問されます。選択肢は2つあります。部分ウィンドウを許容するか、完全な件数を要求して先頭の行を非表示にします。

部分ウィンドウを除外する

完全なウィンドウが利用できるまで NULL にしたい場合は、同じフレームに対して COUNT(*) を使い、CASE 式で短いウィンドウを空にします。

これは境界での正確性まで考慮していることを示す洗練された工夫で、多くの候補者が見落とす細部です。

SELECT
  sale_date,
  CASE WHEN COUNT(*) OVER w = 7
       THEN AVG(amount) OVER w
  END AS moving_avg_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

WINDOWでフレームを再利用する

前のクエリでは名前付きの WINDOW 句を使いました。同じフレームが複数回登場する場合は、WINDOW w AS (...) として1回だけ定義し、OVER w で参照すると、クエリをDRYに保ちながら読みやすくできます。

Postgres、MySQL 8、SQL Serverは名前付きウィンドウをサポートしています。面接で使うと、ウィンドウ関数をコピー＆ペーストしているだけではない習熟度を示せます。

SELECT
  sale_date,
  AVG(amount) OVER w AS avg_7d,
  SUM(amount) OVER w AS sum_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

カレンダー日数と行数の落とし穴

微妙な落とし穴があります。テーブルに欠落した日がある場合、ROWS 6 PRECEDING は直近7記録済み行を対象にするため、7暦日を超える期間にまたがる可能性があります。

間隔を考慮した真のカレンダー基準の7日間平均には、間隔オフセット付きの RANGE を使うか、先に完全な日付系列と結合して毎日に1行が存在するようにします。この違いを説明できることが、前のレッスンで学んだROWSとRANGEの重要なポイントです。

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS calendar_avg_7d
FROM daily_sales;

グループごとの移動平均

累積合計と同様に、移動平均も通常はエンティティごとにリセットする必要があります。PARTITION BY を追加して、商品や店舗ごとに独立したローリングウィンドウを作り、グループ間で値が混ざらないようにします。

フレームと順序は各パーティション内で独立して適用されるため、各グループの先頭行では正しくウォームアップが始まります。

SELECT
  product_id,
  sale_date,
  AVG(amount) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS product_ma_7d
FROM daily_sales;

平滑化の効果

なぜ必要なのでしょうか。日次売上は変動が激しいため、移動平均によって傾向が見えるようになります。アナリストがよく使う回答では、生の値と平滑化した線を並べ、ダッシュボードにシグナルとノイズの両方を表示します。

短期と長期の移動平均（たとえば7日間と30日間）を比較して勢いを検出することもできます。これは、移動平均クロスオーバーに相当するSQLでの手法です。

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS ma_7,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30
FROM daily_sales;

面接チェックリスト

移動平均の質問に確実に答えるには、次の点を説明します。

AVG OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW)
ウィンドウサイズ = preceding + 1
後方ウィンドウと中央寄せウィンドウの選択
部分ウィンドウのウォームアップと、その抑制方法
日付が欠落している場合の ROWS と RANGE の違い
エンティティごとにリセットするための PARTITION BY

クイックチェック

日次の行に対する7日間の後方移動平均には、どのフレームを選びますか。

まとめ：移動平均

移動平均は AVG(value) OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW) で表し、ウィンドウサイズは直前の行数に1を加えたものです。用途に合わせて後方または中央寄せを選び、COUNT によるガードでウォームアップ中の行を扱い、カレンダー上の間隔が重要な場合は RANGE の間隔指定に切り替えます。

PARTITION BY でエンティティごとにリセットし、名前付きの WINDOW でフレームを再利用します。次は、累積合計を全体に占める割合に変換していきます。

よくある質問

「スライディングウィンドウで移動平均を求める」レッスンは無料ですか？

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

「スライディングウィンドウで移動平均を求める」で何を学びますか？

BETWEEN PRECEDING AND CURRENT ROWを使って、N期間の移動平均を計算します。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「スライディングウィンドウで移動平均を求める」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← SQL Interview Prepに戻る