How To Find Percentile from Value

290 views
Skip to first unread message

Iman Rahmat

unread,
Feb 21, 2020, 3:57:44 AM2/21/20
to ClickHouse
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

unread,
Feb 21, 2020, 12:47:18 PM2/21/20
to ClickHouse
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

unread,
Feb 21, 2020, 4:10:46 PM2/21/20
to ClickHouse
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

unread,
Feb 21, 2020, 11:46:37 PM2/21/20
to Denis Zhuravlev, ClickHouse
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.
Reply all
Reply to author
Forward
0 new messages