傅昌林：百万级数据挑战的大师，NineData编程大赛的卓越表现

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

参赛选手：傅昌林

个人简介：HBI Solutions, Inc, VP Engineering

参赛数据库：SQL Server

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

综合得分：78.8

以下是傅昌林选手的代码说明思路简介：

1. 数据范围估计

2. 由于只有715种数字组合且每种组合至多需要一种解答, 算法的主要思想是构建出这个结果集, 对于每条查询数据在这个结果集里寻找匹配

3. 随后按照具体的12条步骤生成最终结果。

以下是傅昌林选手的算法说明，结尾附完整SQL：

---

算法说明：

参赛完整SQL：

;WITH Nums AS (    SELECT CAST(value as INT) as val, value as str    FROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10', ',')), Ops AS (    SELECT value as str    FROM STRING_SPLIT('+,-,*,/', ',')), ExprN2 AS (    SELECT        u.str + op.str + v.str AS str,        CASE            WHEN op.str = '+' THEN u.val + v.val            WHEN op.str = '-' THEN u.val - v.val            WHEN op.str = '*' THEN u.val * v.val            WHEN op.str = '/' THEN u.val / v.val        END AS val,        u.val AS n1,        v.val AS n2,        op.str AS op1    FROM Nums u    LEFT JOIN Nums v ON 1 = 1    LEFT JOIN Ops op ON 1 = 1    WHERE (op.str = '/' AND v.val <> 0 AND u.val % v.val = 0) OR (op.str != '/')), ExprN3 AS (  SELECT        IIF(op.str IN ('*', '/') AND u.op1 IN ('+', '-'), '(' + u.str + ')' + op.str + v.str, u.str + op.str + v.str) AS str,        CASE            WHEN op.str = '+' THEN u.val + v.val            WHEN op.str = '-' THEN u.val - v.val            WHEN op.str = '*' THEN u.val * v.val            WHEN op.str = '/' THEN u.val / v.val        END AS val,        u.n1,    u.n2,        v.val AS n3,    u.op1,        op.str AS op2    FROM ExprN2 u    LEFT JOIN Nums v ON 1 = 1    LEFT JOIN Ops op ON 1 = 1    WHERE (op.str = '/' AND v.val <> 0 AND u.val % v.val = 0) OR (op.str != '/')), ExprN4 AS (  SELECT        IIF(op.str IN ('*', '/') AND u.op2 IN ('+', '-'), '(' + u.str + ')' + op.str + v.str, u.str + op.str + v.str) AS str,        CASE            WHEN op.str = '+' THEN u.val + v.val            WHEN op.str = '-' THEN u.val - v.val            WHEN op.str = '*' THEN u.val * v.val            WHEN op.str = '/' THEN u.val / v.val        END AS val,        u.n1,    u.n2,    u.n3,        v.val AS n4    FROM ExprN3 u    LEFT JOIN Nums v ON 1 = 1    LEFT JOIN Ops op ON 1 = 1    WHERE (op.str = '/' AND v.val <> 0 AND u.val % v.val = 0) OR (op.str != '/')), ExprN2N2 AS (    SELECT    '(' + u.str + ')' + op.str + '(' + v.str + ')' AS str,        CASE            WHEN op.str = '+' THEN u.val + v.val            WHEN op.str = '-' THEN u.val - v.val            WHEN op.str = '*' THEN u.val * v.val            WHEN op.str = '/' THEN IIF(v.val = 0, 0, u.val / v.val)        END AS val,        u.n1,    u.n2,        v.n1 AS n3,        v.n2 AS n4    FROM ExprN2 u    LEFT JOIN ExprN2 v ON 1 = 1    LEFT JOIN Ops op ON 1 = 1    WHERE (op.str = '/' AND IIF(v.val = 0, -1, u.val % v.val) = 0) OR (op.str != '/')), ExprSpecial AS (  -- Case 1: a * (b/c + d) = ab/c + ad  SELECT    a.str + '*(' + b.str + '/' + c.str + '+' + d.str + ')' AS str,    24 AS val,    a.val AS n1,    b.val AS n2,    c.val AS n3,    d.val AS n4  FROM Nums a  LEFT JOIN Nums b ON 1 = 1  LEFT JOIN Nums c ON 1 = 1  LEFT JOIN Nums d ON 1 = 1  WHERE a.val * b.val % c.val = 0 AND a.val * b.val / c.val + a.val * d.val = 24  -- Case 2: a * (b/c - d) = ab/c - ad  UNION  SELECT    a.str + '*(' + b.str + '/' + c.str + '+' + d.str + ')' AS str,    24 AS val,    a.val AS n1,    b.val AS n2,    c.val AS n3,    d.val AS n4  FROM Nums a  LEFT JOIN Nums b ON 1 = 1  LEFT JOIN Nums c ON 1 = 1  LEFT JOIN Nums d ON 1 = 1  WHERE a.val * b.val % c.val = 0 AND a.val * b.val / c.val - a.val * d.val = 24  -- Case 3: a * (b - c/d) = ab - ac/d  UNION  SELECT    a.str + '*(' + b.str + '-' + c.str + '/' + d.str + ')' AS str,    24 AS val,    a.val AS n1,    b.val AS n2,    c.val AS n3,    d.val AS n4  FROM Nums a  LEFT JOIN Nums b ON 1 = 1  LEFT JOIN Nums c ON 1 = 1  LEFT JOIN Nums d ON 1 = 1  WHERE a.val * c.val % d.val = 0 AND a.val *  b.val - a.val * c.val / d.val = 24  -- Case 4: a / (b/c + d) = ac / (b + cd)  UNION  SELECT    a.str + '/(' + b.str + '/' + c.str + '+' + d.str + ')' AS str,    24 AS val,    a.val AS n1,    b.val AS n2,    c.val AS n3,    d.val AS n4  FROM Nums a  LEFT JOIN Nums b ON 1 = 1  LEFT JOIN Nums c ON 1 = 1  LEFT JOIN Nums d ON 1 = 1  WHERE (b.val + c.val * d.val) <> 0 AND a.val * c.val % (b.val + c.val * d.val) = 0 AND a.val *  c.val / (b.val + c.val * d.val) = 24  -- Case 5: a / (b/c - d) = ac / (b - cd)  UNION  SELECT    a.str + '/(' + b.str + '/' + c.str + '-' + d.str + ')' AS str,    24 AS val,    a.val AS n1,    b.val AS n2,    c.val AS n3,    d.val AS n4  FROM Nums a  LEFT JOIN Nums b ON 1 = 1  LEFT JOIN Nums c ON 1 = 1  LEFT JOIN Nums d ON 1 = 1  WHERE (b.val - c.val * d.val) <> 0 AND a.val * c.val % (b.val - c.val * d.val) = 0 AND a.val *  c.val / (b.val - c.val * d.val) = 24  -- Case 6: a / (b - c/d) = ad / (bd - c)  UNION  SELECT    a.str + '/(' + b.str + '-' + c.str + '/' + d.str + ')' AS str,    24 AS val,    a.val AS n1,    b.val AS n2,    c.val AS n3,    d.val AS n4  FROM Nums a  LEFT JOIN Nums b ON 1 = 1  LEFT JOIN Nums c ON 1 = 1  LEFT JOIN Nums d ON 1 = 1  WHERE (b.val * d.val - c.val) <> 0 AND a.val * d.val % (b.val * d.val - c.val) = 0 AND a.val *  d.val / (b.val * d.val - c.val) = 24), ExprMerged AS (  SELECT *  FROM ExprN4  WHERE val = 24  UNION  SELECT *  FROM ExprN2N2  WHERE val = 24  UNION  SELECT *  FROM ExprSpecial), ExprWithTag AS (  SELECT S.str AS result, STRING_AGG(num, ',') WITHIN GROUP (ORDER BY num) as tag  FROM (SELECT str, col, num FROM ExprMerged UNPIVOT (num for col IN (n1, n2, n3, n4)) unpvt) S  GROUP BY S.str ), ResultWithRk AS (  SELECT tag, result, ROW_NUMBER() OVER(PARTITION BY tag ORDER BY result) as rk  FROM ExprWithTag), ResultLookup AS (  SELECT tag, result  FROM ResultWithRk  WHERE Rk = 1)SELECT Src.id, Src.c1, Src.c2, Src.c3, Src.c4, Ans.resultFROM poker24.cards SrcJOIN (  SELECT S.id, STRING_AGG(num, ',') WITHIN GROUP (ORDER BY num) as tag  FROM (SELECT id, col, num FROM poker24.cards UNPIVOT (num for col IN (c1, c2, c3, c4)) unpvt) S  GROUP BY S.id ) IdTag  ON Src.id = IdTag.idLEFT JOIN ResultLookup Ans  ON IdTag.tag = Ans.tagORDER BY Src.id

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

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