使用一条 SQL 计算扑克24点

用一条SQL给出扑克牌24点的计算表达式,PostgreSQL 解法。

题目

题目如下: 《数据库编程大赛:一条SQL计算扑克牌24点

有一张表 cards,id 是自增字段的数字主键,另外有4个字段 c1,c2,c3,c4 ,每个字段随机从 1~10 之间选择一个整数 要求选手使用一条 SQL 给出 24 点的计算公式,返回的内容示例如右图:

其中 result 字段是计算的表达式,只需返回1个解,如果没有解,result 返回null

  1. 24 点的计算规则:只能使用加减乘除四则运算,不能使用阶乘、指数等运算符,每个数字最少使用一次,且只能使用一次,可以使用小括号改变优先级

  2. 只能使用一条 SQL ,可以使用数据库内置函数,但是不能使用存储过程/自定义函数和代码块。

  3. SQL 正确性大家在 NineData 平台 demo 数据库自己验证,或在自己的数据库上验证,组委会评测服务器是 4 核 CPU ,32 GB 内存

  4. 选手个人诚信参赛,不允许提交别人的比赛代码,如果发现有类似代码,工作组以第一个提交的为有效参赛

  5. 每个选手最多提交 3 次比赛代码

  6. 提交的 SQL 不能超过 10 KB大小

作为 MySQL 老司机,NineData 搞的这个比赛暗吹 MySQL 的水平比姜高到不知道哪里去了 —— 为什么这么说呢?

因为 10KB 的大小限制非常猥琐 —— 最快的解法都是质数查表,而这种方式所有解的文本拼接大小大约是 10018 个字符。要想压缩这个表到 10KB 以内,必须要用到一些压缩技巧。

MySQL 是带有 COMPRESS 和 UNCOMPRESS 函数的,而 PostgreSQL 原生是没带的,需要用到 pgsql-gzip 扩展,而这个扩展在 NineData 比赛的平台上是不提供的。

下面是使用 PostgreSQL 的解法:


创建随机测试数据表

CREATE SCHEMA poker24;
DROP TABLE IF EXISTS poker24.cards;
CREATE TABLE poker24.cards AS
SELECT i                   AS id,
       ceil(random() * 10) AS c1,
       ceil(random() * 10) AS c2,
       ceil(random() * 10) AS c3,
       ceil(random() * 10) AS c4
FROM generate_series(1, 1000000) i;

ALTER TABLE poker24.cards ADD PRIMARY KEY (id);

解法

基本思想是是使用质数编码,将所有可能的结果分配唯一主键编号,快速计算 24 点:

