犬の散歩記録をPostgreSQLに保存している場合、1週間ごとの合計距離を犬ごとに集計することで、運動管理や健康チェックが効率的に行えます。本記事では、GROUP BYと日付関数を組み合わせて、週単位で集計するSQLクエリの書き方を解説します。
週単位の集計の基本概念
PostgreSQLでは、日付関数を使って日付を週の始まりに変換することで、週ごとの集計が可能です。DATE_TRUNC関数を使うと、任意の期間単位（年・月・週など）で日付を切り捨てできます。
例えば、DATE_TRUNC(‘week’, walk_date)は、各散歩日の週の開始日（月曜日など）を返します。
基本的なクエリ例
犬の散歩テーブルが次のような構成の場合を考えます。
テーブル名: DogWalk
カラム: DogID, WalkDate, Distance
1週間ごとの合計距離を犬ごとに集計するクエリ例。
SELECT DogID,
DATE_TRUNC(‘week’, WalkDate) AS WeekStart,
SUM(Distance) AS TotalDistance
FROM DogWalk
GROUP BY DogID, DATE_TRUNC(‘week’, WalkDate)
ORDER BY DogID, WeekStart;
集計結果の解説
このクエリでは、犬IDごとに週単位の合計距離を取得できます。WeekStart列はその週の開始日を示し、TotalDistance列にはその週に歩いた距離の合計が表示されます。
ORDER BYを加えることで、犬ごとに週順に結果が並ぶため、後からグラフ化や分析がしやすくなります。
曜日のカスタマイズと注意点
DATE_TRUNC(‘week’, …)はデフォルトで月曜日を週の開始日として扱います。週の開始曜日を日曜日にしたい場合は、OFFSETを調整したり、EXTRACT関数と組み合わせて計算する必要があります。
例えば、日曜日を週の開始日にしたい場合は、WalkDate – INTERVAL ‘1 day’を使って週の計算をずらす方法があります。
応用例：前週との差分を表示
さらに、LAG関数を組み合わせると、前週との距離差を表示できます。
SELECT DogID, WeekStart, TotalDistance,
TotalDistance – LAG(TotalDistance) OVER(PARTITION BY DogID ORDER BY WeekStart) AS DistanceChange
FROM (SELECT DogID,
DATE_TRUNC(‘week’, WalkDate) AS WeekStart,
SUM(Distance) AS TotalDistance
FROM DogWalk
GROUP BY DogID, DATE_TRUNC(‘week’, WalkDate)) AS WeeklyWalks
ORDER BY DogID, WeekStart;
まとめ
PostgreSQLで犬の散歩記録を週単位で集計するには、DATE_TRUNC関数で日付を週の始まりに切り捨て、GROUP BYで犬ごとに集計します。必要に応じてLAG関数を活用することで、前週との差分も簡単に取得可能です。これにより、犬ごとの運動量の分析や管理が効率的に行えます。
コメント