1、
2、
3、排序窗口函数的实现
-- 创建表
Create table Scores (Id int, Score DECIMAL(3,2));
-- 插入数据
insert into Scores (Id, Score) values ('1', '3.5');
insert into Scores (Id, Score) values ('2', '3.65');
insert into Scores (Id, Score) values ('3', '4.0');
insert into Scores (Id, Score) values ('4', '3.85');
insert into Scores (Id, Score) values ('5', '4.0');
insert into Scores (Id, Score) values ('6', '3.65');
-- row_ number() 的实现
select score , (@i:=@i+1) as rank2 from Scores,(select @i:=0) init
order by score desc;
-- dense_rank() 的实现
select
Score,
@i := @i + (@pre <> ( @pre := Score)) Rank1
from
Scores,
(select @i := 0,@pre := -1) init
order by Score desc;
-- rank() 的实现
select a.*, if((lag(score,1) over())=a.score,lag(a.rank2,1) over(),a.rank2) as "rank_sx"
from (
select score , (@i:=@i+1) as rank2 from Scores,(select @i:=0) init
order by score desc)a
4、SQL查询至少连续七天下单的用户
-- 第一步,先排序
select *,row_number() over(partition by id order by date) 排名 from orde;
-- 第二步,求时间差后,得到的时间是否一致。
-- mysql 日期减去数字_MySQL日期加减函数详解
-- https://blog.csdn.net/weixin_32836713/article/details/114802370
select *, left(subdate(date,排名),10) rnk from(select *,row_number() over(partition by id order by date) 排名 from orde)a
-- 第三步,求通过分组求连续登陆的天数值
select id,date,count(*) as t_num from (select *, left(subdate(date,排名),10) rnk from(select *,row_number() over(partition by id order by date) 排名 from orde)a)b GROUP BY id,rnk
-- 第四步,求连续登陆7的id号和第一次登陆的日期。(一个用户有可能两次满足条件,连续登陆7天)
select id , date from(
select id,date,count(*) as t_num from (select *, left(subdate(date,排名),10) rnk from(select *,row_number() over(partition by id order by date) 排名 from orde)a)b GROUP BY id,rnk)c
where c.t_num>6
-- 方法2()
-- 还可以直接进行筛选:
select id,rnk from
(select *, left(subdate(date,排名),10) rnk from (select *,row_number() over(partition by id order by date) 排名 from orde) a) b group by id,rnk having count(*)>6;
-- 下面是文章的来源
-- https://www.niceym.com/34877.html
-- https://www.bbsmax.com/A/8Bz8VbOX5x/
5、hivesql中datediff,date_add和date_sub的用法
hivesql中datediff,date_add和date_sub的用法_香山上的麻雀1008的博客-CSDN博客
https://blog.csdn.net/weixin_32836713/article/details/114802370
6、
7、
8、
9、