EXPLAIN ANALYZE
WITH a(i, result) AS (
    SELECT (split_part(kv, ':', 1))::INTEGER AS i, split_part(kv, ':', 2) AS result
    FROM regexp_split_to_table('152:((1+1)+1)*8,156:(6*2)*(1+1),204:(7+1)*(2+1),228:((1*1)+2)*8,276:(9-1)*(2+1),348:(10+2)*(1+1),140:(4*3)*(1+1),220:(5+1)*(3+1),260:((1+1)+6)*3,340:((1*1)+7)*3,380:(8*3)+(1-1),460:(9+3)*(1+1),580:(10-(1+1))*3,196:((1+1)+4)*4,308:((1*1)+5)*4,364:(6*4)+(1-1),476:(7-(1*1))*4,532:(8+4)*(1+1),644:(9-1)*(4-1),812:((1+1)*10)+4,484:(5*5)-(1*1),572:(5-(1*1))*6,748:(7+5)*(1+1),836:(5-(1+1))*8,676:(6+6)*(1+1),988:(8*6)/(1+1),1196:((1+1)*9)+6,1972:((1+1)*7)+10,1444:((1+1)*8)+8,126:(4*2)*(2+1),198:(2+2)*(5+1),234:(6+2)*(2+1),306:(2+2)*(7-1),342:((2-1)+2)*8,414:((2+1)+9)*2,522:(10-2)*(2+1),150:(3*2)*(3+1),210:((2+1)+3)*4,330:(5+3)*(2+1),390:((2-1)+3)*6,510:(7*3)+(2+1),570:(8*3)*(2-1),690:(9*3)-(2+1),870:(10-(2*1))*3,294:(4+4)*(2+1),462:((2-1)+5)*4,546:(6*4)*(2-1),714:(7-(2-1))*4,798:(4-(2-1))*8,966:(9-(2+1))*4,1218:((2*1)*10)+4,726:(5*5)-(2-1),858:(5-(2-1))*6,1122:(7+5)*(2*1),1254:(5-(2*1))*8,1518:((2+1)*5)+9,1914:(10*2)+(5-1),1014:((2+1)*6)+6,1326:(7-(2+1))*6,1482:(6-(2+1))*8,1794:((2*1)*9)+6,2262:((2+1)*10)-6,1734:((7*7)-1)/2,1938:(8*2)+(7+1),2346:(9*2)+(7-1),2958:((2*1)*7)+10,2166:((2*1)*8)+8,2622:(9*8)/(2+1),3306:((8-1)*2)+10,250:(3+3)*(3+1),350:((3+1)+4)*3,550:(5+3)*(3*1),650:((3-1)+6)*3,850:(7*3)+(3*1),950:((8+1)*3)-3,1150:(9-3)*(3+1),1450:(10-(3-1))*3,490:((3-1)+4)*4,770:(5*4)+(3+1),910:6/(1-(3/4)),1190:(7*4)-(3+1),1330:((3+1)*4)+8,1610:(9-(3*1))*4,2030:(10-4)*(3+1),1430:(6*3)+(5+1),1870:(7+5)*(3-1),2090:(5-(3-1))*8,2530:((3*1)*5)+9,3190:(10*3)-(5+1),1690:(6+6)*(3-1),2210:(7-(3*1))*6,2470:(8-(3+1))*6,2990:((3-1)*9)+6,3770:((3*1)*10)-6,2890:(7-3)*(7-1),3230:(7-(3+1))*8,3910:(9/3)*(7+1),4930:((3-1)*7)+10,3610:((3+1)*8)-8,4370:(9*8)/(3*1),5510:(8/3)*(10-1),5290:(9/3)*(9-1),6670:((10+1)*3)-9,8410:(10+10)+(3+1),686:((4+1)*4)+4,1078:(5*4)+(4*1),1274:((6+1)*4)-4,1666:(7*4)-(4*1),1862:((4*1)*4)+8,2254:(9-(4-1))*4,2842:(10-4)*(4*1),1694:(5*4)+(5-1),2002:6/((5/4)-1),2618:(7*4)-(5-1),2926:(8-4)*(5+1),3542:((4-1)*5)+9,4466:(10-4)*(5-1),2366:((4+1)*6)-6,3094:(7-(4-1))*6,3458:(6-(4-1))*8,4186:(9-(4+1))*6,5278:((4-1)*10)-6,4046:(7-4)*(7+1),4522:(7-(4*1))*8,5474:(7-4)*(9-1),5054:(8-(4+1))*8,6118:(9*8)/(4-1),9338:(10+9)+(4+1),11774:(10+10)+(4*1),2662:(5-(1/5))*5,3146:(6*5)-(5+1),5566:(9-5)*(5+1),7018:((10-5)*5)-1,3718:((5*1)*6)-6,4862:(6*5)-(7-1),5434:(8-(5-1))*6,6578:(9-(5*1))*6,8294:(10-6)*(5+1),7106:(7-(5-1))*8,8602:(9-5)*(7-1),10846:(7*5)-(10+1),7942:((5-1)*8)-8,9614:(9-(5+1))*8,12122:(10+8)+(5+1),11638:(9+9)+(5+1),14674:(10+9)+(5*1),18502:(10+10)+(5-1),4394:((6-1)*6)-6,6422:6/(1-(6/8)),7774:(9-(6-1))*6,9802:(10-6)*(6*1),10166:(9-6)*(7+1),12818:(10+7)+(6+1),9386:(8-(6-1))*8,11362:(9+8)+(6+1),14326:(10-(6+1))*8,13754:(9+9)+(6*1),17342:(10+9)+(6-1),13294:(9+7)+(7+1),16762:(10-7)*(7+1),12274:(8+8)+(7+1),14858:(9-(7-1))*8,18734:(10+8)+(7-1),17986:(9+9)+(7-1),22678:(10-7)*(9-1),13718:(8+8)+(8*1),16606:(9+8)+(8-1),20938:(10-(8-1))*8,135:(3*2)*(2+2),189:(4+2)*(2+2),297:((5*2)+2)*2,459:((7*2)-2)*2,513:(8-2)*(2+2),621:((9+2)*2)+2,783:(10*2)+(2+2),225:(3+3)*(2+2),315:((2+2)+4)*3,495:((5*2)-2)*3,585:((2/2)+3)*6,765:((2/2)+7)*3,855:(8*3)+(2-2),1035:(9-3)*(2+2),1305:((10+3)*2)-2,441:((4*2)-2)*4,693:(5*4)+(2+2),819:(6*4)+(2-2),1071:(7*4)-(2+2),1197:((2+2)*4)+8,1449:(9*2)+(4+2),1827:(10-4)*(2+2),1089:(5*5)-(2/2),1287:(5-(2/2))*6,1683:(7*2)+(5*2),1881:((8+5)*2)-2,2277:((5-2)+9)*2,2871:((5+2)*2)+10,1521:(6/2)*(6+2),1989:((7+2)*2)+6,2223:(8-(2+2))*6,2691:((6/2)+9)*2,3393:(10*2)+(6-2),2601:((7-2)+7)*2,2907:(7-(2+2))*8,4437:((10/2)+7)*2,3249:((2+2)*8)-8,3933:(9*2)+(8-2),4959:(10-2)+(8*2),6003:((9-2)*2)+10,7569:(10+10)+(2+2),375:((3+2)+3)*3,825:((5+2)*3)+3,975:((3-2)+3)*6,1275:((3-2)+7)*3,1425:(8*3)*(3-2),1725:((3+2)*3)+9,2175:(10*3)-(3*2),735:((3+2)*4)+4,1155:((3-2)+5)*4,1365:(6*4)*(3-2),1785:(7-(3-2))*4,1995:(4-(3-2))*8,2415:(9*4)/(3/2),3045:(10*3)-(4+2),1815:(5*5)-(3-2),2145:(5-(3-2))*6,2805:(7*3)+(5-2),3135:(5+3)+(8*2),3795:(9-5)*(3*2),4785:(5-3)*(10+2),2535:((3+2)*6)-6,3315:(7*3)+(6/2),3705:((8+2)*3)-6,4485:(9-(3+2))*6,5655:(10-6)*(3*2),4335:(7+3)+(7*2),4845:(8/3)*(7+2),5865:(9+7)*(3/2),7395:(7-3)+(10*2),5415:(8-(3+2))*8,6555:(9-(3*2))*8,8265:(10+8)+(3*2),7935:(9+9)+(3*2),10005:(10+9)+(3+2),12615:((10-3)*2)+10,1029:((4-2)+4)*4,1617:((5+2)*4)-4,1911:((4*2)-4)*6,2499:(7-4)*(4*2),2793:(8-4)*(4+2),3381:((9-2)*4)-4,4263:((4-2)*10)+4,2541:((5+5)*2)+4,3003:(6*5)-(4+2),3927:(7+5)*(4-2),4389:(5-(4-2))*8,5313:(9-5)*(4+2),6699:(10+4)+(5*2),3549:(6+6)*(4-2),4641:(7-4)*(6+2),5187:(8*6)/(4-2),6279:((4-2)*9)+6,7917:(10-6)*(4+2),6069:((7+7)*2)-4,6783:((7*2)-8)*4,8211:(9+7)+(4*2),10353:((4-2)*7)+10,7581:((4-2)*8)+8,9177:(9-(4+2))*8,11571:(10+8)+(4+2),11109:(9+9)+(4+2),14007:(10-4)+(9*2),17661:((4/10)+2)*10,6171:(5+5)+(7*2),6897:((5/5)+2)*8,8349:((5-2)*5)+9,10527:(5-(2/10))*5,5577:((5-2)*6)+6,7293:(7-(5-2))*6,8151:(6-(5-2))*8,9867:((5/2)*6)+9,12441:((5-2)*10)-6,9537:(7+7)+(5*2),10659:((5*2)-7)*8,12903:(7*5)-(9+2),16269:(10+7)+(5+2),11913:(8*5)-(8*2),14421:(9+8)+(5+2),18183:(10-(5+2))*8,22011:(9-5)+(10*2),27753:(10/5)*(10+2),6591:(6+6)+(6*2),8619:(7-(6/2))*6,9633:(8-(6-2))*6,11661:(9-6)*(6+2),14703:(10+6)+(6+2),12597:(7-(6-2))*8,15249:(9+7)+(6+2),19227:(10-7)*(6+2),14079:(8+8)+(6+2),17043:((6*2)-9)*8,21489:(10-8)*(6*2),20631:((9-6)+9)*2,26013:(9-6)*(10-2),32799:(10+10)+(6-2),16473:(8+7)+(7+2),25143:((10/7)+2)*7,18411:(8-(7-2))*8,22287:((9+7)*2)-8,34017:(10+9)+(7-2),42891:(10-7)*(10-2),20577:((8/2)*8)-8,24909:(9-(8-2))*8,31407:(10+8)+(8-2),30153:(9+9)+(8-2),38019:(10-(9-2))*8,47937:(10+10)+(8/2),58029:(10+9)+(10/2),625:((3*3)*3)-3,875:((3*3)-3)*4,1375:(5*3)+(3*3),1625:(6*3)+(3+3),2125:(7-3)*(3+3),2375:((3+3)-3)*8,2875:(9-(3/3))*3,3625:(10*3)-(3+3),1225:(4*3)+(4*3),1925:((3/3)+5)*4,2275:(6*4)+(3-3),2975:(7-(3/3))*4,3325:(8-4)*(3+3),4025:(9-(4-3))*3,3025:(5*5)-(3/3),3575:(6*5)-(3+3),4675:((5*3)-7)*3,6325:(9-5)*(3+3),7975:(10+5)+(3*3),4225:((6/3)+6)*3,5525:(7*3)+(6-3),6175:((3*3)-6)*8,7475:(9+6)+(3*3),9425:(10-6)*(3+3),7225:((3/7)+3)*7,8075:(8+7)+(3*3),9775:(9-3)*(7-3),9025:8/(3-(8/3)),10925:(9-(3+3))*8,13775:(10+8)+(3+3),13225:(9+9)+(3+3),16675:(10*3)-(9-3),1715:((4+3)*4)-4,2695:((4-3)+5)*4,3185:(6*4)*(4-3),4165:(7-(4-3))*4,4655:((4+3)-4)*8,5635:(9*4)-(4*3),7105:((10-3)*4)-4,4235:(5*5)-(4-3),5005:(5-(4-3))*6,6545:(7+5)+(4*3),7315:(8*4)-(5+3),8855:((5*3)-9)*4,11165:(10/5)*(4*3),5915:(6+6)+(4*3),8645:(8-6)*(4*3),10465:(9-(6-3))*4,13195:(10-4)+(6*3),10115:(7*4)-(7-3),11305:((7-3)*4)+8,13685:(9-7)*(4*3),17255:(10+7)+(4+3),15295:(9+8)+(4+3),19285:(10-(4+3))*8,18515:(9+9)*(4/3),29435:(10*3)-(10-4),7865:((5+5)*3)-6,10285:(7+5)*(5-3),11495:(8-5)*(5+3),13915:(9-(5/5))*3,9295:(6+6)*(5-3),12155:(7+5)*(6/3),13585:(8*6)/(5-3),16445:(9-6)*(5+3),20735:(10+6)+(5+3),17765:(8-5)+(7*3),21505:(9+7)+(5+3),27115:(10-7)*(5+3),19855:(8+8)+(5+3),24035:(9*3)-(8-5),29095:((5/3)*9)+9,36685:(10/5)*(9+3),46255:(10-(10/5))*3,10985:((6-3)*6)+6,14365:(7-(6-3))*6,16055:((6+3)-6)*8,19435:(9+6)+(6+3),24505:((6-3)*10)-6,18785:((7-6)+7)*3,20995:(8+7)+(6+3),25415:(9-6)+(7*3),32045:((6/3)*7)+10,23465:((6/3)*8)+8,28405:(9*8)/(6-3),35815:(10-(8-6))*3,34385:(9*3)-(9-6),43355:(10-6)*(9-3),54665:(3-(6/10))*10,24565:(7+7)+(7+3),27455:((7+3)-7)*8,33235:(9-(7/7))*3,41905:(10-7)+(7*3),30685:((7-3)*8)-8,37145:(9-(8-7))*3,44965:(9-7)*(9+3),56695:(9*3)-(10-7),71485:(10+10)+(7-3),34295:((8+3)-8)*8,41515:(9-8)*(8*3),52345:((10*8)-8)/3,50255:(9-9)+(8*3),63365:(10+9)+(8-3),79895:(10-10)+(8*3),60835:(9+9)+(9-3),76705:((9+9)-10)*3,96715:(9-(10/10))*3,2401:(4*4)+(4+4),3773:((4/4)+5)*4,4459:((4+4)-4)*6,5831:(7-4)*(4+4),6517:(8*4)-(4+4),7889:((9-4)*4)+4,9947:(10*4)-(4*4),5929:(5*5)-(4/4),7007:(5-(4/4))*6,9163:(7-(5-4))*4,10241:(8-5)*(4+4),15631:((10-5)*4)+4,12103:(8+4)*(6-4),14651:(9-6)*(4+4),18473:(10+6)+(4+4),14161:(4-(4/7))*7,15827:(7*4)-(8-4),19159:(9+7)+(4+4),24157:(10-7)*(4+4),17689:(8+8)+(4+4),21413:(9*4)-(8+4),26999:(10-4)*(8-4),41209:((10*10)-4)/4,9317:(5*5)-(5-4),11011:((5+4)-5)*6,14399:(7-(5/5))*4,16093:(4-(5/5))*8,19481:(9-5)+(5*4),24563:(10+5)+(5+4),13013:(6-5)*(6*4),17017:(7+5)*(6-4),19019:((5+4)-6)*8,23023:(9+6)+(5+4),29029:(10-6)+(5*4),22253:(7*5)-(7+4),24871:(8+7)+(5+4),30107:((7-4)*5)+9,37961:((7-5)*10)+4,27797:(5-(8/4))*8,33649:(9-(8-5))*4,42427:(10/5)*(8+4),40733:((9/9)+5)*4,51359:(9-5)*(10-4),64757:((10/5)*10)+4,15379:((6+4)-6)*6,20111:(7-6)*(6*4),22477:(8+6)+(6+4),27209:((6-4)*9)+6,34307:(10+6)*(6/4),26299:(7+7)+(6+4),29393:((6+4)-7)*8,35581:(9+7)*(6/4),44863:((6-4)*7)+10,32851:((6-4)*8)+8,39767:(9-8)*(6*4),50141:((8-6)*10)+4,48139:(9-9)+(6*4),60697:(10-9)*(6*4),76531:(10-10)+(6*4),34391:(7-(7/7))*4,38437:((7+7)-8)*4,42959:((7+4)-8)*8,52003:(9*8)/(7-4),65569:((7/4)*8)+10,62951:(7-(9/9))*4,79373:(10*4)-(9+7),100079:(7-(10/10))*4,48013:((8-4)*8)-8,58121:((8+4)-9)*8,73283:(10-8)*(8+4),70357:(4-(9/9))*8,88711:((9+4)-10)*8,111853:(10+10)+(8-4),107387:(10+9)+(9-4),14641:(5*5)-(5/5),17303:(5*5)-(6-5),30613:(9+5)+(5+5),20449:((5+5)-6)*6,26741:(5*5)-(7-6),29887:(8+6)+(5+5),34969:(7+7)+(5+5),39083:((5+5)-7)*8,59653:(10/5)*(7+5),43681:(5*5)-(8/8),52877:(5*5)-(9-8),66671:(10+5)*(8/5),64009:(5*5)-(9/9),80707:(5*5)-(10-9),101761:(5*5)-(10/10),24167:(5-(6/6))*6,31603:(7+6)+(6+5),35321:((8-5)*6)+6,42757:(9*6)-(6*5),53911:((10-5)*6)-6,41327:(5-(7/7))*6,46189:(8-6)*(7+5),55913:((7-5)*9)+6,51623:((6+5)-8)*8,62491:((8+5)-9)*6,78793:(6*5)/(10/8),75647:((9-6)*5)+9,95381:((9+5)-10)*6,120263:(10+10)*(6/5),73117:(9-7)*(7+5),92191:((7-5)*7)+10,67507:((7-5)*8)+8,81719:((7+5)-9)*8,103037:(10-8)*(7+5),124729:((10-7)*5)+9,157267:((7/5)*10)+10,75449:(8*5)-(8+8),91333:(9*8)/(8-5),115159:((8+5)-10)*8,212773:(10+10)+(9-5),28561:(6+6)+(6+6),41743:(8-6)*(6+6),50531:(6*6)/(9/6),63713:(10*6)-(6*6),66079:(9-7)*(6+6),83317:((10-7)*6)+6,61009:(8*6)/(8-6),73853:((6+6)-9)*8,93119:(10-8)*(6+6),112723:((9-6)*10)-6,108953:((7+7)-10)*6,96577:(8*6)/(9-7),121771:((7+6)-10)*8,116909:(7*6)-(9+9),185861:((10-7)*10)-6,89167:((8-6)*8)+8,107939:(9*8)-(8*6),136097:(8*6)/(10-8),130663:(9+9)*(8/6),164749:((10-8)*9)+6,199433:((9/6)*10)+9,317057:(10+10)+(10-6),192763:((9-7)*7)+10,141151:((9-7)*8)+8,177973:(10*8)-(8*7),215441:(9*8)/(10-7),271643:((10-8)*7)+10,198911:((10-8)*8)+8', ',') AS kv
)
SELECT c.id, c1, c2, c3, c4, result
FROM poker24.cards c LEFT JOIN a a ON a.i =
( CASE c1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c2 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c3 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c4 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END);

