踏踏实实练SQLday1
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的连续开窗函数