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_start | p50 | p90 | p95 | p99 |
---|---|---|---|---|
2024-11-01 00:00:00 | 6637 | 34839 | 60224.5 | 173553.2 |
2024-11-01 01:00:00 | 6707.5 | 33610 | 60844 | 172576.25 |
2024-11-01 02:00:00 | 6630 | 35697 | 60009.8 | 180178.78 |
2024-11-01 03:00:00 | 6559.5 | 36390.4 | 62421.75 | 197639.02 |
2024-11-01 04:00:00 | 6037.5 | 33353.8 | 58833.3 | 166437.79 |
2024-11-01 05:00:00 | 6252 | 35922.3 | 59627 | 154153.07 |
2024-11-01 06:00:00 | 6141 | 36549.4 | 65393.9 | 186734.28 |
2024-11-01 07:00:00 | 6062 | 36955.1 | 66797.2 | 214829.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 thepairs
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.