mysql时间序列与窗⼝函数⼀、datetime 格式数据提取
mysql 中已存在列格式为 datetime 格式时，提取其中的年：
季度： quarter
⽉：month
周：week
⽇：day
时：hour
分：minute
秒：second
select year(a.`订购⽇期`) from `订单` a;
改变显⽰格式⽅法：
1、格式化：
SELECT DATE_FORMAT(a.`订购⽇期`,'%Y-%m') from `订单` a;
2、字符串截取
select left(a.`订购⽇期`,10) from `订单` a;
⼆、
向⽇期添加指定时间间隔函数
date_add(date,INTERVAL expr type)
向⽇期减去指定时间间隔函数
date_sub(date,INTERVAL expr type)
返回两个⽇期之间的函数
datediff(date1,date2)
以不同的格式显⽰时间
date_format(date,format)
三、查看连续登录天数
这类问题，⽤窗⼝函数来解决更⽅便些，下⾯先列举下常⽤的窗⼝函数
1、累计计算窗⼝函数
字符串截取不改变原字符串
sum、avg、max、min
sum(...A...) over (B... order by ...C... D1...D2...)
partition by  分组字段
order by      按什么字段排序
2、分区排序窗⼝函数
row_number() over (B... order by ...C...)    1 2 3 4 5
rank() over (B... order by ...C...)          1 1 1 4 5
dense_rank() over (B... order by ...C...)    1 1 1 2 3
3、分组排序
ntile() 不⽀持分组排序 rows ..between ...
ntile() over (B... order by ...C...)    前 10%的⽤户
4、偏移分析窗⼝函数
应⽤场景：解决今天和昨天的数据差
前offset ⾏数据
lag(exp_str,offset,defval) over (B... order by ...C...)
后offset ⾏数据
lead(exp_str,offset,defval) over (B... order by ...C...)
举个例⼦
创建表，字段有 id,long_time
create table user_login(user_id int,login_time date);
插⼊数据
insert into user_login values
(1,'2019-06-01'),
(1,'2019-06-02'),
(1,'2019-06-03'),
(1,'2019-06-06'),
(1,'2019-06-07'),
(1,'2019-06-08'),
(1,'2019-06-11'),
(1,'2019-06-12'),
(2,'2019-06-01'),
(2,'2019-06-02'),
(2,'2019-06-04'),
(3,'2019-06-01'),
(3,'2019-06-02'),
(4,'2019-06-01'),
(5,'2019-06-01'),
(5,'2019-06-02');
1、显⽰出每个⽤户连续登录天数，不去重⽤户
1、对原表增加分区排序窗⼝函数 row_number，按序号分组，时间排序
2、⽤date_sub()函数，登录⽇期减去序号，如果是连续登录⽇期，那么date_sub()函数结果就会⼀致
3、最后，将数据结果按id 和 date_sub()函数分组
SELECT user_id,
date_sub(login_time, INTERVAL rn DAY) AS login_group,  -- 连续登录数据会⼀致
min(login_time) AS start_login_time,
max(login_time) AS end_login_time,
count(login_time) AS continuous_days
FROM
(SELECT user_id,login_time,
row_number () over (PARTITION BY user_id ORDER BY login_time) AS rn    分区排序窗⼝函数，分组、排序FROM
user_login) t
GROUP BY user_id, date_sub(login_time, INTERVAL rn DAY);/2、
2、计算连续出现n次数据问题
成绩连续出现三次
解题思路：
1、偏移分析窗⼝函数，将成绩向上偏移两位
2、计算score列与偏移后成绩的差值，0值即连续的成绩
SELECT id,score,(score - lead_score) as score3 from
(select id,score,lead(score,2) over (order by id) as lead_score from score order by id) a
WHERE  (score - lead_score)=0;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。