>#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 ```