SQL | 窗口函数 | 查找N%的数据

剔除访问次数前20%的用户后，每类用户的平均访问次数。

用户访问次数表visit_view，

用户编号uid
用户类型utype
访问量uvisit

1、创建表
（1）创建表

CREATE TABLE IF NOT EXISTS visit_view(
uid INT,
utype VARCHAR(255),
uvisit INT
);

（2）插入数据

INSERT INTO visit_view VALUES(10,'A',352),
							 (6,'C',209),
							 (7,'C',110),
							 (4,'E',101),
							 (2,'B',53),
							 (20,'A',53),
							 (11,'C',33),
							 (1,'A',30),
							 (9,'E',29),
							 (8,'B',6);

（3）逻辑SQL
a）如果理解为要剔除访问量排名前20%

mysql> WITH t1 AS (SELECT *,ROW_NUMBER() OVER(ORDER BY uvisit DESC) AS rk
    -> 			   FROM visit_view)
    -> SELECT *
    -> FROM t1
    -> WHERE rk>(SELECT MAX(rk) FROM t1)*0.2;
+------+-------+--------+----+
| uid  | utype | uvisit | rk |
+------+-------+--------+----+
|    7 | C     |    110 |  3 |
|    4 | E     |    101 |  4 |
|    2 | B     |     53 |  5 |
|   20 | A     |     53 |  6 |
|   11 | C     |     33 |  7 |
|    1 | A     |     30 |  8 |
|    9 | E     |     29 |  9 |
|    8 | B     |      6 | 10 |
+------+-------+--------+----+
8 rows in set (0.00 sec)

b) 如果要找到访问量之和占总访问量80%的用户
SUM() OVER() 累积求和

mysql> WITH t1 AS (SELECT *,SUM(uvisit) OVER(ORDER BY uvisit DESC) AS roll_sum
    ->     		   FROM visit_view)
    -> SELECT *
    -> FROM t1
    -> WHERE roll_sum<(SELECT MAX(roll_sum) FROM t1)*0.8;
+------+-------+--------+----------+
| uid  | utype | uvisit | roll_sum |
+------+-------+--------+----------+
|   10 | A     |    352 |      352 |
|    6 | C     |    209 |      561 |
|    7 | C     |    110 |      671 |
|    4 | E     |    101 |      772 |
+------+-------+--------+----------+
4 rows in set (0.00 sec)