查询语法和内置函数

查询语法整体结构

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"]                 -- 将查询的结果导出到文件中

内置函数

条件函数

if

if(boolean condition, type valueTrue, type valueFalseOrNull)--如果表达式 condition 成立，返回结果 valueTrue；否则，返回结果 valueFalseOrNull--返回值类型：valueTrue 表达式结果的类型示例：SQLmysql> select  user_id, if(user_id = 1, "true", "false") as test_if from test;+---------+---------+| user_id | test_if |+---------+---------+| 1       | true    || 2       | false   |+---------+---------+

ifnull,nvl,coalesce,nullif

ifnull(expr1, expr2)--如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2nvl(expr1, expr2)--如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2coalesce(expr1, expr2, ...., expr_n))--返回参数中的第一个非空表达式（从左向右）nullif(expr1, expr2)-- 如果两个参数相等，则返回NULL。否则返回第一个参数的值mysql> select ifnull(1,0);+--------------+| ifnull(1, 0) |+--------------+|            1 |+--------------+mysql> select nvl(null,10);+------------------+| nvl(null,10)     |+------------------+|               10 |+------------------+mysql> select coalesce(NULL, "1111", "0000");+--------------------------------+| coalesce(NULL, "1111", "0000") |+--------------------------------+| 1111                           |+--------------------------------+mysql> select coalesce(NULL, NULL,NULL,"0000", NULL);+----------------------------------------+| coalesce(NULL, NULL,NULL,"0000", NULL) |+----------------------------------------+| 0000                                   |+----------------------------------------+mysql> select nullif(1,1);+--------------+| nullif(1, 1) |+--------------+|         NULL |+--------------+mysql> select nullif(1,0);+--------------+| nullif(1, 0) |+--------------+|            1 |+--------------+

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-- 将表达式和多个可能的值进行比较，当匹配时返回相应的结果mysql> select user_id, case user_id when 1 then "user_id = 1" when 2 then "user_id = 2" else "user_id not exist" end as test_case from test;+---------+-------------+| user_id | test_case   |+---------+-------------+| 1       | user_id = 1 || 2       | user_id = 2 || 3       | "user_id not exist" |+---------+-------------+ mysql> select user_id, case when user_id = 1 then "user_id = 1" when user_id = 2 then "user_id = 2" else "user_id not exist" end as test_case from test;+---------+-------------+| user_id | test_case   |+---------+-------------+| 1       | user_id = 1 || 2       | user_id = 2 |+---------+-------------+

聚合函数

min,max,sum,avg,count和mysql用法一致

min_by和max_by

