SQL で数式を評価 (完全版 + α)
大阪で働く友人 mi との合作*1で不完全版が完全版に!
やっぱり mi はすごかった!!
WITH Input(id, str) AS ( -- idと数式を渡す SELECT 1, '1 + 3 / -8' UNION ALL SELECT 2, '2*3*4*5+99' UNION ALL SELECT 3, '4 * (9 - 4) / (2 * 6 - 2) + 8' UNION ALL SELECT 4, '1 + ((123 * 3 - 69) / 100)' UNION ALL SELECT 5, '2.45/8.5*9.27+(5*0.0023)' ) , Separators(i, ch, str_src, priority) AS ( SELECT 1, '-', 1, 1 UNION ALL SELECT 2, '+', 1, 1 UNION ALL SELECT 3, '*', 1, 2 UNION ALL SELECT 4, '/', 1, 2 UNION ALL SELECT 5, '(', 0, 0 UNION ALL SELECT 6, ')', 0, 0 ) , SeparatorsStrSrc(str, i) AS ( SELECT CAST('[' AS varchar(max)), 0 UNION ALL SELECT str + ch , SSS.i + 1 FROM SeparatorsStrSrc SSS INNER JOIN Separators S ON SSS.i = S.i - 1 WHERE str_src <> 0 ) , SeparatorsStr(str) AS ( SELECT str + ']' FROM SeparatorsStrSrc WHERE i = (SELECT COUNT(*) FROM Separators WHERE str_src <> 0) ) , ExprElementsSrc(id, i, tmp, ele, pre_ch, input_str) AS ( -- 数式を要素に分割する SELECT id , 1 -- 要素に連番を振る , CAST(LEFT(str, 1) AS varchar(max)) , CAST('' AS varchar(max)) , CAST(' ' AS char(1)) , SUBSTRING(str, 2, LEN(str)) FROM Input UNION ALL SELECT id , CASE ele WHEN '' THEN i ELSE i + 1 END , CAST( CASE WHEN LEFT(input_str, 1) = ' ' THEN '' WHEN tmp = '-' THEN CASE WHEN pre_ch LIKE (SELECT str FROM SeparatorsStr) THEN tmp + LEFT(input_str, 1) ELSE LEFT(input_str, 1) END WHEN LEFT(input_str, 1) IN (SELECT ch FROM Separators) OR tmp IN (SELECT ch FROM Separators) THEN LEFT(input_str, 1) ELSE tmp + LEFT(input_str, 1) END AS varchar(max)) , CAST( CASE WHEN LEFT(input_str, 1) = ' ' THEN tmp WHEN LEFT(input_str, 1) = '-' THEN CASE WHEN tmp IN (SELECT ch FROM Separators) THEN tmp ELSE '' END WHEN LEFT(input_str, 1) IN (SELECT ch FROM Separators) OR tmp IN (SELECT ch FROM Separators) THEN CASE WHEN tmp = '-' AND pre_ch LIKE (SELECT str FROM SeparatorsStr) THEN '' ELSE tmp END ELSE '' END AS varchar(max)) , CAST(LEFT(ele, 1) AS char(1)) , SUBSTRING(input_str, 2, LEN(input_str)) FROM ExprElementsSrc WHERE input_str <> '' OR tmp <> '' ) , ExprElements(id, i, ele) AS ( SELECT id , i , ele FROM ExprElementsSrc WHERE ele <> '' ) , Scanner(id, i, val) AS ( SELECT id , i , CAST(ele AS varchar(max)) FROM ExprElements WHERE ele <> '' -- EOFを示す行を追加する UNION ALL SELECT id , MAX(i) + 1 , NULL FROM ExprElements GROUP BY id ) , Operator(op, priority) AS ( SELECT ch , priority FROM Separators WHERE priority <> 0 ) , Calc(id, c, i, pop_count, s0, s1, s2, stack, status) AS ( -- 初期値 SELECT Scanner.id , 1 , 1 , 0 , CAST(scanner.val AS varchar(max)) , CAST(NULL AS varchar(max)) , CAST(NULL AS varchar(max)) , CAST('' AS varchar(max)) , CAST('init' AS varchar(max)) FROM Scanner WHERE Scanner.i = 1 -- 演算 UNION ALL SELECT Calc.id , Calc.c + 1 , Calc.i , 3 , NULL , NULL , NULL , CASE Calc.s1 WHEN '+' THEN CAST(CAST(Calc.s2 AS real) + CAST(Calc.s0 AS real) AS varchar(max)) WHEN '-' THEN CAST(CAST(Calc.s2 AS real) - CAST(Calc.s0 AS real) AS varchar(max)) WHEN '*' THEN CAST(CAST(Calc.s2 AS real) * CAST(Calc.s0 AS real) AS varchar(max)) WHEN '/' THEN CAST(CAST(Calc.s2 AS real) / CAST(Calc.s0 AS real) AS varchar(max)) ELSE NULL END + ' ' + stack , CAST('calc ' + Calc.s1 AS varchar(max)) FROM Calc INNER JOIN Scanner NextVal ON Calc.id = NextVal.id AND Calc.i + 1 = NextVal.i WHERE Calc.pop_count = 0 AND ISNUMERIC(Calc.s2) = 1 AND Calc.s1 IN (SELECT op FROM Operator) AND ISNUMERIC(Calc.s0) = 1 AND (SELECT priority FROM Operator WHERE op = Calc.s1) >= COALESCE((SELECT priority FROM Operator WHERE op = NextVal.val), 0) -- paren UNION ALL SELECT Calc.id , Calc.c + 1 , Calc.i , 3 , NULL , NULL , NULL , s1 + ' ' + stack , CAST('paren' AS varchar(max)) FROM Calc WHERE pop_count = 0 AND s2 = '(' AND ISNUMERIC(s1) = 1 AND s0 = ')' -- pop UNION ALL SELECT Calc.id , Calc.c + 1 , Calc.i , Calc.pop_count - 1 , s1 , s2 , CASE WHEN LEN(stack) > 0 THEN SUBSTRING(stack, 1, CHARINDEX(' ', stack) - 1) ELSE NULL END , CASE WHEN LEN(stack) > 0 THEN SUBSTRING(stack, CHARINDEX(' ', stack) + 1, LEN(stack)) ELSE '' END , CAST('pop' AS varchar(max)) FROM Calc WHERE Calc.pop_count > 0 -- 読み込み UNION ALL SELECT Calc.id , Calc.c + 1 , Calc.i + 1 , Calc.pop_count , CAST(NextVal.val AS varchar(max)) , s0 , s1 , COALESCE(s2, '') + ' ' + stack , CAST('read' AS varchar(max)) FROM Calc INNER JOIN Scanner NextVal ON Calc.id = NextVal.id AND Calc.i + 1 = NextVal.i WHERE NextVal.val IS NOT NULL -- 次の要素がないなら読み込まない AND Calc.pop_count = 0 AND ( (Calc.s0 IS NULL OR Calc.s1 IS NULL OR Calc.s2 IS NULL) -- 下の比較でスタックの中がNULLだったときの保険(スタックの深さを持ったほうがいいのか?) OR -- 計算していない NOT( ISNUMERIC(Calc.s2) = 1 AND Calc.s1 IN (SELECT op FROM Operator) AND ISNUMERIC(calc.s0) = 1 AND (SELECT priority FROM Operator WHERE op = Calc.s1) >= COALESCE((SELECT priority FROM Operator WHERE op = NextVal.val), 0) ) -- 括弧でない AND NOT(s2 = '(' AND ISNUMERIC(s1) = 1 AND s0 = ')') ) ) SELECT Calc.id , Input.str , Calc.s0 AS result FROM Calc INNER JOIN Input ON Calc.id = Input.id WHERE Calc.c = (SELECT MAX(c) FROM Calc calc2 WHERE Calc.id = Calc2.id) ORDER BY id
役割分担としては、Separators と SeparatorsStrSrc と SeparatorsStr と ExprElementsSrc と ExprElements が自分で、Scanner と Operator と Calc(でかいw) が mi です。
しかも Separators と SeparatorsStrSrc と SeparatorsStr は、演算子周りの柔軟さを失わないようにするために後から付けたもので、オリジナル版では Operator は
, Operator(op, priority) AS ( SELECT '+', 1 UNION ALL SELECT '-', 1 UNION ALL SELECT '*', 2 UNION ALL SELECT '/', 2 )
な感じでした。
てことでほとんど mi が作ったようなものです。
再帰部分で UNION ALL を複数回使うとか、まったく考えつきもしなかった方法をさらっと使う、そこにしび(ry
特筆すべきは演算子周りの扱いのうまさ。
演算子の優先順位を変更したければ、Separators(元 Operator) の priority をいじるだけ。
タイトルに + α って付けたのは、実はこの SQL、かけ算と割り算を足し算と引き算より優先してくれるんです。
元々のお題的には不要な機能なんだけど、元々のお題に対応したいときでもかけ算と割り算の優先順位を 1 に変更するだけで対応可能というすばらしさ。
更に、演算子の追加も基本的には Separators(元 Operator) に追加して、「-- 演算」というコメントが付いてる UNION ALL で連結している SELECT に処理を書くだけ。
素晴らしい!
あと、ISNUMERIC なんて構文 (関数ではないらしい) も初めて知りました。
なんか '+' とか '-' でも 1 を返すのが微妙っちゃ微妙だけど。
あ、実行結果はこんな感じ。
id | str | result |
---|---|---|
1 | 1 + 3 / -8 | 0.625 |
2 | 2*3*4*5+99 | 219 |
3 | 4 * (9 - 4) / (2 * 6 - 2) + 8 | 10 |
4 | 1 + ((123 * 3 - 69) / 100) | 4 |
5 | 2.45/8.5*9.27+(5*0.0023) | 2.68344 |
かけ算と割り算の priority も 1 にすると、
id | str | result |
---|---|---|
1 | 1 + 3 / -8 | -0.5 |
2 | 2*3*4*5+99 | 219 |
3 | 4 * (9 - 4) / (2 * 6 - 2) + 8 | 10 |
4 | 1 + ((123 * 3 - 69) / 100) | 4 |
5 | 2.45/8.5*9.27+(5*0.0023) | 2.68344 |
こう。ちゃんと数式を評価できてますね!
すごい!!
*1:自分でも完全版書いたんだけど、こっちの方がすごいのでこっちを採用