数据库编程大赛：一条SQL计算扑克牌24点

12月27日，NineData和云数据库技术社区主办，华为云、火山引擎、开源中国、云和恩墨、TDengine、云猿生数据、DORIS、ITPUB等协办单位和媒体，共同举办了本次《数据库编程大赛》。大赛题目「用一条SQL给出扑克牌24点的计算表达式」。

以下冠军选手郑凌云的参赛情况的介绍：

参赛选手：郑凌云

个人简介：淘宝，负责推荐系统开发

参赛数据库：MySQL

性能评测：百万级数据代码性能评测 0.67秒

综合得分：95

以下是郑凌云选手的代码说明思路简介：

0. 核心：因为4张牌计算24点时的顺序可任意互换，所以不同排列的4张牌可视为同一组合。采用质数编码，把1到10映射成2到29内的质数，4张牌的积可作为该组合的唯一编码

1. 本地写代码，通过简单的回溯算法，生成24点游戏的所有解：152,((1+1)+1)*8,156,(6*2)*(1+1),...

2. 受限于代码大小10k限制，通过把上一步生成的数据进行压缩：SELECT REPLACE(TO_BASE64(COMPRESS('152,((1+1)+1)*8,156,(6*2)*(1+1),...')), ' ', '')

3. 提交的代码中，先对上一步生成的数据解压缩：UNCOMPRESS(FROM_BASE64('XXXX'))，并通过递归CTE生成查询表：152,((1+1)+1)*8;156,(6*2)*(1+1);...

4. 对输入表LEFT JOIN上一步生成的查询表，关联的键值是对c1，c2，c3，c4做质数编码后的积。

以下是郑凌云选手的算法说明，结尾附完整SQL：

算法说明

看到题目是用SQL算24点，感觉挺有意思的，平时使用SQL最多就是简单SELECT一下，从未想过是否可以算24点，就想挑战一下。

阶段一

结合题目条件，分析了一下这个24点游戏的问题。待求解的输入行数比较多，但4张1到10的牌的组合最多只有715种。在这个前提下，自然是先计算出715种组合的所有结果后，再进行LEFT JOIN。

先计算715种组合的结果

大概有两种思路：

1. 求解的方法

2. 构造的方法

求解的方法

即先遍历715种组合，通过回溯法计算每个组合的解（思路就是4个数算24，先4选2，进行加减乘除反向减反向除6种运算，问题化简为3个数算24；然后继续该过程直到最终化简为1个数算24，需要的计算量在4573左右），复杂度在 715 * 4573 = 327万左右。

构造的方法

4个数算24，本质上其实只有两种形式，即：

((a op b) op c) op d = 24

或者

(a op b) op (c op d) = 24

则可以构造出所有1到10的数字进行这两种形式计算，看结果是否能等于24。（可以这样理解：算24第一步肯定是2张牌进行计算；第二步则有两种选择，要么和第3张牌计算，要么和剩下的两张牌计算的结果进行计算）

其中还有两个优化点：

1. x op y （x和y是两张牌的原始点数，而不是计算结果）有两种算法：一是x和y都可以取1到10之间的数，op有加减乘除4种，那总共有400种（很明显其中有重复的）；二是限制y必须大于等于x，op有加减乘除反向减反向除6种，那总共有330种。我们会选择复杂度小的330种这种方式。

2. ((a op b) op c) op d = 24 这种形式，如果直接计算，复杂度在 330 * 6 * 10 * 6 * 10 = 119万左右，而如果从24反向计算回最后一个数，即 ((24 op d) op c) op b = a，其中a是1到10中的某整数，复杂度在 1 * 6 * 10 * 6 * 10 * 6 * 10 = 22万左右。我们选择复杂度小的22万种这种方式。

对于 (a op b) op (c op d) = 24 这种形式，复杂度约 330 * 6 * 330 / 2 = 33万左右（这个表达式是估算的，我没有进行严格计算）。两种形式加起来复杂度在55万左右，比第一种思路的复杂度327万会低很多。所以我们采用第二种思路。

再进行LEFT JOIN

JOIN需要一个关联条件，怎么唯一表示4个数的组合呢？可以排序，可以用带计数的map甚至bitmap，还可以用质数编码后的乘积表示。我采用了质数编码。其实bitmap或许性能更高，但需要一些精细的操作，我并没有去比较过这两种方式的性能区别（有兴趣的人可以试一下），当时也没有纠结，就选了更习惯的质数编码。因为在这个阶段我和很多人一样陷入到怎么求解24点的问题中，并没有意识到JOIN的关键。

SQL代码

WITH RECURSIVE

operations(op) AS (

VALUES ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6)

),

numbers(val, enc) AS (

VALUES ROW(1, 2), ROW(2, 3), ROW(3, 5), ROW(4, 7), ROW(5, 11), ROW(6, 13), ROW(7, 17), ROW(8, 19), ROW(9, 23), ROW(10, 29)

),

reverse_combinations1(enc, val, rst) AS (

SELECT enc, val, 24e0 FROM numbers

),

reverse_combinations1_solution(enc, rst, solution) AS (

SELECT

enc,

CASE op

WHEN 1 THEN val + rst

WHEN 2 THEN val * rst

WHEN 3 THEN val - rst

WHEN 4 THEN rst - val

WHEN 5 THEN val / rst

WHEN 6 THEN rst / val

END,

CASE op

WHEN 1 THEN (12 << 4) | val

WHEN 2 THEN (14 << 4) | val

WHEN 3 THEN (val << 4) | 12

WHEN 4 THEN (11 << 4) | val

WHEN 5 THEN (val << 4) | 14

WHEN 6 THEN (13 << 4) | val

END

FROM reverse_combinations1 CROSS JOIN operations

),

reverse_combinations1_solution_faster AS (

SELECT DISTINCT enc, rst, solution FROM reverse_combinations1_solution

),

reverse_combinations2(enc, val, rst, solution) AS (

SELECT t1.enc * t2.enc, val, rst, solution FROM reverse_combinations1_solution_faster t1 CROSS JOIN numbers t2

),

reverse_combinations2_solution(enc, rst, solution) AS (

SELECT

enc,

CASE op

WHEN 1 THEN val + rst

WHEN 2 THEN val * rst

WHEN 3 THEN val - rst

WHEN 4 THEN rst - val

WHEN 5 THEN val / rst

WHEN 6 THEN rst / val

END,

CASE op

WHEN 1 THEN (12 << 4) | val | (solution << 8)

WHEN 2 THEN (14 << 4) | val | (solution << 8)

WHEN 3 THEN (val << 4) | 12 | (solution << 8)

WHEN 4 THEN (11 << 4) | val | (solution << 8)

WHEN 5 THEN (val << 4) | 14 | (solution << 8)

WHEN 6 THEN (13 << 4) | val | (solution << 8)

END

FROM reverse_combinations2 CROSS JOIN operations

),

reverse_combinations2_solution_faster2x AS (

SELECT DISTINCT enc, rst, solution FROM reverse_combinations2_solution

),

reverse_combinations3(enc, val, rst, solution) AS (

SELECT t1.enc * t2.enc, val, rst, solution FROM reverse_combinations2_solution_faster2x t1 CROSS JOIN numbers t2

),

reverse_combinations3_solution(enc, rst, solution) AS (

SELECT

enc,

CASE op

WHEN 1 THEN val + rst

WHEN 2 THEN val * rst

WHEN 3 THEN val - rst

WHEN 4 THEN rst - val

WHEN 5 THEN val / rst

WHEN 6 THEN rst / val

END,

CASE op

WHEN 1 THEN (12 << 4) | val | (solution << 8)

WHEN 2 THEN (14 << 4) | val | (solution << 8)

WHEN 3 THEN (val << 4) | 12 | (solution << 8)

WHEN 4 THEN (11 << 4) | val | (solution << 8)

WHEN 5 THEN (val << 4) | 14 | (solution << 8)

WHEN 6 THEN (13 << 4) | val | (solution << 8)

END

FROM reverse_combinations3 CROSS JOIN operations

),

