SQL分位数计算优化

介绍

在做数据分析或者门户看板的时候，我们有时会有求大盘数据的分位数的需求，大部分场景在都可以通过Hive提供的分位数函数求解，但是在大数据量（几十亿+）的情况下，HIVE自带的分位数函数往往执行速度受限，极端情况需要需要执行十几小时或者在资源限制下得不到最终结果。

本文介绍几种方式，通过对sql的简单改造，更加快速执行出结果。

案例

案例一：计算过去30天内用户对主播的时长分（double类型的小数）10，25， 50，75，90分位数

案例背景：某时长优化实验需要确定策略阈值，因此需要探查下满足某个特征的用户群体对某类主播的时长分的分位数情况，以便对该人群的直播喜好属性做更精细化的划分

针对该场景常规做法如下：

select
  percentile(play_live_duration_pp,0.1)  as play_live_duration_pp10,
  percentile(play_live_duration_pp,0.25) as play_live_duration_pp25,
  percentile(play_live_duration_pp,0.5)  as play_live_duration_pp50,
  percentile(play_live_duration_pp,0.75) as play_live_duration_pp70,
  percentile(play_live_duration_pp,0.9)  as play_live_duration_pp90
from 
	tableA 
where
	p_date = '20221008'

经测试，该sql由于时长分（上述sql中的play_live_duration_pp）的精度较高，小数点后保留10位以上，整体执行时长17h+，主要原因是在做reduce时，要做最终的merge操作，在map端聚合的效果越差，在reduce阶段的数据倾斜压力就越大（相等于由一个task处理百亿到千亿级别的数据）

改动后:

select
  percentile(round(play_live_duration_pp, 4),0.1)  as play_live_duration_pp10,
  percentile(round(play_live_duration_pp, 4),0.25) as play_live_duration_pp25,
  percentile(round(play_live_duration_pp, 4),0.5)  as play_live_duration_pp50,
  percentile(round(play_live_duration_pp, 4),0.75) as play_live_duration_pp70,
  percentile(round(play_live_duration_pp, 4),0.9)  as play_live_duration_pp90
from 
	tableA   
where
	p_date = '20221008'

改动后执行时间从原先的17h，到改动后的5分钟达成结果。

Ps： 示例中保留了4位小数，若4位不满足述求，可按实际需要保留位数即可，但保留的位数越多，在进行reduce操作时，提前聚合结果的效果越差，即最终的数据倾斜程度越高。

案例二：多维度下观测用户关注数各分位数（10分位数、中位数、90分位数等）的变化情况

案例背景：为了持续观测各类涨关注实验对大盘用户关注数的影响，需要看不同产品、年龄段、用户活跃度以及关注数区间下（实际上可能更多维度）的用户的关注数分位数变化情况

常规做法如下：

select
    nvl(product,'ALL') as product
   ,nvl(age_range,'ALL') as age_range
   ,nvl(user_active_degree,'ALL') as user_active_degree
   ,nvl(follow_user_num_range,'ALL') as follow_user_num_range
   ,percentile(follow_user_num, array(0.1, 0.25, 0.5, 0.75, 0.9)) as percentile_arr
from
    tableA    
where
    p_date = '2022xxxx'
group by
     product
    ,age_range
    ,follow_user_num_range
    ,user_active_degree
with cube

由于需要计算cube, 即计算多个维度的自由组合（此处是2^4, 约计算膨胀16倍左右），假设tableA 数量级在十亿左右，膨胀过后约在百亿级别，整体计算量有一定的膨胀。

Ps: 极端场景下，底表聚合后依旧有百亿~千亿级别，膨胀亦是300多倍的场景，此时，整体约万亿甚至几十万亿的计算，计算量膨胀过大。

改动后：

add jar viewfs://hadoop-lt-cluster/home/ksapp/data/udf/191/percentfile_distribute-udf-1.0-SNAPSHOT.jar;
create temporary function percentile_disbt as 'com.kuaishou.dp.udaf.PercentFileDistributeUDAF';