MAX_BY(expr1, expr2)返回expr2最大值所在行的 expr1 （求分组top1的简介函数）MySQL > select * from tbl;+------+------+------+------+| k1   | k2   | k3   | k4   |+------+------+------+------+|    0 | 3    | 2    |  100 ||    1 | 2    | 3    |    4 ||    4 | 3    | 2    |    2 ||    3 | 4    | 2    |    1 |+------+------+------+------+MySQL > select max_by(k1, k4) from tbl;select max_by(k1, k4) from tbl;--取k4这个列中的最大值对应的k1这个列的值+--------------------+| max_by(`k1`, `k4`) |+--------------------+|                  0 |+--------------------+ -- 练习name   subject   score zss,chinese,99zss,math,89zss,English,79lss,chinese,88lss,math,88lss,English,22www,chinese,99www,math,45zll,chinese,23zll,math,88zll,English,80www,English,94-- 建表语句create table score(name varchar(50),subject varchar(50),score 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  subject,max_by(name,score) as namefrom scoregroup by subject+---------+------+| subject | name |+---------+------+| English | www  || math    | lss  || chinese | www  |+---------+------+

group_concat

VARCHAR GROUP_CONCAT([DISTINCT] VARCHAR 列名[, VARCHAR sep]该函数是类似于 sum() 的聚合函数，group_concat 将结果集中的多行结果连接成一个字符串-- group_concat对于收集的字段只能是string，varchar，char类型  --当不指定分隔符的时候，默认使用 ","VARCHAR ：代表GROUP_CONCAT函数返回值类型[DISTINCT]：可选参数，针对需要拼接的列的值进行去重  [, VARCHAR sep]：拼接成字符串的分隔符，默认是 ","--建表create table example(id int,name varchar(50),age int,gender string,is_marry boolean,marry_date date,marry_datetime datetime)engine = olapdistributed by hash(id) buckets 3;--插入数据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_namefrom example group by gender;+--------+---------------+| gender | gc_name       |+--------+---------------+| female | zl,lss        || male   | zss,ww,tq,mly |+--------+---------------+select gender,group_concat(DISTINCT cast(age as string)) as gc_agefrom example group by gender;+--------+------------+| gender | gc_age     |+--------+------------+| female | 48, 28     || male   | 58, 38, 18 |+--------+------------+-- 求：每一个人有考试成绩的所有科目selectname，group_concat(subject,",")  as all_subjectfrom scoregroup by name

collect_list，collect_set (1.2版本上线)

ARRAY collect_list(expr)--返回一个包含 expr 中所有元素(不包括NULL)的数组，数组中元素顺序是不确定的。ARRAY collect_set(expr)--返回一个包含 expr 中所有去重后元素(不包括NULL)的数组，数组中元素顺序是不确定的。

日期函数

获取当前时间

-- curdate,current_date，now，curtime,current_time，current_timestampselect current_date();+----------------+| current_date() |+----------------+| 2022-11-25     |+----------------+select curdate();+------------+| curdate()  |+------------+| 2022-11-25 |+------------+ select now();+---------------------+| now()               |+---------------------+| 2022-11-25 00:55:15 |+---------------------+select curtime();+-----------+| curtime() |+-----------+| 00:42:13  |+-----------+select current_timestamp();+---------------------+| current_timestamp() |+---------------------+| 2022-11-25 00:42:30 |+---------------------+

last_day(1.2版本上线)

DATE last_day(DATETIME date) -- 返回输入日期中月份的最后一天； --"28"(非闰年的二月份), --"29"(闰年的二月份), --"30"(四月，六月，九月，十一月), --"31"(一月，三月，五月，七月，八月，十月，十二月)select last_day("2000-03-03 01:00:00"); -- 给我返回这个月份中的最后一天的日期  年月日ERROR 1105 (HY000): errCode = 2, detailMessage = No matching function with signature: last_day(varchar(-1)).

from_unixtime

DATETIME FROM_UNIXTIME(INT unix_timestamp[, VARCHAR string_format])-- 将 unix 时间戳转化为对应的 time 格式，返回的格式由 string_format 指定--支持date_format中的format格式，默认为 %Y-%m-%d %H:%i:%s-- 正常使用的三种格式yyyyMMddyyyy-MM-ddyyyy-MM-dd HH:mm:ss示例：SQLmysql> select from_unixtime(1196440219);  -- 时区+---------------------------+| from_unixtime(1196440219) |+---------------------------+| 2007-12-01 00:30:19       |+---------------------------+mysql> select from_unixtime(1196440219, "yyyy-MM-dd HH:mm:ss");+--------------------------------------------------+| from_unixtime(1196440219, "yyyy-MM-dd HH:mm:ss") |+--------------------------------------------------+| 2007-12-01 00:30:19                              |+--------------------------------------------------+mysql> select from_unixtime(1196440219, "%Y-%m-%d");+-----------------------------------------+| from_unixtime(1196440219, "%Y-%m-%d") |+-----------------------------------------+| 2007-12-01                              |+-----------------------------------------+

unix_timestamp

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(DATETIME date), UNIX_TIMESTAMP(DATETIME date, STRING fmt) -- 给一个日期，指定这个日期的格式-- 将日期转换成时间戳，返回值是一个int类型-- 获取当前日期的时间戳select unix_timestamp();+------------------+| unix_timestamp() |+------------------+|       1669309722 |+------------------+-- 获取指定日期的时间戳select unix_timestamp("2022-11-26 01:09:01");+---------------------------------------+| unix_timestamp("2022-11-26 01:09:01") |+---------------------------------------+|                            1669396141 |+---------------------------------------+-- 给定一个特殊日期格式的时间戳，指定格式select unix_timestamp("2022-11-26 01:09-01", "%Y-%m-%d %H:%i-%s");+------------------------------------------------------------+| unix_timestamp("2022-11-26 01:09-01", "%Y-%m-%d %H:%i-%s") |+------------------------------------------------------------+|                                                 1669396141 |+------------------------------------------------------------+

to_date

DATE TO_DATE(DATETIME)--返回 DATETIME 类型中的日期部分。示例：SQLselect to_date("2022-11-20 00:00:00");     +--------------------------------+| to_date("2022-11-20 00:00:00") |+--------------------------------+| 2022-11-20                     |+--------------------------------+

extract

extract(unit FROM DATETIME)   --抽取-- 提取DATETIME某个指定单位的值。--unit单位可以为year, month, day, hour, minute或者second示例：SQLselect 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;+------+-------+------+------+--------+--------+| year | month | day  | hour | minute | second |+------+-------+------+------+--------+--------+| 2022 |     9 |   22 |   17 |      1 |     30 |+------+-------+------+------+--------+--------+

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, SECONDselect date_add("2010-11-30 23:59:59", INTERVAL 2 DAY);+-------------------------------------------------+| date_add("2010-11-30 23:59:59", INTERVAL 2 DAY) |+-------------------------------------------------+| 2010-12-02 23:59:59                             |+-------------------------------------------------+--传一个负数进去也就等同于date_subselect date_add("2010-11-30 23:59:59", INTERVAL -2 DAY);+--------------------------------------------------+| date_add("2010-11-30 23:59:59", INTERVAL -2 DAY) |+--------------------------------------------------+| 2010-11-28 23:59:59                              |+--------------------------------------------------+mysql> select datediff("2022-11-27 22:51:56","2022-11-24 22:50:56");+--------------------------------------------------------+| datediff("2022-11-27 22:51:56", "2022-11-24 22:50:56") |+--------------------------------------------------------+|                                                      3 |+--------------------------------------------------------+

date_format

VARCHAR DATE_FORMAT(DATETIME date, VARCHAR format)--将日期类型按照format的类型转化为字符串示例：SQLselect date_format("2007-10-04 22:23:00", "%H:%i:%s");+------------------------------------------------+| date_format("2007-10-04 22:23:00", "%H:%i:%s") |+------------------------------------------------+| 22:23:00                                       |+------------------------------------------------+select date_format("2007-10-04 22:23:00", "yyyy-MM-dd");+------------------------------------------------+| date_format("2007-10-04 22:23:00", "%Y-%m-%d") |+------------------------------------------------+| 2007-10-04                                     |+------------------------------------------------+

字符串函数

length，lower，upper，reverse获取到字符串的长度，对字符串转大小写和字符串的反转

lpad，rpad

VARCHAR rpad(VARCHAR str, INT len, VARCHAR pad)VARCHAR lpad(VARCHAR str, INT len, VARCHAR pad)-- 返回 str 中长度为 len（从首字母开始算起）的字符串。--如果 len 大于 str 的长度，则在 str 的后面不断补充 pad  字符，--直到该字符串的长度达到 len 为止。如果 len 小于 str 的长度，--该函数相当于截断 str 字符串，只返回长度为 len  的字符串。--len 指的是字符长度而不是字节长度。示例：SQL-- 向左边补齐SELECT lpad("1", 5, "hellohello");+---------------------+| lpad("1", 5, "0") |+---------------------+| 00001             |+---------------------+-- 向右边补齐SELECT rpad("11", 5, "0");+---------------------+| rpad("11", 5, "0")  |+---------------------+| 11000               |+---------------------+

concat，concat_ws

select concat("a", "b");+------------------+| concat("a", "b") |+------------------+| ab               |+------------------+select concat("a", "b", "c");+-----------------------+| concat("a", "b", "c") |+-----------------------+| abc                   |+-----------------------+-- concat中，如果有一个值为null，那么得到的结果就是nullmysql> select concat("a", null, "c");+------------------------+| concat("a", NULL, "c") |+------------------------+| NULL                   |+------------------------+--使用第一个参数 sep 作为连接符--将第二个参数以及后续所有参数(或ARRAY中的所有字符串)拼接成一个字符串。-- 如果分隔符是 NULL，返回 NULL。 concat_ws函数不会跳过空字符串，会跳过 NULL 值。mysql> select concat_ws("_", "a", "b");+----------------------------+| concat_ws("_", "a", "b")   |+----------------------------+| a_b                        |+----------------------------+mysql> select concat_ws(NULL, "d", "is");+----------------------------+| concat_ws(NULL, "d", "is") |+----------------------------+| NULL                       |+----------------------------+

substr

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

ends_with，starts_with

BOOLEAN ENDS_WITH (VARCHAR str, VARCHAR suffix)--如果字符串以指定后缀结尾，返回true。否则，返回false。--任意参数为NULL，返回NULL。BOOLEAN STARTS_WITH (VARCHAR str, VARCHAR prefix)--如果字符串以指定前缀开头，返回true。否则，返回false。--任意参数为NULL，返回NULL。示例：SQLselect ends_with("Hello doris", "doris");+-----------------------------------+| ends_with("Hello doris", "doris") |+-----------------------------------+|                                 1 | +-----------------------------------+select ends_with("Hello doris", "Hello");+-----------------------------------+| ends_with("Hello doris", "Hello") |+-----------------------------------+|                                 0 | +-----------------------------------+select starts_with("hello world","hello");+-------------------------------------+| starts_with("hello world", "hello") |+-------------------------------------+|                                   1 |+-------------------------------------+select starts_with("hello world","world");+-------------------------------------+| starts_with("hello world", "world") |+-------------------------------------+|                                   0 |+-------------------------------------+

trim，ltrim，rtrim

VARCHAR trim(VARCHAR str)-- 将参数 str 中左侧和右侧开始部分连续出现的空格去掉mysql> SELECT trim("   ab d   ") str;+------+| str  |+------+| ab d |+------+VARCHAR ltrim(VARCHAR str)-- 将参数 str 中从左侧部分开始部分连续出现的空格去掉mysql> SELECT ltrim("   ab d") str;+------+| str  |+------+| ab d |+------+VARCHAR rtrim(VARCHAR str)--将参数 str 中从右侧部分开始部分连续出现的空格去掉mysql> SELECT rtrim("ab d   ") str;+------+| str  |+------+| ab d |+------+

null_or_empty，not_null_or_empty

BOOLEAN NULL_OR_EMPTY (VARCHAR str)-- 如果字符串为空字符串或者NULL，返回true。否则，返回false。select null_or_empty(null);+---------------------+| null_or_empty(NULL) |+---------------------+|                   1 |+---------------------+select null_or_empty("");+-------------------+| null_or_empty("") |+-------------------+|                 1 |+-------------------+select null_or_empty("a");+--------------------+| null_or_empty("a") |+--------------------+|                  0 |+--------------------+BOOLEAN NOT_NULL_OR_EMPTY (VARCHAR str)如果字符串为空字符串或者NULL，返回false。否则，返回true。select not_null_or_empty(null);+-------------------------+| not_null_or_empty(NULL) |+-------------------------+|                       0 |+-------------------------+select not_null_or_empty("");+-----------------------+| not_null_or_empty("") |+-----------------------+|                     0 |+-----------------------+select not_null_or_empty("a");+------------------------+| not_null_or_empty("a") |+------------------------+|                      1 |+------------------------+

replace

VARCHAR REPLACE (VARCHAR str, VARCHAR old, VARCHAR new)-- 将str字符串中的old子串全部替换为new串mysql> select replace("http://www.baidu.com:9090", "9090", "");+------------------------------------------------------+| replace("http://www.baidu.com:9090", "9090", "") |+------------------------------------------------------+| http://www.baidu.com:                                |+------------------------------------------------------+

split_part

VARCHAR split_part(VARCHAR content, VARCHAR delimiter, INT field)-- 根据分割符拆分字符串, 返回指定的分割部分(从一开始计数)。mysql> select split_part("hello world", " ", 1);+----------------------------------+| split_part("hello world", " ", 1) |+----------------------------------+| hello                            |+----------------------------------+mysql> select split_part("hello world", " ", 2);+----------------------------------+| split_part("hello world", " ", 2) |+----------------------------------+| world                             |+----------------------------------+mysql> select split_part("2019年7月8号", "月", 1);+-----------------------------------------+| split_part("2019年7月8号", "月", 1)     |+-----------------------------------------+| 2019年7                                 |+-----------------------------------------+mysql> select split_part("abca", "a", 1);+----------------------------+| split_part("abca", "a", 1) |+----------------------------+|                            |+----------------------------+

money_format

VARCHAR money_format(Number)-- 将数字按照货币格式输出，整数部分每隔3位用逗号分隔，小数部分保留2位mysql> select money_format(17014116);+------------------------+| money_format(17014116) |+------------------------+| 17,014,116.00          |+------------------------+mysql> select money_format(1123.456);+------------------------+| money_format(1123.456) |+------------------------+| 1,123.46               |+------------------------+mysql> select money_format(1123.4);+----------------------+| money_format(1123.4) |+----------------------+| 1,123.40             |+----------------------+

数学函数

ceil和floor

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

round

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

truncate

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

数值类型 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

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

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

JSON函数

CREATE TABLE test_json (  id INT,  json_string String)DUPLICATE KEY(id)DISTRIBUTED BY HASH(id) BUCKETS 3PROPERTIES("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}");5.1.7.1get_json_double，get_json_int，get_json_stringDOUBLE 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

VARCHAR json_object(VARCHAR,...)-- 生成一个包含指定Key-Value对的json object, -- 传入的参数是key,value对，且key不能为nullMySQL> 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}              |+---------------------------------+

窗口函数

ROW_NUMBER()，DENSE_RANK()，RANK()

-- 测试rank打行号，名次相同会并列排名，比如两个第一名，就是1 1 然后第二名会显示3select x, y, rank() over(partition by x order by y) as rank from int_t;| x  | y    | rank     ||----|------|----------|| 1  | 1    | 1        || 1  | 2    | 2        || 1  | 2    | 2        || 2  | 1    | 1        || 2  | 2    | 2        || 2  | 3    | 3        || 3  | 1    | 1        || 3  | 1    | 1        || 3  | 2    | 3        |-- 测试dense_rank(),名词相同会并列排名，比如两个第一名，就是1 1 然后第二名会显示2select x, y, dense_rank() over(partition by x order by y) as rank from int_t; | x  | y    | rank     | |----|------|----------| | 1  | 1    | 1        | | 1  | 2    | 2        | | 1  | 2    | 2        | | 2  | 1    | 1        | | 2  | 2    | 2        | | 2  | 3    | 3        | | 3  | 1    | 1        | | 3  | 1    | 1        | | 3  | 2    | 2        |  -- 测试ROW_NUMBER() 按照分组排序要求，返回的编号依次底层，1 2 3 4 5 ， -- 不会有重复值，也不会有空缺值，就是连续递增的整数，从1 开始 select x, y, row_number() over(partition by x order by y) as rank from int_t;| x | y    | rank     ||---|------|----------|| 1 | 1    | 1        || 1 | 2    | 2        || 1 | 2    | 3        || 2 | 1    | 1        || 2 | 2    | 2        || 2 | 3    | 3        || 3 | 1    | 1        || 3 | 1    | 2        || 3 | 2    | 3        |-- 数据孙悟空,语文,87孙悟空,数学,95娜娜,英语,84宋宋,语文,64孙悟空,英语,68宋宋,英语,84婷婷,语文,65娜娜,语文,94宋宋,数学,86婷婷,数学,85娜娜,数学,56婷婷,英语,78-- 建表语句create table stu(name varchar(50),subject varchar(50),score double)DUPLICATE KEY(name)DISTRIBUTED BY HASH(name) BUCKETS 1;-- 通过本地文件的方式导入数据curl \ -u root: \ -H "label:num_test" \ -H "column_separator:," \ -T /root/data/stu.txt \http://doitedu01:8040/api/test/stu/_stream_load

需求：【相同分数并列(假设第一名有两个，排名就是并列第一，然后第三名从2开始)】1.按照分数降序排序，求每个学科中每个人的名次

2.按照每个人的总分进行升序排列，得到每个人总分名次的名次

【相同分数并列(假设第一名有两个，排名就是并列第一，然后第三名从3开始)】3.按照学科进行升序排列，得到每个人的每个学科的名次

4.按照每个人的总分进行升序排列，得到每个人总分名次的名次

【相同分数并列(假设第一名有两个，排名就是并列第一，就再单独比语文的成绩，然后数学，最后英语，分数全部一样，按照学生名字的字典顺序，在前的为第一)】

5.按照每个人的总分进行升序排列，得到每个人总分名次的名次

-- 1.按照学科进行升序排列，得到每个人的每个学科的名次 select name,subject,score,dense_rank() over(partition by subject order by score desc) as rankfrom stu+-----------+---------+-------+------+| name      | subject | score | rank |+-----------+---------+-------+------+| 孙悟空    | 数学    |    95 |    1 || 宋宋      | 数学    |    86 |    2 || 婷婷      | 数学    |    85 |    3 || 娜娜      | 数学    |    56 |    4 || 娜娜      | 英语    |    84 |    1 || 宋宋      | 英语    |    84 |    1 || 婷婷      | 英语    |    78 |    2 || 孙悟空    | 英语    |    68 |    3 || 娜娜      | 语文    |    94 |    1 || 孙悟空    | 语文    |    87 |    2 || 婷婷      | 语文    |    65 |    3 || 宋宋      | 语文    |    64 |    4 |+-----------+---------+-------+------+-- 2.按照每个人的总分进行升序排列，得到每个人总分名次的名次selectname,sum_score,-- 因为是整体按照学生的总分进行求名次，所有学生为1组，就不需要分组了dense_rank() over(order by sum_score desc) as rankfrom (    select     name,sum(score) as sum_score    from stu    group by name) as t ;+-----------+-----------+------+| name      | sum_score | rank |+-----------+-----------+------+| 孙悟空    |       250 |    1 || 宋宋      |       234 |    2 || 娜娜      |       234 |    2 || 婷婷      |       228 |    3 |+-----------+-----------+------+【相同分数并列(假设第一名有两个，排名就是并列第一，然后第三名从3开始)】-- 3.按照学科进行升序排列，得到每个人的每个学科的名次 select name,subject,score,rank() over(partition by subject order by score desc) as rankfrom stu+-----------+---------+-------+------+| name      | subject | score | rank |+-----------+---------+-------+------+| 孙悟空    | 数学    |    95 |    1 || 宋宋      | 数学    |    86 |    2 || 婷婷      | 数学    |    85 |    3 || 娜娜      | 数学    |    56 |    4 || 娜娜      | 英语    |    84 |    1 || 宋宋      | 英语    |    84 |    1 || 婷婷      | 英语    |    78 |    3 || 孙悟空    | 英语    |    68 |    4 || 娜娜      | 语文    |    94 |    1 || 孙悟空    | 语文    |    87 |    2 || 婷婷      | 语文    |    65 |    3 || 宋宋      | 语文    |    64 |    4 |+-----------+---------+-------+------+-- 4.按照每个人的总分进行升序排列，得到每个人总分名次的名次selectname,sum_score,-- 因为是整体按照学生的总分进行求名次，所有学生为1组，就不需要分组了rank() over(order by sum_score desc) as rankfrom (    select     name,sum(score) as sum_score    from stu    group by name) as t ;+-----------+-----------+------+| name      | sum_score | rank |+-----------+-----------+------+| 孙悟空    |       250 |    1 || 宋宋      |       234 |    2 || 娜娜      |       234 |    2 || 婷婷      |       228 |    4 |+-----------+-----------+------+【相同分数并列(假设第一名有两个，排名就是并列第一，就再单独比语文的成绩，然后数学，最后英语，分数全部一样，按照学生名字的字典顺序，在前的为第一)】-- 5.按照每个人的总分进行升序排列，得到每个人总分名次的名次--方案1：利用窗口函数来列转行 select  name,subject,score as math_score,english_score,chinese_score,sum_score, row_number()over(order by sum_score desc ,chinese_score desc ,score desc ,english_score desc,name asc) as num from  (        select          name,subject,score,        lead(score,1,0)over(partition by name order by subject) as english_score,        lead(score,2,0)over(partition by name order by subject) as chinese_score,         sum(score)over(partition by name) as sum_score,        row_number()over(partition by name) as num        from stu  ) as tmp where num = 1  -- 方案2：利用if判断来列转行  select  name,chinese_score,match_score,english_score,sum_score, row_number()over(order by sum_score desc ,chinese_score desc ,match_score desc ,english_score desc,name asc) as num from  (    select     name,    sum(chinese_score) as chinese_score,    sum(match_score) as match_score,    sum(english_score) as english_score,    sum(chinese_score) + sum(match_score) + sum(english_score) as sum_score    from     (        select  name,subject,        if(subject = "语文",score,0) as chinese_score,        if(subject = "数学",score,0) as match_score,        if(subject = "英语",score,0) as english_score        from stu     )as t     group by name ) as t1+-----------+---------+------------+---------------+---------------+-----------+------+| name      | subject | math_score | english_score | chinese_score | sum_score | num  |+-----------+---------+------------+---------------+---------------+-----------+------+| 孙悟空    | 数学    |         95 |            68 |            87 |       250 |    1 || 娜娜      | 数学    |         56 |            84 |            94 |       234 |    2 || 宋宋      | 数学    |         86 |            84 |            64 |       234 |    3 || 婷婷      | 数学    |         85 |            78 |            65 |       228 |    4 |+-----------+---------+------------+---------------+---------------+-----------+------+

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 precedingcurrent row1 following1 PRECEDING rows between unbounded preceding and current row   --指在当前窗口中第一行到当前行的范围rows between unbounded preceding and 1 following   --指在当前窗口中第一行到当前行下一行的范围 rows between unbounded preceding and 1 PRECEDING   --指在当前窗口中第一行到当前行前一行的范围

LEAD() ，LAG()

-- LAG() 方法用来计算当前行向前数若干行的值。LAG(expr, offset, default) OVER (partition_by_clause order_by_clause)-- LEAD() 方法用来计算当前行向后数若干行的值。LEAD(expr, offset, default]) OVER (partition_by_clause order_by_clause)

