Doris 查询

• Doris 查询
  • Doris的查询语法
  • doris内置函数
    • 条件函数
      • if函数
      • ifnull,nvl,coalesce,nullif函数
      • case语法
  • 聚合函数
    • min_by和max_by
      • 求每门课程成绩最高分的那个人
    • group_concat函数
    • collect_list，collect_set
  • 日期函数
    • 获取当前时间
    • last_day
    • from_unixtime
    • unix_timestamp
    • to_date
    • extract
    • date_add，date_sub,datediff
    • date_format
  • 字符串函数
    • length，lower，upper，reverse
    • lpad，rpad
    • concat，concat_ws
    • substr
    • ends_with，starts_with
    • trim，ltrim，rtrim
    • null_or_empty，not_null_or_empty
    • replace
    • split_part
    • money_format
  • 数学函数
    • ceil和floor
    • round
    • truncate
    • abs
    • pow
    • greatest和 least
    • JSON函数
    • 变成json（json_object函数）
  • 窗口函数
    • RANK() 函数
    • DENSE_RANK() 函数
    • ROW_NUMBER() 函数
    • min，max，sum，avg，count
    • LAG() 和 LEAD() 窗口函数

Doris的查询语法

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函数

语法示例：

if(boolean condition, type valueTrue, type valueFalseOrNull)
--如果表达式 condition 成立，返回结果 valueTrue；否则，返回结果 valueFalseOrNull
--返回值类型：valueTrue 表达式结果的类型

示例：

select id,if(id=1,"true","false") as test_if from test.load_local_file_test

ifnull,nvl,coalesce,nullif函数

ifnull(expr1, expr2):如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2.

select ifnull(null,200);
select ifnull(100,200);

nvl(expr1, expr2):如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2

select nvl(100,200);

coalesce(expr1, expr2, ...., expr_n))：返回参数中的第一个非空表达式（从左向右）

select coalesce(100,200,300);
select coalesce(null,null,300);

nullif(expr1, expr2)：如果两个参数相等，则返回NULL。否则返回第一个参数的值

select nullif(100,100);
select nullif(100,200);

case语法

将表达式和多个可能的值进行比较，当匹配时返回相应的结果
语法示例：

-- 方式一
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

示例：

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;

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;

select *,case sex when 1 then '男' when 0 then '女' end 性别 from test.ex_user;

select *,case  when sex = 1 then '男' when sex = 0 then '女' end 性别 from test.ex_user;

聚合函数

min_by和max_by

MAX_BY(expr1, expr2):返回expr2最大值所在行的 expr1 （求分组top1的简介函数）

--这里通过找到最大的user_id返回它的年龄
select max_by(age, user_id) from test.ex_user;

求每门课程成绩最高分的那个人

创建一个score.txt文件，添加如下内容：

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

创建一张表并创建名字、学科、分数的字段。

-- 建表语句
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"
);

然后我们导入数据。

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

doris中的写法

select
subject,max_by(name,score) as name
from score
group by subject

但是无法处理成绩并列的情况。

group_concat函数

求：每一个人有考试成绩的所有科目

select name,group_concat(subject,',') as all_subject from score group by name

语法示例：

