Skip to main content

How to classify results depending on percentiles at runtime?

If you want to classify each result row in a query based on percentile, you can combine arrayMap and arraySum to classify each row.

WITH (
SELECT quantiles(0.33, 0.66)(number)
FROM numbers(10)
) AS cuts
SELECT
number,
arraySum(arrayMap(x -> (x > number), cuts)) AS t,
multiIf(t = 0, 'high', t = 1, 'med', t = 2, 'low', 'none') AS c
FROM numbers(10)

Query id: 71952567-ff29-4559-bacc-1d27d5fdacf9

┌─number─┬─t─┬─c────┐
02 │ low │
12 │ low │
22 │ low │
31 │ med │
41 │ med │
51 │ med │
60 │ high │
70 │ high │
80 │ high │
90 │ high │
└────────┴───┴──────┘

You can set up a materialized view and pull the percentiles from it instead of computing them at runtime.