sql,求3分钟内刷卡次数

Question

数据库：oracle 9i
表: consumetable
字段:cardid(卡号) consumedate(消费时间)  moneys(金额) ，均为varchar2类型
数据量：1000万条左右
需要得到的sql：每张卡在3分钟的刷卡明细（不在3分钟内不需要显示）在线等，谢谢

Answer 1

请参考：SELECT * FROM consumetable
WHERE consumedate BETWEEN SYSDATE AND SYSDATE - 3/(24*60);

Answer 2

luiseradl理解错了，呵呵，和系统时间没关系
同一张卡，其字段值（consumedate）在连续3分钟内刷卡的记录

Answer 3

例如：数据卡号    消费时间                 金额
11      2012-02-01 11:04:04       5.5
11      2012-02-01 11:05:04       3.8
11      2012-02-01 16:08:08       2.8
13      2012-02-01 17:08:08       1.2sql的结果：
11     2012-02-01  11：04：04    5.5
11     2012-02-01  11：05：04    3.8
下面的那条数据因为刷卡时间超过3分钟（16:08:08减11：05：04超过3分钟），不统计

Answer 4

谢谢luoyoummou中肯的意见，没有表达清楚

Answer 5

这个只能在存储过程中判断吧。。如果你是查询特定时间内的话  
select * FROM tf_consumetablet WHERE to_char (consumedate,'yyyymmdd hh:mm')between consumedateand consumedate+3/(24*60)' 这样把consumedate 写入你要查找的时间就可以了。。随即的话就用过程，

Answer 6

bin3509,是想把所有满足上面条件的卡号和记录一次性找出来，你的方法不适用。

Answer 7

希望能给楼主帮助
select a.cardid,a.consumedate from a ,
(select cardid,min(consumedate) begindate from a group by cardid,substr(consumedate,1,10)) b 
where to_date(a.consumedate,'yyyymmddhh24miss') between to_date(b.begindate,'yyyymmddhh24miss') and to_date(b.begindate,'yyyymmddhh24miss') + 3/24/60 and a.cardid = b.cardid
;

Answer 8

bin，怎么写，大概写一下给我，呵呵

Answer 9

select cardid,consumedate,moneys,sum(to_number(moneys)) over(partition by cardid ordr by to_date(consumedate,'yyyy-mm-dd hh24:mi:ss') range between currentrow and 3/480 following ) a from
consumetable
你这解释依然不清楚，我现在每分钟一笔记录，一共六笔，对于第一分钟，它计算的是前三分钟的交易，对于第二分钟呢？计算234还是只计算从4分钟开始（因为23分钟计算过了）？

Answer 10

select cardid,consumedate,moneys,sum(to_number(moneys)) over(partition by cardid order by to_date(consumedate,'yyyy-mm-dd hh24:mi:ss') range between currentrow and 3/480 following ) a from
consumetable

Answer 11

看看是不是楼主希望的效果with consumetable as
(
select '1001' cardid,'2012-02-01 11:04:04' consumedate,'01' moneys from dual
union all
select '1001' cardid,'2012-02-01 11:05:04' consumedate,'02' moneys from dual
union all
select '1001' cardid,'2012-02-01 16:08:08' consumedate,'03' moneys from dual
union all
select '1001' cardid,'2012-02-01 16:10:09' consumedate,'04' moneys from dual
union all
select '1001' cardid,'2012-02-01 16:15:09' consumedate,'05' moneys from dual
union all
select '1002' cardid,'2012-02-01 17:11:09' consumedate,'06' moneys from dual
union all
select '1003' cardid,'2012-02-01 17:17:09' consumedate,'07' moneys from dual
union all
select '1003' cardid,'2012-02-01 17:20:09' consumedate,'08' moneys from dual
)
select distinct
      a.cardid,
      a.consumedate,
      a.moneys
from
     consumetable a,
     (select * 
        from (
             select cardid, consumedate, moneys, rownum rn
             from consumetable 
             ) 
        where rn >1
     ) b,
     (select cardid, count(cardid) ct
       from consumetable 
      group by cardid
     )c
where a.cardid = b.cardid
  and to_date(a.consumedate, 'YYYY/MM/DD HH24:MI:SS') -  to_date(b.consumedate, 'YYYY/MM/DD HH24:MI:SS') <= 3/24/60
  and a.cardid = c.cardid
  and ((c.ct >1 and to_date(a.consumedate, 'YYYY/MM/DD HH24:MI:SS') - to_date(b.consumedate, 'YYYY/MM/DD HH24:MI:SS') <> 0)
        or  c.ct = 1)
order by moneys

Answer 12

order by 是随便写的，改成下面这样，或者不要order by cardid

Answer 13

修正一下with consumetable as
(
select '1001' cardid,'2012-02-01 11:04:04' consumedate,'01' moneys from dual
union all
select '1001' cardid,'2012-02-01 11:05:04' consumedate,'02' moneys from dual
union all
select '1001' cardid,'2012-02-01 16:08:08' consumedate,'03' moneys from dual
union all
select '1001' cardid,'2012-02-01 16:10:09' consumedate,'04' moneys from dual
union all
select '1001' cardid,'2012-02-01 16:15:09' consumedate,'05' moneys from dual
union all
select '1002' cardid,'2012-02-01 17:11:09' consumedate,'06' moneys from dual
union all
select '1003' cardid,'2012-02-01 17:17:09' consumedate,'07' moneys from dual
union all
select '1003' cardid,'2012-02-01 17:20:09' consumedate,'08' moneys from dual
)
select distinct
      a.cardid,
      a.consumedate,
      a.moneys
