SQL之每天一个小函数

Ntile函数使用

函数解释:
可以看成是：它把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。（这个算法在很多当中使用，spark中数据分片的时候也是这个算法，只不过是不均匀的时候，优先分配给较大编号的分片)
语法是：ntile (num) over ([partition_clause] order_by_clause) as your_bucket_num
然后可以根据桶号，选取前或后 n分之几的数据。
数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

例如 : 有表如下

±---------±---------±--------------±-------±-+
| t1.name | t1.cost | t1.orderdate | t1.cn |
±---------±---------±--------------±-------±-+
| jack | 10 | 2017-01-01 | 1 |
| tony | 15 | 2017-01-02 | 1 |
| tony | 29 | 2017-01-04 | 1 |
| jack | 46 | 2017-01-05 | 2 |
| tony | 50 | 2017-01-07 | 2 |
| jack | 55 | 2017-01-08 | 2 |
| jack | 23 | 2017-02-03 | 3 |
| jack | 42 | 2017-04-06 | 3 |
| mart | 62 | 2017-04-08 | 3 |
| mart | 68 | 2017-04-09 | 4 |
| mart | 75 | 2017-04-11 | 4 |
| mart | 94 | 2017-04-13 | 4 |
| neil | 12 | 2017-05-10 | 5 |
| neil | 80 | 2017-06-12 | 5 |
±---------±---------±--------------±-------±-+
##需求为查询前20%时间的订单信息

内层SQL如下:

select name,cost,orderdate,
ntile(5) over(order by orderdate ) cn 
from business;

拿到的结果为
±------±------±------------±----±-+
| name | cost | orderdate | cn |
±------±------±------------±----±-+
| jack | 10 | 2017-01-01 | 1 |
| tony | 15 | 2017-01-02 | 1 |
| tony | 29 | 2017-01-04 | 1 |
| jack | 46 | 2017-01-05 | 2 |
| tony | 50 | 2017-01-07 | 2 |
| jack | 55 | 2017-01-08 | 2 |
| jack | 23 | 2017-02-03 | 3 |
| jack | 42 | 2017-04-06 | 3 |
| mart | 62 | 2017-04-08 | 3 |
| mart | 68 | 2017-04-09 | 4 |
| mart | 75 | 2017-04-11 | 4 |
| mart | 94 | 2017-04-13 | 4 |
| neil | 12 | 2017-05-10 | 5 |
| neil | 80 | 2017-06-12 | 5 |
±------±------±------------±----±-+
这样结果就很明显了,需要前20%只需要选取cn=1的数据即可.

最终SQL为:

select * from(
select name,cost,orderdate,
ntile(5) over(order by orderdate ) cn 
from business ) t1 
where t1.cn =1; 

代码解释:
内层函数中将表中数据分为5份 { ntile(5) } 并根据 orderdate 进行降序排列后选择所需要的信息
显示结果为:
±---------±---------±--------------±-------±-+
| t1.name | t1.cost | t1.orderdate | t1.cn |
±---------±---------±--------------±-------±-+
| neil | 80 | 2017-06-12 | 1 |
| neil | 12 | 2017-05-10 | 1 |
| mart | 94 | 2017-04-13 | 1 |
±---------±---------±--------------±-------±-+