英文:
Implementing BODMAS expression evaluation in Oracle with split variable inputs and inner/outer brackets
问题
Oracle实现基于给定表达式拆分为2个变量的BODMAS表达式。
让我们假设,我有以下表达式:
((Rule1 and Rule2 and Rule3) and (not (rule4))
我有一个包含以下记录的表格:
Item_no item_type active_from active_to rule id
10001 SAR 01-Jan-2023 31-Jan-2023 rule1
10001 SAR 01-Feb-2023 28-Feb-2023 rule1
10001 SAR 01-Jan-2023 31-Dec-9999 rule2
10001 SAR 01-Feb-2023 28-Feb-2023 rule3
10001 SAR 01-Feb-2023 31-Dec-2023 rule3
10001 SAR 01-Jan-2023 31-Dec-2023 rule4
在这里,根据ruleid列,我需要实现BODMAS类型的评估。
即在上述表达式中,首先我需要评估所有括号内的“and”子句,然后获取公共有效期(这里“and”表示rule1、rule2、rule3之间的所有active_from和active_to日期的交集),然后基于此输出,我需要使用not in子句(减去)Rule4。
我试图根据ruleid列在上表中实现“active from”和“active to”子句的评估,使用动态BODMAS类型评估。无论我得到什么表达式,我都需要根据BODMAS类型的评估来评估“active from”和“active to”。请注意,表达式可以是任何类型(AND、OR、NOT)。
我尝试的是:
在这种情况下,我需要使用“AND”运算符传递rule1和rule2,并获得结果,将结果命名为R1。现在,我需要将R1与Rule3组合,使用“AND”运算符并获得结果R2。一旦rule1、rule2和rule3都完成了,我需要将R2与Rule4一起评估。
再举一个表达式的例子:((rule1 and rule2) or ( rule3 and rule4))
在这种情况下,
步骤1:rule1和rule2得到结果R1
步骤2:rule3和rule4得到结果R2
步骤3:将R1与R2进行OR操作,得到最终结果。
如何在Oracle中实现BODMAS表达式评估(首先评估所有内部括号,然后评估外部括号)?
计划创建表格,首先存储内部括号的结果,然后存储外部括号的执行结果,但如果在括号内组合了多个具有相同运算符的规则,我就会遇到困难,因为我的函数一次只允许2个规则。
英文:
Oracle implementation of bodmas expression based on given expression split into 2 variables.
Lets say, i have below expression
((Rule1 and Rule2 and Rule3) and (not (rule4))
I have a table containing following records
Item_no item_type active_from active_to rule id
10001 SAR 01-jan-2023 31-Jan-2023 rule1
10001 SAR 01-Feb-2023 28-Feb-2023 rule1
10001 SAR 01-Jan-2023 31-Dec-9999 rule2
10001 SAR 01-Feb-2023 28-Feb-2023 rule3
10001 SAR 01-Feb-2023 31-Dec-2023 rule3
10001 SAR 01-Jan-2023 31-Dec-2023 rule4
Here based on ruleid column, i need to implement bodmass type evaluation.
I.e.in the above expression, first i need to evaluate all 'and' clause enclosed in brackets first and get the common validity periods( here 'and' denotes intersection of all active from and active to dates between rule1,rule2,rule3) and then based on this output , i need to use not in clause(minus) Rule4.
I am trying to implement evaluation on active from and active to clause in the above table based on ruleid column using bodmass dynamically. Whatever the expression i get, i need to evaluate the 'active from' and 'active to' based on bodmas type evaluations..please note, expression can be of any type.
(AND,OR,NoT)
What i tried is:
In this case, i need to pass rule1 and rule2 with 'AND' operator and get the result and make the result as R1. Now i need to combine this R1 with Rule3 with 'And' operator and get result as R2.once all rule1 and rule2 and rule3 is done, i need to evaluate R2 not with Rule4.
One more expression example: ((rule1 and rule2) or ( rule3 and rule4))
In this case,
Step1: rule1 and rule2 get result as R1
Step2: rule3 and rule4 get result R2
Step3: R1 or with R2 get final result.
How to achieve bodmas expression evaluation in this oracle. ( evaluate all inner bracket first as 2-2 rules and then evaluate outside brackets)?
Planned to create table and store inner brackets results first and then outer brackets for execution but got stuck if i have more than one rule combined with same operator inside brackets.as my function allows only 2 rule at a time
答案1
得分: 0
根据你的要求,以下是代码部分的中文翻译:
根据[我对你之前问题的回答](https://stackoverflow.com/a/76392654/1509264)(你可能可以自己完成),对于 `rule1 and rule2 and rule3 and not rule4`,你可以使用以下代码:
```sql
SELECT item_no,
item_type,
active_from
+ CASE
WHEN prev_rule4 > 0
THEN INTERVAL '1' SECOND
ELSE INTERVAL '0' SECOND
END AS active_from,
active_to
- CASE
WHEN next_rule4 > 0
THEN INTERVAL '1' SECOND
ELSE INTERVAL '0' SECOND
END AS active_to
FROM (
SELECT item_no,
item_type,
rule_id,
dt,
COALESCE(
SUM(CASE rule_id WHEN 'rule1' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
),
0
) AS rule1,
COALESCE(
SUM(CASE rule_id WHEN 'rule2' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
),
0
) AS rule2,
COALESCE(
SUM(CASE rule_id WHEN 'rule3' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
),
0
) AS rule3,
COALESCE(
SUM(CASE rule_id WHEN 'rule4' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
),
0
) AS rule4
FROM table_name
UNPIVOT (
dt FOR active IN ( active_from AS 1, active_to AS -1 )
)
)
MATCH_RECOGNIZE(
PARTITION BY item_no, item_type
ORDER BY dt, rule1 DESC, rule2 DESC
MEASURES
FIRST(dt) AS active_from,
PREV(rule4) AS prev_rule4,
NEXT(dt) AS active_to,
NEXT(rule4) AS next_rule4
PATTERN ( active_rules+ )
DEFINE active_rules AS rule1 > 0 AND rule2 > 0 AND rule3 > 0 AND rule4 = 0
)
对于示例数据:
CREATE TABLE table_name (Item_no, item_type, active_from, active_to, rule_id) AS
SELECT 10001, 'SAR', DATE '2023-01-01', DATE '2023-01-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-02-01', DATE '2023-02-28', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-01-01', DATE '9999-12-31', 'rule2' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-02-01', DATE '2023-02-28', 'rule3' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-02-01', DATE '2023-12-31', 'rule3' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-01-01', DATE '2023-02-03', 'rule4' FROM DUAL;
注意:这与问题中的示例数据不同,因为问题中的示例数据将返回零行,因为 rule4
行涵盖了所有 rule1
和 rule3
行。示例数据已更改,以便返回一行。
输出:
ITEM_NO | ITEM_TYPE | ACTIVE_FROM | ACTIVE_TO |
---|---|---|---|
10001 | SAR | 2023-02-03 00:00:01 | 2023-02-28 00:00:00 |
你可以对之前问题的答案进行类似的更改,通过修改 MATCH_RECOGNIZE
部分的 DEFINE
子句来实现其他你想要的逻辑结果。
英文:
Adapting my answer to your previous question (which you can probably do yourself), then for rule1 and rule2 and rule3 and not rule4
you can use:
SELECT item_no,
item_type,
active_from
+ CASE
WHEN prev_rule4 > 0
THEN INTERVAL '1' SECOND
ELSE INTERVAL '0' SECOND
END AS active_from,
active_to
- CASE
WHEN next_rule4 > 0
THEN INTERVAL '1' SECOND
ELSE INTERVAL '0' SECOND
END AS active_to
FROM (
SELECT item_no,
item_type,
rule_id,
dt,
COALESCE(
SUM(CASE rule_id WHEN 'rule1' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
),
0
) AS rule1,
COALESCE(
SUM(CASE rule_id WHEN 'rule2' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
),
0
) AS rule2,
COALESCE(
SUM(CASE rule_id WHEN 'rule3' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
),
0
) AS rule3,
COALESCE(
SUM(CASE rule_id WHEN 'rule4' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
),
0
) AS rule4
FROM table_name
UNPIVOT (
dt FOR active IN ( active_from AS 1, active_to AS -1 )
)
)
MATCH_RECOGNIZE(
PARTITION BY item_no, item_type
ORDER BY dt, rule1 DESC, rule2 DESC
MEASURES
FIRST(dt) AS active_from,
PREV(rule4) AS prev_rule4,
NEXT(dt) AS active_to,
NEXT(rule4) AS next_rule4
PATTERN ( active_rules+ )
DEFINE active_rules AS rule1 > 0 AND rule2 > 0 AND rule3 > 0 AND rule4 = 0
)
Which, for the sample data:
CREATE TABLE table_name (Item_no, item_type, active_from, active_to, rule_id) AS
SELECT 10001, 'SAR', DATE '2023-01-01', DATE '2023-01-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-02-01', DATE '2023-02-28', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-01-01', DATE '9999-12-31', 'rule2' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-02-01', DATE '2023-02-28', 'rule3' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-02-01', DATE '2023-12-31', 'rule3' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-01-01', DATE '2023-02-03', 'rule4' FROM DUAL;
Note: this is different to the sample data in the question as the sample in the question would return zero rows as the rule4
row covers all rule1
and rule3
rows. The sample data was changed so that a row is returned.
Outputs:
ITEM_NO | ITEM_TYPE | ACTIVE_FROM | ACTIVE_TO |
---|---|---|---|
10001 | SAR | 2023-02-03 00:00:01 | 2023-02-28 00:00:00 |
You can make similar changes to the answers to your previous questions to get the results for the other logic that you want to implement by changing the DEFINE
clause of the MATCH_RECOGNIZE
section.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论