mysql練習(xí)題

題目

建表語句

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);

一、因?yàn)閳鼍爸貜?fù)，選場景的第一個(gè)訪問時(shí)間。所以利用group by userid,changjing 再通過min(inttime)進(jìn)行去重，做到選取場景的第一個(gè)訪問時(shí)間。

select userid, changjing, min(inttime) as inttime from datafrog_test1 group by userid, changjing;

二、在上表基礎(chǔ)上利用窗口row_number窗口函數(shù)，通過用戶id分組，時(shí)間順序排序再排名，方便后面選出前兩個(gè)場景。

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;

三、根據(jù)題目要求，篩選rk<=2的條目，按照userid分組，再通過concat和group_concat連接數(shù)據(jù)。

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練習(xí)題

題目一

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

運(yùn)行結(jié)果

涉及到永久循環(huán)時(shí)，要用while True循環(huán)
涉及到對某個(gè)序列進(jìn)行遍歷時(shí)，使用for循環(huán)
想要對循環(huán)有更加靈活的控制，使用while循環(huán)