求一sql 语句写法

Question

有如下表结构 写一sql语句 实现 查询在8个小时内出现了三次以上的所有记录，就是同一身份证在连续的8个小时内有三条记录以上的所有信息。          身份证                操作时间
1 320114198205071211     200902111603
2 320114198205071211     200903161409
3 32010619770730202X    200903171558操作时间为yyyymmddhh24mi 字段类型 varchar2(12)

Answer 1

如果是固定8小时内的出现次数，比较简单
假设你的数据每天都是在固定8小时内产生的select substr( 操作时间,1,8) 日期,身份证,count(*) 次数
from a
group by substr( 操作时间,1,8),身份证 
having count(*)>3

Answer 2

[Quote=引用 2 楼 hebo2005 的回复:]
如果是固定8小时内的出现次数，比较简单
假设你的数据每天都是在固定8小时内产生的
数据不是固定的8小时内产生的，是1天24小时都有可能产生数据
还有8小时也不是固定的，是保存在另一个表中的一个参数
java 程序读取这个参数 然后才执行sql语句找出在几个小时内频繁出现的记录

Answer 3

没有测试数据，上次ghost把我的分析函数宝典给弄没了。毛估估写了个，仅供参考。SQL> desc test
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
ID       NUMBER       Y                         
IDCARD   VARCHAR2(18) Y                         
OPERTIME VARCHAR2(12) Y                         SQL> 
SELECT t.* FROM test t,
   (SELECT idcard,
      COUNT(*) over (PARTITION BY idcard ORDER BY to_number(opertime)
         RANGE BETWEEN 0 PRECEDING AND 28800 FOLLOWING) total
   FROM test) v
WHERE t.idcard=v.idcard AND v.total>=3;

Answer 4

28800有问题，如果是date型，可以1/3但是一分钟只有60秒，一小时只有60分，这里有点郁闷。

Answer 5

select 身份证 from test 
group by ID 
having To_Date(max(操作时间),'yyyymmddhh24mi')-To_Date(min(操作时间),'yyyymmddhh24mi')<=8

Answer 6

还有点小问题
select 身份证,cout(*) from (
select 身份证 from test 
group by ID 
having To_Date(max(操作时间),'yyyymmddhh24mi')-To_Date(min(操作时间),'yyyymmddhh24mi')<=8)
group by 身份证 having cout(*)>=3

Answer 7

create table QT01
(
  PERID   VARCHAR2(20),
  OPTTIME VARCHAR2(12)
)SELECT *
  FROM QT01 T2
 WHERE T2.PERID IN
       (SELECT PERID
          FROM QT01 T
         WHERE TO_DATE(T.OPTTIME, 'yyyymmddhh24mi') > SYSDATE - INTERVAL '8' HOUR
         GROUP BY PERID
        HAVING COUNT(*) >= 3)
   AND TO_DATE(T2.OPTTIME, 'yyyymmddhh24mi') > SYSDATE - INTERVAL '8' HOUR

Answer 8

用存储过程吧,仅供参考:create or replace package body test_plsql1 isTYPE refcursor IS REF CURSOR;
TYPE typ_varchar_array IS TABLE OF VARCHAR2(12);TYPE type_sfz_query IS TABLE OF VARCHAR2(20); -- Should define in TYPE but not here.function pipe_sfz return type_sfz_query pipelined is 
c_cur refcursor;
v_sfz varchar2(20); 
v_dts typ_varchar_array = null; 
v_count PLS_INTEGER;

begin    
v_dts := new typ_varchar_array();

open c_cur for
select sfz, count(1) from tablename group by sfz;
loop
<<next_sfz>>
fetch c_cur into v_sfz, v_callcount;
exit when c_cur%notfound;
if(v_callcount >= 3) then
select oprDT bulk collect to v_dts from tablename where sfz=v_sfz order by oprDT desc;
v_count := v_dts.count;
for i in 1 .. v_count loop
if (i+2) < v_count then
if (to_date(v_dts(i), 'yyyymmddhh24mi')-to_date(v_dts(i+2), 'yyyymmddhh24mi') < 8/24) then   
 pipe row (v_sfz); 
 goto next_sfz;
end if;
else   
  goto next_sfz;
