sql常用函数笔记

一、if函数

if（expr, v1, v2）其中：当表达式expr为真时返回v1，否则返回v2。

二、计算率

avg函数在计算某率的问题上比较好用，如某类工作的完成率，完成记为1，未完成记为0，则这一列的平均数即为完成率。

三、计算时长

timestampdiff(单位，time1， time2)，如timestampdiff(second，time1， time2)，time1和time2间隔了多少秒。注：time1小，time2大。不满24小时不记为1天，不满60秒不为1分钟等等。

四、除法计算百分数

concat（round（v1*100，2），“%”）

五、date_format函数

date_format(date,format)，例如date_format（date，‘%Y-%m’），只提取日期中的年月。

六、窗口函数和group by

一起用的时候先groupby 然后在分组后的表的基础上进行开窗操作

1、窗口函数

1、累计求和，sum（‘字段1’） over（partition by ‘字段2’ order by ‘字段3’）按照字段2进行分组，对字段1按照字段3的顺序进行累计求和
2、滑动窗口 sum（‘字段1’） over（partition by ‘字段2’ order by ‘字段3’ rows 6 PRECEDING）按照字段2进行分组，对字段1按照字段3的顺序进行累计求当前行和往前6行的7行之和【FOLLOWING为向后的行】

2、groupby聚合

1、各类聚合函数，sum、avg等
2、with rollup：在groupby分组字段的基础上再进行统计数据
3、coalesce：遇null起别名

# 按名字分组后的money汇总，最后一行为（null，所有name的money总数的总数）
select name, sum(money) as money
from test
group by name
with rollup
# 可以将上述运行出来的null 改掉
select coalesce(name,'总金额'), sum(money) as money
from test
group by name
with rollup

3、group_concat & concat_ws

1、concat_ws(separator, str1, str2, …)
2、group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

select department_name, group_concat(distinct concat_ws(:,id,name) order by id separator ';')

七、 left join

下述代码输出的是table1表中增加一列相同的值作为当前日期

select *
from table1 left join
(select max(date_time) as cur_time from table1) t2
on 1

八、日期处理函数

1、计算次日留存率

• 次日留存率 = （去重表中符合次日留存的条目数目）/（去重表中所有条目数目）
• 去重表是，将表中同一个用户同一个时间的访问进行去重，然后将表进行自连接，将次日进行访问了的数据进行横向连接，即同一个用户 第二个表的日期 = 第一个表的日期加一天，用date_add函数
• DATE_ADD(date,INTERVAL expr type)，例：date_add(date, interval 1 day)，与date相差一天的日期。

2、获取某月的天数

• a、last_day(time) 表示参数日期的最后一天
• b、day(last_day(time)) 最后一天的天即是该月的天数

# submit_time = '2021-01-03 00:00:00'
select day(last_day(submit_time)) as month_days
from table1

九、文本处理函数

1、left(col, num) 返回string字符串左边n个字符

2、right(col, num) 同left

3、substring_index(col, ‘分隔符’, Index)

4、substr(col, 起始位置，长度)