doris

数学函数

ceil和floor

SQL
BIGINT ceil(DOUBLE x)
-- 返回大于或等于x的最小整数值.
mysql> select ceil(1);

+-----------+
| ceil(1.0) |
+-----------+
|         1 |
+-----------+
mysql> select ceil(2.4);
+-----------+
| ceil(2.4) |
+-----------+
|         3 |
+-----------+
mysql> select ceil(-10.3);
+-------------+
| ceil(-10.3) |
+-------------+
|         -10 |
+-------------+

BIGINT floor(DOUBLE x)
-- 返回小于或等于x的最大整数值.
mysql> select floor(1);
+------------+
| floor(1.0) |
+------------+
|          1 |
+------------+
mysql> select floor(2.4);
+------------+
| floor(2.4) |
+------------+
|          2 |
+------------+
mysql> select floor(-10.3);
+--------------+
| floor(-10.3) |
+--------------+
|          -11 |
+--------------+

5.1.5.2round

SQL
round(x), round(x, d)
-- 将x四舍五入后保留d位小数，d默认为0。
-- 如果d为负数，则小数点左边d位为0。如果x或d为null，返回null。

mysql> select round(2.4);
+------------+
| round(2.4) |
+------------+
|          2 |
+------------+
mysql> select round(2.5);
+------------+
| round(2.5) |
+------------+
|          3 |
+------------+
mysql> select round(-3.4);
+-------------+
| round(-3.4) |
+-------------+
|          -3 |
+-------------+
mysql> select round(-3.5);
+-------------+
| round(-3.5) |
+-------------+
|          -4 |
+-------------+
mysql> select round(1667.2725, 2);
+---------------------+
| round(1667.2725, 2) |
+---------------------+
|             1667.27 |
+---------------------+
mysql> select round(1667.2725, -2);
+----------------------+
| round(1667.2725, -2) |
+----------------------+
|                 1700 |
+----------------------+

5.1.5.3truncate

SQL
DOUBLE truncate(DOUBLE x, INT d)
-- 按照保留小数的位数d对x进行数值截取。
-- 规则如下：
-- 当d > 0时：保留x的d位小数
-- 当d = 0时：将x的小数部分去除，只保留整数部分
-- 当d < 0时：将x的小数部分去除，整数部分按照 d所指定的位数，采用数字0进行替换

mysql> select truncate(124.3867, 2);
+-----------------------+
| truncate(124.3867, 2) |
+-----------------------+
|                124.38 |
+-----------------------+
mysql> select truncate(124.3867, 0);
+-----------------------+
| truncate(124.3867, 0) |
+-----------------------+
|                   124 |
+-----------------------+
mysql> select truncate(-124.3867, -2);
+-------------------------+
| truncate(-124.3867, -2) |
+-------------------------+
|                    -100 |
+-------------------------+

abs

SQL
数值类型 abs(数值类型 x)
-- 返回x的绝对值.

mysql> select abs(-2);
+---------+
| abs(-2) |
+---------+
|       2 |
+---------+
mysql> select abs(3.254655654);
+------------------+
| abs(3.254655654) |
+------------------+
|      3.254655654 |
+------------------+
mysql> select abs(-3254654236547654354654767);
+---------------------------------+
| abs(-3254654236547654354654767) |
+---------------------------------+
| 3254654236547654354654767       |
+---------------------------------+

pow

SQL
DOUBLE pow(DOUBLE a, DOUBLE b)
-- 求幂次：返回a的b次方.

mysql> select pow(2,0);
+---------------+
| pow(2.0, 0.0) |
+---------------+
|             1 |
+---------------+
mysql> select pow(2,3);
+---------------+
| pow(2.0, 3.0) |
+---------------+
|             8 |
+---------------+
mysql> select round(pow(3,2.4),2);
+--------------------+
| pow(3.0, 2.4)      |
+--------------------+
| 13.966610165238235 |
+--------------------+

greatest和 least

SQL
greatest(col_a, col_b, …, col_n)
-- 返回一行中 n个column的最大值.若column中有NULL，则返回NULL.

least(col_a, col_b, …, col_n)
-- 返回一行中 n个column的最小值.若column中有NULL，则返回NULL.

