SQL Serverで犬の運動記録に過去7日間移動平均を算出するウィンドウ関数活用法

犬の運動記録を分析する際、直近の活動傾向を把握するために「過去7日間の移動平均」を算出することが有効です。SQL Serverではウィンドウ関数を利用すると、各犬ごとに効率的に移動平均を計算できます。

ウィンドウ関数の基本

SQL Serverのウィンドウ関数は、行の集合を指定し、各行に対して集計を行う仕組みです。ROWS BETWEEN句を用いることで、対象となる行の範囲を自由に設定できます。

移動平均を計算する場合、直近N日間の範囲を指定することで、日ごとの平均運動時間を求められます。

過去7日間の移動平均を計算する基本クエリ

以下の例では、運動記録テーブル “DogExercise” を対象に、犬IDごとに過去7日間の運動時間の移動平均を算出しています。

SELECT DogID, RecordDate, ExerciseTime,
       AVG(ExerciseTime) OVER(
           PARTITION BY DogID
           ORDER BY RecordDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS MovingAvg7Days
FROM DogExercise
ORDER BY DogID, RecordDate;

このクエリでは、PARTITION BYで犬ごとに計算を分け、ORDER BYで日付順に並べています。ROWS BETWEEN 6 PRECEDING AND CURRENT ROW により、現在の行を含む直近7行（＝7日間）の平均を計算します。

日付が連続していない場合の対応

運動記録が毎日入力されない場合、ROWS BETWEEN句だけでは正確な過去7日間を取得できません。この場合、日付差を用いたサブクエリやJOINで日付範囲を絞る必要があります。

例えば、RecordDateの7日前以降のレコードを集計するサブクエリを用い、ウィンドウ関数でAVGを計算すると、欠測日があっても正確な移動平均を求められます。

パフォーマンスとインデックス活用

移動平均の計算では、DogIDやRecordDateにインデックスを設定することで、クエリの実行速度を大幅に向上できます。

特に大型犬データベースで日次更新がある場合、Clustered IndexをRecordDateに設定し、非Clustered IndexをDogIDに設定することが効果的です。

まとめ

SQL Serverで犬の運動記録に対して過去7日間の移動平均を算出するには、ウィンドウ関数とROWS BETWEEN句を活用するのが基本です。連続日付が欠落している場合はサブクエリで補正し、インデックスを活用してパフォーマンスを維持すると、効率的かつ正確に分析できます。