數(shù)據(jù)庫面試題：點(diǎn)擊日志表——黑產(chǎn)行為

-- 建表
drop table if exists tencent_video_click;
create table if not exists tencent_video_click(
vuid int,
`date` date, -- 點(diǎn)擊日期
`time` int comment '點(diǎn)擊時間時間戳格式',
platform int default 21
);
select unix_timestamp('2021-03-01 00:00:00'); -- 1614528000
-- 插入造的數(shù)據(jù)
insert into tencent_video_click values
(1, '2021-03-01', 1614528000, 21),
(1, '2021-03-01', 1614528001, 22),
(1, '2021-03-01', 1614528001, 21),
(2, '2021-03-01', 1614528004, 21),
(3, '2021-03-01', 1614528000, 21),
(2, '2021-03-01', 1614528005, 21),
(3, '2021-03-01', 1614528000, 22),
(4, '2021-03-01', 1614528007, 21),
(4, '2021-03-01', 1614528009, 22),
(4, '2021-03-01', 1614528010, 21);

-- 要求：已知同一用戶在移動端連續(xù)點(diǎn)擊兩次時間間隔不高于2秒，則可能產(chǎn)生黑產(chǎn)行為
-- 篩選出21年3月份所有有可能為黑產(chǎn)行為的點(diǎn)擊記錄
/*
分析：
從同一用戶在移動端連續(xù)點(diǎn)擊兩次時間間隔不高于2秒這句話中可以得知 我們需要得到同一用戶在移動端上下兩次點(diǎn)擊時間的數(shù)據(jù)并進(jìn)行關(guān)聯(lián)
    因此需要使用自連接查詢 連接條件是平臺一樣 用戶一樣  篩選條件是兩者時間相減不高于2秒
    然后在此基礎(chǔ)上再加上時間的篩選
*/
select t1.*, t2.time as `第二次點(diǎn)擊` from tencent_video_click as t1 join tencent_video_click as t2
on t1.vuid=t2.vuid and t1.platform=t2.platform
where t2.time-t1.time <= 2 and t2.time-t1.time > 0 and year(t1.date) = 2021 and month(t1.date) = 3;

/*
vuid date time platform 第二次點(diǎn)擊
2021-03-01 1614528000 21 1614528001
2021-03-01 1614528004 21 1614528005
*/

 更多關(guān)于python培訓(xùn)的問題，歡迎咨詢千鋒教育在線名師。千鋒教育擁有多年IT培訓(xùn)服務(wù)經(jīng)驗(yàn)，采用全程面授高品質(zhì)、高體驗(yàn)培養(yǎng)模式，擁有國內(nèi)一體化教學(xué)管理及學(xué)員服務(wù)，助力更多學(xué)員實(shí)現(xiàn)高薪夢想。