end if;
end loop;
end if;
end loop;
return;
end pipe_sfz;procedure get_result(o_cur out refcursor) is
begin
  open o_cur for 
     select * from table(case(pipe_sfz as type_sfz_query));
end get_result;end test_plsql1;

Answer 9

猜你的数据量应该很大,怎么做performance都很难达到很理想的状态,建议是不是可以做一些时间分割,比如使用Trigger或者是一个小时或者多长时间做一次.

Answer 10

select distinct cardid from (
select cardid,count(1) over(partition by cardid order by to_date(dttime,'yyyymmddhh24mi') range between interval'8'hour preceding and interval'8'hour following) num from tb
) where num > 3;

Answer 11

11:34:29 tina@PRACTICE> select * from tb;CARDID             DTTIME
------------------ ------------
320114198205071211 200902111603
32010619770730202X 200903171558
320114198205071211 200902111605
32010619770730202X 200903181558
32010619770730202X 200903171658
320114198205071211 200902112003
320114198205071211 200902111903已选择7行。已用时间:  00: 00: 00.01
11:34:33 tina@PRACTICE> select distinct cardid from (
11:34:38   2  select cardid,count(1) over(partition by cardid order by to_date(dttime,'yyyymmddhh24mi') range between interval'8'hour preceding and interval'8'hour following) num from tb
11:34:38   3  ) where num > 3;CARDID
------------------
320114198205071211已用时间:  00: 00: 00.00

Answer 12

--建表
create table testlog(id varchar2(30),opdate varchar2(30));
--数据
insert into TESTLOG (ID, OPDATE)
values ('320114198205071211', '200902111603');
insert into TESTLOG (ID, OPDATE)
values ('320114198205071211', '200903161409');
insert into TESTLOG (ID, OPDATE)
values ('32010619770730202X', '200903171558');
insert into TESTLOG (ID, OPDATE)
values ('320114198205071211', '200903162009');
insert into TESTLOG (ID, OPDATE)
values ('320114198205071211', '200903162109');
insert into TESTLOG (ID, OPDATE)
values ('320114198205071211', '200903202109');
commit;
--测试
SELECT id, opdate
  FROM (SELECT id, opdate,
               to_date(lag(t.opdate, 2, null) over(PARTITION BY t.id ORDER BY t.opdate),'yyyymmddhh24mi') p2,
               to_date(lag(t.opdate, 1, null) over(PARTITION BY t.id ORDER BY t.opdate),'yyyymmddhh24mi') p1,
               to_date(opdate,'yyyymmddhh24mi') pf,
               to_date(lead(t.opdate, 1, null) over(PARTITION BY t.id ORDER BY t.opdate),'yyyymmddhh24mi') f1,
               to_date(lead(t.opdate, 2, null) over(PARTITION BY t.id ORDER BY t.opdate),'yyyymmddhh24mi') f2
          FROM testlog t)
 WHERE abs(pf - p2) < 8 / 24 OR
       abs(f1 - p1) < 8 / 24 OR
       abs(f2 - pf) < 8 / 24;

Answer 13

13:45:26 tina@PRACTICE> select * from tb;CARDID             DTTIME
------------------ ------------
320114198205071211 200902111603
32010619770730202X 200903171558
320114198205071211 200902111605
32010619770730202X 200903181558
32010619770730202X 200903171658
320114198205071211 200902112003
320114198205071211 200902111903
32010619770730202X 200903170758
32010619770730202X 200903172258已选择9行。已用时间:  00: 00: 00.00
13:45:29 tina@PRACTICE> select * from tb order by 1,2;CARDID             DTTIME
------------------ ------------
32010619770730202X 200903170758
32010619770730202X 200903171558
32010619770730202X 200903171658
32010619770730202X 200903172258
32010619770730202X 200903181558
320114198205071211 200902111603
320114198205071211 200902111605
320114198205071211 200902111903
320114198205071211 200902112003已选择9行。已用时间:  00: 00: 00.01
13:45:42 tina@PRACTICE> select distinct cardid from (
13:45:56   2  select cardid,count(1) over(partition by cardid order by to_date(dttime,'yyyymmddhh24mi') range between interval'8'hour preceding and interval'0'minute following) num from tb
13:45:56   3  ) where num > 3;CARDID
------------------
320114198205071211已用时间:  00: 00: 00.01