Doris(六) -- 查询语法和内置函数

查询语法和内置函数

查询语法整体结构

1. SELECT
2. [ALL | DISTINCT | DISTINCTROW ]            -- 对查询字段的结果是否需要去重，还是全部保留等参数
3. select_expr [, select_expr ...]            -- select的查询字段
4. [FROM table_references
5. [PARTITION partition_list]                 -- from 哪个库里面的那张表甚至哪一个(几个)分区
6. [WHERE where_condition]                    -- WHERE 查询
7. [GROUP BY {col_name | expr | position}     -- group by  聚合
8. [ASC | DESC], ... [WITH ROLLUP]]
9. [HAVING where_condition]                   -- having 针对聚合函数的再一次过滤
10. [ORDER BY {col_name | expr | position}     -- 对结果数据按照字段进行排序
11. [ASC | DESC], ...]                                       -- 排序规则
12. [LIMIT {[offset,] row_count | row_count OFFSET offset}]  -- 限制输出多少行内容
13. [INTO OUTFILE 'file_name']                 -- 将查询的结果导出到文件中

复制代码

内置函数

条件函数

if

1. if(boolean condition, type valueTrue, type valueFalseOrNull)
2. --如果表达式 condition 成立，返回结果 valueTrue；否则，返回结果 valueFalseOrNull
3. --返回值类型：valueTrue 表达式结果的类型
4. 示例：
5. SQL
6. mysql> select  user_id, if(user_id = 1, "true", "false") as test_if from test;
7. +---------+---------+
8. | user_id | test_if |
9. +---------+---------+
10. | 1       | true    |
11. | 2       | false   |
12. +---------+---------+

复制代码

ifnull,nvl,coalesce,nullif

1. ifnull(expr1, expr2)
2. --如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2
4. nvl(expr1, expr2)
5. --如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2
7. coalesce(expr1, expr2, ...., expr_n))
8. --返回参数中的第一个非空表达式（从左向右）
10. nullif(expr1, expr2)
11. -- 如果两个参数相等，则返回NULL。否则返回第一个参数的值
13. mysql> select ifnull(1,0);
14. +--------------+
15. | ifnull(1, 0) |
16. +--------------+
17. |            1 |
18. +--------------+
20. mysql> select nvl(null,10);
21. +------------------+
22. | nvl(null,10)     |
23. +------------------+
24. |               10 |
25. +------------------+
27. mysql> select coalesce(NULL, '1111', '0000');
28. +--------------------------------+
29. | coalesce(NULL, '1111', '0000') |
30. +--------------------------------+
31. | 1111                           |
32. +--------------------------------+
34. mysql> select coalesce(NULL, NULL,NULL,'0000', NULL);
35. +----------------------------------------+
36. | coalesce(NULL, NULL,NULL,'0000', NULL) |
37. +----------------------------------------+
38. | 0000                                   |
39. +----------------------------------------+
41. mysql> select nullif(1,1);
42. +--------------+
43. | nullif(1, 1) |
44. +--------------+
45. |         NULL |
46. +--------------+
48. mysql> select nullif(1,0);
49. +--------------+
50. | nullif(1, 0) |
51. +--------------+
52. |            1 |
53. +--------------+

复制代码

case

1. -- 方式一
2. CASE expression
3.     WHEN condition1 THEN result1
4.     [WHEN condition2 THEN result2]
5.     ...
6.     [WHEN conditionN THEN resultN]
7.     [ELSE result]
8. END
11. -- 方式二
12. CASE WHEN condition1 THEN result1
13.     [WHEN condition2 THEN result2]
14.     ...
15.     [WHEN conditionN THEN resultN]
16.     [ELSE result]
17. END
19. -- 将表达式和多个可能的值进行比较，当匹配时返回相应的结果
22. mysql> select user_id,
23. case user_id
24. when 1 then 'user_id = 1'
25. when 2 then 'user_id = 2'
26. else 'user_id not exist'
27. end as test_case
28. from test;
29. +---------+-------------+
30. | user_id | test_case   |
31. +---------+-------------+
32. | 1       | user_id = 1 |
33. | 2       | user_id = 2 |
34. | 3       | 'user_id not exist' |
35. +---------+-------------+
37. mysql> select user_id,
38. case
39. when user_id = 1 then 'user_id = 1'
40. when user_id = 2 then 'user_id = 2'
41. else 'user_id not exist'
42. end as test_case
43. from test;
44. +---------+-------------+
45. | user_id | test_case   |
46. +---------+-------------+
47. | 1       | user_id = 1 |
48. | 2       | user_id = 2 |
49. +---------+-------------+

复制代码

聚合函数

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

1. MAX_BY(expr1, expr2)
2. 返回expr2最大值所在行的 expr1 （求分组top1的简介函数）
4. MySQL > select * from tbl;
5. +------+------+------+------+
6. | k1   | k2   | k3   | k4   |
7. +------+------+------+------+
8. |    0 | 3    | 2    |  100 |
9. |    1 | 2    | 3    |    4 |
10. |    4 | 3    | 2    |    2 |
11. |    3 | 4    | 2    |    1 |
12. +------+------+------+------+
14. MySQL > select max_by(k1, k4) from tbl;
15. select max_by(k1, k4) from tbl;
16. --取k4这个列中的最大值对应的k1这个列的值
17. +--------------------+
18. | max_by(`k1`, `k4`) |
19. +--------------------+
20. |                  0 |
21. +--------------------+
24. -- 练习
25. name   subject   score
26. zss,chinese,99
27. zss,math,89
28. zss,English,79
29. lss,chinese,88
30. lss,math,88
31. lss,English,22
32. www,chinese,99
33. www,math,45
34. zll,chinese,23
35. zll,math,88
36. zll,English,80
37. www,English,94
39. -- 建表语句
40. create table score
41. (
42. name varchar(50),
43. subject varchar(50),
44. score double
45. )
46. DUPLICATE KEY(name)
47. DISTRIBUTED BY HASH(name) BUCKETS 1;
49. -- 通过本地文件的方式导入数据
50. curl \
51. -u root: \
52. -H "label:salary" \
53. -H "column_separator:," \
54. -T /root/data/salary.txt \
55. http://doitedu01:8040/api/test/salary/_stream_load
58. -- 求每门课程成绩最高分的那个人
59. select  
60. subject,max_by(name,score) as name
61. from score
62. group by subject
65. +---------+------+
66. | subject | name |
67. +---------+------+
68. | English | www  |
69. | math    | lss  |
70. | chinese | www  |
71. +---------+------+

复制代码

group_concat

