乐趣区

PLSQL-练习题3-前多少百分比划等级

jiezi

创建表 T_GOOGS，插入 7 行语句。

CREATE TABLE T_GOOGS(GOODS_TYPE VARCHAR2(10),
GOODS_NAME VARCHAR2(20),
PRICE NUMBER(5)
);


INSERT INTO T_GOOGS VALUES('手机','华为 mate',2999);
INSERT INTO T_GOOGS VALUES('手机','苹果 ipone',7999);
INSERT INTO T_GOOGS VALUES('手机','小米',1999);
INSERT INTO T_GOOGS VALUES('水果','车厘子',79);
INSERT INTO T_GOOGS VALUES('水果','葡萄',79);
INSERT INTO T_GOOGS VALUES('水果','苹果',12);
INSERT INTO T_GOOGS VALUES('电脑','金士顿',499);


SELECT * FROM T_GOOGS;

结果如下图：

现要求：按照 价格升序排列 ，按照价格的 前 20%，定义为 低档 ；按照价格的20% 到 85% 为中档 ，剩下的则为 高档。

解题思路：
1）分不同的档次，一定要用 Case When。
2）前 20% 如何找出来 -> 小于序号最大值 *20% -> 创建序号列 -> 按照价格升序 -> 分析函数

第一步：新添加一列按价格排序的序号列，用分析函数。

WITH TB AS (SELECT T.*,ROW_NUMBER()OVER(ORDER BY PRICE) AS RANK_NUM
       FROM T_GOOGS T 
)

第二步：对比排名数字要小于原表里最大序号的 20%,20%-85%,85% 以上。

SELECT TB.*
       ,CASE WHEN RANK_NUM <= (SELECT MAX(RANK_NUM) FROM TB)*0.2 THEN '低档'  -- 筛选前 20% 的物品
             WHEN RANK_NUM <= (SELECT MAX(RANK_NUM) FROM TB)*0.85 AND RANK_NUM > (SELECT MAX(RANK_NUM) FROM TB)*0.2 THEN '中档' -- 筛选前 20% 到 85% 的物品
                   ELSE '高档' END AS RANK_Percent -- 剩下的 15% 物品
FROM TB

最后的结果：

容易犯错的地方：
不能用 Rank_NUM 和 Max(Rank_NUM) 直接比较。

原因：
要返回聚合函数的效果，比如求最大最小值，一定在 Select …. From TABLE 里才能展示。

Ntile 分析函数 (指定每个分区必须被划分成的组数)

按照 OVER 子句指定的顺序，较大的组排在较小的组前面。
例如，如果总行数是 53，组数是 5，
不能平均分 ，则 =53/5 + 1 先分出去一组，这一组有 11 行记录。
剩余 53-11=42，不能平均分，则 = 42/4+ 1 先分出一组，这一组也有 11 条记录。

则前三个组每组包含 11 行，其余两个组每组包含 10 行。
另一方面，如果总行数可被组数整除，则行数将在组之间平均分布。例如，如果总行数为 50，有五个组，则每组将包含 10 行。

--- 前 20% 的
SELECT * FROM
(SELECT T.*,
     NTILE(5) OVER(ORDER BY PRICE DESC) AS GROUP_NUM FROM T_GOOGS T)
         WHERE GROUP_NUM =1

原理分析：对整张表划分为 5 组（也可以将每一个分配记录的数组元素称为“桶”），即每一部分占总比例的 20%！

语法：里面的参数要整数形式，但后面保留小数点也不会报错。
— 相当于 Trunc 函数，数字直接截取，不用计算四舍五入 —
NTILE (integer_expression) OVER ([ <partition_by_clause>] < order_by_clause > )

参考资料：
1.NTILE (Transact-SQL) – SQL Server
2.Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介
3. 小匿：巩固 SQL – 窗口函数 & 变量 & 数据透视图

退出移动版