PUZZLEUP 2015

newkid

lugionline 发表于 2015-12-3 10:44
比赛题太烦锁了，花了差不多20行M代码

败给你了。贴出来看看？

SQL版的写了没？

lugionline

newkid 发表于 2015-12-3 10:51
败给你了。贴出来看看？

SQL版的写了没？

在家里呢，贴出来就泄露了，不写SQL了，没M简单

〇〇

想不到整数运算比字符快那么多
with a as(select level-1 a from dual connect by level<=3),
e as(select a.a||b.a||c.a||d.a p,a.a b1,b.a b2,c.a b3,d.a b4
from a,a b,a c,a d
where regexp_count(a.a||b.a||c.a||d.a,'2')>regexp_count(a.a||b.a||c.a||d.a,'0')),
g as(select * from(select e.p||f.p p,e.b1||f.b1 b1,e.b2||f.b2 b2,e.b3||f.b3 b3,e.b4||f.b4 b4 ,
power(10,e.b1)+power(10,f.b1)s1,
power(10,e.b2)+power(10,f.b2)s2,
power(10,e.b3)+power(10,f.b3)s3,
power(10,e.b4)+power(10,f.b4)s4
from e,e f)
where instr(b1||b2||b3||b4,'00') not in(1,3,5,7)) ,
h as(select * from
(select e.p||f.p p,e.b1||f.b1 b1,e.b2||f.b2 b2,e.b3||f.b3 b3,e.b4||f.b4 b4 ,
s1+power(10,f.b1)s1,
s2+power(10,f.b2)s2,
s3+power(10,f.b3)s3,
s4+power(10,f.b4)s4
from g e,e f)x
--where substr(s1,1,1)>=substr(s1,3,1) and substr(s2,1,1)>=substr(s2,3,1) and substr(s3,1,1)>=substr(s3,3,1) and substr(s4,1,1)>=substr(s4,3,1)
),
j as (select * from
(select e.p||f.p p,e.b1||f.b1 b1,e.b2||f.b2 b2,e.b3||f.b3 b3,e.b4||f.b4 b4 ,
s1+power(10,f.b1)s1,
s2+power(10,f.b2)s2,
s3+power(10,f.b3)s3,
s4+power(10,f.b4)s4
from h e,e f)x
where substr(s1,1,1)>substr(s1,3,1) and substr(s2,1,1)>substr(s2,3,1) and substr(s3,1,1)>substr(s3,3,1) and substr(s4,1,1)>substr(s4,3,1))
select count(*) from j;

  COUNT(*)
----------
    294879

已用时间:  00: 00: 07.22

----------------

with a as(select level-1 a,level-2 b from dual connect by level<=3),
e as(select a.a||b.a||c.a||d.a p,a.a b1,b.a b2,c.a b3,d.a b4,a.b s1,b.b s2,c.b s3,d.b s4
from a,a b,a c,a d
where regexp_count(a.a||b.a||c.a||d.a,'2')>regexp_count(a.a||b.a||c.a||d.a,'0')),
g as(select * from(select e.p||f.p p,e.b1||f.b1 b1,e.b2||f.b2 b2,e.b3||f.b3 b3,e.b4||f.b4 b4 ,
e.s1+f.s1 s1,
e.s2+f.s2 s2,
e.s3+f.s3 s3,
e.s4+f.s4 s4
from e,e f)
where  instr(b1||b2||b3||b4,'00') not in(1,3,5,7)) ,
h as(select * from
(select e.p||f.p p,e.b1||f.b1 b1,e.b2||f.b2 b2,e.b3||f.b3 b3,e.b4||f.b4 b4 ,
e.s1+f.s1 s1,
e.s2+f.s2 s2,
e.s3+f.s3 s3,
e.s4+f.s4 s4
from g e,e f)x
),
j as (select * from
(select e.p||f.p p,e.b1||f.b1 b1,e.b2||f.b2 b2,e.b3||f.b3 b3,e.b4||f.b4 b4 ,
e.s1+f.s1 s1,
e.s2+f.s2 s2,
e.s3+f.s3 s3,
e.s4+f.s4 s4
from h e,e f)x
where s1>0 and s2>0 and s3>0 and s4>0)
select count(*) from j;

  COUNT(*)
----------
    294879

已用时间:  00: 00: 00.84

newkid

lugionline 发表于 2015-12-3 11:00
在家里呢，贴出来就泄露了，不写SQL了，没M简单

你还怕走光？想太多了吧！这里除了你谁能看得懂M代码？
要是SQL简单还叫比赛吗？咱们从事的是SQL极限运动。要不我个人给你一万PB作为出场费？别人可没有这待遇哟。

newkid

我写的19题的SQL:

