SQLで飲食店のPOSローデータ（時系列データ）を、1時間ごとの売上やオーダー数をもつレコードに整形する

今回の記事ではSQLにて時系列データを成形し、一時間ごとの売り上げやオーダー数を持つレコードに変換します。

ローデータを一時間ごとの売り上げなどを持つデータに整形

これがローデータである飲食店のPOSです。 これを一時間ごとの売上やオーダー数をもつレコードに変換します。 なお今回レコードで必要な列は日付(date)、チェックイン時刻(ckin_time)と売上(uriage)です。

チェックイン時刻の整形

まず最初4桁で表示されているckin_timeを1時間ごとの粒度に書き換えます。 今回はCASE句を使用しました。 明示的に条件外の処理を書いていますが、なくても動くはずです。 これによって、1時間ごとに分けられたレコードを作成することができました。 次にここで整形したデータを合計します。

日付と時間を結合し、同一時間の売り上げ合計のレコードを作る。

1時間ごとに分けられたレコードを日付と時間で結合して同一時間の売り上げ合計のレコードを作成します。 CONCATを使って、日付型と数値型を強引にマージしています。またCASTでtimestamp形式にデータを変更しています。 なお先ほど整形したチェックイン時刻のクエリを、FROM句の中でサブクエリとして持ちます。

昇順に時系列で並べ替えます。

これで目標であった一時間ごとの売り上げやオーダー数を持つレコードに変換することができました。 今回のクエリで注意した点は、列名を再定義するため、定義前と定義後の変数名を混同しないようにすること（サブクリエで変更した名前は、サブクリエの外では変更後の名前で扱う）です。

まとめと全体のコード

時系列データはデータ整形で躓きやすいポイントの一つです。今回は一時間ごとの粒度に変換するだけだったので、CASE句を使用しましたが、文字列などで入ってきたデータを時系型に変換するにはさらに細かいキャストが必要になります。