今天看了道SQL题目，是《SQL数据分析》书中第15章 17个中等难度的SQL题目 的第二道题目。
首先，介绍一下数据表：
一张App累计下载情况表app_download，该表记录了应用商店中App累计下载次数的信息，包含如下3个字段。
字段（字段含义）类型
app_id（App ID）VARCHAR
app_type（App类型）VARCHAR
download（下载次数）INT
app_download表的数据如下表所示：
建表脚本如下：
需求：查询不同类型App的平均下载次数，需要将平均下载次数排在前10%与后10%的App排除在外。
解题：
这里使用rank()函数根据下载数进行排序。
SELECT a.app_type ,AVG(a.download) AS avg_download
FROM
(
SELECT *
,RANK() OVER (ORDER BY download DESC) AS ranking
FROM app_download
) a
WHERE a.ranking>(
SELECT COUNT(*)
FROM app_download)*0.1
AND a.ranking<(
SELECT COUNT(*)
FROM app_download)*0.9
GROUP BY a.app_type;
这里给的建表数据是10条，在上述脚本中，去掉的排名是1，9，10三条数据。
当时的疑惑就是选取了5条，然后他们的排名依次是1，1，3，3，5.这个时候，5*0.1=0.5，5*0.9=4.5.选择两者之间的数据就是1，1，3，3.明显不是去掉了前10%和后10%。
这里想到了10%和第10百分位数的联系：
将一列数按大小排序，第XX位百分位数是指这列数中有XX%的数的值比这个数要小。即第10百分位数是指数列中有10%的数小于此数。
计算方式：
https://zhidao.baidu.com/question/1641558612305725100.html
因为细拆脚本，发现上述脚本有点问题，然后自己进行了新的脚本的编辑，对这部分内容，自己也是刚刚百度学习到的，可能会有错误，希望如果有错误，可以及时提醒我，谢谢～
下面将两种脚本及其输出进行比对，发现其不同。
（现在数据表是奇数条，如下所示）
第一种，就是书中描写的：
SELECT *
FROM
(
SELECT *
,row_number() OVER (ORDER BY download DESC) AS ranking
FROM app_download
) a
WHERE a.ranking>((select count(*)
from app_download ) * 0.1)
AND a.ranking<((select count(*)
from app_download ) * 0.9)
;
结果：
其结果仅仅删除了排名第9的数据。
第二种，自己补充的，涉及到百分位数的概念：
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)
))
;
结果：
这里去掉了排名第1和第9的数据，比较符合预期。
这里为了防止出现bug，再查看一下数据为偶数条的情况：
数据如下：
第一种结果：
还是仅去掉了排名第8的数据。
第二种结果：
网友评论