GBase 8a中不常用函数

1、regexp_split_to_table  #字符串转表

select foo from regexp_split_to_table('the quick brown fox jumps over the lazy dog','\\s+') as foo ;
+------------+
| foo        |
+------------+
| the        |
| quick      |
| brown      |
| fox        |
| jumps      |
| over       |
| the        |
| lazy       |
| dog        | 
+------------+
9 row in set (Elapsed: 00:00:00.01)

2、 string_to_array #字符串转数组

3、char_spell  #汉字转大写拼音

gbase> select char_spell('test测试数据123',',');                      
+------------------------+
| char_spell('test测试数据123',',') |
+-------------------------------------+
| testCE,SHI,SHU,JU123            |
+-------------------------------------+
1 row in set (Elapsed: 00:00:00.00)     

4、date_bin  #时间分批次打包

5、initcap # 首字母转大写，非首字母转小写

6、percentile  #返回数据中第p百分为数 

7、PERCENTILE_CONT、PERCENTILE_DISC # 连续百分位 与 离散百分位

select * from sales order by department,amount;
+---------+------------+----------+----------+
| sale_id | department | employee | amount   |
+---------+------------+----------+----------+
|       5 | IT         | Eve      |  9800.00 |
|      10 | IT         | Eve      | 10500.00 |
|       8 | IT         | Frank    | 11200.00 |
|       1 | Marketing  | Alice    | 12500.00 |
|       6 | Marketing  | Alice    | 15300.00 |
|       2 | Marketing  | Bob      | 18700.00 |
|       4 | Sales      | David    | 16500.00 |
|       9 | Sales      | David    | 19800.00 |
|       3 | Sales      | Charlie  | 24300.00 |
|       7 | Sales      | Charlie  | 27500.00 |
+---------+------------+----------+----------+

PERCENTILE_CONT（连续百分位数）  25%分位
gbase> select sale_id,department,employee,amount,percentile_cont(0.25) within group (order by amount) over(partition by department) as 25_percentile_cont from sales;
+---------+------------+----------+----------+--------------------+
| sale_id | department | employee | amount   | 25_percentile_cont |
+---------+------------+----------+----------+--------------------+
|       5 | IT         | Eve      |  9800.00 |              10150 |
|      10 | IT         | Eve      | 10500.00 |              10150 |
|       8 | IT         | Frank    | 11200.00 |              10150 |
|       1 | Marketing  | Alice    | 12500.00 |              13900 |
|       6 | Marketing  | Alice    | 15300.00 |              13900 |
|       2 | Marketing  | Bob      | 18700.00 |              13900 |
|       4 | Sales      | David    | 16500.00 |              18975 |
|       9 | Sales      | David    | 19800.00 |              18975 |
|       3 | Sales      | Charlie  | 24300.00 |              18975 |
|       7 | Sales      | Charlie  | 27500.00 |              18975 |
+---------+------------+----------+----------+--------------------+
IT    位置 = 1+(0.25*(3-1)) = 1.5  在9800(1)、10500(2)中间位置，插值计算。
Marketing    位置 = 1+(0.25*(3-1)) = 1.5  在12500(1)、15300(2)中间位置，插值计算。
Sales    位置 = 1+(0.25*(4-1)) = 1.75  在16500(1)、19800(2)之间的3/4位置，插值计算。
 

PERCENTILE_DISC（离散百分位数）  25%分位
gbase> select sale_id,department,employee,amount,percentile_disc(0.25) within group (order by amount) over(partition by department) as 25_percentile_disc from sales;
+---------+------------+----------+----------+--------------------+
| sale_id | department | employee | amount   | 25_percentile_disc |
+---------+------------+----------+----------+--------------------+
|       5 | IT         | Eve      |  9800.00 |               9800 |
|      10 | IT         | Eve      | 10500.00 |               9800 |
|       8 | IT         | Frank    | 11200.00 |               9800 |
|       1 | Marketing  | Alice    | 12500.00 |              12500 |
|       6 | Marketing  | Alice    | 15300.00 |              12500 |
|       2 | Marketing  | Bob      | 18700.00 |              12500 |
|       4 | Sales      | David    | 16500.00 |              16500 |
|       9 | Sales      | David    | 19800.00 |              16500 |
|       3 | Sales      | Charlie  | 24300.00 |              16500 |
|       7 | Sales      | Charlie  | 27500.00 |              16500 |
+---------+------------+----------+----------+--------------------+
IT    位置 = CEIL(0.25*3) = 1  对应9800(1)位置
Marketing    位置 = CEIL(0.25*3) = 1  对应12500(1)位置
Sales    位置 = CEIL(0.25*4) = 1  对应16500(1)位置
 

7、mddiff  # 移动差分。按照指定的规则排序，计算当前行与前面n行的差值