在Oracle中实现BODMAS表达式评估,使用分割变量输入和内/外括号。

huangapple go评论58阅读模式
英文:

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 行涵盖了所有 rule1rule3 行。示例数据已更改,以便返回一行。

输出:

ITEM_NO ITEM_TYPE ACTIVE_FROM ACTIVE_TO
10001 SAR 2023-02-03 00:00:01 2023-02-28 00:00:00

fiddle


你可以对之前问题的答案进行类似的更改,通过修改 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

fiddle


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.

huangapple
  • 本文由 发表于 2023年6月5日 15:39:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/76404382.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定