select CARD_NBR,count(distinct trunc(INP_DATE)) from events where INP_DATE>=trunc(ttt)-5 and INP_DATE<trunc(ttt) --时间区间 group by CARD_NBR having count(distinct trunc(INP_DATE)) >=5union select CARD_NBR,count(distinct to_char(INP_DATE,'yyyymm')) from events where INP_DATE>=add_month(sysdate,-4) and INP_DATE<sysdate --时间区间 group by CARD_NBR having count(distinct to_char(INP_DATE,'yyyymm')) >=4
忘了说了,inp_date是数字型的(太阳历) 应该可以更简单
看了2楼的回复,对需求糊涂了。“指定区间内”是指的用户输入的查询开始日期和查询结束日期吗? 那2楼的ttt是指其中一个?另外还想问下楼主:数据有没有可能存在卡号和日期重复的记录。 例如 10001 2007-01-01 10001 2007-01-01 10001 2007-01-02 这样算持续吗?我试着写了一个,不知道可行否?请测试,并告知结果。SQL> select * from event;CARD_NBR INP_DATE -------------------- ---------- a 01-1月 -07 a 02-1月 -07 a 03-1月 -07 a 04-1月 -07 a 05-1月 -07 a 06-1月 -07 a 07-1月 -07 a 10-1月 -07 a 11-1月 -07 a 15-1月 -07 a 20-1月 -07CARD_NBR INP_DATE -------------------- ---------- b 02-1月 -07 b 03-1月 -07 b 04-1月 -07 b 10-1月 -07 b 11-1月 -07已选择16行。 1 select distinct card_nbr from 2 (select card_nbr,inp_date, 3 lead(inp_date,1) over(partition by card_nbr order by inp_date) inp_date1, 4 lead(inp_date,2) over(partition by card_nbr order by inp_date) inp_date2, 5 lead(inp_date,3) over(partition by card_nbr order by inp_date) inp_date3, 6 lead(inp_date,4) over(partition by card_nbr order by inp_date) inp_date4 7 from event) 8 where (inp_date1-inp_date) = 1 9 and (inp_date2-inp_date1) = 1 10 and (inp_date3-inp_date2) = 1 11* and (inp_date4-inp_date3) = 1 SQL> /CARD_NBR -------------------- aLEAD是分析函数,9i支持。 意思是按卡号分组按日期排序后,取当前记录偏移1,2,3,4条的记录的日期 把他们都放在同一行中,然后就可以判断了。
create table t (name varchar2(10),d date); insert into t values('a',to_date('2007-1-1 10:01','yyyy-mm-dd hh24:mi')); insert into t values('a',to_date('2007-1-1 13:04','yyyy-mm-dd hh24:mi')); insert into t values('a',to_date('2007-1-3 10:01','yyyy-mm-dd hh24:mi')); insert into t values('a',to_date('2007-1-4 10:01','yyyy-mm-dd hh24:mi')); insert into t values('a',to_date('2007-1-4 15:21','yyyy-mm-dd hh24:mi')); insert into t values('a',to_date('2007-1-5 10:01','yyyy-mm-dd hh24:mi')); insert into t values('a',to_date('2007-1-9 10:01','yyyy-mm-dd hh24:mi')); insert into t values('a',to_date('2007-1-11 10:01','yyyy-mm-dd hh24:mi')); insert into t values('b',to_date('2007-1-1 10:01','yyyy-mm-dd hh24:mi')); insert into t values('b',to_date('2007-1-2 10:01','yyyy-mm-dd hh24:mi')); insert into t values('b',to_date('2007-1-3 10:01','yyyy-mm-dd hh24:mi')); insert into t values('b',to_date('2007-1-3 12:01','yyyy-mm-dd hh24:mi')); insert into t values('b',to_date('2007-1-4 10:01','yyyy-mm-dd hh24:mi')); select name,min(to_char(d,'yyyy-mm-dd')),max(to_char(d,'yyyy-mm-dd')) from (select name,d,d-rank() over(partition by name order by d)+1 dd from (select distinct name, trunc(d) d from t)) group by name,dd having count(*)>=3;NAME MIN(TO_CHAR(D,'YYYY- MAX(TO_CHAR(D,'YYYY- -------------------- -------------------- -------------------- a 2007-01-03 2007-01-05 b 2007-01-01 2007-01-04
where INP_DATE>=trunc(ttt)-5 and INP_DATE<trunc(ttt) --时间区间
group by CARD_NBR
having count(distinct trunc(INP_DATE)) >=5union select CARD_NBR,count(distinct to_char(INP_DATE,'yyyymm')) from events
where INP_DATE>=add_month(sysdate,-4) and INP_DATE<sysdate --时间区间
group by CARD_NBR
having count(distinct to_char(INP_DATE,'yyyymm')) >=4
应该可以更简单
那2楼的ttt是指其中一个?另外还想问下楼主:数据有没有可能存在卡号和日期重复的记录。
例如
10001 2007-01-01
10001 2007-01-01
10001 2007-01-02
这样算持续吗?我试着写了一个,不知道可行否?请测试,并告知结果。SQL> select * from event;CARD_NBR INP_DATE
-------------------- ----------
a 01-1月 -07
a 02-1月 -07
a 03-1月 -07
a 04-1月 -07
a 05-1月 -07
a 06-1月 -07
a 07-1月 -07
a 10-1月 -07
a 11-1月 -07
a 15-1月 -07
a 20-1月 -07CARD_NBR INP_DATE
-------------------- ----------
b 02-1月 -07
b 03-1月 -07
b 04-1月 -07
b 10-1月 -07
b 11-1月 -07已选择16行。
1 select distinct card_nbr from
2 (select card_nbr,inp_date,
3 lead(inp_date,1) over(partition by card_nbr order by inp_date) inp_date1,
4 lead(inp_date,2) over(partition by card_nbr order by inp_date) inp_date2,
5 lead(inp_date,3) over(partition by card_nbr order by inp_date) inp_date3,
6 lead(inp_date,4) over(partition by card_nbr order by inp_date) inp_date4
7 from event)
8 where (inp_date1-inp_date) = 1
9 and (inp_date2-inp_date1) = 1
10 and (inp_date3-inp_date2) = 1
11* and (inp_date4-inp_date3) = 1
SQL> /CARD_NBR
--------------------
aLEAD是分析函数,9i支持。
意思是按卡号分组按日期排序后,取当前记录偏移1,2,3,4条的记录的日期
把他们都放在同一行中,然后就可以判断了。
如果是参数化的,那只能做存储过程来处理。
两种写法:(具体的希望你能自己研究)
1、分析逻辑,不用分析函数。分组后按时间排序,然后通过CURSOR
一条条取得记录来判断,参数化的连续天数既为循环次数。
2、还是用上面的语句,根据参数判断拼接SQL语句然后执行返回结果集。
insert into t values('a',to_date('2007-1-1 10:01','yyyy-mm-dd hh24:mi'));
insert into t values('a',to_date('2007-1-1 13:04','yyyy-mm-dd hh24:mi'));
insert into t values('a',to_date('2007-1-3 10:01','yyyy-mm-dd hh24:mi'));
insert into t values('a',to_date('2007-1-4 10:01','yyyy-mm-dd hh24:mi'));
insert into t values('a',to_date('2007-1-4 15:21','yyyy-mm-dd hh24:mi'));
insert into t values('a',to_date('2007-1-5 10:01','yyyy-mm-dd hh24:mi'));
insert into t values('a',to_date('2007-1-9 10:01','yyyy-mm-dd hh24:mi'));
insert into t values('a',to_date('2007-1-11 10:01','yyyy-mm-dd hh24:mi'));
insert into t values('b',to_date('2007-1-1 10:01','yyyy-mm-dd hh24:mi'));
insert into t values('b',to_date('2007-1-2 10:01','yyyy-mm-dd hh24:mi'));
insert into t values('b',to_date('2007-1-3 10:01','yyyy-mm-dd hh24:mi'));
insert into t values('b',to_date('2007-1-3 12:01','yyyy-mm-dd hh24:mi'));
insert into t values('b',to_date('2007-1-4 10:01','yyyy-mm-dd hh24:mi'));
select name,min(to_char(d,'yyyy-mm-dd')),max(to_char(d,'yyyy-mm-dd'))
from (select name,d,d-rank() over(partition by name order by d)+1 dd
from (select distinct name, trunc(d) d from t))
group by name,dd
having count(*)>=3;NAME MIN(TO_CHAR(D,'YYYY- MAX(TO_CHAR(D,'YYYY-
-------------------- -------------------- --------------------
a 2007-01-03 2007-01-05
b 2007-01-01 2007-01-04