踏踏实实练SQLday1-1连续登录

发布于:2025-02-11 ⋅ 阅读:(35) ⋅ 点赞:(0)

1连续登录

1.1查询连续登录3天以上的用户

在这里插入图片描述
字段名相同不会覆盖 – select a,a,a – 结果有三列

第一步去重

select id,substr(date,1,10) as date 
from test 
group by id,substr(date,1,10)

substr(字段,i,j)截取多少位
在这里插入图片描述

第二步-开窗rownumber,用date减一下,对结果进行分组 – over()

select id,date,date_add(date,
-row_number() over(partition by id order by date)) as date1 
from 第一步子查询

overnumber开窗函数,用date减一下,对结果分组。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

连续–想起123456789–想起用row_number

开窗函数知识图谱

over() 开窗函数
在这里插入图片描述
select普通字段和聚合函数不能一起使用

普通的聚合函数每组(Group by)只返回一个值,而开窗函数则可视为窗口中的每行都返回一个值。
对查询的结果多出一列,这一列可以是聚合值,也可以是排序值。
开窗函数一般就是over()函数,其窗口是由一个OVER子句定义的多行记录。

  • 聚合开窗函数
sum(score) over (partition by subject order by score) as sum3

sum(score) over (partition by subject 分区 order by score排序) as sum3

max/min

over(里) -- 分区排序 写在外面就必须写组函数,一组显示一行数据
分区排序,写在开窗函数内就可以每行数据都显示出来
  • 排序开窗函数–4种(3+1)
rank--1224
.--1223
rownumber--1234
百分比--

第三步

select id,date1,count(*) as day_cnt 
from 第二步子查询 
group by id ,date1 having count(*)>3

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

1.2查询连续登录最大天数用户

在这里插入图片描述

1.3某个用户连续登录天数

注意先where一下这个用户的数据过滤出来…之后步骤同第二步

某个用户最大连续登录天数 max(date)

1.4用户账户余额大于1000的连续天数

在这里插入图片描述

1.5‘连续类’题目思路:rownumber() over() 减一下,在分组count

2 lead/lag使用(股票波峰波谷)

波峰:当天的价格大于前一天和后一天
波谷:当天的价格小于前一天和后一天

select id,ds,price,case 
	when price>lag_price and price>lead_price then '波峰' 
	when price<lag_price and price<lead_price then '波谷' end as price_type
from(
	select id,ds,price,lag(price,1)over(partition by.id order by ds) as lag_price,
	lead(price,1)over(partition by.id order by ds) as lead_price
	from test
)t

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

3 连续12345

hive的连续开窗函数

4 三种排序(开窗)第几名/前几名/topN

5 天/月gmv汇总(聚合开窗)(一步步累计求和)

6 互相关注(共同好友)

7 炸裂函数

8 每一门课大于60分的学生

9 七日留存


网站公告

今日签到

点亮在社区的每一天
去签到