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

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 sum3sum(score) over (partition by subject 分区 order by score排序) as sum3max/minover(里) -- 分区排序 写在外面就必须写组函数，一组显示一行数据
分区排序，写在开窗函数内就可以每行数据都显示出来

• 排序开窗函数–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_pricefrom test
)t

3 连续12345

hive的连续开窗函数

4 三种排序（开窗）第几名/前几名/topN

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

6 互相关注（共同好友）

7 炸裂函数

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

9 七日留存