GBase 8a的NTILE函数,将数据切分成指定的几块,如果且不均匀,则前面的优先。本文介绍NTILE的用法和使用样例。
目录导航
语法
NTILE(num) OVER([PARTITION BY col_name1,col_name2,…] ORDER BY col_name1 [asc/desc], col_name2 [asc/desc],…)
说明
num参数是指定切成几块。 第一块值为1,第二块为2,以此类推。
如果一个窗口内的数据,无法均匀的分成指定块数,则优先填充前面的。
样例
数据
gbase> create table td(id int, type int,val decimal(20,3));
Query OK, 0 rows affected (Elapsed: 00:00:00.17)
gbase> insert into td values(1,1,111),(1,1,222),(2,1,333),(2,2,444),(3,2,555),(3,2,666),(4,2,777);
Query OK, 7 rows affected (Elapsed: 00:00:00.12)
Records: 7 Duplicates: 0 Warnings: 0
gbase> insert into td values(5,1,null),(50,2,null);
Query OK, 2 rows affected (Elapsed: 00:00:00.09)
Records: 2 Duplicates: 0 Warnings: 0
gbase> select * from td;
+------+------+---------+
gbase> select * from td order by type;
+------+------+---------+
| id | type | val |
+------+------+---------+
| 1 | 1 | 222.000 |
| 2 | 1 | 333.000 |
| 5 | 1 | NULL |
| 1 | 1 | 111.000 |
| 3 | 2 | 666.000 |
| 4 | 2 | 777.000 |
| 3 | 2 | 555.000 |
| 2 | 2 | 444.000 |
| 50 | 2 | NULL |
+------+------+---------+
9 rows in set (Elapsed: 00:00:00.04)
标准NTILE用法
type=2里有5个,分不均匀,分块1为3个,分块2为2个。
gbase> select *,ntile(2)over(partition by type order by id) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 1 | 1 | 111.000 | 1 |
| 1 | 1 | 222.000 | 1 |
| 2 | 1 | 333.000 | 2 |
| 2 | 2 | 444.000 | 1 |
| 3 | 2 | 555.000 | 1 |
| 3 | 2 | 666.000 | 1 |
| 4 | 2 | 777.000 | 2 |
| 5 | 1 | NULL | 2 |
| 50 | 2 | NULL | 2 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.35)
无partition的NTILE
所有数据分成2块。
gbase> select *,ntile(2)over(order by id) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 1 | 1 | 111.000 | 1 |
| 1 | 1 | 222.000 | 1 |
| 2 | 1 | 333.000 | 1 |
| 2 | 2 | 444.000 | 1 |
| 3 | 2 | 555.000 | 1 |
| 3 | 2 | 666.000 | 2 |
| 4 | 2 | 777.000 | 2 |
| 5 | 1 | NULL | 2 |
| 50 | 2 | NULL | 2 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.08)
无order的NTILE
自然顺序,不保证每次相同。
gbase> select *,ntile(2)over(partition by type) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 1 | 1 | 111.000 | 2 |
| 1 | 1 | 222.000 | 1 |
| 2 | 1 | 333.000 | 1 |
| 2 | 2 | 444.000 | 2 |
| 3 | 2 | 555.000 | 1 |
| 3 | 2 | 666.000 | 1 |
| 4 | 2 | 777.000 | 1 |
| 5 | 1 | NULL | 2 |
| 50 | 2 | NULL | 2 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.07)
无Partition和order的NTILE
gbase> select *,ntile(2)over() maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 1 | 1 | 111.000 | 1 |
| 1 | 1 | 222.000 | 1 |
| 2 | 1 | 333.000 | 1 |
| 2 | 2 | 444.000 | 1 |
| 3 | 2 | 555.000 | 1 |
| 3 | 2 | 666.000 | 2 |
| 4 | 2 | 777.000 | 2 |
| 5 | 1 | NULL | 2 |
| 50 | 2 | NULL | 2 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.29)