如下A表 记录用户每次动作 mobile time action re_value(返回值)
13631521739 20120915 1(开通) 0(成功)
15143728915 20120915 1(开通) 0(成功)
15143728915 20120915 1(开通) 2(重复开通)
15143728915 20120918 2(注销) 0(成功)
13631521739 20120925 2(注销) 0(成功)
1. 查询在开通四天之内又注销的用户2.查询在开通四天后又注销的用户
13631521739 20120915 1(开通) 0(成功)
15143728915 20120915 1(开通) 0(成功)
15143728915 20120915 1(开通) 2(重复开通)
15143728915 20120918 2(注销) 0(成功)
13631521739 20120925 2(注销) 0(成功)
1. 查询在开通四天之内又注销的用户2.查询在开通四天后又注销的用户
from (select mobile, time
from ttt
where action = 1
and re_value = 0) a
left join (select mobile, time
from ttt
where action = 2
and re_value = 0) b on a.mobile = b.mobile;
至于4天内外 自己外面包
select ta.mobile from, ta.maxt, tb.maxt from
(select mobile, max(time) maxt, action from a group by mobile where action=1) ta,
(select mobile, max(time) maxt, action from a group by mobile where action=2) tb
where ta.mobile=tb.mobile and round(to_date(tb.maxt,'yyyymmdd')-to_date(ta.maxt,'yyyymmdd')) = 4;
不知道行不行,反正我没试。
with t as(
select '13631521739' as moble,TO_DATE('20120915','YYYYMMDD') as time,'1' as action,'0' as re_value from dual
union
select '15143728915' as moble,TO_DATE('20120915','YYYYMMDD') as time,'1' as action,'0' as re_value from dual
union
select '15143728915' as moble,TO_DATE('20120915','YYYYMMDD') as time,'1' as action,'2' as re_value from dual
union
select '15143728915' as moble,TO_DATE('20120918','YYYYMMDD') as time,'2' as action,'0' as re_value from dual
union
select '13631521739' as moble,TO_DATE('20120925','YYYYMMDD') as time,'2' as action,'0' as re_value from dual
)
--四天以及之内的
SELECT A.MOBLE,A.TIME,B.TIME,TRUNC(B.TIME-A.TIME) AS DAY
FROM T A, T B
WHERE A.MOBLE = B.MOBLE
AND A.RE_VALUE != 2 AND B.RE_VALUE != 2
AND A.ACTION = 1 AND B.ACTION= 2
AND TRUNC(B.TIME-A.TIME)<=4
--四天之后的
SELECT A.MOBLE,A.TIME,B.TIME,TRUNC(B.TIME-A.TIME) AS DAY
FROM T A, T B
WHERE A.MOBLE = B.MOBLE
AND A.RE_VALUE != 2 AND B.RE_VALUE != 2
AND A.ACTION = 1 AND B.ACTION= 2
AND TRUNC(B.TIME-A.TIME)>4
MOBLE TIME TIME DAY
15143728915 15-九月-12 18-九月-12 3
四天之后的是:
MOBLE TIME TIME DAY
1 13631521739 15-九月-12 25-九月-12 10
select X.mobile as "四天之内注销的用户",X.time as "注销时间" from
(
select aaa.time,aaa.mobile from
(select * from A t1 where t1.mobile in( select mobile from A where re_value=0 group by mobile having count(1)>1)) aaa
where action=2
) X ,
(
select bbb.time,bbb.mobile from
(select * from A t1 where t1.mobile in( select mobile from A where re_value=0 group by mobile having count(1)>1)) bbb
where action=1
) Y where X.mobile=Y.mobile and date_format(X.time,'%Y%m%d')-date_format(Y.time,'%Y%m%d')>4 group by X.mobile,Y.time
select distinct(q3.mobile) "四天之内注销的用户",q3.time "注销时间"
from
(select time,mobile from q1 where action=1) q2,
(select time,mobile from q1 where action=2) q3
where q3.mobile=q2.mobile and to_date(q3.time,'yyyyMMdd')-to_date(q2.time,'yyyyMMdd')>4
(
select aaa.time,aaa.mobile from
(select * from A t1 where t1.mobile in( select mobile from A where re_value=0 group by mobile having count(1)>1)) aaa
where action=2
) X ,
(
select bbb.time,bbb.mobile from
(select * from A t1 where t1.mobile in( select mobile from A where re_value=0 group by mobile having count(1)>1)) bbb
where action=1
) Y where X.mobile=Y.mobile and date_format(X.time,'%Y%m%d')-date_format(Y.time,'%Y%m%d')>4