行转列问题
https://www.cnblogs.com/yinjw/p/11767282.html
日期函数
https://baijiahao.baidu.com/sid=1608326786755050044&wfr=spider&for=pc
取得‘2008-01-10’(date)其中的年份-月份,两种做法。
1.concat(year(date),'-',month(date))
2.date_format(date,'%y-%m')
date(now()) 等价于 curdate()
获取时间中的时分秒
小时部分: datepart(hour,列名)
分钟部分: datepart(minute ,列名)
秒部分: datepart(second, 列名)
month函数
求下个月过生日的学生id(一种特殊情况,当当前时间为12月份时)
s_id:学生id
birth_date:学生生日
表为student
select s_id from student where
case when month(date(now)) = 12 then month(birth_date) = 1
else month(birth_date) = month(date(now))+1 end;
week函数
获取日期为一年中的第几周
WEEK(date[,mode])
一般情况下mode参数省略就行。
convert
转换类型
https://www.cnblogs.com/phpk/p/10931804.html
case when then else end
https://blog.csdn.net/u013514928/article/details/80969949
if ifnull nullif isnull
substr/substring
https://blog.csdn.net/csdn_0_001/article/details/79496332
concat
https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc
pencentile
-- 获取income字段的top10%的阈值
select percentile(CAST (salary AS int),0.9)) as income_top10p_threshold from table_1;
-- 获取income字段的10个百分位点
select percentile(CAST (salary AS int),array(0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0)) as income_percentiles
from table_1;
留存 留存率
https://zhuanlan.zhihu.com/p/33728880
https://zhuanlan.zhihu.com/p/68158220
eg.
求出次日用户的留存,三日留存和七日留存。
思路:以次日留存举例,使用自连接 取两个天数(通过两次select)相减等于1(利用case when )时,通过count统计用户数
select a.uid,count( distinct case when day2-day1 = 1 then a.uid end )as 次留
from
(select uid,date_format(dayno,'%y%m%d') as day1 from aui
where app_name = 相机 )a
left join
(select uid,data_format(dayno,'%y%m%d') as day2 from aui
where app_name = 相机) b
on a.uid = b.uid
group by a.day1
计算连续登录天数
https://www.jianshu.com/p/77597eadd3cc
优化和索引
https://blog.csdn.net/weixin_44504146/article/details/92737613
cast函数
在合适的条件下转换数据类型
https://www.cnblogs.com/accumulater/p/6149669.html
timestamp 和 datetime 的区别
https://www.cnblogs.com/mxwz/p/7520309.html
pencent_rank,cume_list
https://yq.aliyun.com/articles/593692
lag(expr,n),lead(expr,n)
https://zhuanlan.zhihu.com/p/116012207
FIRST_VALUE(expr),LAST_VALUE(expr)
https://zhuanlan.zhihu.com/p/116012207
业务专题
https://zhuanlan.zhihu.com/p/116012207
SIGN函数
返回数字的正负
sign(4.5) 返回1
sign(0) 返回0