with cube_base as 
(
    select
         product
        ,age_range
        ,follow_user_num_range
        ,user_active_degree
        ,follow_user_num
        ,count(1) as follow_user_num_freq_cnt
    from
        tableA    
    where
  		p_date = '2022xxxx'
    group by
         product
        ,age_range
        ,follow_user_num_range
        ,user_active_degree
        ,follow_user_num
)   
       
select
   nvl(product,'ALL') as product
  ,nvl(age_range,'ALL') as age_range
  ,nvl(user_active_degree,'ALL') as user_active_degree
  ,nvl(follow_user_num_range,'ALL') as follow_user_num_range
  ,percentile_disbt(follow_user_num,follow_user_num_freq_cnt,array(0.1, 0.25, 0.5, 0.75, 0.9)) as percentile_arr
from cube_base
group by
   product
  ,age_range
  ,follow_user_num_range
  ,user_active_degree
with cube

改动之后，先做第一层聚合，在做cube计算之前，将cube迭代计算的数据量从亿级别缩小到几千到几万之间，整体迭代计算的数据量缩小百倍。

改动前后效果概览，相对之前的效果，提升明显

原理分析

经过以上案例，可以知道做完改动之后的效果明显，这里介绍下percentile函数的计算过程，以便更好的理解为什么做了以上改动能够做提效，以下面的一个SQL为例子

select  
	 product,
     percentile(play_live_duration_pp, 0.5) as play_live_duration_pp50
FROM 
	 tableB
GROUP BY 
	 product

以上SQL的执行流程如下：

• 从表 tableB 中扫描所有的数据，在map阶段，所有value放进一个Map<value,LongWriteable>里,相同的value则增加map中的计数值
  • 这里的value就是时长分（play_live_duration_pp），举个例子，在map阶段会计算（0.34532, 876）,代表这个map中，时长分为0.34532的用户有876个
• 在reduce中把map中的所有entry放入一个List中,然后对List根据value值（play_live_duration_pp）进行全排序(`Collections.sort(entriesList, new MyComparator())😉，
• 在reduce阶段从头开始扫一遍上一步的List, 根据计数器的值总和,分位数,定位到对应的分位数,返回对应的分位数对应的值

可以知道，在第一步和第二步中间，map阶段的聚合效果越差（如案例一、小数点后位数过多，或求分位数指标的基数越多），到reduce阶段，单个task需要处理的数据量就越大，因此最终的执行时间就越长。

同时我们看到，在做cube计算时，第一步在每一个cube计算中都需要重复去计算，如果我们事先把时长分出现的频次计算好（如案例二，将百亿级别的迭代，缩小到百万级别），在第二步的时候，直接基于这个频次做计算，能极大减少迭代的计算量。从而更快速的得到结果

总结

简单总结下，并非所有的分位数计算的场景都需要做这个改动，SQL数据量很小、几分钟就可以出结果的情况下，则没有必要花个几分钟去改动SQL, 白白浪费宝贵的时间。

当然，如果你的SQL计算数据量非常非常大（几十亿或者到几千亿），且分位数对应的指标基数（如案例中的关注数等）不大、这个时候，小改一下，能一定的节省你的时间，同时节省你的计算资源。具体的场景如下：

• 计算量级：
  • 几十亿或者到几千亿 -> 推荐做个改动，避免数据倾斜，同时减少数据膨胀
  • 亿到十亿级别 -> 常规做法即可
• 指标（比如关注数、播放次数等）基数
  • 相同的指标较多，根据指标分组之后，基数小的情况下 -> 推荐做个改动，节省一定时间
  • 根据指标聚合后，对计算量变化不大 -> 常规做法即可
• 执行时间
  • 执行时间小时以上，可看计算量级和指标基数是否满足述求
  • 执行时间分钟级别 -> 常规做法即可，减少改SQL的时间