SELECT DISTINCT t1.imsi, to_char(t1.logoncreationdate,'YYYY-MM-DD') AS loginDate
FROM data_login_log t1
WHERE EXISTS ( SELECT t2.mobile, to_char(t2.cdate,'YYYY-MM-DD') cdate, t2.signstatus
FROM u_state t2
WHERE t2.signstatus=1 AND t2.mobile=t1.imsi
AND to_char(t2.cdate,'YYYY-MM-DD') < to_char(t1.logoncreationdate,'YYYY-MM-DD') );
-- 我有一SQL语句如上所示,但我想查询以上结果中,每个imsi的时间间隔小于7天的记录行
-- 也就是说:一个用户(imsi) 至少在每7天内有一条记录行!-- 呵呵:不知道我表述得清楚不!
FROM data_login_log t1
WHERE EXISTS ( SELECT t2.mobile, to_char(t2.cdate,'YYYY-MM-DD') cdate, t2.signstatus
FROM u_state t2
WHERE t2.signstatus=1 AND t2.mobile=t1.imsi
AND to_char(t2.cdate,'YYYY-MM-DD') < to_char(t1.logoncreationdate,'YYYY-MM-DD') );
-- 我有一SQL语句如上所示,但我想查询以上结果中,每个imsi的时间间隔小于7天的记录行
-- 也就是说:一个用户(imsi) 至少在每7天内有一条记录行!-- 呵呵:不知道我表述得清楚不!
(SELECT DISTINCT t1.imsi, to_char(t1.logoncreationdate, 'YYYY-MM-DD') AS loginDate
FROM data_login_log t1
WHERE EXISTS
(SELECT t2.mobile, to_char(t2.cdate, 'YYYY-MM-DD') cdate, t2.signstatus
FROM u_state t2
WHERE t2.signstatus = 1 AND
t2.mobile = t1.imsi AND
to_char(t2.cdate, 'YYYY-MM-DD') < to_char(t1.logoncreationdate, 'YYYY-MM-DD')))
SELECT imsi, loginDate
FROM (SELECT imsi,
loginDate,
lag(loginDate) over(PARTITION BY imsi ORDER BY loginDate) p_loginDate,
lead(loginDate) over(PARTITION BY imsi ORDER BY loginDate) l_loginDate
FROM tt)
WHERE l_loginDate - loginDate <= 7 OR
loginDate - p_loginDate <= 7;
FROM data_login_log t1
WHERE EXISTS ( SELECT t2.mobile, to_char(t2.cdate,'YYYY-MM-DD') cdate, t2.signstatus
FROM u_state t2
WHERE t2.signstatus=1 AND t2.mobile=t1.imsi
AND to_char(t2.cdate,'YYYY-MM-DD') < to_char(t1.logoncreationdate,'YYYY-MM-DD') )
AND EXISTS(SELECT 1 from data_login_log t3 WHERE t3.imsi=t1.imsi AND ABS(t3.loginDate - t1.loginDate)<=7 );