mysql> select greatest(-1, 0, 5, 8);
+-----------------------+
| greatest(-1, 0, 5, 8) |
+-----------------------+
|                     8 |
+-----------------------+
mysql> select greatest(-1, 0, 5, NULL);
+--------------------------+
| greatest(-1, 0, 5, NULL) |
+--------------------------+
| NULL                     |
+--------------------------+
mysql> select greatest(6.3, 4.29, 7.6876);
+-----------------------------+
| greatest(6.3, 4.29, 7.6876) |
+-----------------------------+
|                      7.6876 |
+-----------------------------+
mysql> select greatest("2022-02-26 20:02:11","2020-01-23 20:02:11","2020-06-22 20:02:11");
+-------------------------------------------------------------------------------+
| greatest('2022-02-26 20:02:11', '2020-01-23 20:02:11', '2020-06-22 20:02:11') |
+-------------------------------------------------------------------------------+
| 2022-02-26 20:02:11                                                           |
+-------------------------------------------------------------------------------+

小练习：

SQL
需求：求每个人工资组成部分中占比最高的工资

-- 准备数据
name user_id jbgz jjgz tcgz
zss,1,2000,3000,5000
lss,2,1000,4000,1000
www,3,5000,1000,5000
tqq,4,4000,300,7000

name user_id jbgz jjgz tcgz
zss,1,2000,3000,5000
lss,2,1000,4000,1000
www,3,5000,1000,5000
tqq,4,4000,300,7000

-- 建表语句
create table salary
(
name varchar(50),
user_id int,
jbgz double,
jjgz double,
tcgz double
)
DUPLICATE KEY(name)
DISTRIBUTED BY HASH(name) BUCKETS 1;

-- 通过本地文件的方式导入数据
curl \
-u root: \
-H "label:salary" \
-H "column_separator:," \
-T /root/data/salary.txt \
http://doitedu01:8040/api/test/salary/_stream_load

select * from salary;
+------+---------+------+------+------+
| name | user_id | jbgz | jjgz | tcgz |
+------+---------+------+------+------+
| lss |       2 | 1000 | 4000 | 1000 |
| tqq |       4 | 4000 | 300 | 7000 |
| www |       3 | 5000 | 1000 | 5000 |
| zss |       1 | 2000 | 3000 | 5000 |
+------+---------+------+------+------+

逻辑分析：最高工资比较好求，直接用greatest函数
greatest用法：greatest（字段一，字段二，字段三。。。）求多个字段中的最大值
工资类型怎么办？只能用这种等值匹配的方式去处理，让表自关联，然后最大值和三种工资匹配

+------+---------+------+------+------+--------+---------+
| name | user_id | jbgz | jjgz | tcgz | max_gz | gz_type |
+------+---------+------+------+------+--------+---------+
| lss |       2 | 1000 | 4000 | 1000 |   4000 | jjgz    |
| tqq |       4 | 4000 | 300 | 7000 |   7000 | tcgz    |
| www |       3 | 5000 | 1000 | 5000 |   5000 | jbgz    |
| zss |       1 | 2000 | 3000 | 5000 |   5000 | tcgz    |
+------+---------+------+------+------+--------+---------+

数组函数（1.2版本正式添加）

Only supported in vectorized engine

仅支持向量化引擎中使用

array()

array_min，array_max，array_avg，array_sum，array_size

SQL
求数组中的最小值，最大值，平均值，数组中所有元素的和，数组的长度
-- 数组中的NULL值会被跳过。空数组以及元素全为NULL值的数组，结果返回NULL值。

array_remove

SQL
ARRAY<T> array_remove(ARRAY<T> arr, T val)
-- 返回移除所有的指定元素后的数组，如果输入参数为NULL，则返回NULL

mysql> set enable_vectorized_engine=true;

mysql> select array_remove(['test', NULL, 'value'], 'value');
+-----------------------------------------------------+
| array_remove(ARRAY('test', NULL, 'value'), 'value') |
+-----------------------------------------------------+
| [test, NULL]                                        |
+-----------------------------------------------------+