漏斗模型分析函数window_funnel

业务目标、到达路径，路径步骤、步骤人数，步骤之间的相对转换率和绝对转换率每一种业务都有他的核心任务和流程，而流程的每一个步骤，都可能有用户流失。所以如果把每一个步骤及其对应的数据（如UV）拼接起来，就会形成一个上大下小的漏斗形态，这就是漏斗模型。

-- 准备数据uid  event_id   event_action  event_timeu001,e1,view_detail_page,2022-11-01 01:10:21u001,e2,add_bag_page,2022-11-01 01:11:13u001,e3,collect_goods_page,2022-11-01 02:07:11u002,e3,collect_goods_page,2022-11-01 01:10:21u002,e4,order_detail_page,2022-11-01 01:11:13u002,e5,pay_detail_page,2022-11-01 02:07:11u002,e6,click_adver_page,2022-11-01 13:07:23u002,e7,home_page,2022-11-01 08:18:12u002,e8,list_detail_page,2022-11-01 23:34:29u002,e1,view_detail_page,2022-11-01 11:25:32u002,e2,add_bag_page,2022-11-01 12:41:21u002,e3,collect_goods_page,2022-11-01 16:21:15u002,e4,order_detail_page,2022-11-01 21:41:12u003,e5,pay_detail_page,2022-11-01 01:10:21u003,e6,click_adver_page,2022-11-01 01:11:13u003,e7,home_page,2022-11-01 02:07:11u001,e4,order_detail_page,2022-11-01 13:07:23u001,e5,pay_detail_page,2022-11-01 08:18:12u001,e6,click_adver_page,2022-11-01 23:34:29u001,e7,home_page,2022-11-01 11:25:32u001,e8,list_detail_page,2022-11-01 12:41:21u001,e1,view_detail_page,2022-11-01 16:21:15u001,e2,add_bag_page,2022-11-01 21:41:12u003,e8,list_detail_page,2022-11-01 13:07:23u003,e1,view_detail_page,2022-11-01 08:18:12u003,e2,add_bag_page,2022-11-01 23:34:29u003,e3,collect_goods_page,2022-11-01 11:25:32u003,e4,order_detail_page,2022-11-01 12:41:21u003,e5,pay_detail_page,2022-11-01 16:21:15u003,e6,click_adver_page,2022-11-01 21:41:12u004,e7,home_page,2022-11-01 01:10:21u004,e8,list_detail_page,2022-11-01 01:11:13u004,e1,view_detail_page,2022-11-01 02:07:11u004,e2,add_bag_page,2022-11-01 13:07:23u004,e3,collect_goods_page,2022-11-01 08:18:12u004,e4,order_detail_page,2022-11-01 23:34:29u004,e5,pay_detail_page,2022-11-01 11:25:32u004,e6,click_adver_page,2022-11-01 12:41:21u004,e7,home_page,2022-11-01 16:21:15u004,e8,list_detail_page,2022-11-01 21:41:12u005,e1,view_detail_page,2022-11-01 01:10:21u005,e2,add_bag_page,2022-11-01 01:11:13u005,e3,collect_goods_page,2022-11-01 02:07:11u005,e4,order_detail_page,2022-11-01 13:07:23u005,e5,pay_detail_page,2022-11-01 08:18:12u005,e6,click_adver_page,2022-11-01 23:34:29u005,e7,home_page,2022-11-01 11:25:32u005,e8,list_detail_page,2022-11-01 12:41:21u005,e1,view_detail_page,2022-11-01 16:21:15u005,e2,add_bag_page,2022-11-01 21:41:12u005,e3,collect_goods_page,2022-11-01 01:10:21u006,e4,order_detail_page,2022-11-01 01:11:13u006,e5,pay_detail_page,2022-11-01 02:07:11u006,e6,click_adver_page,2022-11-01 13:07:23u006,e7,home_page,2022-11-01 08:18:12u006,e8,list_detail_page,2022-11-01 23:34:29u006,e1,view_detail_page,2022-11-01 11:25:32u006,e2,add_bag_page,2022-11-01 12:41:21u006,e3,collect_goods_page,2022-11-01 16:21:15u006,e4,order_detail_page,2022-11-01 21:41:12u006,e5,pay_detail_page,2022-11-01 23:10:21u006,e6,click_adver_page,2022-11-01 01:11:13u007,e7,home_page,2022-11-01 02:07:11u007,e8,list_detail_page,2022-11-01 13:07:23u007,e1,view_detail_page,2022-11-01 08:18:12u007,e2,add_bag_page,2022-11-01 23:34:29u007,e3,collect_goods_page,2022-11-01 11:25:32u007,e4,order_detail_page,2022-11-01 12:41:21u007,e5,pay_detail_page,2022-11-01 16:21:15u007,e6,click_adver_page,2022-11-01 21:41:12u007,e7,home_page,2022-11-01 01:10:21u008,e8,list_detail_page,2022-11-01 01:11:13u008,e1,view_detail_page,2022-11-01 02:07:11u008,e2,add_bag_page,2022-11-01 13:07:23u008,e3,collect_goods_page,2022-11-01 08:18:12u008,e4,order_detail_page,2022-11-01 23:34:29u008,e5,pay_detail_page,2022-11-01 11:25:32u008,e6,click_adver_page,2022-11-01 12:41:21u008,e7,home_page,2022-11-01 16:21:15u008,e8,list_detail_page,2022-11-01 21:41:12u008,e1,view_detail_page,2022-11-01 01:10:21u009,e2,add_bag_page,2022-11-01 01:11:13u009,e3,collect_goods_page,2022-11-01 02:07:11u009,e4,order_detail_page,2022-11-01 13:07:23u009,e5,pay_detail_page,2022-11-01 08:18:12u009,e6,click_adver_page,2022-11-01 23:34:29u009,e7,home_page,2022-11-01 11:25:32u009,e8,list_detail_page,2022-11-01 12:41:21u009,e1,view_detail_page,2022-11-01 16:21:15u009,e2,add_bag_page,2022-11-01 21:41:12u009,e3,collect_goods_page,2022-11-01 01:10:21u010,e4,order_detail_page,2022-11-01 01:11:13u010,e5,pay_detail_page,2022-11-01 02:07:11u010,e6,click_adver_page,2022-11-01 13:07:23u010,e7,home_page,2022-11-01 08:18:12u010,e8,list_detail_page,2022-11-01 23:34:29u010,e5,pay_detail_page,2022-11-01 11:25:32u010,e6,click_adver_page,2022-11-01 12:41:21u010,e7,home_page,2022-11-01 16:21:15u010,e8,list_detail_page,2022-11-01 21:41:12-- 创建表drop table if exists event_info_log;create table event_info_log(uid varchar(20),event_id varchar(20),event_action varchar(20),event_time datetime)DUPLICATE KEY(uid)DISTRIBUTED BY HASH(uid) BUCKETS 1;-- 通过本地文件的方式导入数据curl \ -u root: \ -H "label:event_info_log" \ -H "column_separator:," \ -T /root/data/event_log.txt \ http://linux01:8040/api/test/event_info_log/_stream_load

