created: 2019-12-07T13:26:58.000Z

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だと工夫しないといけないかもしれない

参考