WITH c AS (SELECT 'W' c FROM DUAL UNION ALL SELECT 'B' FROM DUAL UNION ALL SELECT '-' FROM DUAL)
,t AS (
SELECT * FROM (
SELECT c
      ,DECODE(SUBSTR(c,1,1),'W',1,0) w1
      ,DECODE(SUBSTR(c,2,1),'W',1,0) w2
      ,DECODE(SUBSTR(c,3,1),'W',1,0) w3
      ,DECODE(SUBSTR(c,4,1),'W',1,0) w4
      ,DECODE(SUBSTR(c,1,1),'B',1,0) b1
      ,DECODE(SUBSTR(c,2,1),'B',1,0) b2
      ,DECODE(SUBSTR(c,3,1),'B',1,0) b3
      ,DECODE(SUBSTR(c,4,1),'B',1,0) b4
FROM (
       SELECT REPLACE(SYS_CONNECT_BY_PATH(c,','),',') c
         FROM c
        WHERE LEVEL=4
        CONNECT BY LEVEL<=4
       )
)
WHERE w1+w2+w3+w4>b1+b2+b3+b4
)
SELECT COUNT(*)
  FROM t t1,t t2,t t3,t t4
WHERE     t1.w1+t2.w1+t3.w1+t4.w1>t1.b1+t2.b1+t3.b1+t4.b1
       AND t1.w2+t2.w2+t3.w2+t4.w2>t1.b2+t2.b2+t3.b2+t4.b2
       AND t1.w3+t2.w3+t3.w3+t4.w3>t1.b3+t2.b3+t3.b3+t4.b3
       AND t1.w4+t2.w4+t3.w4+t4.w4>t1.b4+t2.b4+t3.b4+t4.b4
;

  COUNT(*)
----------
    294879

Elapsed: 00:00:00.61

〇〇

newkid 发表于 2015-12-3 23:39
我写的19题的SQL:

WITH c AS (SELECT 'W' c FROM DUAL UNION ALL SELECT 'B' FROM DUAL UNION ALL SELEC ...

没想到直截了当的解法这么快，光想着在中途剪枝了，这道题中间剪不了多少

〇〇

newkid 发表于 2015-12-3 23:38
你还怕走光？想太多了吧！这里除了你谁能看得懂M代码？
要是SQL简单还叫比赛吗？咱们从事的是SQL极限运动 ...

也可以参照sql解法，加密文件，到活动结束时候揭开

〇〇

不过在我机器上，464楼还是不如462楼第二种快

〇〇

462楼改成递归又慢得不行了，递归层越多越差
with a as(select level-1 a,level-2 b from dual connect by level<=3),
e as(select a.a||b.a||c.a||d.a p,a.a b1,b.a b2,c.a b3,d.a b4,a.b s1,b.b s2,c.b s3,d.b s4
from a,a b,a c,a d
where regexp_count(a.a||b.a||c.a||d.a,'2')>regexp_count(a.a||b.a||c.a||d.a,'0')),
g(p,s1,s2,s3,s4,lv) as(
select p,s1,s2,s3,s4,1 from(select e.p||f.p p,e.b1||f.b1 b1,e.b2||f.b2 b2,e.b3||f.b3 b3,e.b4||f.b4 b4 ,
e.s1+f.s1 s1,
e.s2+f.s2 s2,
e.s3+f.s3 s3,
e.s4+f.s4 s4
from e,e f)
where  instr(b1||b2||b3||b4,'00') not in(1,3,5,7)
union all
select e.p||f.p p,
e.s1+f.s1 s1,
e.s2+f.s2 s2,
e.s3+f.s3 s3,
e.s4+f.s4 s4,lv+1
from g e,e f where lv<=2)
,j as (select * from g
where lv=3 and s1>0 and s2>0 and s3>0 and s4>0)
select count(*) from j;

  COUNT(*)
----------
    294879

已用时间:  00: 00: 14.82

with a as(select level-1 a,level-2 b from dual connect by level<=3),
e as(select a.a||b.a||c.a||d.a p,a.a b1,b.a b2,c.a b3,d.a b4,a.b s1,b.b s2,c.b s3,d.b s4
from a,a b,a c,a d
where regexp_count(a.a||b.a||c.a||d.a,'2')>regexp_count(a.a||b.a||c.a||d.a,'0')),
g(p,s1,s2,s3,s4,lv) as(
select cast('' as varchar(16)),0,0,0,0,0 from dual
--where  instr(b1||b2||b3||b4,'00') not in(1,3,5,7)
union all
select e.p||f.p p,
e.s1+f.s1 s1,
e.s2+f.s2 s2,
e.s3+f.s3 s3,
e.s4+f.s4 s4,lv+1
from g e,e f where lv<=3)
,j as (select * from g
where lv=4 and s1>0 and s2>0 and s3>0 and s4>0)
select count(*) from j ;

  COUNT(*)
----------
    294879

已用时间:  00: 00: 50.28

〇〇

我写递归最快也就这样了
with a as(select level-1 a,level-2 b from dual connect by level<=3),
e as(select a.a||b.a||c.a||d.a p,a.b b1,b.b b2,c.b b3,d.b b4
from a,a b,a c,a d
where a.b+b.b+c.b+d.b>0),
g(p,s1,s2,s3,s4,lv) as(
select cast(p as varchar(16)),b1,b2,b3,b4,1 from e
union all
select e.p||f.p p,
b1+s1,
b2+s2,
b3+s3,
b4+s4,lv+1
from g e,e f where lv<=3 and(least(b1+s1,b2+s2,b3+s3,b4+s4)>lv-3))
select count(*) from g where lv=4 and least(s1,s2,s3,s4)>0;

  COUNT(*)
----------
    294879

已用时间:  00: 00: 06.20