vlambda博客
学习文章列表

今天是5月15号，马上就要迎来了居家两个月整的纪念日。封闭越是到尾声，便越是多了一份热烈，少了一份耐心。对未来的期盼过多，不由会觉得当前的日子颇使人感到乏味。

介于上篇文章篇幅太长，和朋友交流了下，以后每篇文章都尽量控制1000-1500字左右，同时学到了一个新的Markdown 软件Typora，对文章排版还挺好用的，本篇文章就是Typora初尝试。这期主要内容是对MySQL的一些常用函数进行一个简单的介绍，MySQL函数主要分为数学函数、字符串函数、日期时间函数、条件判断函数、系统信息函数、窗口函数。

1.  数学函数

-- 示例
select floor(5.9) as '向下取整' -- 5;
select rand() as '随机数' -- 0-1的随机数;
select round(5.35534,2) '四舍五入' -- 四舍五入保留两位小数;
....

2.  字符串函数

常见字符串函数

-- 示例
select concat('my','sql') as '字符串拼接' -- 输出为mysql，常用
select left('mysql',2)  -- 从左侧开始取二位，输出为my;
select right('mysql',2) -- 从右侧开始取二位，输出为ql;
select substring('mysql',2,3) -- 从第二位开始，输出3位，输出为ysq;
select replace('mysql','m','h') -- 用h去代替m，输出为myhql;

3. 时间函数

常见时间函数

-- 示例
select curdate() as '当前日期';
select concat(month(curdate()),'月份') -- 输出当前的月份
select now(); select concat(curdate(),curtime()) -- 当前日期时间
select year(curdate()) -- 当前年

4. 条件判断函数

-- 示例
select if(10>5,10,5) as '最大值';
select case when 10>5 then 10 else 5 end as '最大值';

select case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
else 结果n+1 或者null 
end

5. 系统信息函数

6. 窗口函数

6.1 窗口函数介绍

窗口函数也叫分析函数，处理相对复杂的报表和场景。

-- 语法格式
函数名[expr] over(partition by <要分开计算的组> order by <要排序的列> rows between <数据的行数范围>)
-- 分组(partition by)子句，排序(order by)子句，窗口(rows)子句

sum(a) over(partition by b order by rows between D1 and D2);

-- rows between
rows between 2 preceding and current row -- 取当前行和前面两行
rows between unbounded preceding and current row -- 包括本行和之前所有的行
rows between current row and unbounded following -- 包括本行和之后所有的行
rows between 3 preceding and current row -- 包括本行和前面三行
rows between 3 preceding and 1 following -- 从前面三行和下面一行，总共五行
/* 缺少窗口从句条件，窗口规范默认是rows between unbounded preceding and current row.
当order by和窗口从句都缺失, 窗口规范默认是 
rows between unbounded preceding and unbounded following 
*/

6.2 常用窗口函数

6.2.1 聚合类窗口函数

/* 常用聚合函数
count(),sum(),avg(),max(),min()
*/

普通场景下的聚合函数是多条记录聚合为1条（多到一）；聚合类窗口函数是根据分组（partition by）计算，在不同的分组上分别执行。每条记录都会执行（多对多）。

6.2.2 排序窗口函数

-- row_number() over(...); 给行进行排序1.2.3.4...
-- rank() over(...); 按照大小排序，并列后跳跃，生成的序号有可能不连续;
-- dense_rank() over(...); 按照大小排序，并列后仍顺序排序，生成序号时是连续的;
-- ntile(n) over(partition by a order by b); n值切分的片数，将分组数据切分成n片，后面不支持rows between;

6.2.3 偏移分析函数

lag(exp_str,offset,defval) over(partion by ...order by ...); 

lead(exp_str,offset,defval) over(partion by ...order by ...);
-- exp_str是字段名称，offset是偏移量，即是上1个或上N个(Lag向上取，lead向下取）的值，defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL。

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。

7. 结语

窗口函数的概念相对比较复杂，在实际的应用场景也比较多。在下一篇文章中，笔者会结合多表查询和窗口函数，做一些实战的分享。