mysql> select k1, k2, array_remove(k2, 1) from array_type_table_1;
+------+--------------------+-----------------------+
| k1   | k2                 | array_remove(`k2`, 1) |
+------+--------------------+-----------------------+
|    1 | [1, 2, 3]          | [2, 3]                |
|    2 | [1, 3]             | [3]                   |
|    3 | NULL               | NULL                  |
|    4 | [1, 3]             | [3]                   |
|    5 | [NULL, 1, NULL, 2] | [NULL, NULL, 2]       |
+------+--------------------+-----------------------+

mysql> select k1, k2, array_remove(k2, k1) from array_type_table_1;
+------+--------------------+--------------------------+
| k1   | k2                 | array_remove(`k2`, `k1`) |
+------+--------------------+--------------------------+
|    1 | [1, 2, 3]          | [2, 3]                   |
|    2 | [1, 3]             | [1, 3]                   |
|    3 | NULL               | NULL                     |
|    4 | [1, 3]             | [1, 3]                   |
|    5 | [NULL, 1, NULL, 2] | [NULL, 1, NULL, 2]       |
+------+--------------------+--------------------------+

array_sort

SQL
ARRAY<T> array_sort(ARRAY<T> arr)
-- 返回按升序排列后的数组，如果输入数组为NULL，则返回NULL。
-- 如果数组元素包含NULL, 则输出的排序数组会将NULL放在最前面。

mysql> set enable_vectorized_engine=true;
mysql> select k1, k2, array_sort(k2) array_test;
+------+-----------------------------+-----------------------------+
| k1   | k2                          | array_sort(`k2`)            |
+------+-----------------------------+-----------------------------+
| 1   | [1, 2, 3, 4, 5]             | [1, 2, 3, 4, 5]             |
| 2   | [6, 7, 8]                   | [6, 7, 8]                   |
| 3   | []                          | []                          |
| 4   | NULL                        | NULL                        |
| 5   | [1, 2, 3, 4, 5, 4, 3, 2, 1] | [1, 1, 2, 2, 3, 3, 4, 4, 5] |
| 6   | [1, 2, 3, NULL]             | [NULL, 1, 2, 3]             |
| 7   | [1, 2, 3, NULL, NULL]       | [NULL, NULL, 1, 2, 3]       |
| 8   | [1, 1, 2, NULL, NULL]       | [NULL, NULL, 1, 1, 2]       |
| 9   | [1, NULL, 1, 2, NULL, NULL] | [NULL, NULL, NULL, 1, 1, 2] |
+------+-----------------------------+-----------------------------+

array_contains

SQL
BOOLEAN array_contains(ARRAY<T> arr, T value)

-- 判断数组中是否包含value。返回结果如下：
-- 1    - value在数组arr中存在；
-- 0    - value不存在数组arr中；
-- NULL - arr为NULL时。

mysql> set enable_vectorized_engine=true;

mysql> SELECT id,c_array,array_contains(c_array, 5) FROM `array_test`;
+------+-----------------+------------------------------+
| id   | c_array         | array_contains(`c_array`, 5) |
+------+-----------------+------------------------------+
|    1 | [1, 2, 3, 4, 5] |                            1 |
|    2 | [6, 7, 8]       |                            0 |
|    3 | []              |                            0 |
|    4 | NULL            |                         NULL |
+------+-----------------+------------------------------+

array_except

SQL
ARRAY<T> array_except(ARRAY<T> array1, ARRAY<T> array2)
-- 返回一个数组，包含所有在array1内但不在array2内的元素，会对返回的结果数组去重
-- 类似于取差集，将返回的差集结果数组去重

mysql> set enable_vectorized_engine=true;

mysql> select k1,k2,k3,array_except(k2,k3) from array_type_table;
+------+-----------------+--------------+--------------------------+
| k1   | k2              | k3           | array_except(`k2`, `k3`) |
+------+-----------------+--------------+--------------------------+
|    1 | [1, 2, 3]       | [2, 4, 5]    | [1, 3]                   |
|    2 | [2, 3]          | [1, 5]       | [2, 3]                   |
|    3 | [1, 1, 1]       | [2, 2, 2]    | [1]                      |
+------+-----------------+--------------+--------------------------+

array_intersect

SQL
ARRAY<T> array_intersect(ARRAY<T> array1, ARRAY<T> array2)

