mysql练习题
题目
建表语句
create table datafrog_test1
(userid varchar(20),
changjing varchar(20),
inttime varchar(20)
);
insert into datafrog_test1 values
(1,1001,1400),
(2,1002,1401),
(1,1002,1402),
(1,1001,1402),
(2,1003,1403),
(2,1004,1404),
(3,1003,1400)
(4,1004,1402),
(4,1003,1403),
(4,1001,1403),
(4,1002,1404)
(5,1002,1402),
(5,1002,1403),
(5,1001,1404),
(5,1003,1405);
一、因为场景重复，选场景的第一个访问时间。所以利用group by userid,changjing 再通过min(inttime)进行去重，做到选取场景的第一个访问时间。
select userid, changjing, min(inttime) as inttime from datafrog_test1 group by userid, changjing;
二、在上表基础上利用窗口row_number窗口函数，通过用户id分组，时间顺序排序再排名，方便后面选出前两个场景。
select *, row_number() over(partition by userid order by inttime) as rk
from (select userid, changjing, min(inttime) as inttime from datafrog_test1 group by userid, changjing) t;
三、根据题目要求，筛选rk<=2的条目，按照userid分组，再通过concat和group_concat连接数据。
select concat(t2.userid, '-', group_concat(t2.changjing separator '-'))
from (select *, row_number() over(partition by userid order by inttime) as rk
from (select userid, changjing, min(inttime) as inttime from datafrog_test1 group by userid, changjing) t) t2
where rk<=2
group by userid;
python练习题
题目一
lst = [1,4,2,9,5,4,6,11,13,15,6,3]
i=0
while i<len(lst):
print(lst[i])
if lst[i]%2==0:
i+=2
else:
i+=1
运行结果
涉及到永久循环时，要用while True循环
涉及到对某个序列进行遍历时，使用for循环
想要对循环有更加灵活的控制，使用while循环
