数据库编程大赛冠军：郑凌云：0.67秒通过百万级数据评测！SQL代码惊现神之一手！

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, '