再问一个有难度的SQL语句，A,B列相差超过20，并且连续3条以上的数据

Question

本帖最后由 fanhui1022 于 2009-08-17 11:10:21 编辑

Answer 1

我完成了2列相差的计算，写了一个函数来实现的：select datetime, sub from (select to_char(datetime,'yyyy-MM-dd hh24:mi') datetime, 
subtract(num1, num2) sub FROM test order by datetime) where sub > 20函数如下：create or replace function subtract(x number, y number)
  return number is rs number;
   max_ number ;
   min_ number ;
  begin
     if(x >y) then
       max_ := x;
       min_ := y;
     else
       max_ := y;
       min_ :=x;
     end if;
     if(max_ >= 300) and (min_ <= 30) then
       rs := 360 + min_ - max_;
     else
       rs := max_ - min_;
     end if;
     return rs;
end subtract;

Answer 2

select TEST.* from  TEST WHERE to_char(datetime,'yyyy-mm-dd hh24')
IN
(
  select A from 
  (
      select AA.*,row_number() over(partition by a,b order by a) rn
      from 
      (
          select test.*,to_char(datetime,'yyyy-mm-dd hh24') A,case when abs(num2-num1)-20>=0 then 1 else 0 end B
          from test
          order by datetime
      )AA
   ) where rn>4)

Answer 3

不要用关键字当列名:datetime 不好意思写错了!

Answer 4

cosio:
SQL嵌套太多，可能会点慢，数据量也挺大的，1000+W数据。有没有更好的办法？

Answer 5

select d.*
  from (select c.*, count(*) over(partition by pp) cnt
          from (select b.*, b.rn - rownum pp
                  from (select a.*, rownum rn
                          from (select t.*, abs(num1 - num2) gap
                                  from test t
                                 order by datetime) a) b
                 where b.gap >= 20) c) d
 where cnt > 4我这个语句，只对表做一次扫描，性能可能应该还不错

Answer 6

这种的SQL嵌套,不是有多大的影响!你可以先试着运行

Answer 7

  select *
 from 
  (
      select AA.*,row_number() over(partition by a,b order by a) rn
      ,count(*) over(partition by a,b) rm
      from 
      (
          select test.*,to_char(datetime,'yyyy-mm-dd hh24') A,case when abs(num2-num1)-20>=0 then 1 else 0 end B
          from test
          order by datetime
      ) AA
   ) where rm>4
哈哈,看到楼上用count(*),我也用一下!上面语句测试PASS!

Answer 8

把没有用的列,踢出! select datetime,num1,num2
 from 
  (
      select AA.*
      ,count(*) over(partition by a,b) rm
      from 
      (
          select test.*,to_char(datetime,'yyyy-mm-dd hh24') A,case when abs(num2-num1)-20>=0 then 1 else 0 end B
          from test
          order by datetime
      ) AA
   ) where rm>4

Answer 9

select d.*
  from (select c.*, count(*) over(partition by pp) cnt
          from (select b.*, b.rn - rownum pp
                  from (select a.*, rownum rn
                          from (select t.*, abs(num1 - num2) gap
                                  from test t
                                order by datatime) a) b
                where b.gap >= 20) c) d
where cnt >= 3 

Answer 10

我把datetime字段改成了datatime

Answer 11

测试通过，结贴了，我将用cosio写的，非常感谢大家！