犬の散歩距離を週ごとに集計したい場合、PostgreSQLでは日付関数やGROUP BY句を活用して簡単に計算できます。本記事では、具体的なSQL例と設計ポイントを紹介します。
日付を基準に週単位で集計する方法
PostgreSQLにはdate_trunc関数があり、日付を指定の単位に切り捨てることができます。週単位の集計では、date_trunc(‘week’, date_column)を使うと、各行の週の開始日を求めることができます。
例：犬の散歩記録テーブル（walks）にdate列とdistance列がある場合、以下のSQLで週ごとの合計距離を算出可能です。
SELECT date_trunc('week', walk_date) AS week_start,
SUM(distance) AS total_distance
FROM walks
GROUP BY date_trunc('week', walk_date)
ORDER BY week_start;
曜日を考慮した集計
date_trunc(‘week’, date_column)は通常、週の開始日を日曜日や月曜日として返します。必要に応じて、start of weekを調整することもできます。
例えば、月曜日始まりにしたい場合は、以下のように計算できます。
SELECT (date_trunc('week', walk_date) + INTERVAL '1 day') AS week_start_monday,
SUM(distance) AS total_distance
FROM walks
GROUP BY date_trunc('week', walk_date) + INTERVAL '1 day'
ORDER BY week_start_monday;
複数犬のデータを区別して集計
犬ごとに集計したい場合は、GROUP BY句に犬IDを追加します。
SELECT dog_id,
date_trunc('week', walk_date) AS week_start,
SUM(distance) AS total_distance
FROM walks
GROUP BY dog_id, date_trunc('week', walk_date)
ORDER BY dog_id, week_start;
これにより、各犬の週ごとの散歩距離が明確にわかります。
ビューを作成して再利用
この集計を頻繁に利用する場合、マテリアライズドビューや通常ビューを作成すると便利です。マテリアライズドビューを使うと、計算結果を物理的に保持して高速アクセスが可能です。
例：CREATE MATERIALIZED VIEW weekly_walk_summary AS
SELECT dog_id,
date_trunc('week', walk_date) AS week_start,
SUM(distance) AS total_distance
FROM walks
GROUP BY dog_id, date_trunc('week', walk_date);
まとめ
PostgreSQLで犬の散歩距離を1週間ごとに集計するには、date_trunc関数で週の開始日を取得し、SUM関数とGROUP BY句で集計する方法が基本です。犬ごとに集計したい場合はGROUP BYに犬IDを追加し、頻繁に利用する場合はビューやマテリアライズドビューを作成すると効率的です。
コメント