1. VARCHAR GROUP_CONCAT([DISTINCT] VARCHAR 列名[, VARCHAR sep]
3. 该函数是类似于 sum() 的聚合函数，group_concat 将结果集中的多行结果连接成一个字符串
5. -- group_concat对于收集的字段只能是string，varchar，char类型  
6. --当不指定分隔符的时候，默认使用 ','
8. VARCHAR ：代表GROUP_CONCAT函数返回值类型
9. [DISTINCT]：可选参数，针对需要拼接的列的值进行去重  
10. [, VARCHAR sep]：拼接成字符串的分隔符，默认是 ','
13. --建表
14. create table example(
15. id int,
16. name varchar(50),
17. age int,
18. gender string,
19. is_marry boolean,
20. marry_date date,
21. marry_datetime datetime
22. )engine = olap
23. distributed by hash(id) buckets 3;
25. --插入数据
26. insert into example values \
27. (1,'zss',18,'male',0,null,null),\
28. (2,'lss',28,'female',1,'2022-01-01','2022-01-01 11:11:11'),\
29. (3,'ww',38,'male',1,'2022-02-01','2022-02-01 11:11:11'),\
30. (4,'zl',48,'female',0,null,null),\
31. (5,'tq',58,'male',1,'2022-03-01','2022-03-01 11:11:11'),\
32. (6,'mly',18,'male',1,'2022-04-01','2022-04-01 11:11:11'),\
33. (7,null,18,'male',1,'2022-05-01','2022-05-01 11:11:11');
35. --当收集的那一列，有值为null时，他会自动将null的值过滤掉
36. select
37. gender,
38. group_concat(name,',') as gc_name
39. from example
40. group by gender;
41. +--------+---------------+
42. | gender | gc_name       |
43. +--------+---------------+
44. | female | zl,lss        |
45. | male   | zss,ww,tq,mly |
46. +--------+---------------+
48. select
49. gender,
50. group_concat(DISTINCT cast(age as string)) as gc_age
51. from example
52. group by gender;
54. +--------+------------+
55. | gender | gc_age     |
56. +--------+------------+
57. | female | 48, 28     |
58. | male   | 58, 38, 18 |
59. +--------+------------+
62. -- 求：每一个人有考试成绩的所有科目
63. select
64. name，
65. group_concat(subject,',')  as all_subject
66. from score
67. group by name

复制代码

collect_list，collect_set (1.2版本上线)

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

复制代码

日期函数

获取当前时间

1. -- curdate,current_date，now，curtime,current_time，current_timestamp
2. select current_date();
3. +----------------+
4. | current_date() |
5. +----------------+
6. | 2022-11-25     |
7. +----------------+
9. select curdate();
10. +------------+
11. | curdate()  |
12. +------------+
13. | 2022-11-25 |
14. +------------+
16. select now();
17. +---------------------+
18. | now()               |
19. +---------------------+
20. | 2022-11-25 00:55:15 |
21. +---------------------+
23. select curtime();
24. +-----------+
25. | curtime() |
26. +-----------+
27. | 00:42:13  |
28. +-----------+
30. select current_timestamp();
31. +---------------------+
32. | current_timestamp() |
33. +---------------------+
34. | 2022-11-25 00:42:30 |
35. +---------------------+

复制代码

last_day(1.2版本上线)

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

复制代码

from_unixtime

1. DATETIME FROM_UNIXTIME(INT unix_timestamp[, VARCHAR string_format])
2. -- 将 unix 时间戳转化为对应的 time 格式，返回的格式由 string_format 指定
3. --支持date_format中的format格式，默认为 %Y-%m-%d %H:%i:%s
5. -- 正常使用的三种格式
6. yyyyMMdd
7. yyyy-MM-dd
8. yyyy-MM-dd HH:mm:ss
9. 示例：
10. SQL
11. mysql> select from_unixtime(1196440219);  -- 时区
12. +---------------------------+
13. | from_unixtime(1196440219) |
14. +---------------------------+
15. | 2007-12-01 00:30:19       |
16. +---------------------------+
18. mysql> select from_unixtime(1196440219, 'yyyy-MM-dd HH:mm:ss');
19. +--------------------------------------------------+
20. | from_unixtime(1196440219, 'yyyy-MM-dd HH:mm:ss') |
21. +--------------------------------------------------+
22. | 2007-12-01 00:30:19                              |
23. +--------------------------------------------------+
25. mysql> select from_unixtime(1196440219, '%Y-%m-%d');
26. +-----------------------------------------+
27. | from_unixtime(1196440219, '%Y-%m-%d') |
28. +-----------------------------------------+
29. | 2007-12-01                              |
30. +-----------------------------------------+

复制代码

unix_timestamp

1. UNIX_TIMESTAMP(),
2. UNIX_TIMESTAMP(DATETIME date),
3. UNIX_TIMESTAMP(DATETIME date, STRING fmt) -- 给一个日期，指定这个日期的格式
4. -- 将日期转换成时间戳，返回值是一个int类型
6. -- 获取当前日期的时间戳
7. select unix_timestamp();
8. +------------------+
9. | unix_timestamp() |
10. +------------------+
11. |       1669309722 |
12. +------------------+
14. -- 获取指定日期的时间戳
15. select unix_timestamp('2022-11-26 01:09:01');
16. +---------------------------------------+
17. | unix_timestamp('2022-11-26 01:09:01') |
18. +---------------------------------------+
19. |                            1669396141 |
20. +---------------------------------------+
22. -- 给定一个特殊日期格式的时间戳，指定格式
23. select unix_timestamp('2022-11-26 01:09-01', '%Y-%m-%d %H:%i-%s');
24. +------------------------------------------------------------+
25. | unix_timestamp('2022-11-26 01:09-01', '%Y-%m-%d %H:%i-%s') |
26. +------------------------------------------------------------+
27. |                                                 1669396141 |
28. +------------------------------------------------------------+

复制代码

to_date

1. DATE TO_DATE(DATETIME)
2. --返回 DATETIME 类型中的日期部分。
3. 示例：
4. SQL
5. select to_date("2022-11-20 00:00:00");     
6. +--------------------------------+
7. | to_date('2022-11-20 00:00:00') |
8. +--------------------------------+
9. | 2022-11-20                     |
10. +--------------------------------+

复制代码

extract

1. extract(unit FROM DATETIME)   --抽取
2. -- 提取DATETIME某个指定单位的值。
3. --unit单位可以为year, month, day, hour, minute或者second
4. 示例：
5. SQL
6. select
7. extract(year from '2022-09-22 17:01:30') as year,
8. extract(month from '2022-09-22 17:01:30') as month,
9. extract(day from '2022-09-22 17:01:30') as day,
10. extract(hour from '2022-09-22 17:01:30') as hour,
11. extract(minute from '2022-09-22 17:01:30') as minute,
12. extract(second from '2022-09-22 17:01:30') as second;
14. +------+-------+------+------+--------+--------+
15. | year | month | day  | hour | minute | second |
16. +------+-------+------+------+--------+--------+
17. | 2022 |     9 |   22 |   17 |      1 |     30 |
18. +------+-------+------+------+--------+--------+

复制代码

date_add，date_sub,datediff

1. DATE_ADD(DATETIME date,INTERVAL expr type)
3. DATE_SUB(DATETIME date,INTERVAL expr type)
5. DATEDIFF(DATETIME expr1,DATETIME expr2)
6. -- 计算两个日期相差多少天，结果精确到天。
8. -- 向日期添加指定的时间间隔。
9. -- date 参数是合法的日期表达式。
10. -- expr 参数是您希望添加的时间间隔。
11. -- type 参数可以是下列值：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
15. select date_add('2010-11-30 23:59:59', INTERVAL 2 DAY);
16. +-------------------------------------------------+
17. | date_add('2010-11-30 23:59:59', INTERVAL 2 DAY) |
18. +-------------------------------------------------+
19. | 2010-12-02 23:59:59                             |
20. +-------------------------------------------------+
23. --传一个负数进去也就等同于date_sub
24. select date_add('2010-11-30 23:59:59', INTERVAL -2 DAY);
25. +--------------------------------------------------+
26. | date_add('2010-11-30 23:59:59', INTERVAL -2 DAY) |
27. +--------------------------------------------------+
28. | 2010-11-28 23:59:59                              |
29. +--------------------------------------------------+
32. mysql> select datediff('2022-11-27 22:51:56','2022-11-24 22:50:56');
33. +--------------------------------------------------------+
34. | datediff('2022-11-27 22:51:56', '2022-11-24 22:50:56') |
35. +--------------------------------------------------------+
36. |                                                      3 |
37. +--------------------------------------------------------+

复制代码

date_format

1. VARCHAR DATE_FORMAT(DATETIME date, VARCHAR format)
2. --将日期类型按照format的类型转化为字符串
3. 示例：
4. SQL
5. select date_format('2007-10-04 22:23:00', '%H:%i:%s');
6. +------------------------------------------------+
7. | date_format('2007-10-04 22:23:00', '%H:%i:%s') |
8. +------------------------------------------------+
9. | 22:23:00                                       |
10. +------------------------------------------------+
12. select date_format('2007-10-04 22:23:00', 'yyyy-MM-dd');
13. +------------------------------------------------+
14. | date_format('2007-10-04 22:23:00', '%Y-%m-%d') |
15. +------------------------------------------------+
16. | 2007-10-04                                     |
17. +------------------------------------------------+

复制代码

字符串函数

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

1. VARCHAR rpad(VARCHAR str, INT len, VARCHAR pad)
3. VARCHAR lpad(VARCHAR str, INT len, VARCHAR pad)
5. -- 返回 str 中长度为 len（从首字母开始算起）的字符串。
6. --如果 len 大于 str 的长度，则在 str 的后面不断补充 pad  字符，
7. --直到该字符串的长度达到 len 为止。如果 len 小于 str 的长度，
8. --该函数相当于截断 str 字符串，只返回长度为 len  的字符串。
9. --len 指的是字符长度而不是字节长度。
10. 示例：
11. SQL
12. -- 向左边补齐
13. SELECT lpad("1", 5, "hellohello");
14. +---------------------+
15. | lpad("1", 5, "0") |
16. +---------------------+
17. | 00001             |
18. +---------------------+
20. -- 向右边补齐
21. SELECT rpad('11', 5, '0');
22. +---------------------+
23. | rpad('11', 5, '0')  |
24. +---------------------+
25. | 11000               |
26. +---------------------+

复制代码

concat，concat_ws

1. select concat("a", "b");
2. +------------------+
3. | concat('a', 'b') |
4. +------------------+
5. | ab               |
6. +------------------+
8. select concat("a", "b", "c");
9. +-----------------------+
10. | concat('a', 'b', 'c') |
11. +-----------------------+
12. | abc                   |
13. +-----------------------+
15. -- concat中，如果有一个值为null，那么得到的结果就是null
16. mysql> select concat("a", null, "c");
17. +------------------------+
18. | concat('a', NULL, 'c') |
19. +------------------------+
20. | NULL                   |
21. +------------------------+
24. --使用第一个参数 sep 作为连接符
25. --将第二个参数以及后续所有参数(或ARRAY中的所有字符串)拼接成一个字符串。
26. -- 如果分隔符是 NULL，返回 NULL。 concat_ws函数不会跳过空字符串，会跳过 NULL 值。
27. mysql> select concat_ws("_", "a", "b");
28. +----------------------------+
29. | concat_ws("_", "a", "b")   |
30. +----------------------------+
31. | a_b                        |
32. +----------------------------+
34. mysql> select concat_ws(NULL, "d", "is");
35. +----------------------------+
36. | concat_ws(NULL, 'd', 'is') |
37. +----------------------------+
38. | NULL                       |
39. +----------------------------+

复制代码

substr

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

复制代码

ends_with，starts_with

1. BOOLEAN ENDS_WITH (VARCHAR str, VARCHAR suffix)
2. --如果字符串以指定后缀结尾，返回true。否则，返回false。
3. --任意参数为NULL，返回NULL。
5. BOOLEAN STARTS_WITH (VARCHAR str, VARCHAR prefix)
6. --如果字符串以指定前缀开头，返回true。否则，返回false。
7. --任意参数为NULL，返回NULL。
8. 示例：
9. SQL
10. select ends_with("Hello doris", "doris");
11. +-----------------------------------+
12. | ends_with('Hello doris', 'doris') |
13. +-----------------------------------+
14. |                                 1 |
15. +-----------------------------------+
17. select ends_with("Hello doris", "Hello");
18. +-----------------------------------+
19. | ends_with('Hello doris', 'Hello') |
20. +-----------------------------------+
21. |                                 0 |
22. +-----------------------------------+
25. select starts_with("hello world","hello");
26. +-------------------------------------+
27. | starts_with('hello world', 'hello') |
28. +-------------------------------------+
29. |                                   1 |
30. +-------------------------------------+
32. select starts_with("hello world","world");
33. +-------------------------------------+
34. | starts_with('hello world', 'world') |
35. +-------------------------------------+
36. |                                   0 |
37. +-------------------------------------+

复制代码

trim，ltrim，rtrim

1. VARCHAR trim(VARCHAR str)
2. -- 将参数 str 中左侧和右侧开始部分连续出现的空格去掉
3. mysql> SELECT trim('   ab d   ') str;
4. +------+
5. | str  |
6. +------+
7. | ab d |
8. +------+
10. VARCHAR ltrim(VARCHAR str)
11. -- 将参数 str 中从左侧部分开始部分连续出现的空格去掉
12. mysql> SELECT ltrim('   ab d') str;
13. +------+
14. | str  |
15. +------+
16. | ab d |
17. +------+
19. VARCHAR rtrim(VARCHAR str)
20. --将参数 str 中从右侧部分开始部分连续出现的空格去掉
22. mysql> SELECT rtrim('ab d   ') str;
23. +------+
24. | str  |
25. +------+
26. | ab d |
27. +------+

复制代码

null_or_empty，not_null_or_empty

1. BOOLEAN NULL_OR_EMPTY (VARCHAR str)
3. -- 如果字符串为空字符串或者NULL，返回true。否则，返回false。
4. select null_or_empty(null);
5. +---------------------+
6. | null_or_empty(NULL) |
7. +---------------------+
8. |                   1 |
9. +---------------------+
11. select null_or_empty("");
12. +-------------------+
13. | null_or_empty('') |
14. +-------------------+
15. |                 1 |
16. +-------------------+
18. select null_or_empty("a");
19. +--------------------+
20. | null_or_empty('a') |
21. +--------------------+
22. |                  0 |
23. +--------------------+
25. BOOLEAN NOT_NULL_OR_EMPTY (VARCHAR str)
26. 如果字符串为空字符串或者NULL，返回false。否则，返回true。
28. select not_null_or_empty(null);
29. +-------------------------+
30. | not_null_or_empty(NULL) |
31. +-------------------------+
32. |                       0 |
33. +-------------------------+
35. select not_null_or_empty("");
36. +-----------------------+
37. | not_null_or_empty('') |
38. +-----------------------+
39. |                     0 |
40. +-----------------------+
42. select not_null_or_empty("a");
43. +------------------------+
44. | not_null_or_empty('a') |
45. +------------------------+
46. |                      1 |
47. +------------------------+

复制代码

replace

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

复制代码

split_part

1. VARCHAR split_part(VARCHAR content, VARCHAR delimiter, INT field)
2. -- 根据分割符拆分字符串, 返回指定的分割部分(从一开始计数)。
4. mysql> select split_part("hello world", " ", 1);
5. +----------------------------------+
6. | split_part('hello world', ' ', 1) |
7. +----------------------------------+
8. | hello                            |
9. +----------------------------------+
12. mysql> select split_part("hello world", " ", 2);
13. +----------------------------------+
14. | split_part('hello world', ' ', 2) |
15. +----------------------------------+
16. | world                             |
17. +----------------------------------+
19. mysql> select split_part("2019年7月8号", "月", 1);
20. +-----------------------------------------+
21. | split_part('2019年7月8号', '月', 1)     |
22. +-----------------------------------------+
23. | 2019年7                                 |
24. +-----------------------------------------+
26. mysql> select split_part("abca", "a", 1);
27. +----------------------------+
28. | split_part('abca', 'a', 1) |
29. +----------------------------+
30. |                            |
31. +----------------------------+

复制代码

money_format

1. VARCHAR money_format(Number)
2. -- 将数字按照货币格式输出，整数部分每隔3位用逗号分隔，小数部分保留2位
4. mysql> select money_format(17014116);
5. +------------------------+
6. | money_format(17014116) |
7. +------------------------+
8. | 17,014,116.00          |
9. +------------------------+
11. mysql> select money_format(1123.456);
12. +------------------------+
13. | money_format(1123.456) |
14. +------------------------+
15. | 1,123.46               |
16. +------------------------+
18. mysql> select money_format(1123.4);
19. +----------------------+
20. | money_format(1123.4) |
21. +----------------------+
22. | 1,123.40             |
23. +----------------------+

复制代码

数学函数

ceil和floor

1. BIGINT ceil(DOUBLE x)
2. -- 返回大于或等于x的最小整数值.
3. mysql> select ceil(1);
5. +-----------+
6. | ceil(1.0) |
7. +-----------+
8. |         1 |
9. +-----------+
10. mysql> select ceil(2.4);
11. +-----------+
12. | ceil(2.4) |
13. +-----------+
14. |         3 |
15. +-----------+
16. mysql> select ceil(-10.3);
17. +-------------+
18. | ceil(-10.3) |
19. +-------------+
20. |         -10 |
21. +-------------+
23. BIGINT floor(DOUBLE x)
24. -- 返回小于或等于x的最大整数值.
25. mysql> select floor(1);
26. +------------+
27. | floor(1.0) |
28. +------------+
29. |          1 |
30. +------------+
31. mysql> select floor(2.4);
32. +------------+
33. | floor(2.4) |
34. +------------+
35. |          2 |
36. +------------+
37. mysql> select floor(-10.3);
38. +--------------+
39. | floor(-10.3) |
40. +--------------+
41. |          -11 |
42. +--------------+

复制代码

round

1. round(x), round(x, d)
2. -- 将x四舍五入后保留d位小数，d默认为0。
3. -- 如果d为负数，则小数点左边d位为0。如果x或d为null，返回null。
5. mysql> select round(2.4);
6. +------------+
7. | round(2.4) |
8. +------------+
9. |          2 |
10. +------------+
11. mysql> select round(2.5);
12. +------------+
13. | round(2.5) |
14. +------------+
15. |          3 |
16. +------------+
17. mysql> select round(-3.4);
18. +-------------+
19. | round(-3.4) |
20. +-------------+
21. |          -3 |
22. +-------------+
23. mysql> select round(-3.5);
24. +-------------+
25. | round(-3.5) |
26. +-------------+
27. |          -4 |
28. +-------------+
29. mysql> select round(1667.2725, 2);
30. +---------------------+
31. | round(1667.2725, 2) |
32. +---------------------+
33. |             1667.27 |
34. +---------------------+
35. mysql> select round(1667.2725, -2);
36. +----------------------+
37. | round(1667.2725, -2) |
38. +----------------------+
39. |                 1700 |
40. +----------------------+

复制代码

truncate

1. DOUBLE truncate(DOUBLE x, INT d)
2. -- 按照保留小数的位数d对x进行数值截取。
3. -- 规则如下：
4. -- 当d > 0时：保留x的d位小数
5. -- 当d = 0时：将x的小数部分去除，只保留整数部分
6. -- 当d < 0时：将x的小数部分去除，整数部分按照 d所指定的位数，采用数字0进行替换
8. mysql> select truncate(124.3867, 2);
9. +-----------------------+
10. | truncate(124.3867, 2) |
11. +-----------------------+
12. |                124.38 |
13. +-----------------------+
14. mysql> select truncate(124.3867, 0);
15. +-----------------------+
16. | truncate(124.3867, 0) |
17. +-----------------------+
18. |                   124 |
19. +-----------------------+
20. mysql> select truncate(-124.3867, -2);
21. +-------------------------+
22. | truncate(-124.3867, -2) |
23. +-------------------------+
24. |                    -100 |
25. +-------------------------+

复制代码

abs

1. 数值类型 abs(数值类型 x)
2. -- 返回x的绝对值.
4. mysql> select abs(-2);
5. +---------+
6. | abs(-2) |
7. +---------+
8. |       2 |
9. +---------+
10. mysql> select abs(3.254655654);
11. +------------------+
12. | abs(3.254655654) |
13. +------------------+
14. |      3.254655654 |
15. +------------------+
16. mysql> select abs(-3254654236547654354654767);
17. +---------------------------------+
18. | abs(-3254654236547654354654767) |
19. +---------------------------------+
20. | 3254654236547654354654767       |
21. +---------------------------------+

复制代码

pow

1. DOUBLE pow(DOUBLE a, DOUBLE b)
2. -- 求幂次：返回a的b次方.
4. mysql> select pow(2,0);
5. +---------------+
6. | pow(2.0, 0.0) |
7. +---------------+
8. |             1 |
9. +---------------+
10. mysql> select pow(2,3);
11. +---------------+
12. | pow(2.0, 3.0) |
13. +---------------+
14. |             8 |
15. +---------------+
16. mysql> select round(pow(3,2.4),2);
17. +--------------------+
18. | pow(3.0, 2.4)      |
19. +--------------------+
20. | 13.966610165238235 |
21. +--------------------+

复制代码

greatest和 least

1. greatest(col_a, col_b, …, col_n)
2. -- 返回一行中 n个column的最大值.若column中有NULL，则返回NULL.
4. least(col_a, col_b, …, col_n)
5. -- 返回一行中 n个column的最小值.若column中有NULL，则返回NULL.
7. mysql> select greatest(-1, 0, 5, 8);
8. +-----------------------+
9. | greatest(-1, 0, 5, 8) |
10. +-----------------------+
11. |                     8 |
12. +-----------------------+
13. mysql> select greatest(-1, 0, 5, NULL);
14. +--------------------------+
15. | greatest(-1, 0, 5, NULL) |
16. +--------------------------+
17. | NULL                     |
18. +--------------------------+
19. mysql> select greatest(6.3, 4.29, 7.6876);
20. +-----------------------------+
21. | greatest(6.3, 4.29, 7.6876) |
22. +-----------------------------+
23. |                      7.6876 |
24. +-----------------------------+
25. mysql> select greatest("2022-02-26 20:02:11","2020-01-23 20:02:11","2020-06-22 20:02:11");
26. +-------------------------------------------------------------------------------+
27. | greatest('2022-02-26 20:02:11', '2020-01-23 20:02:11', '2020-06-22 20:02:11') |
28. +-------------------------------------------------------------------------------+
29. | 2022-02-26 20:02:11                                                           |
30. +-------------------------------------------------------------------------------+

复制代码

JSON函数

1. CREATE TABLE test_json (
2.   id INT,
3.   json_string String
4. )
5. DUPLICATE KEY(id)
6. DISTRIBUTED BY HASH(id) BUCKETS 3
7. PROPERTIES("replication_num" = "1");
9. --测试数据
10. {"k1":"v31", "k2": 300, "a1": [{"k1":"v41", "k2": 400}, 1, "a", 3.14]}
11. {"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]}}
12. {"k1":"v33", "k2": 500, "a1": [{"k1":"v41", "k2": 400}, 3, "a", 5.14],"a2":{"k3":"v33", "k4": 200,"a2": [{"k5":"v42", "k6": 600}]}}
13. {"k1":"v31"}
14. {"k1":"v31", "k2": 300}
15. {"k1":"v31", "k2": 200 "a1": []}
16. --json是一种里面存着一对对key，value类型的结构
17. --针对值类型的不同：
18. -- 1.简单值："k1":"v31"
19. -- 2.数组：[{"k1":"v41", "k2": 400}, 1, "a", 3.14]
20. -- 3.对象："a2":{"k3":"v33", "k4": 200,"a2": [{"k5":"v42", "k6": 600}]}

复制代码

取值的时候，指定的'$.k1'==>这样的东西我们称之为json path ，json的路劲
-- 通过本地文件的方式导入

1. curl \
2. -u root: \
3. -H "label:load_local_file1" \
4. -H "column_separator:_" \
5. -T /root/data/json.txt \
6. http://doitedu01:8040/api/test/test_json/_stream_load

复制代码

1. -- 用insert into 的方式导入一条
2. INSERT INTO test_json VALUES(7, '{"k1":"v1", "k2": 200}');
3. 5.1.7.1get_json_double，get_json_int，get_json_string
6. DOUBLE get_json_int(VARCHAR json_str, VARCHAR json_path)
7. INT get_json_int(VARCHAR json_str, VARCHAR json_path)
8. VARCHAR get_json_string(VARCHAR json_str, VARCHAR json_path)
10. -- 解析并获取 json 字符串内指定路径的double，int，string 类型的内容。
11. -- 其中 json_path 必须以 $ 符号作为开头，使用 . 作为路径分割符。
12. -- 如果路径中包含 . ，则可以使用双引号包围。
13. -- 使用 [ ] 表示数组下标，从 0 开始。
14. -- path 的内容不能包含 ", [ 和 ]。
15. -- 如果 json_string 格式不对，或 json_path 格式不对，或无法找到匹配项，则返回 NULL。
17. --1.获取到k1对应的value的值
18. mysql> select  id, get_json_string(json_string,'$.k1') as k1 from test_json;
19. +------+------+
20. | id   | k1   |
21. +------+------+
22. |    2 | v32  |
23. |    4 | v31  |
24. |    5 | v31  |
25. |    6 | v31  |
26. |    1 | v31  |
27. |    3 | v33  |
28. +------+------+
30. --2.获取到key 为a1 里面的数组
31. mysql> select  id, get_json_string(json_string,'$.a1') as arr from test_json;
32. +------+------------------------------------+
33. | id   | arr                                |
34. +------+------------------------------------+
35. |    1 | [{"k1":"v41","k2":400},1,"a",3.14] |
36. |    3 | [{"k1":"v41","k2":400},3,"a",5.14] |
37. |    2 | [{"k1":"v41","k2":400},2,"a",4.14] |
38. |    4 | NULL                               |
39. |    5 | NULL                               |
40. |    6 | []                                 |
41. +------+------------------------------------+
44. --3.获取到key 为a1 里面的数组中第一个元素的值
45. mysql> select  id, get_json_string(json_string,'$.a1[0]') as arr from test_json;
46. +------+-----------------------+
47. | id   | arr                   |
48. +------+-----------------------+
49. |    2 | {"k1":"v41","k2":400} |
50. |    1 | {"k1":"v41","k2":400} |
51. |    3 | {"k1":"v41","k2":400} |
52. |    4 | NULL                  |
53. |    5 | NULL                  |
54. |    6 | NULL                  |
55. +------+-----------------------+
57. --4.获取到key 为a1 里面的数组中第一个元素的值（这个值是一个json串，再次获取到这个字符串中）
58. select  id, get_json_string(get_json_string(json_string,'$.a1[0]'),'$.k1') as arr from test_json;
59. +------+------+
60. | id   | arr  |
61. +------+------+
62. |    2 | v41  |
63. |    1 | v41  |
64. |    3 | v41  |
65. |    4 | NULL |
66. |    5 | NULL |
67. |    6 | NULL |
68. +------+------+
69. 6 rows in set (0.02 sec)

复制代码

json_object

1. VARCHAR json_object(VARCHAR,...)
2. -- 生成一个包含指定Key-Value对的json object,
3. -- 传入的参数是key,value对，且key不能为null
6. MySQL> select json_object('time',curtime());
7. +--------------------------------+
8. | json_object('time', curtime()) |
9. +--------------------------------+
10. | {"time": "10:49:18"}           |
11. +--------------------------------+
14. MySQL> SELECT json_object('id', 87, 'name', 'carrot');
15. +-----------------------------------------+
16. | json_object('id', 87, 'name', 'carrot') |
17. +-----------------------------------------+
18. | {"id": 87, "name": "carrot"}            |
19. +-----------------------------------------+
21. json_object('id', 87, 'name', 'carrot');
23. MySQL> select json_object('username',null);
24. +---------------------------------+
25. | json_object('username', 'NULL') |
26. +---------------------------------+
27. | {"username": NULL}              |
28. +---------------------------------+

复制代码

窗口函数

ROW_NUMBER()，DENSE_RANK()，RANK()

1. -- 测试rank打行号，名次相同会并列排名，比如两个第一名，就是1 1 然后第二名会显示3
2. select x, y, rank() over(partition by x order by y) as rank from int_t;
4. | x  | y    | rank     |
5. |----|------|----------|
6. | 1  | 1    | 1        |
7. | 1  | 2    | 2        |
8. | 1  | 2    | 2        |
9. | 2  | 1    | 1        |
10. | 2  | 2    | 2        |
11. | 2  | 3    | 3        |
12. | 3  | 1    | 1        |
13. | 3  | 1    | 1        |
14. | 3  | 2    | 3        |
17. -- 测试dense_rank(),名词相同会并列排名，比如两个第一名，就是1 1 然后第二名会显示2
18. select x, y, dense_rank() over(partition by x order by y) as rank from int_t;
19. | x  | y    | rank     |
20. |----|------|----------|
21. | 1  | 1    | 1        |
22. | 1  | 2    | 2        |
23. | 1  | 2    | 2        |
24. | 2  | 1    | 1        |
25. | 2  | 2    | 2        |
26. | 2  | 3    | 3        |
27. | 3  | 1    | 1        |
28. | 3  | 1    | 1        |
29. | 3  | 2    | 2        |
31. -- 测试ROW_NUMBER() 按照分组排序要求，返回的编号依次底层，1 2 3 4 5 ，
32. -- 不会有重复值，也不会有空缺值，就是连续递增的整数，从1 开始
33. select x, y, row_number() over(partition by x order by y) as rank from int_t;
35. | x | y    | rank     |
36. |---|------|----------|
37. | 1 | 1    | 1        |
38. | 1 | 2    | 2        |
39. | 1 | 2    | 3        |
40. | 2 | 1    | 1        |
41. | 2 | 2    | 2        |
42. | 2 | 3    | 3        |
43. | 3 | 1    | 1        |
44. | 3 | 1    | 2        |
45. | 3 | 2    | 3        |
49. -- 数据
50. 孙悟空,语文,87
51. 孙悟空,数学,95
52. 娜娜,英语,84
53. 宋宋,语文,64
54. 孙悟空,英语,68
55. 宋宋,英语,84
56. 婷婷,语文,65
57. 娜娜,语文,94
58. 宋宋,数学,86
59. 婷婷,数学,85
60. 娜娜,数学,56
61. 婷婷,英语,78
63. -- 建表语句
64. create table stu
65. (
66. name varchar(50),
67. subject varchar(50),
68. score double
69. )
70. DUPLICATE KEY(name)
71. DISTRIBUTED BY HASH(name) BUCKETS 1;
73. -- 通过本地文件的方式导入数据
74. curl \
75. -u root: \
76. -H "label:num_test" \
77. -H "column_separator:," \
78. -T /root/data/stu.txt \
79. http://doitedu01:8040/api/test/stu/_stream_load

复制代码

需求：
【相同分数并列(假设第一名有两个，排名就是并列第一，然后第三名从2开始)】
1.按照分数降序排序，求每个学科中每个人的名次
2.按照每个人的总分进行升序排列，得到每个人总分名次的名次
【相同分数并列(假设第一名有两个，排名就是并列第一，然后第三名从3开始)】
3.按照学科进行升序排列，得到每个人的每个学科的名次
4.按照每个人的总分进行升序排列，得到每个人总分名次的名次
【相同分数并列
(假设第一名有两个，排名就是并列第一，
就再单独比语文的成绩，然后数学，最后英语，
分数全部一样，按照学生名字的字典顺序，在前的为第一)】
5.按照每个人的总分进行升序排列，得到每个人总分名次的名次

1. -- 1.按照学科进行升序排列，得到每个人的每个学科的名次
2. select
3. name,subject,score,
4. dense_rank() over(partition by subject order by score desc) as rank
5. from stu
6. +-----------+---------+-------+------+
7. | name      | subject | score | rank |
8. +-----------+---------+-------+------+
9. | 孙悟空    | 数学    |    95 |    1 |
10. | 宋宋      | 数学    |    86 |    2 |
11. | 婷婷      | 数学    |    85 |    3 |
12. | 娜娜      | 数学    |    56 |    4 |
13. | 娜娜      | 英语    |    84 |    1 |
14. | 宋宋      | 英语    |    84 |    1 |
15. | 婷婷      | 英语    |    78 |    2 |
16. | 孙悟空    | 英语    |    68 |    3 |
17. | 娜娜      | 语文    |    94 |    1 |
18. | 孙悟空    | 语文    |    87 |    2 |
19. | 婷婷      | 语文    |    65 |    3 |
20. | 宋宋      | 语文    |    64 |    4 |
21. +-----------+---------+-------+------+
23. -- 2.按照每个人的总分进行升序排列，得到每个人总分名次的名次
24. select
25. name,sum_score,
26. -- 因为是整体按照学生的总分进行求名次，所有学生为1组，就不需要分组了
27. dense_rank() over(order by sum_score desc) as rank
28. from
29. (
30.     select
31.     name,sum(score) as sum_score
32.     from stu
33.     group by name
34. ) as t ;
35. +-----------+-----------+------+
36. | name      | sum_score | rank |
37. +-----------+-----------+------+
38. | 孙悟空    |       250 |    1 |
39. | 宋宋      |       234 |    2 |
40. | 娜娜      |       234 |    2 |
41. | 婷婷      |       228 |    3 |
42. +-----------+-----------+------+
45. 【相同分数并列(假设第一名有两个，排名就是并列第一，然后第三名从3开始)】
46. -- 3.按照学科进行升序排列，得到每个人的每个学科的名次
47. select
48. name,subject,score,
49. rank() over(partition by subject order by score desc) as rank
50. from stu
51. +-----------+---------+-------+------+
52. | name      | subject | score | rank |
53. +-----------+---------+-------+------+
54. | 孙悟空    | 数学    |    95 |    1 |
55. | 宋宋      | 数学    |    86 |    2 |
56. | 婷婷      | 数学    |    85 |    3 |
57. | 娜娜      | 数学    |    56 |    4 |
58. | 娜娜      | 英语    |    84 |    1 |
59. | 宋宋      | 英语    |    84 |    1 |
60. | 婷婷      | 英语    |    78 |    3 |
61. | 孙悟空    | 英语    |    68 |    4 |
62. | 娜娜      | 语文    |    94 |    1 |
63. | 孙悟空    | 语文    |    87 |    2 |
64. | 婷婷      | 语文    |    65 |    3 |
65. | 宋宋      | 语文    |    64 |    4 |
66. +-----------+---------+-------+------+
68. -- 4.按照每个人的总分进行升序排列，得到每个人总分名次的名次
69. select
70. name,sum_score,
71. -- 因为是整体按照学生的总分进行求名次，所有学生为1组，就不需要分组了
72. rank() over(order by sum_score desc) as rank
73. from
74. (
75.     select
76.     name,sum(score) as sum_score
77.     from stu
78.     group by name
79. ) as t ;
80. +-----------+-----------+------+
81. | name      | sum_score | rank |
82. +-----------+-----------+------+
83. | 孙悟空    |       250 |    1 |
84. | 宋宋      |       234 |    2 |
85. | 娜娜      |       234 |    2 |
86. | 婷婷      |       228 |    4 |
87. +-----------+-----------+------+
89. 【相同分数并列
90. (假设第一名有两个，排名就是并列第一，
91. 就再单独比语文的成绩，然后数学，最后英语，
92. 分数全部一样，按照学生名字的字典顺序，在前的为第一)】
94. -- 5.按照每个人的总分进行升序排列，得到每个人总分名次的名次
95. --方案1：利用窗口函数来列转行
96. select
97. name,subject,score as math_score,english_score,chinese_score,sum_score,
98. row_number()over(order by sum_score desc ,chinese_score desc ,score desc ,english_score desc,name asc) as num
99. from
100. (
101.         select  
102.         name,subject,score,
103.         lead(score,1,0)over(partition by name order by subject) as english_score,
104.         lead(score,2,0)over(partition by name order by subject) as chinese_score,
105.         sum(score)over(partition by name) as sum_score,
106.         row_number()over(partition by name) as num
107.         from stu
108. ) as tmp
109. where num = 1
111. -- 方案2：利用if判断来列转行
112.   select
113. name,chinese_score,match_score,english_score,sum_score,
114. row_number()over(order by sum_score desc ,chinese_score desc ,match_score desc ,english_score desc,name asc) as num
115. from
116. (
117.     select
118.     name,
119.     sum(chinese_score) as chinese_score,
120.     sum(match_score) as match_score,
121.     sum(english_score) as english_score,
122.     sum(chinese_score) + sum(match_score) + sum(english_score) as sum_score
123.     from
124.     (
125.         select  name,subject,
126.         if(subject = '语文',score,0) as chinese_score,
127.         if(subject = '数学',score,0) as match_score,
128.         if(subject = '英语',score,0) as english_score
129.         from stu
130.     )as t
131.     group by name
132. ) as t1
134. +-----------+---------+------------+---------------+---------------+-----------+------+
135. | name      | subject | math_score | english_score | chinese_score | sum_score | num  |
136. +-----------+---------+------------+---------------+---------------+-----------+------+
137. | 孙悟空    | 数学    |         95 |            68 |            87 |       250 |    1 |
138. | 娜娜      | 数学    |         56 |            84 |            94 |       234 |    2 |
139. | 宋宋      | 数学    |         86 |            84 |            64 |       234 |    3 |
140. | 婷婷      | 数学    |         85 |            78 |            65 |       228 |    4 |
141. +-----------+---------+------------+---------------+---------------+-----------+------+

复制代码

min，max，sum，avg，count

1. min(x)over()   -- 取窗口中x列的最小值
2. max(x)over()   -- 取窗口中x列的最大值
3. sum(x)over()   -- 取窗口中x列的数据总和
4. avg(x)over()   -- 取窗口中x列的数据平均值
5. count(x)over() -- 取窗口中x列有多少行
7. unbounded preceding
8. current row
9. 1 following
10. 1 PRECEDING
12. rows between unbounded preceding and current row   --指在当前窗口中第一行到当前行的范围
13. rows between unbounded preceding and 1 following   --指在当前窗口中第一行到当前行下一行的范围
14. rows between unbounded preceding and 1 PRECEDING   --指在当前窗口中第一行到当前行前一行的范围

复制代码

LEAD() ，LAG()

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

复制代码

漏斗模型分析函数window_funnel

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

1. -- 准备数据
2. uid  event_id   event_action  event_time
3. u001,e1,view_detail_page,2022-11-01 01:10:21
4. u001,e2,add_bag_page,2022-11-01 01:11:13
5. u001,e3,collect_goods_page,2022-11-01 02:07:11
6. u002,e3,collect_goods_page,2022-11-01 01:10:21
7. u002,e4,order_detail_page,2022-11-01 01:11:13
8. u002,e5,pay_detail_page,2022-11-01 02:07:11
9. u002,e6,click_adver_page,2022-11-01 13:07:23
10. u002,e7,home_page,2022-11-01 08:18:12
11. u002,e8,list_detail_page,2022-11-01 23:34:29
12. u002,e1,view_detail_page,2022-11-01 11:25:32
13. u002,e2,add_bag_page,2022-11-01 12:41:21
14. u002,e3,collect_goods_page,2022-11-01 16:21:15
15. u002,e4,order_detail_page,2022-11-01 21:41:12
16. u003,e5,pay_detail_page,2022-11-01 01:10:21
17. u003,e6,click_adver_page,2022-11-01 01:11:13
18. u003,e7,home_page,2022-11-01 02:07:11
19. u001,e4,order_detail_page,2022-11-01 13:07:23
20. u001,e5,pay_detail_page,2022-11-01 08:18:12
21. u001,e6,click_adver_page,2022-11-01 23:34:29
22. u001,e7,home_page,2022-11-01 11:25:32
23. u001,e8,list_detail_page,2022-11-01 12:41:21
24. u001,e1,view_detail_page,2022-11-01 16:21:15
25. u001,e2,add_bag_page,2022-11-01 21:41:12
26. u003,e8,list_detail_page,2022-11-01 13:07:23
27. u003,e1,view_detail_page,2022-11-01 08:18:12
28. u003,e2,add_bag_page,2022-11-01 23:34:29
29. u003,e3,collect_goods_page,2022-11-01 11:25:32
30. u003,e4,order_detail_page,2022-11-01 12:41:21
31. u003,e5,pay_detail_page,2022-11-01 16:21:15
32. u003,e6,click_adver_page,2022-11-01 21:41:12
33. u004,e7,home_page,2022-11-01 01:10:21
34. u004,e8,list_detail_page,2022-11-01 01:11:13
35. u004,e1,view_detail_page,2022-11-01 02:07:11
36. u004,e2,add_bag_page,2022-11-01 13:07:23
37. u004,e3,collect_goods_page,2022-11-01 08:18:12
38. u004,e4,order_detail_page,2022-11-01 23:34:29
39. u004,e5,pay_detail_page,2022-11-01 11:25:32
40. u004,e6,click_adver_page,2022-11-01 12:41:21
41. u004,e7,home_page,2022-11-01 16:21:15
42. u004,e8,list_detail_page,2022-11-01 21:41:12
43. u005,e1,view_detail_page,2022-11-01 01:10:21
44. u005,e2,add_bag_page,2022-11-01 01:11:13
45. u005,e3,collect_goods_page,2022-11-01 02:07:11
46. u005,e4,order_detail_page,2022-11-01 13:07:23
47. u005,e5,pay_detail_page,2022-11-01 08:18:12
48. u005,e6,click_adver_page,2022-11-01 23:34:29
49. u005,e7,home_page,2022-11-01 11:25:32
50. u005,e8,list_detail_page,2022-11-01 12:41:21
51. u005,e1,view_detail_page,2022-11-01 16:21:15
52. u005,e2,add_bag_page,2022-11-01 21:41:12
53. u005,e3,collect_goods_page,2022-11-01 01:10:21
54. u006,e4,order_detail_page,2022-11-01 01:11:13
55. u006,e5,pay_detail_page,2022-11-01 02:07:11
56. u006,e6,click_adver_page,2022-11-01 13:07:23
57. u006,e7,home_page,2022-11-01 08:18:12
58. u006,e8,list_detail_page,2022-11-01 23:34:29
59. u006,e1,view_detail_page,2022-11-01 11:25:32
60. u006,e2,add_bag_page,2022-11-01 12:41:21
61. u006,e3,collect_goods_page,2022-11-01 16:21:15
62. u006,e4,order_detail_page,2022-11-01 21:41:12
63. u006,e5,pay_detail_page,2022-11-01 23:10:21
64. u006,e6,click_adver_page,2022-11-01 01:11:13
65. u007,e7,home_page,2022-11-01 02:07:11
66. u007,e8,list_detail_page,2022-11-01 13:07:23
67. u007,e1,view_detail_page,2022-11-01 08:18:12
68. u007,e2,add_bag_page,2022-11-01 23:34:29
69. u007,e3,collect_goods_page,2022-11-01 11:25:32
70. u007,e4,order_detail_page,2022-11-01 12:41:21
71. u007,e5,pay_detail_page,2022-11-01 16:21:15
72. u007,e6,click_adver_page,2022-11-01 21:41:12
73. u007,e7,home_page,2022-11-01 01:10:21
74. u008,e8,list_detail_page,2022-11-01 01:11:13
75. u008,e1,view_detail_page,2022-11-01 02:07:11
76. u008,e2,add_bag_page,2022-11-01 13:07:23
77. u008,e3,collect_goods_page,2022-11-01 08:18:12
78. u008,e4,order_detail_page,2022-11-01 23:34:29
79. u008,e5,pay_detail_page,2022-11-01 11:25:32
80. u008,e6,click_adver_page,2022-11-01 12:41:21
81. u008,e7,home_page,2022-11-01 16:21:15
82. u008,e8,list_detail_page,2022-11-01 21:41:12
83. u008,e1,view_detail_page,2022-11-01 01:10:21
84. u009,e2,add_bag_page,2022-11-01 01:11:13
85. u009,e3,collect_goods_page,2022-11-01 02:07:11
86. u009,e4,order_detail_page,2022-11-01 13:07:23
87. u009,e5,pay_detail_page,2022-11-01 08:18:12
88. u009,e6,click_adver_page,2022-11-01 23:34:29
89. u009,e7,home_page,2022-11-01 11:25:32
90. u009,e8,list_detail_page,2022-11-01 12:41:21
91. u009,e1,view_detail_page,2022-11-01 16:21:15
92. u009,e2,add_bag_page,2022-11-01 21:41:12
93. u009,e3,collect_goods_page,2022-11-01 01:10:21
94. u010,e4,order_detail_page,2022-11-01 01:11:13
95. u010,e5,pay_detail_page,2022-11-01 02:07:11
96. u010,e6,click_adver_page,2022-11-01 13:07:23
97. u010,e7,home_page,2022-11-01 08:18:12
98. u010,e8,list_detail_page,2022-11-01 23:34:29
99. u010,e5,pay_detail_page,2022-11-01 11:25:32
100. u010,e6,click_adver_page,2022-11-01 12:41:21
101. u010,e7,home_page,2022-11-01 16:21:15
102. u010,e8,list_detail_page,2022-11-01 21:41:12
104. -- 创建表
105. drop table if exists event_info_log;
106. create table event_info_log
107. (
108. uid varchar(20),
109. event_id varchar(20),
110. event_action varchar(20),
111. event_time datetime
112. )
113. DUPLICATE KEY(uid)
114. DISTRIBUTED BY HASH(uid) BUCKETS 1;
116. -- 通过本地文件的方式导入数据
117. curl \
118. -u root: \
119. -H "label:event_info_log" \
120. -H "column_separator:," \
121. -T /root/data/event_log.txt \
122. http://linux01:8040/api/test/event_info_log/_stream_load

复制代码

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

1. window_funnel(window, mode, timestamp_column, event1, event2, ... , eventN)
3. -- 漏斗分析函数搜索滑动时间窗口内最大的发生的最大事件序列长度。
4. -- window ：滑动时间窗口大小，单位为秒。
5. -- mode  ：保留，目前只支持default。-- 相邻两个事件之间没有时间间隔要求，并且相邻两个事件中可以做其他的事件
6. -- timestamp_column ：指定时间列，类型为DATETIME, 滑动窗口沿着此列工作。
7. -- eventN ：表示事件的布尔表达式。
9. select
10. user_id,
11. window_funnel(3600*24, 'default', event_time, event_id='e1', event_id='e2' , event_id='e4', event_id='e5') as step
12. from event_info_log
13. group by user_id
15. +---------+------+
16. | user_id | step |
17. +---------+------+
18. | u006    |    4 |
19. | u007    |    2 |
20. | u005    |    3 |
21. | u004    |    3 |
22. | u010    |    0 |
23. | u001    |    3 |
24. | u003    |    2 |
25. | u002    |    3 |
26. | u008    |    3 |
27. | u009    |    2 |
28. +---------+------+
32. -- 算每一层级的转换率
33. select
34. '购买转化漏斗' as funnel_name,
35. sum(if(step >= 1 ,1,0)) as step1,
36. sum(if(step >= 2 ,1,0)) as step2,
37. sum(if(step >= 3 ,1,0)) as step3,
38. sum(if(step >= 4 ,1,0)) as step4,
39. round(sum(if(step >= 2 ,1,0))/sum(if(step >= 1 ,1,0)),2) as 'step1->step2_radio',
40. round(sum(if(step >= 3 ,1,0))/sum(if(step >= 2 ,1,0)),2) as 'step2->step3_radio',
41. round(sum(if(step >= 4 ,1,0))/sum(if(step >= 3 ,1,0)),2) as 'step3->step4_radio'
42. from
43. (
44. select
45. user_id,
46. window_funnel(3600*24, 'default', report_date, event_id='e1', event_id='e2' , event_id='e4', event_id='e5') as step
47. from event_info_log
48. where to_date(report_date) = '2022-11-01'
49. and event_id in('e1','e4','e5','e2')
50. group by user_id
51. ) as t1
53. -- res
54. +--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
55. | funnel_name        | step1 | step2 | step3 | step4 | step1->step2_radio | step2->step3_radio | step3->step4_radio |
56. +--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
57. | 购买转化漏斗       |     9 |     9 |     6 |     1 |                  1 |               0.67 |               0.17 |
58. +--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！