reverse_combinations_solution(enc, solution) AS (

SELECT

enc *

CASE ROUND(rst)

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,

ROUND(rst) | solution << 8

FROM reverse_combinations3_solution WHERE (ROUND(rst) BETWEEN 1 AND 10) AND ABS(ROUND(rst) - rst) < 0.000001

),

reverse_combinations_solution_distinct(enc, solution) AS (

SELECT enc, ANY_VALUE(solution) FROM reverse_combinations_solution GROUP BY enc

),

reverse_format_solution(enc, expr, solution) AS (

SELECT enc, CAST((solution & 0x0F) AS CHAR), solution >> 8 FROM reverse_combinations_solution_distinct

UNION ALL

SELECT enc,

CASE (solution & 0xF0) >> 4

WHEN 11 THEN CONCAT('(', expr, '+', solution & 0x0F, ')')

WHEN 12 THEN CONCAT('(', expr, '-', solution & 0x0F, ')')

WHEN 13 THEN CONCAT('(', expr, '*', solution & 0x0F, ')')

WHEN 14 THEN CONCAT('(', expr, '/', solution & 0x0F, ')')

ELSE

CASE solution & 0x0F

WHEN 12 THEN CONCAT('(', (solution & 0xF0) >> 4, '-', expr, ')')

WHEN 14 THEN CONCAT('(', (solution & 0xF0) >> 4, '/', expr, ')')

END

END,

solution >> 8

FROM reverse_format_solution WHERE solution > 0

),

reverse_solution(enc, expr) AS (

SELECT enc, expr FROM reverse_format_solution WHERE solution = 0

),

combinations2(enc, c1, c2) AS (

SELECT t1.enc * t2.enc, CAST(t1.val AS DOUBLE), CAST(t2.val AS DOUBLE)

FROM numbers t1

JOIN numbers t2 ON t1.val <= t2.val

),

combinations2_solution(enc, rst, expr) AS (

SELECT

enc,

CASE op

WHEN 1 THEN c1 + c2

WHEN 2 THEN c1 * c2

WHEN 3 THEN c1 - c2

WHEN 4 THEN c2 - c1

WHEN 5 THEN c1 / c2

WHEN 6 THEN c2 / c1

END,

CASE op

WHEN 1 THEN CONCAT('(', c1, '+', c2, ')')

WHEN 2 THEN CONCAT('(', c1, '*', c2, ')')

WHEN 3 THEN CONCAT('(', c1, '-', c2, ')')

WHEN 4 THEN CONCAT('(', c2, '-', c1, ')')

WHEN 5 THEN CONCAT('(', c1, '/', c2, ')')

WHEN 6 THEN CONCAT('(', c2, '/', c1, ')')

END

FROM combinations2 CROSS JOIN operations

),

combinations2_solution_faster7x AS (

SELECT DISTINCT enc, rst, expr FROM combinations2_solution

),

combinations4(enc1, enc2, rst1, rst2, expr1, expr2) AS (

SELECT t1.enc, t2.enc, t1.rst, t2.rst, t1.expr, t2.expr

FROM combinations2_solution_faster7x t1

JOIN combinations2_solution_faster7x t2 ON t1.enc <= t2.enc

),

combinations4_faster2x AS (

SELECT DISTINCT enc1, enc2, rst1, rst2, expr1, expr2 FROM combinations4

),

combinations4_solution(enc, rst, expr) AS (

SELECT

enc1 * enc2,

CASE op

WHEN 1 THEN rst1 + rst2

WHEN 2 THEN rst1 * rst2

WHEN 3 THEN rst1 - rst2

WHEN 4 THEN rst2 - rst1

WHEN 5 THEN rst1 / rst2

WHEN 6 THEN rst2 / rst1

END,

CASE op

WHEN 1 THEN CONCAT(expr1, '+', expr2)

WHEN 2 THEN CONCAT(expr1, '*', expr2)

WHEN 3 THEN CONCAT(expr1, '-', expr2)

WHEN 4 THEN CONCAT(expr2, '-', expr1)

WHEN 5 THEN CONCAT(expr1, '/', expr2)

WHEN 6 THEN CONCAT(expr2, '/', expr1)

END

FROM combinations4_faster2x CROSS JOIN operations

),

solution(enc, expr) AS (

SELECT enc, ANY_VALUE(expr) FROM combinations4_solution WHERE ABS(rst - 24) < 0.000001 GROUP BY enc

),

lookup_table(enc, result) AS (

SELECT enc, ANY_VALUE(expr) FROM (

SELECT enc, expr FROM solution

UNION ALL

SELECT enc, expr FROM reverse_solution

) t1 GROUP BY enc

)

SELECT id, c1, c2, c3, c4, result

FROM poker24.cards

LEFT JOIN lookup_table ON enc=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

最后分析下性能

耗时：join(20ms) + 构造lookup_table(187ms) = 207ms

这里构造lookup_table的耗时，即为计算715种组合24点解的耗时。

阶段二

怎么更快地求解24点问题呢？因为前面计算出了结果，看了下其实只有566个答案，尝试直接把答案构造进SQL进行JOIN，发现超过了题目要求的10KB限制。我对SQL也不是很熟悉，一度放弃了该方案，直到发现MySQL中有COMPRESS()函数。。。

1. 通过 SELECT REPLACE(TO_BASE64(COMPRESS('152,((1+1)+1)*8,156,(6*2)*(1+1),...')), ' ', '') 把结果编码输出

2. 提交的代码中，先对上一步生成的数据解压缩：UNCOMPRESS(FROM_BASE64('XXXX'))，并通过递归CTE生成查询表：152,((1+1)+1)*8;156,(6*2)*(1+1);...

3. 对输入表LEFT JOIN上一步生成的查询表，关联的键值是对c1，c2，c3，c4做质数编码后的乘积

SQL代码v1

决采用拼接union select或values（）的长度超过10K字节限制的问题，主要采取了以下压缩空间的技巧：

