求取sale前20%的用户ID
select user_id from ( select user_id, NTILE(5) OVER(ORDER BY sales desc) AS nt from orders )A where nt=1; ## 结果如下 +----------+ | user_id | +----------+ | tom1 | | tom3 | +----------+ CUME_DIST、PERCENT_RANK select user_id,user_type,sales, --没有partition,所有数据均为1组 CUME_DIST() OVER(ORDER BY sales) AS cd1, --按照user_type进行分组 CUME_DIST() OVER(PARTITION BY user_type ORDER BY sales) AS cd2 from orders; ## 上述结果如下 +----------+------------+--------+------+----------------------+--+ | user_id | user_type | sales | cd1 | cd2 | +----------+------------+--------+------+----------------------+--+ | liliu | new | 1 | 0.3 | 0.2857142857142857 | | tom | new | 1 | 0.3 | 0.2857142857142857 | | zhangsa | new | 2 | 0.5 | 0.42857142857142855 | | wanger | new | 3 | 0.7 | 0.5714285714285714 | | tom2 | new | 5 | 0.9 | 0.8571428571428571 | | tom3 | new | 5 | 0.9 | 0.8571428571428571 | | tom1 | new | 6 | 1.0 | 1.0 | | lisi | old | 1 | 0.3 | 0.3333333333333333 | | tomas | old | 2 | 0.5 | 0.6666666666666666 | | tomson | old | 3 | 0.7 | 1.0 | +----------+------------+--------+------+----------------------+--+ select user_type,sales, --分组内总行数 SUM(1) OVER(PARTITION BY user_type) AS s, --RANK值 RANK() OVER(ORDER BY sales) AS r, PERCENT_RANK() OVER(ORDER BY sales) AS pr, --分组内 PERCENT_RANK() OVER(PARTITION BY user_type ORDER BY sales) AS prg from orders; ## 上述结果如下 +------------+--------+----+-----+---------------------+---------------------+--+ | user_type | sales | s | r | pr | prg | +------------+--------+----+-----+---------------------+---------------------+--+ | new | 1 | 7 | 1 | 0.0 | 0.0 | | new | 1 | 7 | 1 | 0.0 | 0.0 | | new | 2 | 7 | 4 | 0.3333333333333333 | 0.3333333333333333 | | new | 3 | 7 | 6 | 0.5555555555555556 | 0.5 | | new | 5 | 7 | 8 | 0.7777777777777778 | 0.6666666666666666 | | new | 5 | 7 | 8 | 0.7777777777777778 | 0.6666666666666666 | | new | 6 | 7 | 10 | 1.0 | 1.0 | | old | 1 | 3 | 1 | 0.0 | 0.0 | | old | 2 | 3 | 4 | 0.3333333333333333 | 0.5 | | old | 3 | 3 | 6 | 0.5555555555555556 | 1.0 | +------------+--------+----+-----+---------------------+---------------------+--+