-- 返回一个数组，包含array1和array2的交集中的所有元素，不包含重复项
-- 两个数组去交集后。将返回的结果去重

mysql> set enable_vectorized_engine=true;

mysql> select k1,k2,k3,array_intersect(k2,k3) from array_type_table;
+------+-----------------+--------------+-----------------------------+
| k1   | k2              | k3           | array_intersect(`k2`, `k3`) |
+------+-----------------+--------------+-----------------------------+
|    1 | [1, 2, 3]       | [2, 4, 5]    | [2]                         |
|    2 | [2, 3]          | [1, 5]       | []                          |
|    3 | [1, 1, 1]       | [2, 2, 2]    | []                          |
+------+-----------------+--------------+-----------------------------+

mysql> select k1,k2,k3,array_intersect(k2,k3) from array_type_table_nullable;
+------+-----------------+--------------+-----------------------------+
| k1   | k2              | k3           | array_intersect(`k2`, `k3`) |
+------+-----------------+--------------+-----------------------------+
|    1 | [1, NULL, 3]    | [1, 3, 5]    | [1, 3]                      |
|    2 | [NULL, NULL, 2] | [2, NULL, 4] | [NULL, 2]                   |
|    3 | NULL            | [1, 2, 3]    | NULL                        |
+------+-----------------+--------------+-----------------------------+

array_union

SQL
ARRAY<T> array_union(ARRAY<T> array1, ARRAY<T> array2)
-- 返回一个数组，包含array1和array2的并集中的所有元素，不包含重复项
-- 取两个数组的并集，将返回的结果去重

mysql> set enable_vectorized_engine=true;

mysql> select k1,k2,k3,array_union(k2,k3) from array_type_table;
+------+-----------------+--------------+-------------------------+
| k1   | k2              | k3           | array_union(`k2`, `k3`) |
+------+-----------------+--------------+-------------------------+
|    1 | [1, 2, 3]       | [2, 4, 5]    | [1, 2, 3, 4, 5]         |
|    2 | [2, 3]          | [1, 5]       | [2, 3, 1, 5]            |
|    3 | [1, 1, 1]       | [2, 2, 2]    | [1, 2]                  |
+------+-----------------+--------------+-------------------------+

array_distinct

SQL
ARRAY<T> array_distinct(ARRAY<T> arr)

-- 返回去除了重复元素的数组，如果输入数组为NULL，则返回NULL。

mysql> set enable_vectorized_engine=true;

mysql> select k1, k2, array_distinct(k2) from array_test;
+------+-----------------------------+---------------------------+
| k1   | k2                          | array_distinct(k2)        |
+------+-----------------------------+---------------------------+
| 1    | [1, 2, 3, 4, 5]             | [1, 2, 3, 4, 5]           |
| 2    | [6, 7, 8]                   | [6, 7, 8]                 |
| 3    | []                          | []                        |
| 4    | NULL                        | NULL                      |
| 5    | [1, 2, 3, 4, 5, 4, 3, 2, 1] | [1, 2, 3, 4, 5]           |
| 6    | [1, 2, 3, NULL]             | [1, 2, 3, NULL]           |
| 7    | [1, 2, 3, NULL, NULL]       | [1, 2, 3, NULL]           |
+------+-----------------------------+---------------------------+

JSON函数

建表，导入测试数据

