>#Doris 查询 [TOC] Doris的查询语法 ------------ ```sql SELECT [ALL | DISTINCT | DISTINCTROW ] -- 对查询字段的结果是否需要去重，还是全部保留等参数 select_expr [, select_expr ...] -- select的查询字段 [FROM table_references [PARTITION partition_list] -- from 哪个库里面的那张表甚至哪一个(几个)分区 [WHERE where_condition] -- WHERE 查询 [GROUP BY {col_name | expr | position} -- group by 聚合 [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] -- having 针对聚合函数的再一次过滤 [ORDER BY {col_name | expr | position} -- 对结果数据按照字段进行排序 [ASC | DESC], ...] -- 排序规则 [LIMIT {[offset,] row_count | row_count OFFSET offset}] -- 限制输出多少行内容 [INTO OUTFILE 'file_name'] -- 将查询的结果导出到文件中 ``` doris内置函数 ------------ ### 条件函数 #### if函数 tn2>语法示例： ```sql if(boolean condition, type valueTrue, type valueFalseOrNull) --如果表达式 condition 成立，返回结果 valueTrue；否则，返回结果 valueFalseOrNull --返回值类型：valueTrue 表达式结果的类型 ``` tn2>示例： ```sql select id,if(id=1,"true","false") as test_if from test.load_local_file_test ```  #### ifnull,nvl,coalesce,nullif函数 tn2>`ifnull(expr1, expr2)`:如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2. ```sql select ifnull(null,200); select ifnull(100,200); ```   tn2>`nvl(expr1, expr2)`:如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2 ```sql select nvl(100,200); ```  tn2>`coalesce(expr1, expr2, ...., expr_n))`：返回参数中的第一个非空表达式（从左向右） ```sql select coalesce(100,200,300); select coalesce(null,null,300); ```   tn2>`nullif(expr1, expr2)`：如果两个参数相等，则返回NULL。否则返回第一个参数的值 ```sql select nullif(100,100); select nullif(100,200); ```   #### case语法 tn2>将表达式和多个可能的值进行比较，当匹配时返回相应的结果 语法示例： ```sql -- 方式一 CASE expression WHEN condition1 THEN result1 [WHEN condition2 THEN result2] ... [WHEN conditionN THEN resultN] [ELSE result] END -- 方式二 CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2] ... [WHEN conditionN THEN resultN] [ELSE result] END ``` tn2>示例： ```sql select id, case id when 1 then 'id = 1' when 2 then 'id = 2' else 'id not exist' end as test_case from test.load_local_file_test; ```  ```sql select id, case when id = 1 then 'id = 1' when id = 2 then 'id = 2' else 'id not exist' end as test_case from test.load_local_file_test; ```  ```sql select *,case sex when 1 then '男' when 0 then '女' end 性别 from test.ex_user; ```  ```sql select *,case when sex = 1 then '男' when sex = 0 then '女' end 性别 from test.ex_user; ```  聚合函数 ------------ ### min_by和max_by tn2>`MAX_BY(expr1, expr2)`:返回expr2最大值所在行的 expr1 （求分组top1的简介函数） ```sql --这里通过找到最大的user_id返回它的年龄 select max_by(age, user_id) from test.ex_user; ```  #### 求每门课程成绩最高分的那个人 tn2>创建一个`score.txt`文件，添加如下内容： ```bash zss,chinese,99 zss,math,89 zss,English,79 lss,chinese,88 lss,math,88 lss,English,22 www,chinese,99 www,math,45 zll,chinese,23 zll,math,88 zll,English,80 www,English,94 ``` tn2>创建一张表并创建名字、学科、分数的字段。 ```bash -- 建表语句 create table test.score ( name varchar(50), subject varchar(50), score double ) DUPLICATE KEY(name) DISTRIBUTED BY HASH(name) BUCKETS 1 PROPERTIES ( "replication_num" = "1" ); ```  tn2>然后我们导入数据。 ```bash curl -u root: -H "label:score_20240616" -H "column_separator:," -T "C:\Users\bob.he\OneDrive - Oerlikon Group\Desktop\project\Doris\score.txt" http://127.0.0.1:8040/api/test/score/_stream_load ```  tn2>doris中的写法 ```bash select subject,max_by(name,score) as name from score group by subject ```  tn2>但是无法处理成绩并列的情况。 ### group_concat函数 tn2>求：每一个人有考试成绩的所有科目 ```bash select name,group_concat(subject,',') as all_subject from score group by name ```  tn2>语法示例： ```sql VARCHAR GROUP_CONCAT([DISTINCT] VARCHAR 列名[, VARCHAR sep] 该函数是类似于 sum() 的聚合函数，group_concat 将结果集中的多行结果连接成一个字符串 -- group_concat对于收集的字段只能是string，varchar，char类型 --当不指定分隔符的时候，默认使用 ',' VARCHAR ：代表GROUP_CONCAT函数返回值类型 [DISTINCT]：可选参数，针对需要拼接的列的值进行去重 [, VARCHAR sep]：拼接成字符串的分隔符，默认是 ',' ``` ```sql select name,group_concat(subject,',') as all_subject from score group by name; 相当于 select name,concat_ws(',',collect_list(subject)) from score group by name; ```  tn2>简单示例： 首先建表并建立表数据。 ```sql --建表 create table test.example( id int, name varchar(50), age int, gender string, is_marry boolean, marry_date date, marry_datetime datetime )engine = olap distributed by hash(id) buckets 3 PROPERTIES ( "replication_num" = "1" ); --插入数据 insert into example values (1,'zss',18,'male',0,null,null), (2,'lss',28,'female',1,'2022-01-01','2022-01-01 11:11:11'), (3,'ww',38,'male',1,'2022-02-01','2022-02-01 11:11:11'), (4,'zl',48,'female',0,null,null), (5,'tq',58,'male',1,'2022-03-01','2022-03-01 11:11:11'), (6,'mly',18,'male',1,'2022-04-01','2022-04-01 11:11:11'), (7,null,18,'male',1,'2022-05-01','2022-05-01 11:11:11'); ```  tn2>当收集的那一列，有值为null时，他会自动将null的值过滤掉。 ```sql select gender, group_concat(name,',') as gc_name from example group by gender; ```  ```sql select gender, group_concat(DISTINCT cast(age as string)) as gc_age from example group by gender; ```  ### collect_list，collect_set tn2>`collect_list`用于将某列的所有值收集到一个数组中，包括重复值。 例如，按性别分组，收集每个性别对应的名字列表： ```sql select gender, collect_list(name) as name_list from test.example group by gender; ```  tn2>`collect_set`用于将某列的不重复值收集到一个数组中。 例如，按性别分组，收集每个性别对应的不重复名字集合： ```sql select gender, collect_set(name) as unique_name_set from test.example group by gender; ```  tn>虽然这里的数据不是很体现，但简单来讲上一个是收集重复的，下面那个收集不重复的。 日期函数 ------------ ### 获取当前时间 tn2>curdate，current_date，now，curtime，current_time，current_timestamp ```sql select current_date(),curdate(), now(),curtime(),current_timestamp(); ```  ### last_day tn2>`DATE last_day(DATETIME date)`返回输入日期中月份的最后一天； `28`(非闰年的二月份), `29`(闰年的二月份), `30`(四月，六月，九月，十一月), `31`(一月，三月，五月，七月，八月，十月，十二月) 举例：给我返回这个月份中的最后一天的日期 年月日 ```sql select last_day('2000-03-03 01:00:00'); ```  ### from_unixtime tn2>`DATETIME FROM_UNIXTIME(INT unix_timestamp[, VARCHAR string_format])`将 unix 时间戳转化为对应的 time 格式，返回的格式由 `string_format` 指定,支持date_format中的format格式，默认为 `%Y-%m-%d %H:%i:%s` 正常使用的三种格式: `yyyyMMdd` `yyyy-MM-dd` `yyyy-MM-dd HH:mm:ss`  ### unix_timestamp tn2>这个方法是将日期生成时间搓，可以传三种方法： ```sql UNIX_TIMESTAMP(), UNIX_TIMESTAMP(DATETIME date), UNIX_TIMESTAMP(DATETIME date, STRING fmt) -- 给一个日期，指定这个日期的格式 -- 将日期转换成时间戳，返回值是一个int类型 ``` tn2>获取当前日期的时间戳 ```sql select unix_timestamp(); ```  tn2>获取指定日期的时间戳 ```sql select unix_timestamp('2022-11-26 01:09:01'); ```  tn2>给定一个特殊日期格式的时间戳，指定格式 ```sql select unix_timestamp('2022-11-26 01:09-01', '%Y-%m-%d %H:%i-%s'); ```  ### to_date tn2>`DATE TO_DATE(DATETIME)`返回 DATETIME 类型中的日期部分。 ```sql select to_date("2022-11-20 00:00:00"); ```  ### extract tn2>`extract(unit FROM DATETIME)`抽取提取DATETIME某个指定单位的值。 unit单位可以为`year`, `month`, `day`, `hour`, `minute`或者`second`。 ```sql select extract(year from '2022-09-22 17:01:30') as year, extract(month from '2022-09-22 17:01:30') as month, extract(day from '2022-09-22 17:01:30') as day, extract(hour from '2022-09-22 17:01:30') as hour, extract(minute from '2022-09-22 17:01:30') as minute, extract(second from '2022-09-22 17:01:30') as second; ```  ### date_add，date_sub,datediff tn2>语法： ```sql DATE_ADD(DATETIME date,INTERVAL expr type) DATE_SUB(DATETIME date,INTERVAL expr type) DATEDIFF(DATETIME expr1,DATETIME expr2) -- 计算两个日期相差多少天，结果精确到天。 -- 向日期添加指定的时间间隔。 -- date 参数是合法的日期表达式。 -- expr 参数是您希望添加的时间间隔。 -- type 参数可以是下列值：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND ``` tn2>示例： ```bash select date_add('2010-11-30 23:59:59', INTERVAL 2 DAY),date_add('2010-11-30 23:59:59', INTERVAL -2 DAY),datediff('2022-11-27 22:51:56','2022-11-24 22:50:56'); ```  ### date_format tn2>`VARCHAR DATE_FORMAT(DATETIME date, VARCHAR format)`将日期类型按照format的类型转化为字符串 ```bash select date_format('2007-10-04 22:23:00', '%H:%i:%s'), date_format('2007-10-04 22:23:00', 'yyyy-MM-dd'),date_format('2007-10-04 22:23:00', '%Y-%m'); ```  字符串函数 ------------ ### length，lower，upper，reverse ```bash --返回字符串的长度 SELECT length('Hello, Doris!') AS length_result; ## 13 --将字符串转换为小写 SELECT lower('HELLO, DORIS!') AS lower_result; ## hello, doris! --将字符串转换为大写 SELECT upper('hello, doris!') AS upper_result; ## HELLO, DORIS! --返回字符串的反转 SELECT reverse('hello, doris!') AS reverse_result; ## !sirod ,olleh ``` ### lpad，rpad ```sql -- 向左边补齐 SELECT lpad("1", 5, "0"); # 00001 SELECT rpad("1", 5, "0"); # 10000 -- 向右边补齐 SELECT rpad('11', 5, '0'); # 11000 ``` ### concat，concat_ws tn2>concat(str1, str2, ..., strN) 功能：将多个字符串 str1, str2, ..., strN 连接成一个字符串。 参数：多个字符串。 返回值：连接后的字符串。 ```bash select concat("a", "b"); # ab select concat("a", "b", "c"); # abc -- concat中，如果有一个值为null，那么得到的结果就是null select concat("a", null, "c"); # NULL ``` ### substr ```bash --求子字符串，返回第一个参数描述的字符串中从start开始长度为len的部分字符串。 --首字母的下标为1。 select substr("Hello doris", 2, 1); # e select substr("Hello doris", 1, 2); # He ``` ### ends_with，starts_with tn2>ends_with(str, suffix) 功能：如果字符串 str 以指定的后缀 suffix 结尾，则返回 true，否则返回 false。 参数： str：要检查的字符串。 suffix：要匹配的后缀字符串。 返回值：BOOLEAN 类型，true 或 false。如果任意参数为 NULL，则返回 NULL。 ```bash select ends_with("Hello doris", "doris"); # 1 select ends_with("Hello doris", "Hello"); # 0 ``` tn2>starts_with(str, prefix) 功能：如果字符串 str 以指定的前缀 prefix 开头，则返回 true，否则返回 false。 参数： str：要检查的字符串。 prefix：要匹配的前缀字符串。 返回值：BOOLEAN 类型，true 或 false。如果任意参数为 NULL，则返回 NULL。 ```bash select starts_with("hello world","hello"); # 1 select starts_with("hello world","world"); # 0 ``` ### trim，ltrim，rtrim ```bash VARCHAR trim(VARCHAR str) -- 将参数 str 中左侧和右侧开始部分连续出现的空格去掉 SELECT trim(' ab d ') str; #ab d VARCHAR ltrim(VARCHAR str) -- 将参数 str 中从左侧部分开始部分连续出现的空格去掉 SELECT ltrim(' ab d') str; #ab d VARCHAR rtrim(VARCHAR str) --将参数 str 中从右侧部分开始部分连续出现的空格去掉 SELECT rtrim('ab d ') str; # ab d ``` ### null_or_empty，not_null_or_empty tn2>`BOOLEAN NULL_OR_EMPTY (VARCHAR str)`如果字符串为空字符串或者NULL，返回true。否则，返回false。 ```bash select null_or_empty(null); # 1 select null_or_empty(""); # 1 select null_or_empty("a"); # 0 ``` tn2>`BOOLEAN NOT_NULL_OR_EMPTY (VARCHAR str)`如果字符串为空字符串或者NULL，返回false。否则，返回true。 ```bash select not_null_or_empty(null); # 0 select not_null_or_empty(""); # 0 select not_null_or_empty("a"); # 1 ``` ### replace tn2>`VARCHAR REPLACE (VARCHAR str, VARCHAR old, VARCHAR new)`将str字符串中的old子串全部替换为new串 ```bash select replace("http://www.baidu.com:9090", "9090", ""); # http://www.baidu.com: ``` ### split_part tn2>`VARCHAR split_part(VARCHAR content, VARCHAR delimiter, INT field)`根据分割符拆分字符串, 返回指定的分割部分(从1开始计数)。 ```bash select split_part("hello world", " ", 1); # hello select split_part("hello world", " ", 2); # world select split_part("2019年7月8号", "月", 1); # 2019年7 select split_part("abca", "a", 1); # ``` ### money_format tn2>`VARCHAR money_format(Number)`将数字按照货币格式输出，整数部分每隔3位用逗号分隔，小数部分保留2位。 ```bash select money_format(17014116); # 17,014,116.00 select money_format(1123.456); # 1,123.46 select money_format(1123.4); # 1,123.40 ``` 数学函数 ------------ ### ceil和floor ```bash BIGINT ceil(DOUBLE x) -- 返回大于或等于x的最小整数值. select ceil(1); # 1 select ceil(2.4); # 3 select ceil(-10.3); # -10 BIGINT floor(DOUBLE x) -- 返回小于或等于x的最大整数值. select floor(1); # 1 select floor(2.4); # 2 select floor(-10.3); # -11 ``` ### round ```bash round(x), round(x, d) -- 将x四舍五入后保留d位小数，d默认为0。 -- 如果d为负数，则小数点左边d位为0。如果x或d为null，返回null。 select round(2.4); # 2 select round(2.5); # 3 select round(-3.4); # -3 select round(-3.5); # -4 select round(1667.2725, 2); # 1667.27 select round(1667.2725, -2); # 1700 ``` ### truncate ```bash DOUBLE truncate(DOUBLE x, INT d) -- 按照保留小数的位数d对x进行数值截取。 -- 规则如下： -- 当d > 0时：保留x的d位小数 -- 当d = 0时：将x的小数部分去除，只保留整数部分 -- 当d < 0时：将x的小数部分去除，整数部分按照 d所指定的位数，采用数字0进行替换 select truncate(124.3867, 2); # 124.38 select truncate(124.3867, 0); # 124 select truncate(-124.3867, -2); # -100 ``` ### abs ```bash 数值类型 abs(数值类型 x) -- 返回x的绝对值. select abs(-2); # 2 select abs(3.254655654); # 3.254655654 select abs(-3254654236547654354654767); # 3254654236547654354654767 ``` ### pow ```bash DOUBLE pow(DOUBLE a, DOUBLE b) -- 求幂次：返回a的b次方. select pow(2,0); # 1 select pow(2,3); # 8 select round(pow(3,2.4),2); # 13.97 ``` ### greatest和 least ```bash greatest(col_a, col_b, …, col_n) -- 返回一行中 n个column的最大值.若column中有NULL，则返回NULL. least(col_a, col_b, …, col_n) -- 返回一行中 n个column的最小值.若column中有NULL，则返回NULL. select greatest(-1, 0, 5, 8); # 8 select greatest(-1, 0, 5, NULL); # (NULL) select greatest(6.3, 4.29, 7.6876); # 7.6876 select 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 ``` ### JSON函数 tn2>首先创建一个测试表。 ```bash CREATE TABLE test_json ( id INT, json_string String ) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 3 PROPERTIES("replication_num" = "1"); ``` tn2>创建一个`json.txt`测试数据。 数据如下图所示： ```bash 1_{"k1":"v31", "k2": 300, "a1": [{"k1":"v41", "k2": 400}, 1, "a", 3.14]} 2_{"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]}} 3_{"k1":"v33", "k2": 500, "a1": [{"k1":"v41", "k2": 400}, 3, "a", 5.14],"a2":{"k3":"v33", "k4": 200,"a2": [{"k5":"v42", "k6": 600}]}} 4_{"k1":"v31"} 5_{"k1":"v31", "k2": 300} 6_{"k1":"v31", "k2": 200 ,"a1": []} ``` tn2>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的路劲 接下来我们通过本地方式进行导入： ```bash curl -u root: -H "label:load_local_file111111" -H "column_separator:_" -T "C:\Users\bob.he\OneDrive - Oerlikon Group\Desktop\project\Doris\json.txt" http://127.0.0.1:8040/api/test/test_json/_stream_load ```  tn2>用`insert into` 的方式导入一条 ```bash INSERT INTO test_json VALUES(7, '{"k1":"v1", "k2": 200}'); ```  tn2>下面是查出的数据。  tn2>获取int类型K2的值。 ```bash SELECT get_json_int(json_string,'$.k2') from test_json ```  tn2>如果我们想获取中括号里面的字符串类型`k1`我们可以这样写： ```bash SELECT get_json_string(get_json_string(json_string,'$.a1[0]'),'$.k1') from test_json ```  ### 变成json（json_object函数） tn2>`VARCHAR json_object(VARCHAR,...)`生成一个包含指定Key-Value对的json object, 传入的参数是key,value对，且key不能为null。 ```bash select json_object('time',curtime()); # {"time":"5:48:2"} SELECT json_object('id', 87, 'name', 'carrot'); # {"id":87,"name":"carrot"} select json_object('username',null); # {"username":null} ``` 窗口函数 ------------ tn2>doris中的窗口函数和hive中的窗口函数的用法一样 首先，我们需要创建一个表 int_t，并插入一些数据以便进行测试。以下是创建表和插入数据的 SQL 语句： ```sql -- 创建表 CREATE TABLE int_t ( x INT, y INT ) DUPLICATE KEY(x) DISTRIBUTED BY HASH(x) BUCKETS 3 PROPERTIES ( "replication_num" = "1" -- 设置复制因子为1 ); -- 插入数据 INSERT INTO int_t (x, y) VALUES (1, 1), (1, 2), (1, 2), (2, 1), (2, 2), (2, 3), (3, 1), (3, 1), (3, 2); ```  ### RANK() 函数 tn2>RANK() 函数用于根据排序列的值进行排名，如果排序列的值相同，则会并列排名，后续的排名会跳过并列的排名数。 ```sql select x, y, rank() over(partition by x order by y) as rank from int_t; ```  tn2>解释： 按照 x 分组，然后在每个分组内按照 y 排序。 对于 x=1 的分组，y=1 排名第 1，y=2 有两个相同的值，都排名第 2，下一个不同的值会排名第 4（跳过了第 3 名）。 对于 x=2 的分组，y=1 排名第 1，y=2 排名第 2，y=3 排名第 3。 对于 x=3 的分组，y=1 有两个相同的值，都排名第 1，y=2 排名第 3。 ### DENSE_RANK() 函数 tn2>DENSE_RANK() 函数也用于根据排序列的值进行排名，如果排序列的值相同，则会并列排名，但后续的排名不会跳过并列的排名数。 ```sql select x, y, dense_rank() over(partition by x order by y) as rank from int_t; ```  tn2>解释： 按照 x 分组，然后在每个分组内按照 y 排序。 对于 x=1 的分组，y=1 排名第 1，y=2 有两个相同的值，都排名第 2，下一个不同的值会排名第 3（不会跳过排名）。 对于 x=2 的分组，y=1 排名第 1，y=2 排名第 2，y=3 排名第 3。 对于 x=3 的分组，y=1 有两个相同的值，都排名第 1，y=2 排名第 2。 ### ROW_NUMBER() 函数 tn2>ROW_NUMBER() 函数用于按照分组排序要求，返回连续递增的整数，从 1 开始，不会有重复值，也不会有空缺值。 ```sql select x, y, row_number() over(partition by x order by y) as rank from int_t; ```  tn2>解释： 按照 x 分组，然后在每个分组内按照 y 排序。 对于 x=1 的分组，y=1 排名第 1，y=2 的两个值分别排名第 2 和第 3。 对于 x=2 的分组，y=1 排名第 1，y=2 排名第 2，y=3 排名第 3。 对于 x=3 的分组，y=1 的两个值分别排名第 1 和第 2，y=2 排名第 3。 ### min，max，sum，avg，count ```sql min(x)over() -- 取窗口中x列的最小值 max(x)over() -- 取窗口中x列的最大值 sum(x)over() -- 取窗口中x列的数据总和 avg(x)over() -- 取窗口中x列的数据平均值 count(x)over() -- 取窗口中x列有多少行 unbounded preceding current row 1 following 1 PRECEDING rows between unbounded preceding and current row --指在当前窗口中第一行到当前行的范围 rows between unbounded preceding and 1 following --指在当前窗口中第一行到当前行下一行的范围 rows between unbounded preceding and 1 PRECEDING --指在当前窗口中第一行到当前行前一行的范围 ``` ### LAG() 和 LEAD() 窗口函数 tn2>创建表并插入数据 首先，我们创建一个表并插入一些数据以便进行测试： ```sql CREATE TABLE sales_data ( date DATE, product_id INT, sales_amount DECIMAL(10, 2) ) PROPERTIES ( "replication_num" = "1" -- 设置复制因子为1 ); -- 插入数据 INSERT INTO sales_data (date, product_id, sales_amount) VALUES ('2023-10-01', 101, 100.50), ('2023-10-01', 102, 150.75), ('2023-10-02', 101, 200.25), ('2023-10-02', 102, 175.30), ('2023-10-03', 101, 180.80), ('2023-10-03', 102, 200.45), ('2023-10-04', 101, 220.60), ('2023-10-04', 102, 190.75); SELECT * from sales_data ```  tn2>LAG() 函数 LAG() 函数用于获取当前行之前若干行的值。 ```sql select date, product_id, sales_amount, lag(sales_amount, 1, NULL) over(partition by product_id order by date) as prev_sales from sales_data; ```  tn2>LEAD() 函数 LEAD() 函数用于获取当前行之后若干行的值。 ```sql select date, product_id, sales_amount, lead(sales_amount, 1, NULL) over(partition by product_id order by date) as next_sales from sales_data; ```