--创建测试数据库test show databases ; create database if not exists test; use test;
一、关系运算
1、等值比较：=
select 1 where 1 = 1; --1
select 1 where 0 = 1; --NULL
2、不等值比较：<>
select 1 where 1 <> 2; --1
select 1 where 2 <> 2; --NULL
3、小于比较：<
select 1 where 1 < 2; --1
select 1 where 3 < 2; --NULL
4、小于等于比较：<=
select 1 where 1 <= 2; --1
select 1 where 3 <= 2; --NULL
5、大于比较：>
select 1 where 3 > 2; --1
select 1 where 1 > 2; --NULL
6、大于等于比较：>=
select 1 where 3 >= 2; --1
select 1 where 1 >= 2; --NULL
7、空值判断：IS NULL
select 1 where null is null ; --1
select 1 where 1 is null ; --NULL
8、非空判断：IS NOT NULL
select 1 where 1 is not null ; --1
select 1 where null is not null ; --NULL
9、LIKE 比较：LIKE
--字符 ”_” 表示任意单个字符、字符 ”%” 表示任意数量的字符
select 1 where 'footb' like 'foot_'; --1
select 1 where 'football' like 'foot____'; --1
select 1 where 'football' like 'foot%'; --1
select 1 where 'f' like 'foot_'; --NULL
--注意：否定比较时候用 NOT A LIKE B
select 1 where not 'f' like 'foot_'; --1
10、JAVA的LIKE 比较：RLIKE
select 1 where 'football' rlike '^f.*l$'; --1
11、正则匹配：REGEXP
select 1 where 'football' regexp '^f.*l$'; --1
二、数学运算
1、加法操作：+
select 1 + 2; --3
2、减法操作：-
select 1 - 0.5; --0.5
3、乘法操作：*
select 1 * 2; --2
4、除法操作：/
select 1 / 2; --0.5
5、取余操作：%
--通过 round 指定精度
select 5 % 2; --1
select 2.3456 % 2; --0.3456
select round(2.3456 % 2,2); --0.35
6、位与操作：&
select 4 & 8; --0
7、位或操作：|
select 4 | 8; --12
8、位异或操作：^
select 4 ^ 8; --12
9、按位取反操作：~
select ~ 4; -- -5
三、逻辑运算
1、逻辑与操作：AND
select 1 where 1 = 1 and 2 = 2; --1
2、逻辑与操作：OR
select 1 where 1 = 1 or 1 = 2; --1
3、逻辑非操作：NOT
select 1 where not 1 = 2; --1
四、条件运算
1、IF 函数：IF
select if(1=2,100,200); --200
2、非空查找函数：COALESCE
--返回参数中的第一个非空值；如果所有值都为 NULL，那么返回NULL。
select coalesce(null,'100',null,'50'); --100
3、条件判断函数：CASE
--3.1 描述：如果 a 等于 b，那么返回 c；如果 a 等于 d，那么返回 e；否则返回 f。
select case 100 when 50 then 'tom' when 100 then 'jack' else 'ketty' end ; --jack
--3.2 描述：如果 a 为 TRUE，则返回 b；如果 c 为 TRUE，则返回 d；否则返回 e。
select case when 1 = 2 then 'tom' when 1 = 1 then 'jack' else 'ketty' end ; --jack
五、数值计算
1、近似函数：round
--说明：返回 double 类型的整数值部分 （遵循四舍五入）
select round(3.1415926); --3
2、指定精度近似函数：round
--说明：返回指定精度 d 的 double类型
select round(3.1415926,4); --3.1416
3、向下取整函数：floor
select floor(3.1415926); --3
4、向上取整函数：ceil
select ceil(3.1415926); --4
5、向上取整函数：ceiling
select ceiling(3.1415926); --4
6、取随机函数：rand
--说明：返回一个 0 到 1 范围内的随机数。如果指定种子 seed，则会得到一个稳定的随机数序列。
select rand(); --0.766571624011412、再执行就变成0.9370576946962733
select rand(10);--0.7304302967434272、再执行还是0.7304302967434272
7、自然指数函数：exp
--说明：返回自然对数 e 的 a 次方
select exp(2); --7.38905609893065
8、以 10 为底的对数函数：log10
--说明：返回以 10 为底的 a 的对数
select log10(100); --2
9、以 2 为底的对数函数：log2
select log2(8); --3
10、对数函数：log
select log(2,1024); --10
11、幂运算函数：pow
select pow(2,6); --64
12、幂运算函数：power
--说明：返回以 a 的 p 次幂，与 pow 功能相同。
select power(2,7); --128
13、平方根函数：sqrt
--说明：返回 a 的 平方根
select sqrt(64); --8
14、二进制函数：bin
select bin(5); --101
15、十六进制函数：hex
select hex(21); --15
select hex('abc'); --616263
16、反转十六进制函数：unhex
select unhex(616263); --'616263'
select unhex('616263');--'616263'
17、进制转换函数：conv
--说明：将数值 num 从 from_base 进制转化到 to_base 进制
select conv(17,10,2); --10001
select conv(17,10,16); --11
18、绝对值函数：abs
select abs(-1); --1
select abs(3.14); --3.14
19、正取余函数：pmod
--说明：返回正的 a 除以 b 的余数
select pmod(9,4); --1
20、正弦函数：sin
select sin(10); -- -0.5440211108893698
21、反正弦函数：asin
select asin(-0.5440211108893698); -- -0.5752220392306202
22、余弦函数：cos
select cos(2); -- -0.4161468365471424
23、反余弦函数：acos
select acos(-0.4161468365471424); --2
24、positive 函数：positive
--说明：返回 a 自身
select positive(-1); -- -1
25、negative 函数：negative
--说明：返回 a 的相反数
select negative(-1); -- 1
六、日期函数
1、UNIX 时间戳转日期函数：fom_unixtime
--说明：转化 UNIX 时间戳（从1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式。
select from_unixtime(1717166095); -- 2024-05-31 14:34:55
2、获取当前 UNIX 时间戳函数：unix_timestamp
select unix_timestamp(); -- 1717127619
select unix_timestamp(date_format(current_timestamp,'yyyy-MM-dd HH:mm:ss')) ; --1717166095
3、日期转 UNIX 时间戳函数：unix_timestamp
select unix_timestamp('2024-05-31 14:34:55'); --1717166095
4、指定格式日期转 UNIX 时间戳函数：unix_timestamp
select unix_timestamp('2024-05-31 14:34:55','yyyy-MM-dd HH:mm:ss') ; --1717166095
5、日期时间转日期函数：to_date
select to_date('2024-05-31 14:34:55'); --2024-05-31
6、日期转年函数：year
select year('2024-05-31 14:34:55'); --2024
7、日期转月函数：month
select month('2024-05-31 14:34:55'); --5
8、日期转天函数：day
select day('2024-05-31 14:34:55'); --31
9、日期转小时函数：hour
select hour('2024-05-31 14:34:55'); --14
10、日期转分钟函数：minute
select minute('2024-05-31 14:34:55'); --34
11、日期转秒函数：second
select second('2024-05-31 14:34:55'); --55
12、日期转周函数：weekofyear
select weekofyear('2024-05-31 14:34:55'); --22
13、日期比较函数：datediff
select datediff('2024-05-31','2024-01-01'); --151
14、日期增加函数：date_add
select date_add('2024-05-01',30); --2024-05-31
15、日期减少函数：date_sub
select date_sub('2024-05-31',30); --2024-05-01
七、字符串函数
1、字符串长度函数：length
select length('zhangsan'); --8
2、字符串反转函数：reverse
select reverse('zhangsan'); --gnijgnaw
3、字符串连接函数：concat
select concat('zhan','gs','an'); --zhangsan
4、带分隔符字符串连接函数：concat_ws
select concat_ws(',','zhan','gs','an'); --zhan,gs,an
5、字符串截取函数：substr | substring
select substr('zhangsan',5); --gsng
select substr('zhangsan',2,3); --ang
select substring('zhangsan',2,4); --angj
7、字符串转大写函数：upper | ucase
select upper('apple'); --APPLE
8、字符串转小写函数：lower | lcase
select lower('APPLE'); --apple
9、去空格函数：trim
select trim(' zhangsan '); --zhangsan
10、左边去空格函数：ltrim
select ltrim(' zhangsan '); --zhangsan
11、右边去空格函数：rtrim
select rtrim(' zhangsan '); -- zhangsan
12、正则表达式替换函数：regexp_replace
--语法：regexp_replace(string A, string B, string C)
--描述：将字符串 A 中的符合 Java 正则表达式 B 的部分替换为 C
select regexp_replace('zhangsan', 'ang|ing', ''); --wj
13、正则表达式解析函数：regexp_extract
--语法：regexp_extract(string subject, string pattern, int index)
--描述：将字符串 subject 按照 pattern 正则表达式的规则拆分，返回 index 指定的字符。
select regexp_extract('football', 'foo(.*?)(ball)', 1); --t
14、URL 解析函数：parse_url
--描述：返回 URL 中指定的部分。partToExtract 的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
select parse_url('https://www.baidu.com/path/p.php?k1=v1&k2=v2#ref1', 'HOST'); --www.baidu.com
select parse_url('https://www.baidu.com/path/p.php?k1=v1&k2=v2#ref1', 'QUERY', 'k1'); --v1
15、JSON 解析函数：get_json_object
select get_json_object('{"name":"wj","age":"29"}', '$.name'); --wj
16、空字符串函数：space
--语法：space(int n) 描述：返回长度为 n 的字符串
select space(3); --
17、重复字符串函数：repeat
select repeat('abc',3); --abcabcabc
18、首字符 ASCII 函数：ascii
--描述：返回字符串 str 第一个字符的 ascii 码
select ascii('abc'); --97
19、左补足函数：lpad
--语法：lpad(string str, int len, string pad) 描述：将 str 用 pad 进行左补足到 len 位
select lpad('abc',10,'#@'); --#@#@#@#abc
20、右补足函数：rpad
select rpad('abc',10,'#@'); --abc#@#@#@#
21、分割字符串函数：split
--语法：split(string str, string pat)
--描述：按照 pat 字符串分割 str，会返回分割后的字符串数组。
select split('ab,cd,ef',','); --["ab","cd","ef"]
22、集合查找函数：find_in_set
--语法：find_in_set(string str, string strList)
--描述：返回 str 在 strlist 第一次出现的位置，strlist 是用逗号分割的字符串。如果没有找到该 str 字符，则返回 0。
select find_in_set('cd','{ab,cd,ef}'); --2
八、窗口函数
create table t_window(
name string,
orderdate date,
cost float
)
row format delimited fields terminated by ','
lines terminated by '\n';
--表数据
--[root@hurys22 opt]# cat t_window.txt
--jack,2015-01-01,10
--tony,2015-01-02,15
--jack,2015-02-03,23
--tony,2015-01-04,29
--jack,2015-01-05,46
--jack,2015-04-06,42
--tony,2015-01-07,50
--jack,2015-01-08,55
--mart,2015-04-08,62
--mart,2015-04-09,68
--neil,2015-05-10,12
--mart,2015-04-11,75
--neil,2015-06-12,80
--mart,2015-04-13,94
--加载本地数据
load data local inpath '/opt/t_window.txt' into table t_window;
--查看表数据
select * from t_window;
1、统计函数：count
select count(name) from t_window; --14
select count(distinct name) from t_window; --4
2、求和函数：sum
select sum(cost) from t_window; --661
3、平均值函数：avg
select avg(cost) from t_window; --47.214285714285715
4、最小值函数：min
select min(cost) from t_window; --10
5、最大值函数：max
select max(cost) from t_window; --94
6、总体方差函数：var_pop
select var_pop(cost) from t_window; --668.8826530612243
select round(var_pop(cost),2) from t_window; --668.88
7、样本方差函数：var_samp
select var_samp(cost) from t_window; --720.3351648351647
8、总体标准差函数：stddev_pop
select stddev_pop(cost) from t_window; --25.862765765888696
9、样本标准差函数：stddev_samp
select stddev_samp(cost) from t_window; --26.839060431303565
10、百分位函数：percentile
描述：求准确的 p 对应的百分位数，p 必须介于 0 和 1 之间，返回类型为 double，但是 col 字段目前只支持整数，不支持浮点数类型。
select percentile(cast(cost as int), 0.5) from t_window; --48
11、百分位函数：percentile
select percentile(cast(cost as int), array(0.2, 0.3, 0.5)) from t_window; --[19.8,28.4,48.0]
12、近似百分位函数：percentile_approx
select percentile_approx(cost,0.5, 5) from t_window; --37.125
13、近似百分位函数：percentile_approx
select percentile_approx(cost,array(0.2, 0.3, 0.5), 5) from t_window; --[12.333333333333334,20.533333333333335,37.125]
14、直方图：histogram_numeric
select histogram_numeric(cost,100) from t_window; --[{"x":10.0,"y":1.0},{"x":12.0,"y":1.0},{"x":15.0,"y":1.0},{"x":23.0,"y":1.0},{"x":29.0,"y":1.0},{"x":42.0,"y":1.0},{"x":46.0,"y":1.0},{"x":50.0,"y":1.0},{"x":55.0,"y":1.0},{"x":62.0,"y":1.0},{"x":68.0,"y":1.0},{"x":75.0,"y":1.0},{"x":80.0,"y":1.0},{"x":94.0,"y":1.0}]
九、聚合函数+over
1、partition by子句
select
name, orderdate, cost,sum(cost) over (partition by month(orderdate))
from t_window
;
--mart,2015-04-13,94,341
2、order by子句、 window子句
--unbounded preceding 起始行、unbounded following 结尾行、
--current row 当前行、1 preceding 前1行、1 following 后1行
select
name, orderdate, cost,
sum(cost) over() as sample1,--所有的cost列相加
sum(cost) over(partition by name) as sample2, --将按名字分组，然后把每一组的cost列相加
sum(cost) over(partition by name order by orderdate) as sample3, --将按名字分组，然后把每一组的cost列相加,根据日期排序
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as sample4,
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as sample5,
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as sample6,
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as sample7
from t_window;
--mart,2015-04-08,62,661,299,62,62,62,130,299
--mart,2015-04-09,68,661,299,130,130,130,205,237
--mart,2015-04-11,75,661,299,205,205,143,237,169
--mart,2015-04-13,94,661,299,299,299,169,169,94
3、ntile子句
--用于将分组数据按照顺序切分成n片，返回当前切片值
select
name, orderdate, cost,
ntile(3) over () as sample1
from t_window;
--mart,2015-04-13,94,1
--neil,2015-06-12,80,1
select
name, orderdate, cost,
ntile(3) over () as sample1,
ntile(3) over (partition by name) as sample2,
ntile(2) over (partition by month(orderdate)) as sample3,
ntile(3) over (partition by name order by cost desc ) as smaple4
from t_window;
--jack,2015-01-08,55,2,3,2,1
--jack,2015-01-05,46,2,2,2,1
--jack,2015-04-06,42,2,2,2,2
--jack,2015-02-03,23,3,1,1,2
--jack,2015-01-01,10,3,1,1,3
4、序号函数row_number rank dense_rank子句
select
name, orderdate, cost,
row_number() over () as t1,
row_number() over (order by name) as t2,
rank() over (order by name) as t3,
dense_rank() over (order by name) as t4
from t_window
;
--jack,2015-01-01,10,14,1,1,1
--jack,2015-02-03,23,12,2,1,1
--jack,2015-01-05,46,10,3,1,1
--jack,2015-04-06,42,9,4,1,1
--jack,2015-01-08,55,7,5,1,1
5、前后函数LAG和LEAD函数
--返回上下数据行的数据
select
name, orderdate, cost,
lag(orderdate,1) over (partition by name order by orderdate) as sample1,
lag(orderdate,1,'1972-01-01') over (partition by name order by orderdate) as sample2,
lead(orderdate,1,'3000-01-01') over (partition by name order by orderdate) as sample3
from t_window
;
--jack,2015-01-01,10,NULL,1972-01-01,2015-01-05
--jack,2015-01-05,46,2015-01-01,2015-01-01,2015-01-08
--jack,2015-01-08,55,2015-01-05,2015-01-05,2015-02-03
--jack,2015-02-03,23,2015-01-08,2015-01-08,2015-04-06
--jack,2015-04-06,42,2015-02-03,2015-02-03,3000-01-01
6、首尾函数first_value和last_value
--first_value取分组内排序后，截止到当前行，第一个值
--last_value取分组内排序后，截止到当前行，最后一个值
select
name, orderdate, cost,
first_value(orderdate) over (partition by name order by orderdate) as time1,
last_value(orderdate) over (partition by name order by orderdate) as time2
from t_window
;
--jack,2015-01-01,10,2015-01-01,2015-01-01
--jack,2015-01-05,46,2015-01-01,2015-01-05
--jack,2015-01-08,55,2015-01-01,2015-01-08
--jack,2015-02-03,23,2015-01-01,2015-02-03
--jack,2015-04-06,42,2015-01-01,2015-04-06
7、分布函数：percent_rank() 和 cume_dist()
--percent_rank() 等级值百分比 某个数值在数据集中的排位作为数据集的百分比值返回，此处的百分比值的范围为 0 到 1。
--此函数可用于计算值在数据集内的相对位置。如班级成绩为例，返回的百分数30%表示某个分数排在班级总分排名的前30%。
--cume_dist() 累计分布值 如果按升序排列，则统计：小于等于当前值的行数/总行数。
--如果是降序排列，则统计：大于等于当前值的行数/总行数。
select
name, orderdate, cost,
rank() over (partition by name order by cost desc ) as r1,
percent_rank() over (partition by name order by cost desc ) as r2,
cume_dist() over (partition by name order by cost desc) as r3
from t_window
;
--jack,2015-01-08,55,1,0,0.2
--jack,2015-01-05,46,2,0.25,0.4
--jack,2015-04-06,42,3,0.5,0.6
--jack,2015-02-03,23,4,0.75,0.8
--jack,2015-01-01,10,5,1,1
--mart,2015-04-13,94,1,0,0.25
--mart,2015-04-11,75,2,0.3333333333333333,0.5
--mart,2015-04-09,68,3,0.6666666666666666,0.75
--mart,2015-04-08,62,4,1,1