Atom.

Clickhouse: Latency quantiles between two events, every hour - Thu Nov 287:12 PM
Return P50, P90, P95, and P99 latencies between two events, every hour.

Example Performance
Elapsed: 10.691s
Read: 50,496,805 rows (3.77 GB)
Total rows: 171,034,194 rows

Example output
bucket_startp50p90p95p99
2024-11-01 00:00:0066373483960224.5173553.2
2024-11-01 01:00:006707.53361060844172576.25
2024-11-01 02:00:0066303569760009.8180178.78
2024-11-01 03:00:006559.536390.462421.75197639.02
2024-11-01 04:00:006037.533353.858833.3166437.79
2024-11-01 05:00:00625235922.359627154153.07
2024-11-01 06:00:00614136549.465393.9186734.28
2024-11-01 07:00:00606236955.166797.2214829.64

To customize the query:

  • Replace INTERVAL 1 HOUR to change the length of each bucket of time.
  • Change the WHERE clause at the end to filter based on different pairs of events.
  • Change the WHERE clause in the pairs CTE to pre filter. The more that this initial filter can remove, the faster the query will run.
  • Change PARTITION BY client_id to group by a different column.