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),...')), '\n', '')
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),...')), '\n', '') 把结果编码输出
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 ALLSELECT CAST(SUBSTRING_INDEX(dat,',',1) AS SIGNED),SUBSTRING_INDEX(SUBSTRING_INDEX(dat,',',2),',',-1),SUBSTRING(dat,LENGTH(SUBSTRING_INDEX(dat,',',2))+2)FROM lookup_tableWHERE 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,resultFROM poker24.cardsLEFT JOIN lookup_table_faster5x ON enc=CASE c1WHEN 1 THEN 2WHEN 2 THEN 3WHEN 3 THEN 5WHEN 4 THEN 7WHEN 5 THEN 11WHEN 6 THEN 13WHEN 7 THEN 17WHEN 8 THEN 19WHEN 9 THEN 23WHEN 10 THEN 29END *CASE c2WHEN 1 THEN 2WHEN 2 THEN 3WHEN 3 THEN 5WHEN 4 THEN 7WHEN 5 THEN 11WHEN 6 THEN 13WHEN 7 THEN 17WHEN 8 THEN 19WHEN 9 THEN 23WHEN 10 THEN 29END *CASE c3WHEN 1 THEN 2WHEN 2 THEN 3WHEN 3 THEN 5WHEN 4 THEN 7WHEN 5 THEN 11WHEN 6 THEN 13WHEN 7 THEN 17WHEN 8 THEN 19WHEN 9 THEN 23WHEN 10 THEN 29END *CASE c4WHEN 1 THEN 2WHEN 2 THEN 3WHEN 3 THEN 5WHEN 4 THEN 7WHEN 5 THEN 11WHEN 6 THEN 13WHEN 7 THEN 17WHEN 8 THEN 19WHEN 9 THEN 23WHEN 10 THEN 29END
数据库编程大赛下一次再聚！
感谢大家对本次《数据库编程大赛》的关注和支持，欢迎加入技术交流群，更多精彩活动不断，我们下次再相聚！