封装、要素(时间范围，事件的排序时间依据，漏斗模型的事件链)

window_funnel(window, mode, timestamp_column, event1, event2, ... , eventN)-- 漏斗分析函数搜索滑动时间窗口内最大的发生的最大事件序列长度。-- window ：滑动时间窗口大小，单位为秒。-- mode  ：保留，目前只支持default。-- 相邻两个事件之间没有时间间隔要求，并且相邻两个事件中可以做其他的事件-- timestamp_column ：指定时间列，类型为DATETIME, 滑动窗口沿着此列工作。-- eventN ：表示事件的布尔表达式。select user_id,window_funnel(3600*24, "default", event_time, event_id="e1", event_id="e2" , event_id="e4", event_id="e5") as stepfrom event_info_log group by user_id+---------+------+| user_id | step |+---------+------+| u006    |    4 || u007    |    2 || u005    |    3 || u004    |    3 || u010    |    0 || u001    |    3 || u003    |    2 || u002    |    3 || u008    |    3 || u009    |    2 |+---------+------+-- 算每一层级的转换率select"购买转化漏斗" as funnel_name,sum(if(step >= 1 ,1,0)) as step1,sum(if(step >= 2 ,1,0)) as step2,sum(if(step >= 3 ,1,0)) as step3,sum(if(step >= 4 ,1,0)) as step4,round(sum(if(step >= 2 ,1,0))/sum(if(step >= 1 ,1,0)),2) as "step1->step2_radio",round(sum(if(step >= 3 ,1,0))/sum(if(step >= 2 ,1,0)),2) as "step2->step3_radio",round(sum(if(step >= 4 ,1,0))/sum(if(step >= 3 ,1,0)),2) as "step3->step4_radio"from(select user_id,window_funnel(3600*24, "default", report_date, event_id="e1", event_id="e2" , event_id="e4", event_id="e5") as stepfrom event_info_log where to_date(report_date) = "2022-11-01"and event_id in("e1","e4","e5","e2")group by user_id) as t1 -- res+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+| funnel_name        | step1 | step2 | step3 | step4 | step1->step2_radio | step2->step3_radio | step3->step4_radio |+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+| 购买转化漏斗       |     9 |     9 |     6 |     1 |                  1 |               0.67 |               0.17 |+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+