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:自分でも完全版書いたんだけど、こっちの方がすごいのでこっちを採用
行持ち・列持ち (横持ち)
ちょっとまとめ。
行・列
行と列は漢字からイメージすると覚えやすい。
例えば、以下のテーブル
col_a | col_b | col_c | col_d |
---|---|---|---|
1 | 2 | 3 | 4 |
5 | 6 | 7 | 8 |
なら、一行目は 1, 2, 3, 4 だし、三列目は 3, 7 となる。
列持ち (横持ち)
では、列持ちとはどういうテーブルのことを言うのかというと、情報を「列で」持っているテーブルのことを言う。
なぜ「横」持ちかというと、横に何列もあるからであって、列自体の方向とは関係はない。
例えば、商品の分類を「横持ち」しているテーブルは以下のようになる。
id | name | group_1 | group_2 | group_3 | group_4 | group_5 | group_6 | group_7 | group_8 | group_9 | group_10 |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | aaa | Y | N | N | N | N | N | N | N | N | N |
2 | bbb | N | N | N | Y | N | N | N | N | N | N |
3 | ccc | N | N | N | N | N | N | N | N | Y | N |
4 | ddd | Y | N | N | N | N | N | N | N | N | N |
この設計方法の欠点は、変更に弱い点と、SQL が複雑になる点、一行のサイズが大きくなる点などが挙げられる。
変更に弱い
例えば、分類が 100 個に増えた場合、テーブル構成を変える必要がでてきてしまう。
(まずないが) 逆に、分類が減った場合にもテーブル構成を変える必要がある。
SQL が複雑になる
商品テーブルの他に、商品分類テーブルがあったとする*1。
id | name |
---|---|
1 | group 1 |
2 | group 2 |
3 | group 3 |
4 | group 4 |
5 | group 5 |
6 | group 6 |
7 | group 7 |
8 | group 8 |
9 | group 9 |
10 | group 10 |
商品名と分類名の一覧を取得したい場合、以下のような SQL が必要となる。
SELECT Item.name , ItemGroup.name FROM Items Item INNER JOIN ItemGroups ItemGroup ON ItemGroup.id = CASE WHEN Item.group_1 = 'Y' THEN 1 WHEN Item.group_2 = 'Y' THEN 2 WHEN Item.group_3 = 'Y' THEN 3 WHEN Item.group_4 = 'Y' THEN 4 WHEN Item.group_5 = 'Y' THEN 5 WHEN Item.group_6 = 'Y' THEN 6 WHEN Item.group_7 = 'Y' THEN 7 WHEN Item.group_8 = 'Y' THEN 8 WHEN Item.group_9 = 'Y' THEN 9 WHEN Item.group_10 = 'Y' THEN 10 END
これはお世辞にも読みやすい SQL とは言えないし、パフォーマンス的にもあまりいいものではない。
行持ち
では、行持ちではどうなるのかを見てみよう。
まず、商品テーブルは以下のようになる。
id | name | g_id |
---|---|---|
1 | aaa | 1 |
2 | bbb | 4 |
3 | ccc | 9 |
4 | ddd | 1 |
そして、商品分類テーブルは変わらない。
id | name |
---|---|
1 | group 1 |
2 | group 2 |
3 | group 3 |
4 | group 4 |
5 | group 5 |
6 | group 6 |
7 | group 7 |
8 | group 8 |
9 | group 9 |
10 | group 10 |
これだけでも、サイズに与えるインパクトが分かってもらえると思う。
更に、商品名と分類名の一覧を取得したい場合の SQL は、
SELECT Item.name , ItemGroup.name FROM Items Item INNER JOIN ItemGroups ItemGroup ON Item.g_id = ItemGroup.id
でいい。
商品分類を増やすのは、商品分類テーブルに追加するだけだし、減らす場合は商品分類テーブルから削除するだけだ。
更に、Items.g_id と ItemGroups.id にリレーションシップを設定しておけば、商品テーブルで使っている分類を削除できないようにすることも容易に出来る。
これは少し特殊な「列持ち」の例なのだが、それでも列持ちからくる不便さはよく分かってもらえると思う。
非正規化と列持ちを同じように扱う場合があるが、非正規化とは「正規化した上でパフォーマンスを考慮して正規化をわざとくずす」ことであり、列持ちははじめから正規化なんて考えていない*2ことが多いため、別物と思っておいた方がいいだろう。
「以下について教えてあげよう♪」・・・SQL で
「以下について教えてあげよう♪」 - ...ing logging 3.0 経由、以下について教えてあげよう♪
書いてみた。やっぱり SQL で。
WITH Input(id, str) AS ( SELECT 1, 'Abc012_59F_#012Gh' ) , Transform(id, res, input_str) AS ( SELECT id , CAST('' AS varchar(max)) , str FROM Input UNION ALL SELECT id , res + CASE WHEN LEFT(input_str, 1) = '#' THEN input_str WHEN LEFT(input_str, 1) LIKE '[0-9]' THEN CAST(9 - CAST(LEFT(input_str, 1) AS int) AS char(1)) ELSE LOWER(LEFT(input_str, 1)) END , CASE LEFT(input_str, 1) WHEN '#' THEN '' ELSE SUBSTRING(input_str, 2, LEN(input_str)) END FROM Transform WHERE input_str <> '' ) , Result(id, val) AS ( SELECT id , res FROM Transform WHERE input_str = '' ) SELECT id , val FROM Result ORDER BY id
なんかもういろいろとダメかもしれんね...orz
あ、途中結果はこんな感じ。
id | res | input_str |
---|---|---|
1 | Abc012_59F_#012Gh | |
1 | a | bc012_59F_#012Gh |
1 | ab | c012_59F_#012Gh |
1 | abc | 012_59F_#012Gh |
1 | abc9 | 12_59F_#012Gh |
1 | abc98 | 2_59F_#012Gh |
1 | abc987 | _59F_#012Gh |
1 | abc987_ | 59F_#012Gh |
1 | abc987_4 | 9F_#012Gh |
1 | abc987_40 | F_#012Gh |
1 | abc987_40f | _#012Gh |
1 | abc987_40f_ | #012Gh |
1 | abc987_40f_#012Gh |