WITH RECURSIVE lookup_table(enc,result,dat) AS(

SELECT 0,

'err_placeholder',

UNCOMPRESS(FROM_BASE64('ICcAAHicTVpbkiMhDLvOTCZd4WUD97/YWpIhWzUf0zTY4Ics06nW3j8/9a/+xt9rvav5+8df7ffFwXcr4/0z8e6n8bktzH/F/Ib5bcb8/dz3fcT7Wv6ugDrK+2e8+hXY4tkosPPZy9mA/756CChHweTz

iucV6/9+aqh5D8zff1ee4X0tD5+woG4/Asfva7x7uTs2PvvACccViBPMhzPw3npYZGGtFPgY54QD81c9FnvVEjreY8UEe9mvZLxtxgQ7Av09YZIJ5RK4uus9N7zejg04Tq/3ey2c2H8/acHviV47rBQnnHcLM/xWwsjYpEbW71+4sTms3o5b6g6ZjV4xWr3DCH/3fS9+3s+HboSG9hw3jwr5mPq3f1/tba3R6l8FFm7oVCi31nIWdFq90+39KtzlKOgwkmH+pJv53ma6/cVJb8f8Hc+P3q+Zbm8vub3t2OGg2zhh+D0B3W7D5fYUOHGicDse8H7CQuM8r/d2BjaF4X1tFXEEden3CRvL75S4bNGvkhBuqjCSHI9lIcKGZryko5pkwm0Wlg03YVe1hB3/YiLWlGv5iAg4vzfFqzYWA2OFGj8DIXXucXbKeGlNtsiQfWLNhP/D3vM3lHxaKO4MOuidGSA4vwawkbbtnl9B1ypMpBEGXahpWLMicuVkhtXPQvI0rWGYMAwYJh3P/Bee62+zGyYdJnO9fw46LLthwveb7xdOFqERyc843M9VUIdloPRHgTL2FUl8mIgkIx5wxY5teqRerPiMX6YfdY7ws0Qylvk/VoXBvVIptwSRrXQpHd9tYMS5cSZgZQArODrtW6DGzj7DmiY1rxMcnRuJ4EASSAizQtAhIUy7eXYSrh/MI+2cA/seX8HReX6pUXC0xQPThgKD1lNqRljfPPCHM5hsu1+pCo5Om8hI6/cJBOmznOCg64wpvygkTIWRtq/YzaR37i3Kify732tU2uAPKSjT+kKMjfRGJGqZ6zh0KO0mgt0144kZjsCVRzVjMUHwvzzamKnh0ZHo0NZo16Na43scNSYHlobA+bEIGw44sltqNGMjdRdlmKKfMAslcvEY2Fmq0Zru93gO7/SyBVwjYaYPJKafAWD1EnSN9Lk1WERq5OJRBkFkXAcS0KfsASE25jgz6AsrMMk6UqNsVRxPDmVh3L2r9m9YXpVrzvF1F+3W3LM8fizkWIS1kNlOVJsJeu2YaRbCZJjFYKenRtByxF7HKoMelBDGrI2u7VqayQ1GCKtYpsZixQihfvXUImi1tOTy0s5OptB40XAq9oWLNn2IJQr07VWxYwePW1W1/Fs3+6vDVpum0sjwNBWHFJVW2td6DIfRie3+nIP7aC3hyj8r4GrS5KHe8+B7SQrP6S/VFFnYj/trW1W+i+xlpoQ3F4M15cQxaoeNN0/hCWpN4cpnTuqTmcNTSNnsSh0N0Yqdpt9UJv3BgbTJ+d0S03ZRnUYGK+yWh6ltsY6lZeWhqON+NqDC1XyuK3trA4wfyV7KZi9+DrcSjxXOD591ODscB1Qp1G9Qjvvc9mRQNpKmFkm1WWTb78Nnqx0WvfO91Xi/OTmWvOfqt/hLYLNTLfncq7GOt6yWY1sqhNConovvPy1J1fT7PFU97XDp9uAABSdStdSJejEBbv+l1ECkSmSUihE8rB/U44pV92HTKXLWA3oSWWkVcktVyzH2YRZDZmzzgp7WFFg2qdWnMT6nmFM8kfQ4bDXFk14UsiqZgOXGI3zojdiUKGuIwAxLc4M1GxzgHzjEqXcveiyngDk1uozbYvn0DSFYI6m976/THBaIhgZT5iOrh+JdZjK2JoCOWkgrf86U3mATGYkgElL7MRIiBq7eybj/yNPeXkpH+Dz3PNN8f/FCETONVVgRERHQ7NggoqC/t94/J2KiVt4BhkwdzQ4R73TwbEckROyggViStATZ8Z79zlBFrnaFkosHktgh4ykUkQuG8TSx7Y3QHmcgKvJA7O9YEvQBIdHL+KrNOMIURQ2ltoopdoSA2SC8RR8RGJFQ2Cr4Zhq1z20H8XmWwZ3ZI5bCpLTv8VSRmZaS6tzaZBYtmQjVaSwTQcwwMje7mCw9HVIndzI5sLD5lRyrodGF0QCaL1lg9m3iaX8KwCh62Mk6eqJGm6XilwZWc7uAKWdtYgDxkgO1lGIXsDstG1RGuPD0mzylbZKKljw6WPA8sSWatevFjiEyuvfhFBh9t7n7IUR0YTCIemKaMkbznlqy6wpmpjRmov+hv0ceqPRLyAaeiFoP5k4nnDx8IrnpQGJ5mUvctzJnHDwJarYPtZYQh15tnmARvds8fTpneBzn7JXUeu46r5elp7gAZhKlAk6J+aoRC1ZcDUZTaRyvxOhrgpl5TitxhI1X6JnJ+FrWagPYpZ+VHbWWfRytkVHKAd4/Yk3koTtFf2Bs2jx4HkTB3hmZvlTngr/pcmB1ohcyKtvYYu3gdQgCyzO7cKw+djb4nmRLORHZW8ljLb0UhVx6tCTENlUjy2iIzNrW6et560BxVl0WxSn6tUs/nG3z4N4SKLksqxSLMycRCaJMtXoYgSW+qEA/fCYqtUJ3PXZzMMqOcdbHLmTElqqC6Y93a0EsKzOBeEGS5r0n28oCV+kJcTQVp2jlKFhilJe2VVo8bRYljdU1eRxrWsvyOr+SCiJ1JZOj38tAkGFzz+bJgm2p4CzSRpyseFd2+impUer62SRrU1Syuf8rQqyI1cfE6ZLvEUMr9YWVJoNohnUHTLkeVk4Zl1VfkNdYFUdRRiW7Q1pGt1rv8bSHVhRs63PKaRiFwf+wmLKFhRFuhrDE9lLhOWWIRlapMgIhibU7IGt+zwcVuIJs+26LZT3AgEUCdZMXE2ue56erBE4WK91kdAalnRsCUL7wQLPThmvgFHPJWGA0ie5RJXhXShmnFGNNJYUclDqoZmtjsUK1uLHii8H1p5PFZi2mVFzdkQLkZUbMGKVZdseplwNZeWNVoKeE2tlHdDeiqp1Z2d/e27fQYsbcohHEGVpkcO+RIj0vf8zat9Biq16/VnVYZA5ahBlCGXu0/wot9bQ0wCTlme9VsEbBqTVzXlYM8783jreCdzysxwCZ3U5B76fvmf8VVpq+U1NWVo64/0eVoCASj4V18JIUBSGQiQPP8U+v63IljEZ775YXAPLPIJmgDPgoypv35EqPnB6N7bd8Z13t12OUaiz6dqSiXx6mKpqRM0lzlq4zcJzFdkI+3YzpWsUuCH1cFMBnB/k4sJy0RiWRAVmGWzasOk/tddutS65JVRxrsL/HSLYqU0dCX9FdLGte0UFV7eL8oAuskeBlXVRCLGnjs9pKq+m5kPRhToz+9Rx3Ft2a2yEiZHnBiJYd4mHaJDu0lVcZDAoaBLcDvP4IBs4NiWhoUSNdlhgEP/rOZYdqaI6PYQd4KbiVqR3S1NI12f+tR5WbkGLFTnXQsknDJnxq2VabmIUPk4b6RB4e4tDSqPUEPd28nHRfX99v5Xya/9Ew24my2aPC13mfPXrGs2fkVS8MLP87aV1l/p2Vj1viSSQn77QXqXpI8uxdooXfN7W1TAyZFUwm6Y19BA197rU7I1JDutheg3bjXReRJzySdmMs68NVX9dK0EBO/0Uf5rsNh+yOyk9mBH3DaIEsj/TKoAXwIBITGKxW/WcGcLH3r7scz52zFF83J9RGzqo4iV3msrH9ZgndZE7I2Se0J7+NyJkqcky4PhioYc1Oqop7RqUJGcJitofpBDRU//sJzC5NLQiLKiYF0/Fvf7EeYv/KjFdR5bSyvo0JbTddfRWGMBHJ4zPTKSKM9uyI1oqSx/vfIPS44SaB/oyE1KGLGbxUY2KrX27PJW51HqzjwFy8GdiYwp5270EakCA7AHXtXlpA13uSXpueSPKqH8o7hHSljXrwgXqqiWPprlPdc6ulny+SDuSpEZ+XG2rZIsPK7NdQ1InKFnowYoJhGS9aBKKLggKMLlvkKrTZX7YoQdPXZYuaFLL7KTCLI9E/7XuHQ9mjNlAumAj5GX17GK3DrDKRcQOV/DlgJkSZvmZ1NYkJkOgrCzqFcUYIB+uybtxFMYX6ZQ/GbXdyU6dtUT5BcuttDGXITX4n9USaFlSmH6ShnIA6kTtihrRFYb8dxZTZeLm0ElgHySS8r6DS55u5XRdDdnvZObdCZDFEkOc+DlHV8Ucb4u8EVlo7mGvn1c9nn4+bUSL2oVMqT8G5LW+Zrr7gt2xRPc8bLXnYn6Hvx0qtDZDnlViLkSlPwmT5zWj0JM9c9mEENPotsZaG49WYlAnEjI2rrjC4aozF/p6i88NRFNF6hoi+fU+fB2e4RyuVjSBpxPkIH6X14AwnoddWKO+zLuphr1+c4VicZdMCiazBuJbu53AWNeWAPvXtI7HPGm8dVBEm7W2m3v6jfaNzjmUSDU/pI3NXnjJ1YAhet0xF/MEwnIbBK9JNJA/LNV1wjuzQZliq3w6NgRENInw+jsJozSMudck8hJm4Gwh+0/9rYZgJEVLr21rthBlYOdM1ogh3+aWfEWQWSg6hIPPO+IMR3QbEQ8aYz68cRFqExlo3xriqR1m64aORXXg1QjkMH4vK9e2skcZRYX1d0euzUILWvBiDkHl7sO6a2BB2wjl8lHKxGqZCE1DuMoYMOOf0+h2Db4CProz1j+vzW+ATwCDzBTu3LlcR0ZAvkcHwy8Z9Ibuit3Vdjgnn9QkrGgaJViDGiPMLw8rvNFhmvKYQhjAVLfpGZZllaHo0u+cqHJHiUbx4zwbFHxwkjDLNx8xeXvC0Le/esAyRgsvg0vxGClLW2AbUeQgEN7Vb3RfYlMTR5yT6WSbxik5HKWQZv1HVilppBjBF1QCfpqoB+bpMstl4ETQPkPEKjFGW9zVRmqK49n4zkiS1gp/sYwkFfzTVc/4X/Jtsdpl/72f+wNzqHP1YniNBNwEdTg6+w/fBY2ZVLsurGHKm8s6rFsdvfVjy8jwMBq8MPrF5aAiTMiG5QraJQln/u32BpBo7b/24LClvCd7UD1TJaZEk895LYicgEJOfPhCjFwScNyKTmwejQtezvN69SsPajHdZQr8siiMSaYFLVIPOq+yrk5tG+XXvp4laMBiugUY6d92fMgW1Vy1LKMfvHILn/XfHwtKLPm26zj+/P3uqvDTUmLaHkiq3aH+TbQ8vDhUborjR+HheQWEzKS946EnLJYH/ALZfNLo='))

UNION ALL

SELECT CAST(SUBSTRING_INDEX(dat,',',1) AS SIGNED),

SUBSTRING_INDEX(SUBSTRING_INDEX(dat,',',2),',',-1),

SUBSTRING(dat,LENGTH(SUBSTRING_INDEX(dat,',',2))+2)

FROM lookup_table

WHERE LENGTH(dat)>0

)

SELECT id, c1, c2, c3, c4, result

FROM poker24.cards

LEFT JOIN lookup_table ON enc=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

分析下性能v1

耗时：join(152ms) + lookup_table(2ms) = 154ms

看起来是快了，但是当输入数据规模增大时，这还不如前面那个方法呢。问题来了，为什么这个join这么慢？？

自然猜测数据库对阶段一中的代码和阶段二中的代码的优化不同。我并不是SQL专家，通过explain也没有发现有价值的线索（已经有'Materialize CTE'，有'hash join'了），加优化hints也没用。为了让数据库做类似阶段一的优化，我尝试对lookup_table做了类似阶段一的一些复杂处理，其中验证了GROUP BY和DISTINCT操作都可以让数据库进入优化状态，看explain的结果，多出来的一步是在'hash join'前多了个'Using temporary'。

SQL代码v2

WITH RECURSIVE lookup_table(enc,result,dat) AS(

SELECT 0,

'err_placeholder',

UNCOMPRESS(FROM_BASE64('ICcAAHicTVpbkiMhDLvOTCZd4WUD97/YWpIhWzUf0zTY4Ics06nW3j8/9a/+xt9rvav5+8df7ffFwXcr4/0z8e6n8bktzH/F/Ib5bcb8/dz3fcT7Wv6ugDrK+2e8+hXY4tkosPPZy9mA/756CChHweTziucV6/9+aqh5D8zff1ee4X0tD5+woG4/Asfva7x7uTs2PvvACccViBPMhzPw3npYZGGtFPgY54QD81c9FnvVEjreY8UEe9mvZLxtxgQ7Av09YZIJ5RK4uus9N7zejg04Tq/3ey2c2H8/acHviV47rBQnnHcLM/xWwsjYpEbW71+4sTms3o5b6g6ZjV4xWr3DCH/3fS9+3s+HboSG9hw3jwr5mPq3f1/tba3R6l8FFm7oVCi31nIWdFq90+39KtzlKOgwkmH+pJv53ma6/cVJb8f8Hc+P3q+Zbm8vub3t2OGg2zhh+D0B3W7D5fYUOHGicDse8H7CQuM8r/d2BjaF4X1tFXEEden3CRvL75S4bNGvkhBuqjCSHI9lIcKGZryko5pkwm0Wlg03YVe1hB3/YiLWlGv5iAg4vzfFqzYWA2OFGj8DIXXucXbKeGlNtsiQfWLNhP/D3vM3lHxaKO4MOuidGSA4vwawkbbtnl9B1ypMpBEGXahpWLMicuVkhtXPQvI0rWGYMAwYJh3P/Bee62+zGyYdJnO9fw46LLthwveb7xdOFqERyc843M9VUIdloPRHgTL2FUl8mIgkIx5wxY5teqRerPiMX6YfdY7ws0Qylvk/VoXBvVIptwSRrXQpHd9tYMS5cSZgZQArODrtW6DGzj7DmiY1rxMcnRuJ4EASSAizQtAhIUy7eXYSrh/MI+2cA/seX8HReX6pUXC0xQPThgKD1lNqRljfPPCHM5hsu1+pCo5Om8hI6/cJBOmznOCg64wpvygkTIWRtq/YzaR37i3Kify732tU2uAPKSjT+kKMjfRGJGqZ6zh0KO0mgt0144kZjsCVRzVjMUHwvzzamKnh0ZHo0NZo16Na43scNSYHlobA+bEIGw44sltqNGMjdRdlmKKfMAslcvEY2Fmq0Zru93gO7/SyBVwjYaYPJKafAWD1EnSN9Lk1WERq5OJRBkFkXAcS0KfsASE25jgz6AsrMMk6UqNsVRxPDmVh3L2r9m9YXpVrzvF1F+3W3LM8fizkWIS1kNlOVJsJeu2YaRbCZJjFYKenRtByxF7HKoMelBDGrI2u7VqayQ1GCKtYpsZixQihfvXUImi1tOTy0s5OptB40XAq9oWLNn2IJQr07VWxYwePW1W1/Fs3+6vDVpum0sjwNBWHFJVW2td6DIfRie3+nIP7aC3hyj8r4GrS5KHe8+B7SQrP6S/VFFnYj/trW1W+i+xlpoQ3F4M15cQxaoeNN0/hCWpN4cpnTuqTmcNTSNnsSh0N0Yqdpt9UJv3BgbTJ+d0S03ZRnUYGK+yWh6ltsY6lZeWhqON+NqDC1XyuK3trA4wfyV7KZi9+DrcSjxXOD591ODscB1Qp1G9Qjvvc9mRQNpKmFkm1WWTb78Nnqx0WvfO91Xi/OTmWvOfqt/hLYLNTLfncq7GOt6yWY1sqhNConovvPy1J1fT7PFU97XDp9uAABSdStdSJejEBbv+l1ECkSmSUihE8rB/U44pV92HTKXLWA3oSWWkVcktVyzH2YRZDZmzzgp7WFFg2qdWnMT6nmFM8kfQ4bDXFk14UsiqZgOXGI3zojdiUKGuIwAxLc4M1GxzgHzjEqXcveiyngDk1uozbYvn0DSFYI6m976/THBaIhgZT5iOrh+JdZjK2JoCOWkgrf86U3mATGYkgElL7MRIiBq7eybj/yNPeXkpH+Dz3PNN8f/FCETONVVgRERHQ7NggoqC/t94/J2KiVt4BhkwdzQ4R73TwbEckROyggViStATZ8Z79zlBFrnaFkosHktgh4ykUkQuG8TSx7Y3QHmcgKvJA7O9YEvQBIdHL+KrNOMIURQ2ltoopdoSA2SC8RR8RGJFQ2Cr4Zhq1z20H8XmWwZ3ZI5bCpLTv8VSRmZaS6tzaZBYtmQjVaSwTQcwwMje7mCw9HVIndzI5sLD5lRyrodGF0QCaL1lg9m3iaX8KwCh62Mk6eqJGm6XilwZWc7uAKWdtYgDxkgO1lGIXsDstG1RGuPD0mzylbZKKljw6WPA8sSWatevFjiEyuvfhFBh9t7n7IUR0YTCIemKaMkbznlqy6wpmpjRmov+hv0ceqPRLyAaeiFoP5k4nnDx8IrnpQGJ5mUvctzJnHDwJarYPtZYQh15tnmARvds8fTpneBzn7JXUeu46r5elp7gAZhKlAk6J+aoRC1ZcDUZTaRyvxOhrgpl5TitxhI1X6JnJ+FrWagPYpZ+VHbWWfRytkVHKAd4/Yk3koTtFf2Bs2jx4HkTB3hmZvlTngr/pcmB1ohcyKtvYYu3gdQgCyzO7cKw+djb4nmRLORHZW8ljLb0UhVx6tCTENlUjy2iIzNrW6et560BxVl0WxSn6tUs/nG3z4N4SKLksqxSLMycRCaJMtXoYgSW+qEA/fCYqtUJ3PXZzMMqOcdbHLmTElqqC6Y93a0EsKzOBeEGS5r0n28oCV+kJcTQVp2jlKFhilJe2VVo8bRYljdU1eRxrWsvyOr+SCiJ1JZOj38tAkGFzz+bJgm2p4CzSRpyseFd2+impUer62SRrU1Syuf8rQqyI1cfE6ZLvEUMr9YWVJoNohnUHTLkeVk4Zl1VfkNdYFUdRRiW7Q1pGt1rv8bSHVhRs63PKaRiFwf+wmLKFhRFuhrDE9lLhOWWIRlapMgIhibU7IGt+zwcVuIJs+26LZT3AgEUCdZMXE2ue56erBE4WK91kdAalnRsCUL7wQLPThmvgFHPJWGA0ie5RJXhXShmnFGNNJYUclDqoZmtjsUK1uLHii8H1p5PFZi2mVFzdkQLkZUbMGKVZdseplwNZeWNVoKeE2tlHdDeiqp1Z2d/e27fQYsbcohHEGVpkcO+RIj0vf8zat9Biq16/VnVYZA5ahBlCGXu0/wot9bQ0wCTlme9VsEbBqTVzXlYM8783jreCdzysxwCZ3U5B76fvmf8VVpq+U1NWVo64/0eVoCASj4V18JIUBSGQiQPP8U+v63IljEZ775YXAPLPIJmgDPgoypv35EqPnB6N7bd8Z13t12OUaiz6dqSiXx6mKpqRM0lzlq4zcJzFdkI+3YzpWsUuCH1cFMBnB/k4sJy0RiWRAVmGWzasOk/tddutS65JVRxrsL/HSLYqU0dCX9FdLGte0UFV7eL8oAuskeBlXVRCLGnjs9pKq+m5kPRhToz+9Rx3Ft2a2yEiZHnBiJYd4mHaJDu0lVcZDAoaBLcDvP4IBs4NiWhoUSNdlhgEP/rOZYdqaI6PYQd4KbiVqR3S1NI12f+tR5WbkGLFTnXQsknDJnxq2VabmIUPk4b6RB4e4tDSqPUEPd28nHRfX99v5Xya/9Ew24my2aPC13mfPXrGs2fkVS8MLP87aV1l/p2Vj1viSSQn77QXqXpI8uxdooXfN7W1TAyZFUwm6Y19BA197rU7I1JDutheg3bjXReRJzySdmMs68NVX9dK0EBO/0Uf5rsNh+yOyk9mBH3DaIEsj/TKoAXwIBITGKxW/WcGcLH3r7scz52zFF83J9RGzqo4iV3msrH9ZgndZE7I2Se0J7+NyJkqcky4PhioYc1Oqop7RqUJGcJitofpBDRU//sJzC5NLQiLKiYF0/Fvf7EeYv/KjFdR5bSyvo0JbTddfRWGMBHJ4zPTKSKM9uyI1oqSx/vfIPS44SaB/oyE1KGLGbxUY2KrX27PJW51HqzjwFy8GdiYwp5270EakCA7AHXtXlpA13uSXpueSPKqH8o7hHSljXrwgXqqiWPprlPdc6ulny+SDuSpEZ+XG2rZIsPK7NdQ1InKFnowYoJhGS9aBKKLggKMLlvkKrTZX7YoQdPXZYuaFLL7KTCLI9E/7XuHQ9mjNlAumAj5GX17GK3DrDKRcQOV/DlgJkSZvmZ1NYkJkOgrCzqFcUYIB+uybtxFMYX6ZQ/GbXdyU6dtUT5BcuttDGXITX4n9USaFlSmH6ShnIA6kTtihrRFYb8dxZTZeLm0ElgHySS8r6DS55u5XRdDdnvZObdCZDFEkOc+DlHV8Ucb4u8EVlo7mGvn1c9nn4+bUSL2oVMqT8G5LW+Zrr7gt2xRPc8bLXnYn6Hvx0qtDZDnlViLkSlPwmT5zWj0JM9c9mEENPotsZaG49WYlAnEjI2rrjC4aozF/p6i88NRFNF6hoi+fU+fB2e4RyuVjSBpxPkIH6X14AwnoddWKO+zLuphr1+c4VicZdMCiazBuJbu53AWNeWAPvXtI7HPGm8dVBEm7W2m3v6jfaNzjmUSDU/pI3NXnjJ1YAhet0xF/MEwnIbBK9JNJA/LNV1wjuzQZliq3w6NgRENInw+jsJozSMudck8hJm4Gwh+0/9rYZgJEVLr21rthBlYOdM1ogh3+aWfEWQWSg6hIPPO+IMR3QbEQ8aYz68cRFqExlo3xriqR1m64aORXXg1QjkMH4vK9e2skcZRYX1d0euzUILWvBiDkHl7sO6a2BB2wjl8lHKxGqZCE1DuMoYMOOf0+h2Db4CProz1j+vzW+ATwCDzBTu3LlcR0ZAvkcHwy8Z9Ibuit3Vdjgnn9QkrGgaJViDGiPMLw8rvNFhmvKYQhjAVLfpGZZllaHo0u+cqHJHiUbx4zwbFHxwkjDLNx8xeXvC0Le/esAyRgsvg0vxGClLW2AbUeQgEN7Vb3RfYlMTR5yT6WSbxik5HKWQZv1HVilppBjBF1QCfpqoB+bpMstl4ETQPkPEKjFGW9zVRmqK49n4zkiS1gp/sYwkFfzTVc/4X/Jtsdpl/72f+wNzqHP1YniNBNwEdTg6+w/fBY2ZVLsurGHKm8s6rFsdvfVjy8jwMBq8MPrF5aAiTMiG5QraJQln/u32BpBo7b/24LClvCd7UD1TJaZEk895LYicgEJOfPhCjFwScNyKTmwejQtezvN69SsPajHdZQr8siiMSaYFLVIPOq+yrk5tG+XXvp4laMBiugUY6d92fMgW1Vy1LKMfvHILn/XfHwtKLPm26zj+/P3uqvDTUmLaHkiq3aH+TbQ8vDhUborjR+HheQWEzKS946EnLJYH/ALZfNLo='))

UNION ALL

SELECT CAST(SUBSTRING_INDEX(dat,',',1) AS SIGNED),

SUBSTRING_INDEX(SUBSTRING_INDEX(dat,',',2),',',-1),

SUBSTRING(dat,LENGTH(SUBSTRING_INDEX(dat,',',2))+2)

FROM lookup_table

WHERE LENGTH(dat)>0

),

lookup_table_faster5x AS (

SELECT enc,ANY_VALUE(result) AS result FROM lookup_table GROUP BY enc

)

SELECT id, c1, c2, c3, c4, result

FROM poker24.cards

LEFT JOIN lookup_table_faster5x ON enc=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

分析下性能v2

耗时：join(6ms) + lookup_table(3ms) = 9ms

当最终性能评测时，输入数据量是我们测试数据的100倍，所以预估性能评测的耗时在600ms左右，和最终测试结果基本吻合。

后记

答辩时有位评委老师（好像是周振兴老师？线上答辩也分不清哪位老师在说话，尴尬 ）问到我阶段一的方法预估耗时会是多少，根据上面数据，预估值是在2秒左右。当时没有反应过来评委老师问题后面的问题。其实阶段一还可以再优化的，因为后面我采用了阶段二的方法，就没对阶段一的方法深入优化了。阶段一中的JOIN速度同样有优化空间。下面我在阶段一的代码上增加几行代码对JOIN做一下优化，给出一种即使不硬编码，也能在大约800ms内解出100万输入行的代码（也就是说是否硬编码差距就200ms左右）

SQL代码

WITH RECURSIVE

operations(op) AS (

VALUES ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6)

),

numbers(val, enc) AS (

VALUES ROW(1, 2), ROW(2, 3), ROW(3, 5), ROW(4, 7), ROW(5, 11), ROW(6, 13), ROW(7, 17), ROW(8, 19), ROW(9, 23), ROW(10, 29)

),

reverse_combinations1(enc, val, rst) AS (

SELECT enc, val, 24e0 FROM numbers

),

reverse_combinations1_solution(enc, rst, solution) AS (

SELECT

enc,

CASE op

WHEN 1 THEN val + rst

WHEN 2 THEN val * rst

WHEN 3 THEN val - rst

WHEN 4 THEN rst - val

WHEN 5 THEN val / rst

WHEN 6 THEN rst / val

END,

CASE op

WHEN 1 THEN (12 << 4) | val

WHEN 2 THEN (14 << 4) | val

WHEN 3 THEN (val << 4) | 12

WHEN 4 THEN (11 << 4) | val

WHEN 5 THEN (val << 4) | 14

WHEN 6 THEN (13 << 4) | val

END

FROM reverse_combinations1 CROSS JOIN operations

),

reverse_combinations1_solution_faster AS (

SELECT DISTINCT enc, rst, solution FROM reverse_combinations1_solution

),

reverse_combinations2(enc, val, rst, solution) AS (

SELECT t1.enc * t2.enc, val, rst, solution FROM reverse_combinations1_solution_faster t1 CROSS JOIN numbers t2

),

reverse_combinations2_solution(enc, rst, solution) AS (

SELECT

enc,

CASE op

WHEN 1 THEN val + rst

WHEN 2 THEN val * rst

WHEN 3 THEN val - rst

WHEN 4 THEN rst - val

WHEN 5 THEN val / rst

WHEN 6 THEN rst / val

END,

CASE op

WHEN 1 THEN (12 << 4) | val | (solution << 8)

WHEN 2 THEN (14 << 4) | val | (solution << 8)

WHEN 3 THEN (val << 4) | 12 | (solution << 8)

WHEN 4 THEN (11 << 4) | val | (solution << 8)

WHEN 5 THEN (val << 4) | 14 | (solution << 8)

WHEN 6 THEN (13 << 4) | val | (solution << 8)

END

FROM reverse_combinations2 CROSS JOIN operations

),

reverse_combinations2_solution_faster2x AS (

SELECT DISTINCT enc, rst, solution FROM reverse_combinations2_solution

),

reverse_combinations3(enc, val, rst, solution) AS (

SELECT t1.enc * t2.enc, val, rst, solution FROM reverse_combinations2_solution_faster2x t1 CROSS JOIN numbers t2

),

reverse_combinations3_solution(enc, rst, solution) AS (

SELECT

enc,

CASE op

WHEN 1 THEN val + rst

WHEN 2 THEN val * rst

WHEN 3 THEN val - rst

WHEN 4 THEN rst - val

WHEN 5 THEN val / rst

WHEN 6 THEN rst / val

END,

CASE op

WHEN 1 THEN (12 << 4) | val | (solution << 8)

WHEN 2 THEN (14 << 4) | val | (solution << 8)

WHEN 3 THEN (val << 4) | 12 | (solution << 8)

WHEN 4 THEN (11 << 4) | val | (solution << 8)

WHEN 5 THEN (val << 4) | 14 | (solution << 8)

WHEN 6 THEN (13 << 4) | val | (solution << 8)

END

FROM reverse_combinations3 CROSS JOIN operations

),

reverse_combinations_solution(enc, solution) AS (

SELECT

enc *

CASE ROUND(rst)

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,

ROUND(rst) | solution << 8

FROM reverse_combinations3_solution WHERE (ROUND(rst) BETWEEN 1 AND 10) AND ABS(ROUND(rst) - rst) < 0.000001

),

reverse_combinations_solution_distinct(enc, solution) AS (

SELECT enc, ANY_VALUE(solution) FROM reverse_combinations_solution GROUP BY enc

),

reverse_format_solution(enc, expr, solution) AS (

SELECT enc, CAST((solution & 0x0F) AS CHAR), solution >> 8 FROM reverse_combinations_solution_distinct

UNION ALL

SELECT enc,

CASE (solution & 0xF0) >> 4

WHEN 11 THEN CONCAT('(', expr, '+', solution & 0x0F, ')')

WHEN 12 THEN CONCAT('(', expr, '-', solution & 0x0F, ')')

WHEN 13 THEN CONCAT('(', expr, '*', solution & 0x0F, ')')

WHEN 14 THEN CONCAT('(', expr, '/', solution & 0x0F, ')')

ELSE

CASE solution & 0x0F

WHEN 12 THEN CONCAT('(', (solution & 0xF0) >> 4, '-', expr, ')')

WHEN 14 THEN CONCAT('(', (solution & 0xF0) >> 4, '/', expr, ')')

END

END,

solution >> 8

FROM reverse_format_solution WHERE solution > 0

),

reverse_solution(enc, expr) AS (

SELECT enc, expr FROM reverse_format_solution WHERE solution = 0

),

combinations2(enc, c1, c2) AS (

SELECT t1.enc * t2.enc, CAST(t1.val AS DOUBLE), CAST(t2.val AS DOUBLE)

FROM numbers t1

JOIN numbers t2 ON t1.val <= t2.val

),

combinations2_solution(enc, rst, expr) AS (

SELECT

enc,

CASE op

WHEN 1 THEN c1 + c2

WHEN 2 THEN c1 * c2

WHEN 3 THEN c1 - c2

WHEN 4 THEN c2 - c1

WHEN 5 THEN c1 / c2

WHEN 6 THEN c2 / c1

END,

CASE op

WHEN 1 THEN CONCAT('(', c1, '+', c2, ')')

WHEN 2 THEN CONCAT('(', c1, '*', c2, ')')

WHEN 3 THEN CONCAT('(', c1, '-', c2, ')')

WHEN 4 THEN CONCAT('(', c2, '-', c1, ')')

WHEN 5 THEN CONCAT('(', c1, '/', c2, ')')

WHEN 6 THEN CONCAT('(', c2, '/', c1, ')')

END

FROM combinations2 CROSS JOIN operations

),

combinations2_solution_faster7x AS (

SELECT DISTINCT enc, rst, expr FROM combinations2_solution

),

combinations4(enc1, enc2, rst1, rst2, expr1, expr2) AS (

SELECT t1.enc, t2.enc, t1.rst, t2.rst, t1.expr, t2.expr

FROM combinations2_solution_faster7x t1

JOIN combinations2_solution_faster7x t2 ON t1.enc <= t2.enc

),

combinations4_faster2x AS (

SELECT DISTINCT enc1, enc2, rst1, rst2, expr1, expr2 FROM combinations4

),

combinations4_solution(enc, rst, expr) AS (

SELECT

enc1 * enc2,

CASE op

WHEN 1 THEN rst1 + rst2

WHEN 2 THEN rst1 * rst2

WHEN 3 THEN rst1 - rst2

WHEN 4 THEN rst2 - rst1

WHEN 5 THEN rst1 / rst2

WHEN 6 THEN rst2 / rst1

END,

CASE op

WHEN 1 THEN CONCAT(expr1, '+', expr2)

WHEN 2 THEN CONCAT(expr1, '*', expr2)

WHEN 3 THEN CONCAT(expr1, '-', expr2)

WHEN 4 THEN CONCAT(expr2, '-', expr1)

WHEN 5 THEN CONCAT(expr1, '/', expr2)

WHEN 6 THEN CONCAT(expr2, '/', expr1)

END

FROM combinations4_faster2x CROSS JOIN operations

),

solution(enc, expr) AS (

SELECT enc, ANY_VALUE(expr) FROM combinations4_solution WHERE ABS(rst - 24) < 0.000001 GROUP BY enc

),

lookup_table(enc, result) AS (

SELECT enc, ANY_VALUE(expr) FROM (

SELECT enc, expr FROM solution

UNION ALL

SELECT enc, expr FROM reverse_solution

) t1 GROUP BY enc

),

json_agg(dat) AS (

SELECT JSON_ARRAYAGG(JSON_OBJECT('enc', enc, 'result', result)) FROM lookup_table

),

json_map(enc, result) AS (

SELECT enc, result FROM json_agg, JSON_TABLE(json_agg.dat, '$[*]' COLUMNS(enc INT PATH '$.enc', result CHAR(16) PATH '$.result')) t

),

lookup_table_faster AS (

SELECT enc, ANY_VALUE(result) result FROM json_map GROUP BY enc

)

SELECT id, c1, c2, c3, c4, result

FROM poker24.cards

LEFT JOIN lookup_table_faster ON enc=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

总结一下

非常感谢主办方玖章算术举行的趣味比赛，让我们在工作之余，可以有机会动动脑子，学学东西，跟业界同行有一些学习交流的机会。感谢评委老师们在答辩过程中的指点。感谢一起参加比赛的选手们的精彩分享。在这个过程中，真的学到了不少东西，对SQL也有了更多理解。

参赛完整SQL

WITH RECURSIVE lookup_table(enc,result,dat) AS(
    SELECT 0,
           'err_placeholder',
           UNCOMPRESS(FROM_BASE64('ICcAAHicTVpbkiMhDLvOTCZd4WUD97/YWpIhWzUf0zTY4Ics06nW3j8/9a/+xt9rvav5+8df7ffFwXcr4/0z8e6n8bktzH/F/Ib5bcb8/dz3fcT7Wv6ugDrK+2e8+hXY4tkosPPZy9mA/756CChHweTziucV6/9+aqh5D8zff1ee4X0tD5+woG4/Asfva7x7uTs2PvvACccViBPMhzPw3npYZGGtFPgY54QD81c9FnvVEjreY8UEe9mvZLxtxgQ7Av09YZIJ5RK4uus9N7zejg04Tq/3ey2c2H8/acHviV47rBQnnHcLM/xWwsjYpEbW71+4sTms3o5b6g6ZjV4xWr3DCH/3fS9+3s+HboSG9hw3jwr5mPq3f1/tba3R6l8FFm7oVCi31nIWdFq90+39KtzlKOgwkmH+pJv53ma6/cVJb8f8Hc+P3q+Zbm8vub3t2OGg2zhh+D0B3W7D5fYUOHGicDse8H7CQuM8r/d2BjaF4X1tFXEEden3CRvL75S4bNGvkhBuqjCSHI9lIcKGZryko5pkwm0Wlg03YVe1hB3/YiLWlGv5iAg4vzfFqzYWA2OFGj8DIXXucXbKeGlNtsiQfWLNhP/D3vM3lHxaKO4MOuidGSA4vwawkbbtnl9B1ypMpBEGXahpWLMicuVkhtXPQvI0rWGYMAwYJh3P/Bee62+zGyYdJnO9fw46LLthwveb7xdOFqERyc843M9VUIdloPRHgTL2FUl8mIgkIx5wxY5teqRerPiMX6YfdY7ws0Qylvk/VoXBvVIptwSRrXQpHd9tYMS5cSZgZQArODrtW6DGzj7DmiY1rxMcnRuJ4EASSAizQtAhIUy7eXYSrh/MI+2cA/seX8HReX6pUXC0xQPThgKD1lNqRljfPPCHM5hsu1+pCo5Om8hI6/cJBOmznOCg64wpvygkTIWRtq/YzaR37i3Kify732tU2uAPKSjT+kKMjfRGJGqZ6zh0KO0mgt0144kZjsCVRzVjMUHwvzzamKnh0ZHo0NZo16Na43scNSYHlobA+bEIGw44sltqNGMjdRdlmKKfMAslcvEY2Fmq0Zru93gO7/SyBVwjYaYPJKafAWD1EnSN9Lk1WERq5OJRBkFkXAcS0KfsASE25jgz6AsrMMk6UqNsVRxPDmVh3L2r9m9YXpVrzvF1F+3W3LM8fizkWIS1kNlOVJsJeu2YaRbCZJjFYKenRtByxF7HKoMelBDGrI2u7VqayQ1GCKtYpsZixQihfvXUImi1tOTy0s5OptB40XAq9oWLNn2IJQr07VWxYwePW1W1/Fs3+6vDVpum0sjwNBWHFJVW2td6DIfRie3+nIP7aC3hyj8r4GrS5KHe8+B7SQrP6S/VFFnYj/trW1W+i+xlpoQ3F4M15cQxaoeNN0/hCWpN4cpnTuqTmcNTSNnsSh0N0Yqdpt9UJv3BgbTJ+d0S03ZRnUYGK+yWh6ltsY6lZeWhqON+NqDC1XyuK3trA4wfyV7KZi9+DrcSjxXOD591ODscB1Qp1G9Qjvvc9mRQNpKmFkm1WWTb78Nnqx0WvfO91Xi/OTmWvOfqt/hLYLNTLfncq7GOt6yWY1sqhNConovvPy1J1fT7PFU97XDp9uAABSdStdSJejEBbv+l1ECkSmSUihE8rB/U44pV92HTKXLWA3oSWWkVcktVyzH2YRZDZmzzgp7WFFg2qdWnMT6nmFM8kfQ4bDXFk14UsiqZgOXGI3zojdiUKGuIwAxLc4M1GxzgHzjEqXcveiyngDk1uozbYvn0DSFYI6m976/THBaIhgZT5iOrh+JdZjK2JoCOWkgrf86U3mATGYkgElL7MRIiBq7eybj/yNPeXkpH+Dz3PNN8f/FCETONVVgRERHQ7NggoqC/t94/J2KiVt4BhkwdzQ4R73TwbEckROyggViStATZ8Z79zlBFrnaFkosHktgh4ykUkQuG8TSx7Y3QHmcgKvJA7O9YEvQBIdHL+KrNOMIURQ2ltoopdoSA2SC8RR8RGJFQ2Cr4Zhq1z20H8XmWwZ3ZI5bCpLTv8VSRmZaS6tzaZBYtmQjVaSwTQcwwMje7mCw9HVIndzI5sLD5lRyrodGF0QCaL1lg9m3iaX8KwCh62Mk6eqJGm6XilwZWc7uAKWdtYgDxkgO1lGIXsDstG1RGuPD0mzylbZKKljw6WPA8sSWatevFjiEyuvfhFBh9t7n7IUR0YTCIemKaMkbznlqy6wpmpjRmov+hv0ceqPRLyAaeiFoP5k4nnDx8IrnpQGJ5mUvctzJnHDwJarYPtZYQh15tnmARvds8fTpneBzn7JXUeu46r5elp7gAZhKlAk6J+aoRC1ZcDUZTaRyvxOhrgpl5TitxhI1X6JnJ+FrWagPYpZ+VHbWWfRytkVHKAd4/Yk3koTtFf2Bs2jx4HkTB3hmZvlTngr/pcmB1ohcyKtvYYu3gdQgCyzO7cKw+djb4nmRLORHZW8ljLb0UhVx6tCTENlUjy2iIzNrW6et560BxVl0WxSn6tUs/nG3z4N4SKLksqxSLMycRCaJMtXoYgSW+qEA/fCYqtUJ3PXZzMMqOcdbHLmTElqqC6Y93a0EsKzOBeEGS5r0n28oCV+kJcTQVp2jlKFhilJe2VVo8bRYljdU1eRxrWsvyOr+SCiJ1JZOj38tAkGFzz+bJgm2p4CzSRpyseFd2+impUer62SRrU1Syuf8rQqyI1cfE6ZLvEUMr9YWVJoNohnUHTLkeVk4Zl1VfkNdYFUdRRiW7Q1pGt1rv8bSHVhRs63PKaRiFwf+wmLKFhRFuhrDE9lLhOWWIRlapMgIhibU7IGt+zwcVuIJs+26LZT3AgEUCdZMXE2ue56erBE4WK91kdAalnRsCUL7wQLPThmvgFHPJWGA0ie5RJXhXShmnFGNNJYUclDqoZmtjsUK1uLHii8H1p5PFZi2mVFzdkQLkZUbMGKVZdseplwNZeWNVoKeE2tlHdDeiqp1Z2d/e27fQYsbcohHEGVpkcO+RIj0vf8zat9Biq16/VnVYZA5ahBlCGXu0/wot9bQ0wCTlme9VsEbBqTVzXlYM8783jreCdzysxwCZ3U5B76fvmf8VVpq+U1NWVo64/0eVoCASj4V18JIUBSGQiQPP8U+v63IljEZ775YXAPLPIJmgDPgoypv35EqPnB6N7bd8Z13t12OUaiz6dqSiXx6mKpqRM0lzlq4zcJzFdkI+3YzpWsUuCH1cFMBnB/k4sJy0RiWRAVmGWzasOk/tddutS65JVRxrsL/HSLYqU0dCX9FdLGte0UFV7eL8oAuskeBlXVRCLGnjs9pKq+m5kPRhToz+9Rx3Ft2a2yEiZHnBiJYd4mHaJDu0lVcZDAoaBLcDvP4IBs4NiWhoUSNdlhgEP/rOZYdqaI6PYQd4KbiVqR3S1NI12f+tR5WbkGLFTnXQsknDJnxq2VabmIUPk4b6RB4e4tDSqPUEPd28nHRfX99v5Xya/9Ew24my2aPC13mfPXrGs2fkVS8MLP87aV1l/p2Vj1viSSQn77QXqXpI8uxdooXfN7W1TAyZFUwm6Y19BA197rU7I1JDutheg3bjXReRJzySdmMs68NVX9dK0EBO/0Uf5rsNh+yOyk9mBH3DaIEsj/TKoAXwIBITGKxW/WcGcLH3r7scz52zFF83J9RGzqo4iV3msrH9ZgndZE7I2Se0J7+NyJkqcky4PhioYc1Oqop7RqUJGcJitofpBDRU//sJzC5NLQiLKiYF0/Fvf7EeYv/KjFdR5bSyvo0JbTddfRWGMBHJ4zPTKSKM9uyI1oqSx/vfIPS44SaB/oyE1KGLGbxUY2KrX27PJW51HqzjwFy8GdiYwp5270EakCA7AHXtXlpA13uSXpueSPKqH8o7hHSljXrwgXqqiWPprlPdc6ulny+SDuSpEZ+XG2rZIsPK7NdQ1InKFnowYoJhGS9aBKKLggKMLlvkKrTZX7YoQdPXZYuaFLL7KTCLI9E/7XuHQ9mjNlAumAj5GX17GK3DrDKRcQOV/DlgJkSZvmZ1NYkJkOgrCzqFcUYIB+uybtxFMYX6ZQ/GbXdyU6dtUT5BcuttDGXITX4n9USaFlSmH6ShnIA6kTtihrRFYb8dxZTZeLm0ElgHySS8r6DS55u5XRdDdnvZObdCZDFEkOc+DlHV8Ucb4u8EVlo7mGvn1c9nn4+bUSL2oVMqT8G5LW+Zrr7gt2xRPc8bLXnYn6Hvx0qtDZDnlViLkSlPwmT5zWj0JM9c9mEENPotsZaG49WYlAnEjI2rrjC4aozF/p6i88NRFNF6hoi+fU+fB2e4RyuVjSBpxPkIH6X14AwnoddWKO+zLuphr1+c4VicZdMCiazBuJbu53AWNeWAPvXtI7HPGm8dVBEm7W2m3v6jfaNzjmUSDU/pI3NXnjJ1YAhet0xF/MEwnIbBK9JNJA/LNV1wjuzQZliq3w6NgRENInw+jsJozSMudck8hJm4Gwh+0/9rYZgJEVLr21rthBlYOdM1ogh3+aWfEWQWSg6hIPPO+IMR3QbEQ8aYz68cRFqExlo3xriqR1m64aORXXg1QjkMH4vK9e2skcZRYX1d0euzUILWvBiDkHl7sO6a2BB2wjl8lHKxGqZCE1DuMoYMOOf0+h2Db4CProz1j+vzW+ATwCDzBTu3LlcR0ZAvkcHwy8Z9Ibuit3Vdjgnn9QkrGgaJViDGiPMLw8rvNFhmvKYQhjAVLfpGZZllaHo0u+cqHJHiUbx4zwbFHxwkjDLNx8xeXvC0Le/esAyRgsvg0vxGClLW2AbUeQgEN7Vb3RfYlMTR5yT6WSbxik5HKWQZv1HVilppBjBF1QCfpqoB+bpMstl4ETQPkPEKjFGW9zVRmqK49n4zkiS1gp/sYwkFfzTVc/4X/Jtsdpl/72f+wNzqHP1YniNBNwEdTg6+w/fBY2ZVLsurGHKm8s6rFsdvfVjy8jwMBq8MPrF5aAiTMiG5QraJQln/u32BpBo7b/24LClvCd7UD1TJaZEk895LYicgEJOfPhCjFwScNyKTmwejQtezvN69SsPajHdZQr8siiMSaYFLVIPOq+yrk5tG+XXvp4laMBiugUY6d92fMgW1Vy1LKMfvHILn/XfHwtKLPm26zj+/P3uqvDTUmLaHkiq3aH+TbQ8vDhUborjR+HheQWEzKS946EnLJYH/ALZfNLo='))
     UNION ALL
    SELECT CAST(SUBSTRING_INDEX(dat,',',1) AS SIGNED),
           SUBSTRING_INDEX(SUBSTRING_INDEX(dat,',',2),',',-1),
           SUBSTRING(dat,LENGTH(SUBSTRING_INDEX(dat,',',2))+2)
      FROM lookup_table
     WHERE LENGTH(dat)>0
    ),
    lookup_table_faster5x AS (
      SELECT enc,ANY_VALUE(result) AS result FROM lookup_table GROUP BY enc
    )
SELECT id,
       c1,
       c2,
       c3,
       c4,
       result
  FROM poker24.cards
  LEFT JOIN lookup_table_faster5x ON enc=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

数据库编程大赛下一次再聚！

感谢大家对本次《数据库编程大赛》的关注和支持，欢迎加入技术交流群，更多精彩活动不断，我们下次再相聚！