SELECT
PERCENTILE(orders, 0.5) OVER (PARTITION BY deal_id, type) as per50,
PERCENTILE(orders, 0.25) OVER (PARTITION BY deal_id, type) as per25,
PERCENTILE(orders, 0.75) OVER (PARTITION BY deal_id, type) as per75,
*
from temp
为了获得orders这个字段在每个deal_id, type上的分位点。比如有两个deal_id,3个type,那么就是想看6种(2*3)组合,每个组合内部的二分位点,1/4,3/4分位点。
不过有点慢,如果有的type含的量大会倾斜,尤其是对于浮点数orders,整数会好一些。