当然,这里的字符串长度超过了 10000: 10896 个。我们可以用一些手段来压缩,比如把这个巨长的 CASE 弄成一个 inline 函数,然后再把主键从十进制数字字面值换成十六进制,其实长度就在 10KB 以内了。 不过规则禁止我们使用存储过程,这就要想其他办法了。主要就是如何压缩中间那个长字符串。


压缩优化

当然,这里的字符串长度超过了 10000: 10896 个。所以需要用到额外的压缩功能,来满足题目要求。 Pigsty 原生提供了 pgsql-gzip 扩展:

CREATE EXTENSION IF NOT EXISTS gzip;

然后我们把上面的结果表压缩一下,10018个字符压缩到 7796 个,总长度 8796,满足题目要求

WITH a(i, result) AS (SELECT (split_part(kv, ':', 1))::INTEGER AS i, split_part(kv, ':', 2) AS result
FROM regexp_split_to_table(encode(gunzip('\x1F8B08000000000000034D5A5B722B2B0CDC4EE278CABC24E0EE7F6157DD2DF0A9CA47860109F468B518576BFFFDFCD4BFFA1B7FAFF5AEE6FFFDF8ABFDBE38F86E65FCF733F1EEA7F1B92DCC7FC5FC86F96DC6FCFDDCF77DC4FB5AFEAE803ACA7F3FE3D5AFC016CF46819DCF5ECE06FCF7D54340390A269F573CAF58FFF75343CD7B60FEFEBBF20CEF6B79F8840575FB11387E5FE3DDCBDDB1F1D9074E38AE409C603E9C81F7D6C3220B6BA5C0C738271C98BFEAB1D8AB96D0F11E2B26D8CB7E25E36D3326D811E8EF09934C2897C0D55DEFB9E1F5766CC0717ABDDF6BE1C4FEFB490B7E4FF4DA61A538E1BC5B98E1B71246C62635B27EFFC28DCD61F576DC5277C86CF48AD1EA1D46F8BBEF7BF1F37E3E742334B4E7B87954C8C7D4BFFDFB6A6F6B8D56FF2AB07043A742B9B596B3A0D3EA9D6EEF57E12E474187910CF327DDCCF736D3ED2F4E7A3BE6EF787EF47ECD747B7BC9ED6DC70E07DDC609C3EF09E8761B2EB7A7C0891385DBF180F713161AE779BDB733B0290CEF6BAB8823A84BBF4FD8587EA7C4658B7E958470538591E4782C0B113634E3251DD524136EB3B06CB809BBAA25ECF81713B1A65CCB4744C0F9BD295EB5B118182BD4F81908A9738FB353C64B6BB24586EC136B26FC1FF69EBFA1E4D3427167D041EFCC00C1F935808DB46DF7FC0ABA5661228D30E8424DC39A15912B2733AC7E1692A7690DC38461C030E978E6BFF05C7F9BDD30E93099EBFD73D061D90D13BEDF7CBF70B2088D487EC6E17EAE823A2C03A53F0A94B1AF48E2C3442419F1802B7644A247EAC58ACFF865FA51E7083F4B246399FF635518DC2B95724B10D94A97D2F1DD06469C1B67025606B082A3D3BE056AECEC33AC6952F33AC1D1B991080E248184302B041D12C2B49B6727E1FAC13CD2CE39B0EFF1151C9DE7971A05475B3C306D2830683DA56684F5CD037F3883C9B6FB95AAE0E8B4898CB47E9F40903ECB090EBACE98F28B42C2541869FB8ADD4C7AE7DEA29CC8BFFBBD46A50DFE908232AD2FC4D8486F44A296B98E4387D26E22D85D339E98E1085C795433161304FFCBA38D991A1E1D890E6D8D763DAA35BEC75163726069089C1F8BB0E18023BBA54633365277518629FA09B35022178F819DA51AADE97E8FE7F04E2F5BC0351266FA4062FA1900562F41D7489F5B8345A4462E1E651044C67520017DCA1E1062638E3383BEB00293AC2335CA56C5F1E45016C6DDBB6AFF86E555B9E61C5F77D16ECD3DCBE3C7428E45580B99ED44B599A0D78E9966214C86590C767A6A042D47EC75AC32E8410961CCDAE8DAAEA599DC6084B08A656A2C568C10EA574F2D82564B4B2E2FEDEC640A8D170DA7625FB868D38758A240DF5E153B76F0B85555CBBF75B3BF3A6CB5692A8D0C4F5371485169A57DADC770189DD8EECF39B88FD612AEFCB302AE264D1EEA3D0FBE97A4F09CFE524D9185FDB8BFB655E5BBC85E664A78733158534E1CA376D878F3149EA0D614AE7CE6A43E99393C8594CDAED4D110ADD869FA4D65D21F1C489B9CDF2D316D17D56964B0C26E7998DA16EB585A561E8A3AEE67032A5CCDE7BAB2B736C0F891ECA56CF6E2E7702BF158E1FCF05987B3C371409542FD06E5B8CF6D4F066523696A91549B45B6FD3E7CB6DA61D13BDF5B8DF79B9363C97BAE7E8BBF04363BD592CFBD1AEB78CB6A39B6A542088DEAB9F8FED392544DBFCF53D5D30E976E0F0E507022554B9DA81713E0F65F4A0D44AA4446A918C1C3FA413DAE58751F369D22673DA02791955621B754B51C631F663164C6362FE8694D8165935A7D1AE3738A39C513498FC356533CE94521AB9209586E3CC287DE884D89B28608CCB0343758B3C101FE81439C7AF7A2C7720A9853A3CBB82D964FDF10823592DAFBFE3ACD6181686830653EB27A28DE6526636B02E8A885B4F2E74CE90D369191082221B51F232162E0EA9D8CFB8F3CEDEDA57484CF73CF33CDF7172F1431D3588515111101CD8E0D220AFA7BEBFD7322266AE51D60C8D4D1EC10F14E07CF764442C40E1A8825494B901DEFD9EF0C55E46A5728B97820891D329E4211B960184F13DBDE08ED7106A2220FC4FE8E25411F1012BD8CAFDA8C234C51D4506AAB98624708980DC25BF41181110985AD826FA651FBDC76109F6719DC993D62294C4AFB1E4F15996929A9CEAD4D66D19289509DC63211C40C2373B38BC9D2D32175722793030B9B5FC9B11A1A5D180DA0F9920566DF269EF6A7008CA2879DACA3276AB4592A7E696035B70B9872D62606102F39504B297601BBD3B24165840B4FBFC953DA26A968C9A38305CF135BA259BB5EEC1822A37B1F4E81D1779BBB1F4244170683A827A6296334EFA925BBAE60664A6326FA1FFA7BE4814ABF846CE089A8F560EE74C2C9C327929B0E249697B9C47D2B73C6C193A066FB506B0971E8D5E60916D1BBCDD3A77386C771CE5E49ADE7AEF37A597A8A0B6026512AE09498AF1AB160C5D5603495C6F14A8CBE269899E7B41247D878859E998CAF65AD36805DFA59D9516BD9C7D11A19A51CE0FD23D6441EBA53F407C6A6CD83E74114EC9D91E94B752EF89B2E0756277A21A3B28D2DD60E5E8720B03CB30BC7EA636783EF49B6941391BD953CD6D24B51C8A5474B426C5335B28C86C8AC6D9DBE9EB70E1467D565519CA25FBBF4C3D9360FEE2D8192CBB24AB13873129120CA54AB871158E28B0AF4C367A2522B74D7633707A3EC18677DEC42466CA92A98FE78B716C4B2321388172469DE7BB2AD2C70959E104753718A568E82254679695BA5C5D36651D2585D93C7B1A6B52CAFF32BA92052573239FABD0C041936F76C9E2CD8960ACE226DC4C98A7765A79F921AA5AE9F4DB23645259BFB9F22C48A587D4C9C2EF91E31B4525F58693288665877C094EB61E5947159F505798D5571146554B23BA46574ABF51E4F7B6845C1B63EA79C865118FC0F8B295B5818E166084B6C2F159E53866864952A23109258BB03B2E6F77C50812BC8B6EFB658D6030C582450377931B1E6797EBA4AE064B1D24D466750DAB92100E50B0F343B6DB8064E31978C054693E81E558277A594714A31D65452C841A9836AB636162B548B1B2BBE185C7F3A596CD6624AC5D51D29405E66C48C519A65779C7A3990953756057A4AA89D7D447723AADA9995FDEDBD7D0B2D66CC2D1A419CA14506F71E29D2F3F2C7AC7D0B2DB6EAF56B558745E6A045982194B147FBA7D0524F4B034C529EF95E056B149C5A33E765C530FF7BE3782B78C7C37A0C90D9ED14F47EFA9EF94F61A5E93B356565E588FB3F54090A22F15858072F495110029938F01CFFF4BA2E57C268B4F76E790120FF0C9209CA808FA2BC794FAEF4C8E9D1D87ECB77D6D57E3D46A9C6A26F472AFAE561AAA21939933467E93A03C7596C27E4D3CD98AE55EC82D0C745017C76908F03CB496B5412199065B865C3AAF3D45EB7DDBAE49A54C5B106FB7B8C64AB327524F415DDC5B2E6151D54D52ECE0FBAC01A095ED64525C492363EABADB49A9E0B491FE6C4E85FCF7167D1ADB91D224296178C68D9211EA64DB2435B7995C1A0A041703BC0EB8F60E0DC9088861635D2658941F0A3EF5C76A886E6F818768097825B99DA214D2D5D93FDDF7A54B9092956EC54072D9B346CC2A7966DB58959F83069A84FE4E1210E2D8D5A4FD0D3CDCB49F7F5F5FD56CEA7F91F0DB39D289B3D2A7C9DF7D9A3673C7B465EF5C2C0F2BF93D655E6DF59F9B8259E4472F24E7B91AA8724CFDE255AF87D535BCBC49059C16492DED847D0D0E75EBB332235A48BED356837DE751179C2236937C6B23E5CF575AD040DE4F45FF461BEDB70C8EEA8FC6446D0378C16C8F248AF0C5A000F223181C16AD57F6600177BFFBACBF1DC394BF17573426DE4AC8A93D8652E1BDB6F96D04DE6849C7D427BF2DB889CA922C784EB83811AD6ECA4AAB867549A90212C667B984E4043F5BF9FC0ECD2D482B0A86252301DFFF617EB21F6AFCC7815554E2BEBDB98D076D3D557610813913C3E339D22C268CF8E68AD2879BCFF0D428F1B6E12E8CF48481DBA98C14B3526B6FAE5F65CE256E7C13A0ECCC59B818D29EC69F71EA40109B20350D7EEA50574BD27E9B5E98924AFFAA1BC434857DAA8071FA8A79A3896EE3AD53DB75AFAF922E9409E1AF179B9A1962D32ACCC7E0D459DA86CA10723261896F1A24520BA2828C0E8B245AE429BFD658B12347D5DB6A84921BB9F02B33812FDD3BE7738943D6A03E58289909FD1B787D13ACC2A13193750C99F03664294E96B565793980089BEB2A05318678470B02EEBC65D1453A85FF660DC76273775DA16E51324B7DEC65086DCE477524FA4694165FA411ACA09A813B92366485B14F6DB514C998D974B2B8175904CC2FB0A2A7DBE99DB753164B7979D732B4416430479EEE310551D7FB421FE4E60A5B583B9765EFD7CF6F9B81925621F3AA5F2149CDBF296E9EA0B7ECB16D5F3BCD19287FD19FA7EACD4DA00795E89B51899F2244C96DF8C464FF2CC651F4640A3DF126B69385E8D499940CCD8B8EA0A83ABC658ECEF293A3F1C4511AD6788E8DBF7F47970867BB452D90892469C8FF0515A0FCE70127A6D85F23EEBA21EF6FAC5198EC559362D90C81A8C6BE97E0E6751530EE853DF3E12FBACF1D6411561D2DE66EAED3FDA373AE75826D1F0943E3277E529530786E075CB54C41F0CC36918BC22DD44F2B05CD305E7C80E6D86A5FAEDD01818D120C2E7E3288CD63CE25297CC439889BB81E037FD9F1686991021B5BEADD54E988195335D238A70975FFA194166A1E4100A32EF8C3F18D16D403C648CF9FCCA41A44568AC75638CAB7A94A51B3E1AD985572394C3F0B1A85CDFCE1A691C15D6D715BD3E0B2568CD8B310899B707EBAE890D61279CC3472917AB612A3401E52E63C880734EAFDF31F806F8E8CA58FFB83EBF053E010C325FB073EB7215110DF912190CBF6CDC17B22B7A5BD7E598705E9FB0A261906805628C38BF30ACFC4E8365C66B0A610853D1A26F54965986A647B37BAEC211291EC58BF76C50FCC141C228D37CCCECE5054FDBF2EE0DCB1029B80C2ECD6FA420658D6D409D87407053BBD57D814D491C7D4EA29F6512AFE874944296F11B55ADA895660053540DF069AA1A90AFCB249B8D1741F30019AFC01865795F13A5298A6BEF372349522BF8C93E9650F047533DE73FC1BFC96697F9F77EE60FCCADCED18FE539127413D0E1E4E03B7C1F3C6656E5B2BC8A21672AEFBC6A71FCD687252FCFC360F0CAE0139B8786302913922B649B2894F59FDB1748AAB1F3D68FCB92F296E04DFD40959C164932EFBD2476020231F9E903317A41C0792332B979302A743DCBEBDDAB34ACCD789725F4CBA238229116B84435E8BCCABE3AB969945FF77E9AA80583E11A68A473D7FD2953507BD5B28472FCCE2178DE3F772C2CBDE8D3A6EBFCF3FBB3A7CA4B438D697B28A9728BF637D9F6F0E250B1218A1B8D8FE715143693F282879EB45C12F83F3248B10120270000'), 'escape'), ',') AS kv)
SELECT c.id, c1, c2, c3, c4, result FROM poker24.cards c LEFT JOIN a a ON a.i =
(CASE c1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c2 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c3 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c4 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END);

