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

内置函数

条件函数

if

if(boolean condition, type valueTrue, type valueFalseOrNull)
--如果表达式 condition 成立,返回结果 valueTrue;否则,返回结果 valueFalseOrNull
--返回值类型:valueTrue 表达式结果的类型
示例:
SQL
mysql> 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,否则返回 expr2

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

coalesce(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,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 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 name
from score
group 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 = olap
distributed 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_name
from 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_age
from example 
group by gender;

+--------+------------+
| gender | gc_age     |
+--------+------------+
| female | 48, 28     |
| male   | 58, 38, 18 |
+--------+------------+


-- 求:每一个人有考试成绩的所有科目
select
name,
group_concat(subject,',')  as all_subject
from score
group by name

collect_list,collect_set (1.2版本上线)

ARRAY<T> collect_list(expr)
--返回一个包含 expr 中所有元素(不包括NULL)的数组,数组中元素顺序是不确定的。

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

日期函数

获取当前时间

-- curdate,current_date,now,curtime,current_time,current_timestamp
select 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

-- 正常使用的三种格式
yyyyMMdd
yyyy-MM-dd
yyyy-MM-dd HH:mm:ss
示例:
SQL
mysql> 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 类型中的日期部分。
示例:
SQL
select 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
示例:
SQL
select 
extract(year from '2022-09-22 17:01:30') as year,
extract(month from '2022-09-22 17:01:30') as month,
extract(day from '2022-09-22 17:01:30') as day,
extract(hour from '2022-09-22 17:01:30') as hour,
extract(minute from '2022-09-22 17:01:30') as minute,
extract(second from '2022-09-22 17:01:30') as second;

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



select 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_sub
select 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的类型转化为字符串
示例:
SQL
select 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,那么得到的结果就是null
mysql> 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。
示例:
SQL
select 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 3
PROPERTIES("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_string


DOUBLE 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不能为null


MySQL> 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 然后第二名会显示3
select 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 然后第二名会显示2
select 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 rank
from 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.按照每个人的总分进行升序排列,得到每个人总分名次的名次
select
name,sum_score,
-- 因为是整体按照学生的总分进行求名次,所有学生为1组,就不需要分组了
dense_rank() over(order by sum_score desc) as rank
from 
(
    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 rank
from 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.按照每个人的总分进行升序排列,得到每个人总分名次的名次
select
name,sum_score,
-- 因为是整体按照学生的总分进行求名次,所有学生为1组,就不需要分组了
rank() over(order by sum_score desc) as rank
from 
(
    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 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   --指在当前窗口中第一行到当前行前一行的范围

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_time
u001,e1,view_detail_page,2022-11-01 01:10:21
u001,e2,add_bag_page,2022-11-01 01:11:13
u001,e3,collect_goods_page,2022-11-01 02:07:11
u002,e3,collect_goods_page,2022-11-01 01:10:21
u002,e4,order_detail_page,2022-11-01 01:11:13
u002,e5,pay_detail_page,2022-11-01 02:07:11
u002,e6,click_adver_page,2022-11-01 13:07:23
u002,e7,home_page,2022-11-01 08:18:12
u002,e8,list_detail_page,2022-11-01 23:34:29
u002,e1,view_detail_page,2022-11-01 11:25:32
u002,e2,add_bag_page,2022-11-01 12:41:21
u002,e3,collect_goods_page,2022-11-01 16:21:15
u002,e4,order_detail_page,2022-11-01 21:41:12
u003,e5,pay_detail_page,2022-11-01 01:10:21
u003,e6,click_adver_page,2022-11-01 01:11:13
u003,e7,home_page,2022-11-01 02:07:11
u001,e4,order_detail_page,2022-11-01 13:07:23
u001,e5,pay_detail_page,2022-11-01 08:18:12
u001,e6,click_adver_page,2022-11-01 23:34:29
u001,e7,home_page,2022-11-01 11:25:32
u001,e8,list_detail_page,2022-11-01 12:41:21
u001,e1,view_detail_page,2022-11-01 16:21:15
u001,e2,add_bag_page,2022-11-01 21:41:12
u003,e8,list_detail_page,2022-11-01 13:07:23
u003,e1,view_detail_page,2022-11-01 08:18:12
u003,e2,add_bag_page,2022-11-01 23:34:29
u003,e3,collect_goods_page,2022-11-01 11:25:32
u003,e4,order_detail_page,2022-11-01 12:41:21
u003,e5,pay_detail_page,2022-11-01 16:21:15
u003,e6,click_adver_page,2022-11-01 21:41:12
u004,e7,home_page,2022-11-01 01:10:21
u004,e8,list_detail_page,2022-11-01 01:11:13
u004,e1,view_detail_page,2022-11-01 02:07:11
u004,e2,add_bag_page,2022-11-01 13:07:23
u004,e3,collect_goods_page,2022-11-01 08:18:12
u004,e4,order_detail_page,2022-11-01 23:34:29
u004,e5,pay_detail_page,2022-11-01 11:25:32
u004,e6,click_adver_page,2022-11-01 12:41:21
u004,e7,home_page,2022-11-01 16:21:15
u004,e8,list_detail_page,2022-11-01 21:41:12
u005,e1,view_detail_page,2022-11-01 01:10:21
u005,e2,add_bag_page,2022-11-01 01:11:13
u005,e3,collect_goods_page,2022-11-01 02:07:11
u005,e4,order_detail_page,2022-11-01 13:07:23
u005,e5,pay_detail_page,2022-11-01 08:18:12
u005,e6,click_adver_page,2022-11-01 23:34:29
u005,e7,home_page,2022-11-01 11:25:32
u005,e8,list_detail_page,2022-11-01 12:41:21
u005,e1,view_detail_page,2022-11-01 16:21:15
u005,e2,add_bag_page,2022-11-01 21:41:12
u005,e3,collect_goods_page,2022-11-01 01:10:21
u006,e4,order_detail_page,2022-11-01 01:11:13
u006,e5,pay_detail_page,2022-11-01 02:07:11
u006,e6,click_adver_page,2022-11-01 13:07:23
u006,e7,home_page,2022-11-01 08:18:12
u006,e8,list_detail_page,2022-11-01 23:34:29
u006,e1,view_detail_page,2022-11-01 11:25:32
u006,e2,add_bag_page,2022-11-01 12:41:21
u006,e3,collect_goods_page,2022-11-01 16:21:15
u006,e4,order_detail_page,2022-11-01 21:41:12
u006,e5,pay_detail_page,2022-11-01 23:10:21
u006,e6,click_adver_page,2022-11-01 01:11:13
u007,e7,home_page,2022-11-01 02:07:11
u007,e8,list_detail_page,2022-11-01 13:07:23
u007,e1,view_detail_page,2022-11-01 08:18:12
u007,e2,add_bag_page,2022-11-01 23:34:29
u007,e3,collect_goods_page,2022-11-01 11:25:32
u007,e4,order_detail_page,2022-11-01 12:41:21
u007,e5,pay_detail_page,2022-11-01 16:21:15
u007,e6,click_adver_page,2022-11-01 21:41:12
u007,e7,home_page,2022-11-01 01:10:21
u008,e8,list_detail_page,2022-11-01 01:11:13
u008,e1,view_detail_page,2022-11-01 02:07:11
u008,e2,add_bag_page,2022-11-01 13:07:23
u008,e3,collect_goods_page,2022-11-01 08:18:12
u008,e4,order_detail_page,2022-11-01 23:34:29
u008,e5,pay_detail_page,2022-11-01 11:25:32
u008,e6,click_adver_page,2022-11-01 12:41:21
u008,e7,home_page,2022-11-01 16:21:15
u008,e8,list_detail_page,2022-11-01 21:41:12
u008,e1,view_detail_page,2022-11-01 01:10:21
u009,e2,add_bag_page,2022-11-01 01:11:13
u009,e3,collect_goods_page,2022-11-01 02:07:11
u009,e4,order_detail_page,2022-11-01 13:07:23
u009,e5,pay_detail_page,2022-11-01 08:18:12
u009,e6,click_adver_page,2022-11-01 23:34:29
u009,e7,home_page,2022-11-01 11:25:32
u009,e8,list_detail_page,2022-11-01 12:41:21
u009,e1,view_detail_page,2022-11-01 16:21:15
u009,e2,add_bag_page,2022-11-01 21:41:12
u009,e3,collect_goods_page,2022-11-01 01:10:21
u010,e4,order_detail_page,2022-11-01 01:11:13
u010,e5,pay_detail_page,2022-11-01 02:07:11
u010,e6,click_adver_page,2022-11-01 13:07:23
u010,e7,home_page,2022-11-01 08:18:12
u010,e8,list_detail_page,2022-11-01 23:34:29
u010,e5,pay_detail_page,2022-11-01 11:25:32
u010,e6,click_adver_page,2022-11-01 12:41:21
u010,e7,home_page,2022-11-01 16:21:15
u010,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 step
from 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 step
from 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 |
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+

热门相关:地球第一剑   法医娇宠,扑倒傲娇王爷   法医娇宠,扑倒傲娇王爷   梦回大明春   重生当学神,又又又考第一了!