Home » Other » General » Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas ***
| Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas *** [message #570101] |
Mon, 05 November 2012 11:56  |
Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
The purpose of this puzzle is to wrap-up all SQL or PL/SQL techniques to calculate hardcoded arithmetic expressions and formulas. Hadcoded arithmetic expression is a string storing any arithmetic expression where operands are constants. For example:
Formula is also a string storing any arithmetic expression where operands are either constants or variables. Variable values are provided separately. To calculate formula we need to substitute variables referenced in the formula with their values and then calculate resulting arithmetic expression. Formula examples:
a + b
a + b - 100
(a + b) * 100 / d
SY.
[EDITED by LF: added puzzle number to the topic title. I can't decide how difficult it is. I hope someone else will be kind and supply that piece of information.
Edit MC: 3 stars given the definition we defined of star]
[Updated on: Tue, 06 November 2012 01:47] by Moderator Report message to a moderator |
|
|
|
| Re: Calculating hardcoded arithmetic expressions and formulas [message #570105 is a reply to message #570101] |
Mon, 05 November 2012 12:37  |
Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Package DBMS_AW based solution
------------------------------
This solution requires Oracle supplied DBMS_AW package which comes with OLAP option. You will not be able to use this solution if OLAP option is not installed.
Expressions
-----------
with expressions as (
select '2 * 3' expr from dual union all
select '(2 + 3) * 5' expr from dual
)
select expr || ' = ' || dbms_aw.eval_number(expr) result
from expressions
/
RESULT
------------------
2 * 3 = 6
(2 + 3) * 5 = 25
SQL>
Formulas
--------
This solution uses, besides Oracle supplied DBMS_AW package, MODEL clause and therefore will not work prior to Oracle 10:
with formulas as (
select 1 id, 'a+ab' frm from dual union all
select 2, 'a*c+bc' from dual
),
variables as (
select 1 id, 'a' var, 2 val from dual union all
select 1, 'ab', 3 from dual union all
select 2, 'a', 6 from dual union all
select 2, 'bc', 7 from dual union all
select 2, 'c', 9 from dual
)
select id,
frm || ' = ' || expr || ' = ' || dbms_aw.eval_number(expr) result
from (select id,
frm,
expr,
r
from formulas f,
variables v
where v.id = f.id
model
partition by(f.id id)
dimension by(row_number() over(partition by f.id order by 1) r)
measures(
frm,
regexp_replace(frm,'(\w+)',' \1 ') expr,
' ' || var || ' ' var,
val
)
rules(
expr[any] order by r desc = regexp_replace(nvl(expr[cv() + 1],expr[cv()]),var[cv()],val[cv()])
)
)
where r = 1
/
ID RESULT
---------- ----------------------
1 a+ab = 2+3 = 5
2 a*c+bc = 6*9+7 = 61
SQL>
SY.
|
|
|
|
| Re: Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas [message #570106 is a reply to message #570101] |
Mon, 05 November 2012 12:45 |
Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
XMLQUERY based solution
-----------------------
Requires Oracle 11.
Expressions
-----------
with expressions as (
select '2 * 3' expr from dual union all
select '(2 + 3) * 5' expr from dual
)
select expr || ' = ' || xmlcast(xmlquery(expr returning content) as number) result
from expressions
/
RESULT
------------------
2 * 3 = 6
(2 + 3) * 5 = 25
SQL>
Formulas
--------
with formulas as (
select 1 id, 'a+ab' frm from dual union all
select 2, 'a*c+bc' from dual
),
variables as (
select 1 id, 'a' var, 2 val from dual union all
select 1, 'ab', 3 from dual union all
select 2, 'a', 6 from dual union all
select 2, 'bc', 7 from dual union all
select 2, 'c', 9 from dual
)
select id,
frm || ' = ' || expr || ' = ' || xmlcast(xmlquery(expr returning content) as number) result
from (select id,
frm,
expr,
r
from formulas f,
variables v
where v.id = f.id
model
partition by(f.id id)
dimension by(row_number() over(partition by f.id order by 1) r)
measures(
frm,
regexp_replace(frm,'(\w+)',' \1 ') expr,
' ' || var || ' ' var,
val
)
rules(
expr[any] order by r desc = regexp_replace(nvl(expr[cv() + 1],expr[cv()]),var[cv()],val[cv()])
)
)
where r = 1
/
ID RESULT
---------- ----------------------
1 a+ab = 2+3 = 5
2 a*c+bc = 6*9+7 = 61
SQL>
SY.
|
|
|
|
| Re: Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas [message #570109 is a reply to message #570101] |
Mon, 05 November 2012 12:56 |
Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
DBMS_XMLGEN based solution
--------------------------
Expressions
-----------
with expressions as (
select '2 * 3' expr from dual union all
select '(2 + 3) * 5' expr from dual
)
select expr || ' = ' ||
dbms_xmlgen.getxmltype(
'select ' || expr || ' x from dual'
).extract('/ROWSET/ROW/X/text()').getnumberval() result
from expressions
/
RESULT
------------------
2 * 3 = 6
(2 + 3) * 5 = 25
SQL>
Formulas
--------
with formulas as (
select 1 id, 'a+ab' frm from dual union all
select 2, 'a*c+bc' from dual
),
variables as (
select 1 id, 'a' var, 2 val from dual union all
select 1, 'ab', 3 from dual union all
select 2, 'a', 6 from dual union all
select 2, 'bc', 7 from dual union all
select 2, 'c', 9 from dual
)
select id,
frm || ' = ' || expr || ' = ' ||
dbms_xmlgen.getxmltype(
'select ' || expr || ' x from dual'
).extract('/ROWSET/ROW/X/text()').getnumberval() result
from (select id,
frm,
expr,
r
from formulas f,
variables v
where v.id = f.id
model
partition by(f.id id)
dimension by(row_number() over(partition by f.id order by 1) r)
measures(
frm,
regexp_replace(frm,'(\w+)',' \1 ') expr,
' ' || var || ' ' var,
val
)
rules(
expr[any] order by r desc = regexp_replace(nvl(expr[cv() + 1],expr[cv()]),var[cv()],val[cv()])
)
)
where r = 1
/
ID RESULT
---------- ---------------------
1 a+ab = 2+3 = 5
2 a*c+bc = 6*9+7 = 61
SQL>
SY.
|
|
|
|
| Re: Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas [message #570118 is a reply to message #570101] |
Mon, 05 November 2012 14:39 |
Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Recursive subquery factoring based solutions for formulas
---------------------------------------------------------
Requires Oracle 11.2:
with formulas as (
select 1 id, 'a+ab' frm from dual union all
select 2, 'a*c+bc' from dual
),
variables as (
select 1 id, 'a' var, 2 val from dual union all
select 1, 'ab', 3 from dual union all
select 2, 'a', 6 from dual union all
select 2, 'bc', 7 from dual union all
select 2, 'c', 9 from dual
),
t as (
select id,
' ' || v.var || ' ' var,
val,
row_number() over(partition by id order by 1) rn,
count(*) over(partition by id) cnt
from variables v
),
r(
id,
frm,
expr,
lvl,
cnt
) as (
select id,
frm,
regexp_replace(frm,'(\w+)',' \1 '),
0,
-1
from formulas
union all
select r.id,
r.frm,
regexp_replace(r.expr,t.var,t.val),
r.lvl + 1,
t.cnt
from r,
t
where t.id = r.id
and t.rn = r.lvl + 1
)
select id,
frm || ' = ' || expr || ' = ' ||
dbms_aw.eval_number(expr) result
from r
where lvl = cnt
/
ID RESULT
---------- --------------------
1 a+ab = 2+3 = 5
2 a*c+bc = 6*9+7 = 61
SQL>
with formulas as (
select 1 id, 'a+ab' frm from dual union all
select 2, 'a*c+bc' from dual
),
variables as (
select 1 id, 'a' var, 2 val from dual union all
select 1, 'ab', 3 from dual union all
select 2, 'a', 6 from dual union all
select 2, 'bc', 7 from dual union all
select 2, 'c', 9 from dual
),
t as (
select id,
' ' || v.var || ' ' var,
val,
row_number() over(partition by id order by 1) rn,
count(*) over(partition by id) cnt
from variables v
),
r(
id,
frm,
expr,
lvl,
cnt
) as (
select id,
frm,
regexp_replace(frm,'(\w+)',' \1 '),
0,
-1
from formulas
union all
select r.id,
r.frm,
regexp_replace(r.expr,t.var,t.val),
r.lvl + 1,
t.cnt
from r,
t
where t.id = r.id
and t.rn = r.lvl + 1
)
select id,
frm || ' = ' || expr || ' = ' ||
xmlcast(xmlquery(expr returning content) as number) result
from r
where lvl = cnt
/
ID RESULT
---------- --------------------
1 a+ab = 2+3 = 5
2 a*c+bc = 6*9+7 = 61
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Mon Nov 25 21:38:41 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