from
     consumetable a,
     consumetable b,
     (select cardid, count(cardid) ct
       from consumetable 
      group by cardid
     )c
where a.cardid = b.cardid
  and to_date(a.consumedate, 'YYYY/MM/DD HH24:MI:SS') -  to_date(b.consumedate, 'YYYY/MM/DD HH24:MI:SS') <= 3/24/60
  and a.cardid = c.cardid
  and ((c.ct >1 and to_date(a.consumedate, 'YYYY/MM/DD HH24:MI:SS') - to_date(b.consumedate, 'YYYY/MM/DD HH24:MI:SS') <> 0)
        or  c.ct = 1)
order by cardid

Answer 14

<= 3/24/60这个写法是包含了3分钟，
如果不包含3分钟就改成下面这样就行了
< 3/24/60

Answer 15

select a.cardid,a.consumedate from a ,
(select cardid,min(consumedate) begindate from a group by cardid,substr(consumedate,1,10)) b  
where to_date(a.consumedate,'yyyymmddhh24miss') between to_date(b.begindate,'yyyymmddhh24miss') and to_date(b.begindate,'yyyymmddhh24miss') + 3/24/60 and a.cardid = b.cardid
;

Answer 16

select * from consumetabl  where consumedate
 between sysdate-1/480 and sysdate+1/480 

Answer 17

with tb as (
select '11' as id, '2012-02-15 08:25:00' as fdate from dual
union
select '11', '2012-02-15 08:29:00' from dual
union
select '12', '2012-02-15 08:22:00' from dual
union
select '12', '2012-02-15 08:28:00' from dual
union
select '13', '2012-02-15 08:21:00' from dual
union
select '13', '2012-02-15 08:28:00' from dual
)
select id, fdate,(fdate - lastdate)*24*60 as miBetween
  from (select id,
               to_date(fdate, 'yyyy-mm-dd hh24:mi:ss') as fDate,
               to_date(lastDate, 'yyyy-mm-dd hh24:mi:ss') as lastDate
          from (--用开窗函数,按ID分组,取出每一条记录与上一条记录的时间差
                select id,
                       fdate,
                       nvl(lag(fdate) over(partition by id order by fdate),--添加处理,每个ID开始的第一记录,比较时间为自己本身
                           fdate) as lastDate
                  from tb))
--结果
ID FDATE        MIBETWEEN
-- ----------- ----------
1 11 2012-02-15 08:25:00 0
2 11 2012-02-15 08:29:00 4
3 12 2012-02-15 08:22:00 0
4 12 2012-02-15 08:28:00 6
5 13 2012-02-15 08:21:00 0
6 13 2012-02-15 08:28:00 7 --添加过滤条件,时间差小于三分钟的不要.
 where fdate - lastdate >= 3/24/60
    or fdate - lastdate = 0;

Answer 18

http://www.itpub.net/thread-1573142-1-1.html  LZ可以看下这个帖子。

Answer 19

With consumetable As
 (Select '1001' cardid, '2012-02-01 11:04:04' consumedate, '01' moneys
    From dual
  Union All
  Select '1001' cardid, '2012-02-01 11:05:04' consumedate, '02' moneys
    From dual
  Union All
  Select '1001' cardid, '2012-02-01 16:08:08' consumedate, '03' moneys
    From dual
  Union All
  Select '1001' cardid, '2012-02-01 16:10:09' consumedate, '04' moneys
    From dual
  Union All
  Select '1001' cardid, '2012-02-01 16:15:09' consumedate, '03' moneys
    From dual
  Union All
  Select '1002' cardid, '2012-02-01 17:11:09' consumedate, '06' moneys
    From dual
  Union All
  Select '1003' cardid, '2012-02-01 17:17:09' consumedate, '07' moneys
    From dual
  Union All
  Select '1003' cardid, '2012-02-01 17:20:09' consumedate, '08' moneys From dual)
Select e.*
  From (Select d.*,
               --滚动统计（累积），用以与正常排序至比较，进而去除vp值为0以后的数据
               Sum(d.vp) over(Partition By d.cardid Order By d.consumedate_v Asc rows Between unbounded preceding And Current Row) rk2
          From (Select c.*,
                       --vp值为1标记前后时间差小于3分钟，反之为1
                       (Select Case When c.consumedate_v - c.consumedate_p < (1 / 24 / 60) * 3 Then 1 Else 0 End From dual) vp
                  From (Select b.*,                               
                               dense_rank() over(Partition By b.cardid Order By b.consumedate_v Asc) rk,--计算正常排序号                       
                               --向前1条记录检索consumedate_v值        
                               lag(b.consumedate_v, 1, b.consumedate_v) over(Partition By b.cardid Order By b.consumedate_v Asc) consumedate_p 
                          From (Select a.*,
                                       to_date(a.consumedate,
                                               'YYYY-MM-DD HH24:MI:SS') consumedate_v
                                  From consumetable a --
                                ) b --
                        ) c --
                ) d --
        ) e
 Where rk = rk2;