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

Dec 7, 2019 22:26 · 298 words · 1 minute read

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

参考

tweet Share