How To Find Percentile from Value

[Iman Rahmat]

Dear Master Data Clickhouse,

please help me, i wanna to get Percentile from the value.

for example

quantile(x)(START_RSRP)=-102

 i wanna to find x, could you please suggest how to ?

thanks very much

[GeneZ]

There could be a better way to do this, but I know how to calculate quantiles for all the values in a table by definition using SQL in clickhouse:

WITH 

    (

        SELECT count(*)

        FROM t1

    ) AS total

SELECT 

    t11.value, 

    count(*) / total

FROM 

(

    SELECT DISTINCT value

    FROM t1

) AS t11

, t1 AS t12

WHERE t12.value <= t11.value

GROUP BY value

ORDER BY value ASC

[Denis Zhuravlev]

with (select quantilesExact(0,.1,.2,.3,.4,.5,.6,.7,.8,.9,1)(x) from (select number x from numbers(10000))) as b select x, arrayFirst(i -> x < b[i], arrayEnumerate(b))-1 r from (select number*1000+rand()%150 x  from numbers(10))
┌────x─┬──r─┐
│   29 │  1 │
│ 1064 │  2 │
│ 2027 │  3 │
│ 3017 │  4 │
│ 4148 │  5 │
│ 5092 │  6 │
│ 6108 │  7 │
│ 7054 │  8 │
│ 8027 │  9 │
│ 9120 │ 10 │
└──────┴────┘

[Iman Rahmat]

dear all, 

thanks for suggestion, but i found simple query, like this countIf(RSRP<-102)/count(total)*100 as Percentile

Terima kasih,

Hormat Saya,
Iman Rahmat Hidayat.

--
You received this message because you are subscribed to the Google Groups "ClickHouse" group.
To unsubscribe from this group and stop receiving emails from it, send an email to clickhouse+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/clickhouse/adb07487-3248-40c5-9350-35ad896b6215%40googlegroups.com.