SQLで5分ごとの平均を出す

こんな2カラムがあるテーブルで5分ごとのdurationの平均とかMAXとかをサマりたい

• duration
  • doubleが入っている
  • レスポンスを返すまでにかかった時間
• utc_timestamp
  • timestampが入っている
  • アクセスされた時刻

SQL

prestoだとこんな感じ (datetimeがintに変換できればどのDBでもできる)

5分は300秒なので、datetimeをUNIXTIMEに変換して(intにして)
300でわった商が同じなら同じGROUPに入る

SELECT
  from_unixtime((CAST(to_unixtime(utc_timestamp) AS INTEGER) / 300) * 300) AT TIME ZONE 'Asia/Tokyo' window,
  AVG(duration), 
  MAX(duration),
  APPROX_PERCENTILE(duration, 0.98)  -- 98パーセンタイル
FROM lblog.api
GROUP BY
  CAST(to_unixtime(utc_timestamp) AS INTEGER) / 300
ORDER BY
  MIN(utc_timestamp)

パーセンタイルは他のDBだと工夫しないといけないかもしれない

参考

• 6.6. Mathematical Functions and Operators — Presto 0.229 Documentation
• 6.10. Date and Time Functions and Operators — Presto 0.172 Documentation