2022-03-11-随堂测试

作业题
1、找出全部夺得3连贯的队伍

WITH tmp as (
SELECT team, (year - row_number() over (partition by team order by year)) num
FROM t1
)
SELECT team
FROM tmp
GROUP BY team, num
HAVING COUNT(*) = 3;

[

](https://blog.csdn.net/weixin_44847293/article/details/109692802)
2、找出每个id在在一天之内所有的波峰与波谷值

select id,time,price,case when price >p1 and price >p2 then ‘波峰’ when price from (
select id,time,price,LAG(price,1,price) over(partition by id order by id) as p1,
LEAD(price,1,price) over(partition by id order by id) as p2
from t2) t3
where (price >p1 and price >p2) or ( price <p1 and price <p2 );

3、写SQL
3.1、每个id浏览时长、步长

select id,sum(timeStep) sum_time,max(rank) sum_step
from (select id, dt,browseid,row_number() over (partition by id order by dt) rank,
(unix_timestamp(dt, ‘yyyy/MM/dd HH:mm’) -
unix_timestamp(nvl(lag(dt) over (partition by id order by dt),dt), ‘yyyy/MM/dd HH:mm’))/60 as timeStep from t3) tmp1
group by id;

3.2、如果两次浏览之间的间隔超过30分钟，认为是两个不同的浏览时间；再求每个id浏 览时长、步长

select firstType,id,(max(unix_timestamp(dt, ‘yyyy/MM/dd HH:mm’))
- min(unix_timestamp(dt, ‘yyyy/MM/dd HH:mm’)))/60 as period,count(id) step
from (select id, dt,browseid,rank,minuxBefore,type,
sum(type) over (partition by id order by dt rows
between unbounded preceding and current row) as firstType
from(select id, dt,browseid,rank,minuxBefore,type
from (select id, dt,browseid,
row_number() over (partition by id order by dt) rank,
(unix_timestamp(dt, ‘yyyy/MM/dd HH:mm’) - unix_timestamp(nvl(lag(dt) over (partition by id order by dt),dt), ‘yyyy/MM/dd HH:mm’))/60 minuxBefore,
case when (unix_timestamp(dt, ‘yyyy/MM/dd HH:mm’)
- unix_timestamp(nvl(lag(dt) over (partition by id order by dt),dt), ‘yyyy/MM/dd HH:mm’))/60 >=30 then 1
else 0
end type
from t3
)t4)t5)t6 group by id,firstType;

备注：请仔细阅读计算规则