结果

在本地 M1 Macbook Pro 上单核执行时间大约是 0.58 秒,比第一名 0.67s 稍微快一点。

当然,因为 NineData 上面那个 PostgreSQL 没有 gzip 扩展,所以我也没用他们的平台(4c 32G)去提交成绩。

 Merge Right Join  (cost=118104.17..768224.17 rows=5000000 width=68) (actual time=457.485..555.265 rows=1000000 loops=1)
   Merge Cond: (((split_part(kv.kv, ':'::text, 1))::integer) = ((((CASE c.c1 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END * CASE c.c2 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c3 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c4 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END)))
   ->  Sort  (cost=62.33..64.83 rows=1000 width=64) (actual time=0.851..0.872 rows=566 loops=1)
         Sort Key: ((split_part(kv.kv, ':'::text, 1))::integer)
         Sort Method: quicksort  Memory: 59kB
         ->  Function Scan on regexp_split_to_table kv  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.491..0.654 rows=566 loops=1)
   ->  Sort  (cost=118041.84..120541.84 rows=1000000 width=36) (actual time=456.629..494.693 rows=1000000 loops=1)
         Sort Key: ((((CASE c.c1 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END * CASE c.c2 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c3 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c4 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END))
         Sort Method: external sort  Disk: 56760kB
         ->  Seq Scan on cards c  (cost=0.00..18384.00 rows=1000000 width=36) (actual time=0.028..213.760 rows=1000000 loops=1)
 Planning Time: 0.363 ms
 Execution Time: 581.782 ms

以上就是使用 PostgreSQL 一条SQL计算扑克牌24点的解法。

其实,如果在用上并行优化也许还能再快点,然后 PostgreSQL 还有一种其他数据库做不到的解法。那就是直接把这个查表动作封装成一个扩展,然后用C语言直接暴露存储过程给 SQL 调用。这样就能把这个计算过程优化到极致了。当然,这种我们也懒得折腾了。


Last modified 2024-06-25: refactor software & applet (405c5ea)