统计小白er

V1

2023/01/17阅读:16主题:全栈蓝

SQL刷题-T3:app下载情况统计

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.11 )= 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.91 )= 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(1over(),
row_number() over(order by download desc)/sum(1over()
from app_download;

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

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

select * ,
ntile(10over(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 结语

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

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

分类:

后端

标签:

数据库

作者介绍

统计小白er
V1