SQL
CREATE TABLE test_json (
id INT,
json_string String
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 3
PROPERTIES("replication_num" = "1");

--测试数据
{"k1":"v31", "k2": 300, "a1": [{"k1":"v41", "k2": 400}, 1, "a", 3.14]}
{"k1":"v32", "k2": 400, "a1": [{"k1":"v41", "k2": 400}, 2, "a", 4.14],"a2":{"k3":"v33", "k4": 200,"a2": [{"k1":"v41", "k2": 400}, 2, "a", 4.14]}}
{"k1":"v33", "k2": 500, "a1": [{"k1":"v41", "k2": 400}, 3, "a", 5.14],"a2":{"k3":"v33", "k4": 200,"a2": [{"k5":"v42", "k6": 600}]}}
{"k1":"v31"}
{"k1":"v31", "k2": 300}
{"k1":"v31", "k2": 200 "a1": []}

--json是一种里面存着一对对key，value类型的结构
--针对值类型的不同：
1.简单值："k1":"v31"
2.数组：[{"k1":"v41", "k2": 400}, 1, "a", 3.14]
3.对象："a2":{"k3":"v33", "k4": 200,"a2": [{"k5":"v42", "k6": 600}]}

取值的时候，指定的'$.k1'==>这样的东西我们称之为json path ，json的路劲

-- 通过本地文件的方式导入
curl \
-u root: \
-H "label:load_local_file1" \
-H "column_separator:_" \
-T /root/data/json.txt \
http://doitedu01:8040/api/test/test_json/_stream_load

-- 用insert into 的方式导入一条
INSERT INTO test_json VALUES(7, '{"k1":"v1", "k2": 200}');

get_json_double，get_json_int，get_json_string

SQL
语法：
DOUBLE get_json_int(VARCHAR json_str, VARCHAR json_path)
INT get_json_int(VARCHAR json_str, VARCHAR json_path)
VARCHAR get_json_string(VARCHAR json_str, VARCHAR json_path)

-- 解析并获取 json 字符串内指定路径的double，int，string 类型的内容。
-- 其中 json_path 必须以 $ 符号作为开头，使用 . 作为路径分割符。
-- 如果路径中包含 . ，则可以使用双引号包围。
-- 使用 [ ] 表示数组下标，从 0 开始。
-- path 的内容不能包含 ", [ 和 ]。
-- 如果 json_string 格式不对，或 json_path 格式不对，或无法找到匹配项，则返回 NULL。

--1.获取到k1对应的value的值
mysql> select id, get_json_string(json_string,'$.k1') as k1 from test_json;
+------+------+
| id   | k1   |
+------+------+
|    2 | v32 |
|    4 | v31 |
|    5 | v31 |
|    6 | v31 |
|    1 | v31 |
|    3 | v33 |
+------+------+

--2.获取到key 为a1 里面的数组
mysql> select id, get_json_string(json_string,'$.a1') as arr from test_json;
+------+------------------------------------+
| id   | arr                                |
+------+------------------------------------+
|    1 | [{"k1":"v41","k2":400},1,"a",3.14] |
|    3 | [{"k1":"v41","k2":400},3,"a",5.14] |
|    2 | [{"k1":"v41","k2":400},2,"a",4.14] |
|    4 | NULL                               |
|    5 | NULL                               |
|    6 | []                                 |
+------+------------------------------------+

--3.获取到key 为a1 里面的数组中第一个元素的值
mysql> select id, get_json_string(json_string,'$.a1[0]') as arr from test_json;
+------+-----------------------+
| id   | arr                   |
+------+-----------------------+
|    2 | {"k1":"v41","k2":400} |
|    1 | {"k1":"v41","k2":400} |
|    3 | {"k1":"v41","k2":400} |
|    4 | NULL                  |
|    5 | NULL                  |
|    6 | NULL                  |
+------+-----------------------+

--4.获取到key 为a1 里面的数组中第一个元素的值（这个值是一个json串，再次获取到这个字符串中）
select id, get_json_string(get_json_string(json_string,'$.a1[0]'),'$.k1') as arr from test_json;
+------+------+
| id   | arr |
+------+------+
|    2 | v41 |
|    1 | v41 |
|    3 | v41 |
|    4 | NULL |
|    5 | NULL |
|    6 | NULL |
+------+------+
6 rows in set (0.02 sec)

json_object

SQL
VARCHAR json_object(VARCHAR,...)
-- 生成一个包含指定Key-Value对的json object,
-- 传入的参数是key,value对，且key不能为null

87

MySQL> select json_object('time',curtime());
+--------------------------------+
| json_object('time', curtime()) |
+--------------------------------+
| {"time": "10:49:18"}           |
+--------------------------------+

MySQL> SELECT json_object('id', 87, 'name', 'carrot');
+-----------------------------------------+
| json_object('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+

json_object('id', 87, 'name', 'carrot');

MySQL> select json_object('username',null);
+---------------------------------+
| json_object('username', 'NULL') |
+---------------------------------+
| {"username": NULL}              |
+---------------------------------+