一、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