# 查詢語法和內置函數 ## 查詢語法整體結構 ```sql SELECT [ALL | DISTINCT | DISTINCTROW ] -- 對查詢欄位的結果是否需要去重，還是全部保留等參數 select_expr [, select_expr ...] -- select的查詢欄位 [FROM ...

查詢語法和內置函數

查詢語法整體結構

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

Doris(六) -- 查詢語法和內置函數