puzzleup 2018

〇〇 · 发表于 2018-11-8 12:02

solomon_007 发表于 2018-11-8 11:15
SQL> with t as (select level -1 n from dual connect by level

怎么保证是合法的日期

solomon_007 · 发表于 2018-11-8 13:45

〇〇发表于 2018-11-8 12:02
怎么保证是合法的日期

--加个日期格式验证
SQL>  with function fn_valid_date(p_str in varchar2)
  2       return int
  3       is
  4       l_date date;
  5       begin
  6       l_date := to_date(p_str,'ddmmyy');
  7       return 1;
  8       exception when others then
  9       return 0;
10       end;
11       t as (select level -1 n from dual connect by level <=100),
12       d as (select lpad(n,2,'0') dd from t where n between 1 and 31 and substr(n,1,1)<> nvl(substr(n,2,1),'x')),
13       m as (select lpad(n,2,'0') mm from t where n between 1 and 12 and substr(n,1,1)<> nvl(substr(n,2,1),'x')),
14       y as (select lpad(n,2,'0') yy from t where n between 0 and 99 and substr(n,1,1)<> nvl(substr(n,2,1),'x')),
15       h as (select lpad(n,2,'0') hh from t where n between 0 and 23 and substr(n,1,1)<> nvl(substr(n,2,1),'x')),
16       i as (select lpad(n,2,'0') mi from t where n between 0 and 59 and substr(n,1,1)<> nvl(substr(n,2,1),'x')),
17       s (time_num) as (
18          select dd||mm||yy||hh||mi time_num
19          from d,m,y,h,i
20          where instr(dd,substr(mm,1,1))=0 and instr(dd,substr(mm,2,1))=0
21             and instr(dd||mm,substr(yy,1,1))=0 and instr(dd||mm,substr(yy,2,1))=0
22             and instr(dd||mm||yy,substr(hh,1,1))=0 and instr(dd||mm||yy,substr(hh,2,1))=0
23             and instr(dd||mm||yy||hh,substr(mi,1,1))=0 and instr(dd||mm||yy||hh,substr(mi,2,1))=0
24             and fn_valid_date (dd||mm||yy) = 1
25       )
26 select min(abs(a.time_num - b.time_num)),max(abs(a.time_num - b.time_num))
27    from s a,s b
28 where a.time_num <> b.time_num
29  /
MIN(ABS(A.TIME_NUM-B.TIME_NUM) MAX(ABS(A.TIME_NUM-B.TIME_NUM)
------------------------------ ------------------------------
                           9                   1501979184

newkid · 发表于 2018-11-9 03:51

〇〇发表于 2018-11-8 10:44
改成最大的呢

因为日，月，小时的第一位都很有限，所以最大最小是很容易推理出来的。

我还是玩了一把SQL，BITAND真是好东西，速度比加菲猫快：
WITH n as (select to_char(level-1) n,power(2,level-1) b from dual connect by level<=10)
,ddmm as (
select dm,sum(b) b
from (select to_char(date '2001-1-1'+level-1,'ddmm') dm from dual connect by level<=365)
   ,n
where instr(dm,n)>0
group by dm having count(*)=4
)
,hhmi as (
select hhmi,sum(b) b
  from (select to_char(date '2001-1-1'+level/(24*60),'hh24mi') hhmi from dual connect by level<24*60)
   ,n
where instr(hhmi,n)>0
group by hhmi having count(*)=4
)
,ddmmhhmi as (
select dm,hhmi,trunc(log(2,b)) b1,round(log(2,b-power(2,trunc(log(2,b))))) b2
  from (select dm,hhmi,power(2,10)-1-ddmm.b-hhmi.b b from ddmm,hhmi
      where bitand(ddmm.b,hhmi.b)=0
   )
)
select min(str),max(str) from (
select dm||decode(n,1,b1||b2,b2||b1)||hhmi as str
  from ddmmhhmi,(select * from n where n<2)
);

〇〇 · 发表于 2018-11-9 08:34

newkid 发表于 2018-11-9 03:51
因为日，月，小时的第一位都很有限，所以最大最小是很容易推理出来的。

我还是玩了一把SQL，BITAND真 ...

bitand真是好东西

〇〇 · 发表于 2018-11-9 08:35

solomon_007 发表于 2018-11-8 13:45
--加个日期格式验证
SQL>  with function fn_valid_date(p_str in varchar2)
  2       retur ...

12c真是好东西

solomon_007 · 发表于 2018-11-9 09:41

newkid 发表于 2018-11-9 03:51
因为日，月，小时的第一位都很有限，所以最大最小是很容易推理出来的。

我还是玩了一把SQL，BITAND真 ...

BITAND 又学习了！

〇〇 · 发表于 2018-11-9 12:59

本帖最后由〇〇于 2018-11-9 13:01 编辑

newkid 发表于 2018-11-9 03:51
因为日，月，小时的第一位都很有限，所以最大最小是很容易推理出来的。

我还是玩了一把SQL，BITAND真 ...

答案不一样
SQL> set timi on
SQL> WITH n as (select to_char(level-1) n,power(2,level-1) b from dual connect by level<=10)
  2  ,ddmm as (
  3  select dm,sum(b) b
  4    from (select to_char(date '2001-1-1'+level-1,'ddmm') dm from dual connect by level<=365)
  5       ,n
  6    where instr(dm,n)>0
  7  group by dm having count(*)=4
  8  )
  9  ,hhmi as (
10  select hhmi,sum(b) b
11 from (select to_char(date '2001-1-1'+level/(24*60),'hh24mi') hhmi from dual connect by level<24*60)
12       ,n
13    where instr(hhmi,n)>0
14  group by hhmi having count(*)=4
15  )
16  ,ddmmhhmi as (
17  select dm,hhmi,trunc(log(2,b)) b1,round(log(2,b-power(2,trunc(log(2,b))))) b2
18 from (select dm,hhmi,power(2,10)-1-ddmm.b-hhmi.b b from ddmm,hhmi
19       where bitand(ddmm.b,hhmi.b)=0
20       )
21  )
22  select min(str),max(str) from (
23  select dm||decode(n,1,b1||b2,b2||b1)||hhmi as str
24 from ddmmhhmi,(select * from n where n<2)
25  );

MIN(STR)
--------------------------------------------------------------------------------
MAX(STR)
--------------------------------------------------------------------------------
1406782359
2908761543

Elapsed: 00:00:00.59

加菲猫

MIN(ABS(A.TIME_NUM-B.TIME_NUM)) MAX(ABS(A.TIME_NUM-B.TIME_NUM))
------------------------------- -------------------------------
                           9                   1501979184

Elapsed: 00:00:04.39
SQL> /

MIN(ABS(A.TIME_NUM-B.TIME_NUM)) MAX(ABS(A.TIME_NUM-B.TIME_NUM))
------------------------------- -------------------------------
                           9                   1501979184

Elapsed: 00:00:03.74

solomon_007 · 发表于 2018-11-9 13:18

〇〇发表于 2018-11-9 12:59
答案不一样
SQL> set timi on
SQL> WITH n as (select to_char(level-1) n,power(2,level-1) b from du ...

是一样的啊，只是NEWKID找的是最大差值的那两个具体的值。。。

SQL> WITH n as (select to_char(level-1) n,power(2,level-1) b from dual connect by level<=10)
  2  ,ddmm as (
  3  select dm,sum(b) b
  4    from (select to_char(date '2001-1-1'+level-1,'ddmm') dm from dual connect by level<=365)
  5       ,n
  6    where instr(dm,n)>0
  7  group by dm having count(*)=4
  8  )
  9  ,hhmi as (
10  select hhmi,sum(b) b
11 from (select to_char(date '2001-1-1'+level/(24*60),'hh24mi') hhmi from dual connect by level<24*60)
12       ,n
13    where instr(hhmi,n)>0
14  group by hhmi having count(*)=4
15  )
16  ,ddmmhhmi as (
17  select dm,hhmi,trunc(log(2,b)) b1,round(log(2,b-power(2,trunc(log(2,b))))) b2
18 from (select dm,hhmi,power(2,10)-1-ddmm.b-hhmi.b b from ddmm,hhmi
19       where bitand(ddmm.b,hhmi.b)=0
20       )
21  ),
22  s as (
23  select str from (
24  select dm||decode(n,1,b1||b2,b2||b1)||hhmi as str
25 from ddmmhhmi,(select * from n where n<2)
26  ))
27  select min(abs(s1.str - s2.str)),max(abs(s1.str - s2.str))
28 from s s1, s s2
29 where s1.str <> s2.str
30  /
MIN(ABS(S1.STR-S2.STR)) MAX(ABS(S1.STR-S2.STR))
----------------------- -----------------------
                  9             1501979184

〇〇 · 发表于 2018-11-9 13:54

solomon_007 发表于 2018-11-9 13:18
是一样的啊，只是NEWKID找的是最大差值的那两个具体的值。。。

SQL> WITH n as (select to_char(level ...

谢谢

〇〇 · 发表于 2018-11-9 14:01

本帖最后由〇〇于 2018-11-9 14:08 编辑

solomon_007 发表于 2018-11-9 13:18
是一样的啊，只是NEWKID找的是最大差值的那两个具体的值。。。

SQL> WITH n as (select to_char(level ...

这样就不快了
MIN(ABS(S1.STR-S2.STR)) MAX(ABS(S1.STR-S2.STR))
----------------------- -----------------------
                  9             1501979184

Elapsed: 00:00:03.24

改一下连接条件
SQL> 29
29*  where s1.str <> s2.str
SQL> c/</
29*  where s1.str > s2.str
SQL> /

MIN(ABS(S1.STR-S2.STR)) MAX(ABS(S1.STR-S2.STR))
----------------------- -----------------------
                  9             1501979184

Elapsed: 00:00:01.91
SQL> 27
27* select min(abs(s1.str - s2.str)),max(abs(s1.str - s2.str))
SQL> c/abs/
27* select min((s1.str - s2.str)),max(abs(s1.str - s2.str))
SQL> c/abs/
27* select min((s1.str - s2.str)),max((s1.str - s2.str))
SQL> /

MIN((S1.STR-S2.STR)) MAX((S1.STR-S2.STR))
-------------------- --------------------
               9          1501979184

Elapsed: 00:00:01.69

[每日一题] puzzleup 2018

浏览过的版块