VARCHAR GROUP_CONCAT([DISTINCT] VARCHAR 列名[, VARCHAR sep]
该函数是类似于 sum() 的聚合函数，group_concat 将结果集中的多行结果连接成一个字符串
-- group_concat对于收集的字段只能是string，varchar，char类型  
--当不指定分隔符的时候，默认使用 ','
VARCHAR ：代表GROUP_CONCAT函数返回值类型
[DISTINCT]：可选参数，针对需要拼接的列的值进行去重  
[, VARCHAR sep]：拼接成字符串的分隔符，默认是 ','

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;

简单示例：
首先建表并建立表数据。

--建表
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');

当收集的那一列，有值为null时，他会自动将null的值过滤掉。

select 
gender,
group_concat(name,',') as gc_name
from example 
group by gender;

select 
gender,
group_concat(DISTINCT cast(age as string)) as gc_age
from example 
group by gender;

collect_list，collect_set

collect_list用于将某列的所有值收集到一个数组中，包括重复值。
例如，按性别分组，收集每个性别对应的名字列表：

select gender, collect_list(name) as name_list
from test.example
group by gender;

collect_set用于将某列的不重复值收集到一个数组中。
例如，按性别分组，收集每个性别对应的不重复名字集合：

select gender, collect_set(name) as unique_name_set
from test.example
group by gender;

日期函数

获取当前时间

curdate，current_date，now，curtime，current_time，current_timestamp

select current_date(),curdate(), now(),curtime(),current_timestamp();

last_day

DATE last_day(DATETIME date)返回输入日期中月份的最后一天；
28(非闰年的二月份),
29(闰年的二月份),
30(四月，六月，九月，十一月),
31(一月，三月，五月，七月，八月，十月，十二月)
举例：给我返回这个月份中的最后一天的日期 年月日

select last_day('2000-03-03 01:00:00');

from_unixtime

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 HHss

unix_timestamp

这个方法是将日期生成时间搓，可以传三种方法：

UNIX_TIMESTAMP(), 
UNIX_TIMESTAMP(DATETIME date), 
UNIX_TIMESTAMP(DATETIME date, STRING fmt) -- 给一个日期，指定这个日期的格式
-- 将日期转换成时间戳，返回值是一个int类型

获取当前日期的时间戳

select unix_timestamp();

获取指定日期的时间戳

select unix_timestamp('2022-11-26 01:09:01');

给定一个特殊日期格式的时间戳，指定格式

select unix_timestamp('2022-11-26 01:09-01', '%Y-%m-%d %H:%i-%s');

to_date

DATE TO_DATE(DATETIME)返回 DATETIME 类型中的日期部分。

select to_date("2022-11-20 00:00:00");

extract

extract(unit FROM DATETIME)抽取提取DATETIME某个指定单位的值。
unit单位可以为year, month, day, hour, minute或者second。

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

语法：

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

示例：

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

VARCHAR DATE_FORMAT(DATETIME date, VARCHAR format)将日期类型按照format的类型转化为字符串

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

--返回字符串的长度
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

-- 向左边补齐
SELECT lpad("1", 5, "0");
# 00001
SELECT rpad("1", 5, "0");
# 10000
-- 向右边补齐
SELECT rpad('11', 5, '0');
# 11000

concat，concat_ws

concat(str1, str2, …, strN)
功能：将多个字符串 str1, str2, …, strN 连接成一个字符串。
参数：多个字符串。
返回值：连接后的字符串。

select concat("a", "b");
# ab
select concat("a", "b", "c");
# abc
-- concat中，如果有一个值为null，那么得到的结果就是null
select concat("a", null, "c");
# NULL

substr

--求子字符串，返回第一个参数描述的字符串中从start开始长度为len的部分字符串。
--首字母的下标为1。
select substr("Hello doris", 2, 1);
# e
select substr("Hello doris", 1, 2);
# He

ends_with，starts_with

ends_with(str, suffix)
功能：如果字符串 str 以指定的后缀 suffix 结尾，则返回 true，否则返回 false。
参数：
str：要检查的字符串。
suffix：要匹配的后缀字符串。
返回值：BOOLEAN 类型，true 或 false。如果任意参数为 NULL，则返回 NULL。

select ends_with("Hello doris", "doris");
# 1
select ends_with("Hello doris", "Hello");
# 0

starts_with(str, prefix)
功能：如果字符串 str 以指定的前缀 prefix 开头，则返回 true，否则返回 false。
参数：
str：要检查的字符串。
prefix：要匹配的前缀字符串。
返回值：BOOLEAN 类型，true 或 false。如果任意参数为 NULL，则返回 NULL。

select starts_with("hello world","hello");
# 1
select starts_with("hello world","world");
# 0

trim，ltrim，rtrim

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

BOOLEAN NULL_OR_EMPTY (VARCHAR str)如果字符串为空字符串或者NULL，返回true。否则，返回false。

select null_or_empty(null);
# 1
select null_or_empty("");
# 1
select null_or_empty("a");
# 0

BOOLEAN NOT_NULL_OR_EMPTY (VARCHAR str)如果字符串为空字符串或者NULL，返回false。否则，返回true。

select not_null_or_empty(null);
# 0
select not_null_or_empty("");
# 0
select not_null_or_empty("a");
# 1

replace

VARCHAR REPLACE (VARCHAR str, VARCHAR old, VARCHAR new)将str字符串中的old子串全部替换为new串

select replace("http://www.baidu.com:9090", "9090", "");
# http://www.baidu.com:

split_part

VARCHAR split_part(VARCHAR content, VARCHAR delimiter, INT field)根据分割符拆分字符串, 返回指定的分割部分(从1开始计数)。

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

VARCHAR money_format(Number)将数字按照货币格式输出，整数部分每隔3位用逗号分隔，小数部分保留2位。

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

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

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

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

数值类型 abs(数值类型 x)
-- 返回x的绝对值.
select abs(-2);
# 2
select abs(3.254655654);
# 3.254655654
select abs(-3254654236547654354654767);
# 3254654236547654354654767

pow

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

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函数

首先创建一个测试表。

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

创建一个json.txt测试数据。
数据如下图所示：

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": []}

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_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

用insert into 的方式导入一条

INSERT INTO test_json VALUES(7, '{"k1":"v1", "k2": 200}');

下面是查出的数据。

获取int类型K2的值。

SELECT get_json_int(json_string,'$.k2') from test_json

如果我们想获取中括号里面的字符串类型k1我们可以这样写：

SELECT get_json_string(get_json_string(json_string,'$.a1[0]'),'$.k1') from test_json

变成json（json_object函数）

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

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}

窗口函数

doris中的窗口函数和hive中的窗口函数的用法一样
首先，我们需要创建一个表 int_t，并插入一些数据以便进行测试。以下是创建表和插入数据的 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() 函数

RANK() 函数用于根据排序列的值进行排名，如果排序列的值相同，则会并列排名，后续的排名会跳过并列的排名数。

select x, y, rank() over(partition by x order by y) as rank from int_t;

解释：
按照 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() 函数

DENSE_RANK() 函数也用于根据排序列的值进行排名，如果排序列的值相同，则会并列排名，但后续的排名不会跳过并列的排名数。

select x, y, dense_rank() over(partition by x order by y) as rank from int_t;

解释：
按照 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() 函数

ROW_NUMBER() 函数用于按照分组排序要求，返回连续递增的整数，从 1 开始，不会有重复值，也不会有空缺值。

select x, y, row_number() over(partition by x order by y) as rank from int_t;

解释：
按照 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

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() 窗口函数

创建表并插入数据
首先，我们创建一个表并插入一些数据以便进行测试：

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

LAG() 函数
LAG() 函数用于获取当前行之前若干行的值。

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;

LEAD() 函数
LEAD() 函数用于获取当前行之后若干行的值。

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;