SQLで犬の運動連続日数を検出するウィンドウ関数と日付差分の活用法

犬の運動記録を管理するテーブルでは、連続した日付で運動が行われた期間（連続日数）を抽出することが重要です。SQLのウィンドウ関数と日付差分を組み合わせることで、効率的に連続日数を検出できます。

連続日数検出の基本概念

連続日数を検出するには、各レコードに対して日付の差分を計算し、ギャップを識別する方法が有効です。犬ごとに運動日を昇順で並べ、行番号を付与することで、連続日数のグループ化が可能になります。

例として、ROW_NUMBER()を使って犬ごとに日付順の連番を割り振り、visit_dateからその連番を引くと、同じ差分を持つレコードは連続日数のグループになります。

サンプルクエリの設計

犬の運動記録テーブルがDogExercise(dog_id, exercise_date, duration)の場合、次のようなクエリで連続日数を検出できます。

例：
WITH Ordered AS (
SELECT dog_id, exercise_date, ROW_NUMBER() OVER(PARTITION BY dog_id ORDER BY exercise_date) AS rn
FROM DogExercise
)
SELECT dog_id, MIN(exercise_date) AS start_date, MAX(exercise_date) AS end_date, COUNT(*) AS consecutive_days
FROM Ordered
GROUP BY dog_id, DATEADD(DAY, -rn, exercise_date);

ポイントと応用

DATEADD(DAY, -rn, exercise_date)でグループ化することで、連続する日付を同一グループとして扱います。この手法により、欠測日を含まない連続運動期間を正確に計算できます。

さらに、HAVING句を使って、例えば連続日数が3日以上の期間のみを抽出することも可能です。

パフォーマンス向上の工夫

大規模データの場合、exercise_date列にインデックスを付与することで、ORDER BYやROW_NUMBER()による計算を効率化できます。また、犬ごとに分割して処理する場合はPARTITION BYを活用すると性能が向上します。

まとめ

• ROW_NUMBER()で犬ごとに連番を付与
• 日付差分(DATEADD/計算)で連続日数のグループ化
• MIN/ MAX/ COUNTで開始日、終了日、連続日数を算出
• HAVING句で条件付き抽出が可能
• インデックス活用で大規模データの性能向上

この手法を用いることで、犬の運動連続日数を正確に把握し、分析や管理に活用できます。