Cloud Spanner で分間の注文数を集計するクエリが書きたかったので書いてみた。なお、注文日時 (OrderDateTime
) は UTC で格納されているが、集計期間の指定や集計結果では JST で扱いたいものとする。
SELECT
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', TIMESTAMP_ADD(Orders.OrderDateTime, INTERVAL 9 HOUR), 'UTC') ordered_at,
COUNT(1) cnt
FROM Orders
WHERE Orders.OrderDateTime BETWEEN '2024-03-26 10:00:00+9:00' AND '2024-03-26 23:59:59+9:00'
GROUP BY FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', TIMESTAMP_ADD(Orders.OrderDateTime, INTERVAL 9 HOUR), 'UTC')
ORDER BY ordered_at;
GCP の Spanner Studio から実行した際、以前はこのクエリだとエラーになったような気がするんだけど、今は特に問題なく実行できている。(こんな感じのエラーが出てたような記憶...)
SELECT list expression references Orders.OrderDateTime which is neither grouped nor aggregated at line XX, column XX...
ちなみに、秒間で集計したい場合は FORMAT_TIMESTAMP
を %Y-%m-%d %H:%M:%S
のようにする。
現場からは以上です。
(追記)
FORMAT_TIMESTAMP
とか TIMESTAMP_ADD
が冗長だったのでまとめた。
WITH row_data AS (
SELECT
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', TIMESTAMP_ADD(Orders.OrderDateTime, INTERVAL 9 HOUR), 'UTC') AS ordered_at
FROM Orders
WHERE Orders.OrderDateTime BETWEEN '2024-03-26 10:00:00+9:00' AND '2024-03-26 23:59:59+9:00'
)
SELECT
row_data.ordered_at,
COUNT(*) cnt
FROM row_data
GROUP BY ordered_at
ORDER BY ordered_at;