回答

リレーショナルデータベースでの時系列データ処理の問題は、分析と従来のSQLプログラミングの交差点で発生しました。SQL-92には特別なウィンドウ関数がないため、動的なメトリック（移動合計、平均など）の計算や時間条件のためにサブクエリを使用する必要があります。

問題 — 移動ウィンドウによる集約、時間に基づく前の/次の値の検索、任意のカレンダー間隔による効果的なグループ化（例：週間/月間指標の計算）に対する標準的なツールの欠如。

解決策：

標準的な手段のみを使用して、各行または計算された基準（例：月、週）によるグループ化のための相関サブクエリを使用します：

コード例：

-- ウィンドウ関数なしでの週ごとのグループ化の例
SELECT 
   YEAR(event_date) AS year,
   WEEK(event_date) AS week,
   SUM(value) AS total
FROM timeseries
GROUP BY YEAR(event_date), WEEK(event_date)
ORDER BY year, week;

-- 前のレコードを見つけるための相関サブクエリ
SELECT t1.id, t1.event_date, t1.value,
       (
         SELECT t2.value 
           FROM timeseries t2 
          WHERE t2.event_date < t1.event_date
          ORDER BY t2.event_date DESC
          LIMIT 1
       ) as prev_value
FROM timeseries t1;

主な特徴：

時間関数（YEAR、MONTH、WEEKなど）に基づいて明示的にグループ化および集約する必要があります。
移動計算を取得するためには、相関サブクエリまたは一時テーブルを使用せざるを得ません。
大量データでのパフォーマンスが低下します — 各行へのサブクエリは実行速度を急速に遅くします。

トリッキーな質問。

WEEK(event_date)はすべての日付に対してカレンダー週を一意に決定しますか？

いいえ — 異なるDBMS（さらには同じDBMSのパラメータ）において、年の最初の週の定義は異なります（例：ISO 8601とアメリカのシステム），これは集約時に異なる結果を引き起こす可能性があります。関数の動作モードを明示的に指定するか、YEARWEEKを使用する必要があります。

SELECT YEARWEEK(event_date, 1)  -- 1: ISO週は月曜日から始まります
FROM timeseries;

相関サブクエリは前の値を検索する際に重複を自動的に削除しますか？

いいえ、相関サブクエリはデフォルトで重複をフィルタリングしません。同じ日付に複数のイベントが存在する場合、サブクエリはソートで最初のものを返しますが、他は無視されます。

GROUP BYを通じて日付を集約し、時間を無視できますか？

はい、しかし、異なるDBMSでDATE(event_date)やTRUNC(event_date)を使用して、明示的に時間部分を切り捨てる必要があります：

SELECT DATE(event_datetime), COUNT(*)
FROM events
GROUP BY DATE(event_datetime)

一般的なエラーとアンチパターン

レポート期間のためのロケールおよびカレンダー基準を考慮せずにYEAR/MONTH/WEEKを使用すること
大量データでの実行時間の大幅な増加 — 移動ウィンドウのために過度に複雑な相関サブクエリを作成すること
タイム関数を使用する際にタイムゾーンを考慮しないこと

実生活の例

ネガティブケース

チームはWEEK(date)関数に基づいて週次分析を行い、ISO-8601のパラメータ設定を行いませんでした。その結果、1月の第一週のレポートが「失われ」、一部のイベントがアメリカ的論理に従って昨年の12月に関係していました。分析が一致しませんでした。

利点：

期間の分解を迅速に実現

欠点：

データが不正確で、レポートがビジネスロジックと一致しない

ポジティブケース

専門家はYEARWEEK(date, 1)とカレンダーテーブルを導入し、国とビジネス部門間でのレポートの整合性を大幅に向上させました。