3. app下载情况统计（2星）

3.1 题目介绍

现有一张app累计下载情况表app_download，该表记录了应用商店中app累计下载次数的信息，包括三个字段，app_id（app ID）：varchar，app_type（app类型）：varchar，download（下载次数）：int。

查询不同类型app的平均下载次数，需将平均下载次数排在前10%与后10%的app排除在外。输出app_type（app类型），avg_download（平均下载次数）。

3.2 注释（思路想法）

这里使用了百分位数的概念来找第10百分位数和第90百分位数，然后通过筛选条件来获取处于两者之间的数据，再对其进行聚合求均值。

举个例子：

3.3 脚本

SELECT
        t.app_type,
        avg( t.download ) AS avg_download 
FROM
        (
        SELECT
                * 
        FROM
                ( SELECT *, row_number() OVER ( ORDER BY download DESC ) AS ranking FROM app_download ) a 
        WHERE
                a.ranking >(
                SELECT
                IF
                        (
                                MOD ( ( SELECT count(*) FROM app_download ) * 0.1, 1 )= 0,
                                ((
                                                ceil(( SELECT count(*) FROM app_download ) * 0.1 ))+((
                                                ceil(( SELECT count(*) FROM app_download ) * 0.1 ))+ 1 
                                        ))/ 2,
                                ceil(( SELECT count(*) FROM app_download ) * 0.1 ) 
                        )) 
                AND a.ranking <(
                SELECT
                IF
                        (
                                MOD ( ( SELECT count(*) FROM app_download ) * 0.9, 1 )= 0,
                                ((
                                                ceil(( SELECT count(*) FROM app_download ) * 0.9 ))+((
                                                ceil(( SELECT count(*) FROM app_download ) * 0.9 ))+ 1 
                                        ))/ 2,
                                ceil(( SELECT count(*) FROM app_download ) * 0.9 ) 
                        )) 
        ) t 
GROUP BY
        t.app_type 
ORDER BY
        t.app_type
; 

还有很多其他的方法，但是其他方法一直说服不了我关于去掉前10%和后10%的方法。

3.4 拓展

下面给出其他方法：

3.4.1 通过排序和计数的方法

select *,
row_number() over(order by download desc),
sum(1) over(),
row_number() over(order by download desc)/sum(1) over()
from app_download;

-- 如果数据量较大，可能会存在相同的数据，所以使用rank()会相对更合理一些
select *,
rank() over(order by download desc),
sum(1) over(),
row_number() over(order by download desc)/sum(1) over()
from app_download;

3.4.2 ntile()函数（可能会存在分桶不均匀的情况）

select * ,
ntile(10) over(order by download desc) 
from app_download;

3.4.3 cume_dist()或者percent_rank()：

select * ,
cume_dist() over(order by download desc),
percent_rank() over(order by download desc) 
from app_download
;

这里给出两者的计算公式：

cume_dist()：升序的情况，该列中小于等于当前行所对应的值的个数/该列的总个数。

percent_rank()：升序的情况，（该值所处的排列后顺序的位置-1）/（该列的总个数-1）。

❝

ps：这里对于如何选取去掉前10%和后10%的方法，可能会存在一定的不同。这里一定要注意指标的计算方式，计算方式不同，可能会导致最终的结果不同。

❞

3.5 结语

该系列也会一直更新，喜欢的可以关注一下~

麻烦大家一点时间给点个赞或者点个在看吧～