SQL 开发，一道测试题

fage090

newkid 发表于 2017-4-18 07:18
改成这样就行：
select * from
(select id,

大师

bell6248

newkid 发表于 2017-4-17 22:24
你的方法是有漏洞的，比如下面这个测试例子，CC就没找出来：

create table t_xyh1 (id number,name va ...

这种反例举得很好。

我的语句也只要改改就可以了，就是复杂了点， 呵呵

测试如下：

SQL> select  id,
  2          name,
  3          to_char(RDateTime, 'yyyymmdd hh24:mi:ss') RDateTime
  4  from
  5  (select id,
  6          name,
  7          RDateTime,
  8          flag,
  9          count(distinct name) over(partition by flag) cnt1,
10          count(name) over(partition by flag) cnt2,
11          count(*) over(partition by flag, name) cnt3
12  from
13  (select id,
14          name,
15          RDateTime,
16          min(id) over(partition by name order by RDateTime range between NUMTODSINTERVAL(2, 'hour') preceding and NUMTODSINTERVAL(2, 'hour') following) flag
17     from t_xyh1))
18  where cnt1 <> cnt2
19    and cnt2 > 1
20    and cnt3 > 1;

        ID NAME                   RDATETIME
---------- ---------------------- -----------------
         4 aa                     20120101 11:11:11
         1 aa                     20120101 12:11:11
         2 aa                     20120101 13:11:11

SQL>


SQL> select id, name, to_char(RDateTime, 'yyyymmdd hh24:mi:ss') RDateTime from t_xyh2;

        ID NAME                   RDATETIME
---------- ---------------------- -----------------
         1 aa                     20120101 10:00:00
         2 bb                     20120101 11:11:11
         3 cc                     20120101 11:50:00
         4 cc                     20120101 13:00:00

SQL>
SQL> select  id,
  2          name,
  3          to_char(RDateTime, 'yyyymmdd hh24:mi:ss') RDateTime
  4  from
  5  (select id,
  6          name,
  7          RDateTime,
  8          flag,
  9          count(distinct name) over(partition by flag) cnt1,
10          count(name) over(partition by flag) cnt2,
11          count(*) over(partition by flag, name) cnt3
12  from
13  (select id,
14          name,
15          RDateTime,
16          min(id) over(partition by name order by RDateTime range between NUMTODSINTERVAL(2, 'hour') preceding and NUMTODSINTERVAL(2, 'hour') following) flag
17     from t_xyh2))
18  where cnt1 <> cnt2
19    and cnt2 > 1
20    and cnt3 > 1;

        ID NAME                   RDATETIME
---------- ---------------------- -----------------
         3 cc                     20120101 11:50:00
         4 cc                     20120101 13:00:00

lastwinner

分析函数就ok，newkid的真简化

workflow263

select distinct t1.id, t1.name,t1.rdatetime
from   t_xyh11 t1,  t_xyh11 t2
where t1.name=t2.name
and abs(t2.rdatetime-t1.rdatetime)*24 <=2
and t1.rdatetime<>t2.rdatetime;

这样也可以吧

newkid

bell6248 发表于 2017-4-18 11:03
这种反例举得很好。

我的语句也只要改改就可以了，就是复杂了点， 呵呵

还是有漏洞，下面例子最后一行漏掉了：

delete t_xyh1;
insert into t_xyh1 values(3,'cc',to_date('20120101 11:50:00','yyyymmdd hh24:mi:ss'));
insert into t_xyh1 values(4,'cc',to_date('20120101 13:00:00','yyyymmdd hh24:mi:ss'));
insert into t_xyh1 values(4,'cc',to_date('20120101 14:00:00','yyyymmdd hh24:mi:ss'));

select  id,
          name,
          to_char(RDateTime, 'yyyymmdd hh24:mi:ss') RDateTime
  from
  (select id,
          name,
          RDateTime,
          flag,
          count(distinct name) over(partition by flag) cnt1,
         count(name) over(partition by flag) cnt2,
         count(*) over(partition by flag, name) cnt3
from
(select id,
         name,
         RDateTime,
         min(id) over(partition by name order by RDateTime range between NUMTODSINTERVAL(2, 'hour') preceding and NUMTODSINTERVAL(2, 'hour') following) flag
    from t_xyh1))
where cnt1 <> cnt2
   and cnt2 > 1
   and cnt3 > 1;

bell6248

newkid 发表于 2017-4-19 06:47
还是有漏洞，下面例子最后一行漏掉了：

delete t_xyh1;

我把你给的2中特殊情况和LZ原先的数据进行了整合进行测试， 测试如下， 你看看是否还是有特殊情况

SQL> select id, name, to_char(RDateTime, 'yyyymmdd hh24:mi:ss') RDateTime from t_xyh1;

        ID NAME                   RDATETIME
---------- ---------------------- -----------------
         1 aa                     20120101 12:11:11
         2 aa                     20120101 13:11:11
         3 aa                     20120102 12:11:11
         4 aa                     20120101 11:11:11
         5 bb                     20120101 11:11:11
         6 bb                     20120102 11:11:11
         7 bb                     20120103 11:11:11
         8 bb                     20120104 11:11:11
        11 aa                     20140101 10:00:00
        12 bb                     20140101 11:11:11
        13 cc                     20140101 11:50:00
        14 cc                     20140101 13:00:00
        23 cc                     20160101 11:50:00
        24 cc                     20160101 13:00:00
        25 cc                     20160101 14:00:00

15 rows selected

SQL>
SQL> select  id,
  2          name,
  3          to_char(RDateTime, 'yyyymmdd hh24:mi:ss') RDateTime
  4  from
  5  (select id,
  6          name,
  7          RDateTime,
  8          flag,
  9          count(distinct name) over(partition by flag) cnt1,
10          count(name) over(partition by flag) cnt2,
11          count(*) over(partition by flag, name) cnt3
12  from
13  (select id,
14          name,
15          RDateTime,
16          least(flag, max(rn) over(partition by name order by RDateTime)) flag
17  from
18  (select id,
19          name,
20          RDateTime,
21          flag,
22          case when flag <= max(id) over(partition by name order by RDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) then null else id end rn
23  from
24  (select id,
25          name,
26          RDateTime,
27          min(id) over(partition by name order by RDateTime range between NUMTODSINTERVAL(2, 'hour') preceding and NUMTODSINTERVAL(2, 'hour') following) flag
28     from t_xyh1))))
29  where cnt1 <> cnt2
30     and cnt2 > 1
31     and cnt3 > 1;

        ID NAME                   RDATETIME
---------- ---------------------- -----------------
         4 aa                     20120101 11:11:11
         1 aa                     20120101 12:11:11
         2 aa                     20120101 13:11:11
        13 cc                     20140101 11:50:00
        14 cc                     20140101 13:00:00
        25 cc                     20160101 14:00:00
        23 cc                     20160101 11:50:00
        24 cc                     20160101 13:00:00

8 rows selected

SQL>

lastwinner

workflow263 发表于 2017-4-18 13:55
select distinct t1.id, t1.name,t1.rdatetime
from   t_xyh11 t1,  t_xyh11 t2
where t1.name=t2.name ...

可以，但是表扫了两次，效率不高

newkid

bell6248 发表于 2017-4-19 14:05
我把你给的2中特殊情况和LZ原先的数据进行了整合进行测试， 测试如下， 你看看是否还是有特殊情况
...

轻易击垮：

delete t_xyh1;
insert into t_xyh1 values(3,'cc',to_date('20120101 11:50:00','yyyymmdd hh24:mi:ss'));
insert into t_xyh1 values(2,'cc',to_date('20120101 13:00:00','yyyymmdd hh24:mi:ss'));
insert into t_xyh1 values(1,'cc',to_date('20120101 14:00:00','yyyymmdd hh24:mi:ss'));


在这个新例子中你的查询漏掉了第一个。
我能猜到你下一步要怎么调整，但是劝你别再钻牛角尖了，这个写法越来越臃肿而且反复扫描数据，比原来的表连接法还低效。

bell6248

newkid  2017-4-19 23:35
壺

delete t_xyh1;

按照LZ提供数据看，列ID的值是按照日期字段升序而递增的，否则就先按照日期字段从小到大排序先得到一个类似的ID字段，之后代码不变