MVEL advanced
Drools Expert - mvel - RHS - advanced Training Materials
Copyright Notice
Copyright © 2004-2023 by NobleProg Limited All rights reserved.
This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise.
Selecting data from many different Fact Types ⌘
Cartesian product (direct product)
rule "Sample" when d :Dept() e :Emp() then System.out.println(e.name + "\t" + d.dname) end
ELISON OPERATIONS ELISON SALES ELISON RESEARCH ELISON ACCOUNTING FORD OPERATIONS FORD SALES FORD RESEARCH FORD ACCOUNTING ... BUSH OPERATIONS BUSH SALES BUSH RESEARCH BUSH ACCOUNTING
Only those pairs which have the matching deptno in both fact types should be selected
rule "Sample" when d :Dept() e :Emp(d.deptno == deptno) then System.out.println(e.name + "\t" + d.dname) end
ELISON ACCOUNTING FORD RESEARCH CARNEGIE RESEARCH THATCHER RESEARCH TOOSK RESEARCH WALTON SALES BUFFETT SALES GATES SALES BAROSSO SALES CHIRACK SALES PUTIN RESEARCH MERKEL ACCOUNTING BLAIR SALES BUSH ACCOUNTING
Salgrade Example Find the name of an employee and their salary grade
rule "Sample" when s :Salgrade() e :Emp(sal >= s.losal && <= s.hisal) then System.out.println(e.name + "\t" + s.grade) end
ELISON 2 FORD 1 CARNEGIE 4 THATCHER 1 TOOSK 4 WALTON 2 BUFFETT 1 GATES 3 BAROSSO 3 CHIRACK 2 PUTIN 4 MERKEL 4 BLAIR 4 BUSH 5
More Complex Example ⌘
rule "Sample" when s :Salgrade() d :Dept() e :Emp(sal >= s.losal && <= s.hisal, d.deptno == deptno) then System.out.println(e.name + "\t" + s.grade + "\t" + d.dname ) end
ELISON 2 ACCOUNTING FORD 1 RESEARCH CARNEGIE 4 RESEARCH THATCHER 1 RESEARCH TOOSK 4 RESEARCH WALTON 2 SALES BUFFETT 1 SALES GATES 3 SALES BAROSSO 3 SALES CHIRACK 2 SALES PUTIN 4 RESEARCH MERKEL 4 ACCOUNTING BLAIR 4 SALES BUSH 5 ACCOUNTING
Exercises
31. Exercise
Select the name of the employee and the city (LOC column in DEPT table) in which they work.
BUSH NEW YORK BLAIR PARIS MERKEL NEW YORK PUTIN LONDON CHIRACK PARIS BAROSSO PARIS GATES PARIS BUFFETT PARIS WALTON PARIS TOOSK LONDON THATCHER LONDON CARNEGIE LONDON FORD LONDON ELISON NEW YORK
32. Exercise
Select the names of the employees, and the name and number of their department.
BUSH ACCOUNTING 10 BLAIR SALES 30 MERKEL ACCOUNTING 10 PUTIN RESEARCH 20 CHIRACK SALES 30 BAROSSO SALES 30 GATES SALES 30 BUFFETT SALES 30 WALTON SALES 30 TOOSK RESEARCH 20 THATCHER RESEARCH 20 CARNEGIE RESEARCH 20 FORD RESEARCH 20 ELISON ACCOUNTING 10
33. Exercise
Select the names of the employees, their salary and salary grade, but only those whose salary is more than 2000.
BUSH 5000.00 5 BLAIR 2850.00 4 MERKEL 2450.00 4 PUTIN 2975.00 4 TOOSK 3000.00 4 CARNEGIE 3000.00 4
34. Exercise
Select employees working in London.
PUTIN LONDON TOOSK LONDON THATCHER LONDON CARNEGIE LONDON FORD LONDON
35. Exercise
Select employees and their salary grades except those from London.
BUSH NEW YORK 5 BLAIR PARIS 4 MERKEL NEW YORK 4 CHIRACK PARIS 2 BAROSSO PARIS 3 GATES PARIS 3 BUFFETT PARIS 1 WALTON PARIS 2 ELISON NEW YORK 2
Finding outstanding facts ⌘
Find departments with employees
when d :Dept() exists (Emp(deptno == d.deptno)) then System.out.println( d.dname )
ACCOUNTING RESEARCH SALES
Find departments without employees
when d :Dept() not (Emp(deptno == d.deptno)) then System.out.println( d.dname )
OPERATIONS
Looking up Facts of the same type
We need to report who is whose boss
rule "Sample" when e :Emp() m :Emp(id == e.mgr) then System.out.println( e.name + " reports to " + m.name ) end
ELISON reports to MERKEL FORD reports to CARNEGIE CARNEGIE reports to PUTIN THATCHER reports to TOOSK TOOSK reports to PUTIN WALTON reports to BLAIR BUFFETT reports to BLAIR GATES reports to BLAIR BAROSSO reports to BLAIR CHIRACK reports to BLAIR PUTIN reports to BUSH MERKEL reports to BUSH BLAIR reports to BUSH
Exercises
36. Exercise
Find departments without employees.
OPERATIONS
37. *Exercise
Select an employee’s name and his/her boss’s name
ELISON boss is MERKEL FORD boss is CARNEGIE CARNEGIE boss is PUTIN THATCHER boss is TOOSK TOOSK boss is PUTIN WALTON boss is BLAIR BUFFETT boss is BLAIR GATES boss is BLAIR BAROSSO boss is BLAIR CHIRACK boss is BLAIR PUTIN boss is BUSH MERKEL boss is BUSH BLAIR boss is BUSH
38. Exercise
Select an employee who doesn't have a boss.
BUSH
39. Exercise
Show all employees who have no subordinates.
ELISON FORD THATCHER WALTON BUFFETT GATES BAROSSO CHIRACK
41. Exercise
Select employees hired earlier than their bosses.
THATCHER was hired in 1980, her boss TOOSK who was hired in 1981
44. ***Exercise
Find jobs which are in department 10 but not in department 20.
PRESIDENT
Accumulate Functions ⌘
Drools has a couple of built in accumulate functions:
- average
- min
- max
- count
- sum
Find the average salary in the whole company.
when avg: Number() from accumulate (Emp(s: sal),average(s)) then System.out.println(avg)
2073
Which can be also written this way:
when accumulate (e: Emp(), avg : average(e.sal)) then System.out.println(avg)
Syntax
accumulate( <source pattern>; <functions> [;<constraints>] )
Comparing to SQL:
SELECT AVG(column-name) FROM table-name
You can also use many accumulate functions in one go:
when accumulate (e: Emp(), $avg : average(e.sal), $min : min(e.sal), $max : max(e.sal) ) then System.out.println( "Average salary: " + $avg + "\n" + "Min salary: " + $min + "\n" + "Max salary: " + $max + "\n" )
Average salary: 2073.214285714286 Min salary: 800.0 Max salary: 5000.0
Exercises
23. Exercise
Find the minimal, maximal and average salaries of people employed in 1981.
Average salary: 2282.5 Min salary: 950.0 Max salary: 5000.0
24. Exercise
Find the difference between maximal and minimal salary.
Salary range: 4200.0
26. Exercise
How many managers work for the company?
3
Groups ⌘
Groups contain all facts which have the same values in a property or properties
Also known as factor levels in statistics.
CARNEGIE ANALYST 3000.00 TOOSK ANALYST 3000.00 ELISON CLERK 1300.00 FORD CLERK 1100.00 THATCHER CLERK 800.00 BUFFETT CLERK 950.00 PUTIN MANAGER 2975.00 MERKEL MANAGER 2450.00 BLAIR MANAGER 2850.00 BUSH PRESIDENT 5000.00 GATES SALESMAN 1500.00 BAROSSO SALESMAN 1600.00 WALTON SALESMAN 1250.00 CHIRACK SALESMAN 1250.00
There are five JOB groups above: ANALYST, CLERK, MANAGER, PRESIDENT and SALESMAN.
Comparing to SQL:
SELECT job, avg(sal) FROM Emp GROUP BY job;
Example
rule "Sample" when Emp(j: job) accumulate (e :Emp(job == j), $avg : average(e.sal) ) then System.out.println( "Average salary in position " + j + "is: " + $avg ) end
Average salary in position CLERKis: 1037.5 Average salary in position CLERKis: 1037.5 Average salary in position ANALYSTis: 3000.0 Average salary in position CLERKis: 1037.5 Average salary in position ANALYSTis: 3000.0 Average salary in position SALESMANis: 1400.0 Average salary in position CLERKis: 1037.5 Average salary in position SALESMANis: 1400.0 Average salary in position SALESMANis: 1400.0 Average salary in position SALESMANis: 1400.0 Average salary in position MANAGERis: 2758.3333333333335 Average salary in position MANAGERis: 2758.3333333333335 Average salary in position MANAGERis: 2758.3333333333335 Average salary in position PRESIDENTis: 5000.0
Declaring and inserting new facts ⌘
The problem with the solution above is that it repeats (loops) 14 times (the number of employees).
To solve this problem, we need to mark a group by inserting new fact SalByJob, and check whether we have already calculated the average, which the condition
not SalByJob(j == job)
does.
The code below will return exactly 5 averages.
declare SalByJob job : String avgSal : Double end rule "Sample" when Emp(j: job) and not SalByJob(j == job) accumulate (e :Emp(job == j), $avg : average(e.sal) ) then System.out.println( "Average salary in position " + j + "is: " + $avg + "\n" ) insert(new SalByJob(j,$avg)) end
Simpler version:
declare SalByJob job : String end rule "Sample" when Emp(j: job) and not SalByJob(j == job) accumulate (e :Emp(job == j), $avg : average(e.sal) ) then System.out.println( "Average salary in position " + j + "is: " + $avg ) insert(new SalByJob(j)) end
Average salary in position CLERKis: 1037.5 Average salary in position ANALYSTis: 3000.0 Average salary in position SALESMANis: 1400.0 Average salary in position MANAGERis: 2758.3333333333335 Average salary in position PRESIDENTis: 5000.0
Another Example
Let’s find out how many people were hired in a specific year
declare PeopleHiredInYear year : int pplcnt : int end rule "Sample" when Emp(y: hiredate) and not PeopleHiredInYear(y == year) accumulate (e :Emp(hiredate == y), $pplcnt : count(e.id)) then System.out.println( "In " + y + " there where " + $pplcnt + " people hired") insert(new PeopleHiredInYear(y,$pplcnt)) end
In 1982 there where 2 people hired In 1983 there where 1 people hired In 1980 there where 1 people hired In 1981 there where 10 people hired
Simpler version:
declare PeopleHiredInYear
year : int
end
rule "Sample"
when
Emp($y: hiredate) and
not PeopleHiredInYear($y == year)
accumulate ($e :Emp(hiredate == $y), $pplcnt : count($e.id))
then
System.out.println( "In " + $y + " there where " + $pplcnt + " people hired")
insert(new PeopleHiredInYear($y))
endExercises
25. Exercise
Find the average salary for every post.
ANALYST 3000.000000 CLERK 1037.500000 MANAGER 2758.333333 PRESIDENT 5000.000000 SALESMAN 1400.000000
27. Exercise
Find the average annual salaries in departments.
10 35000.000000 20 26100.000000 30 18800.000000
43. *Exercise
Find post filled both in 1982 and 1983.
CLERK
Filtering down results of an accumulate function ⌘
Find jobs with an average salary greater than 2000
rule "Sample"
when
Emp(j: job) and not SalByJob(j == job)
accumulate (e :Emp(job == j),
$avg : average(e.sal),
$min : min(e.sal),
$max : max(e.sal)
)
Number(doubleValue > 2000) from $avg
then
System.out.println(
"Salaries in position " + j + "\n" +
"Average salary: " + $avg + "\n" +
"Min salary: " + $min + "\n" +
"Max salary: " + $max + "\n"
)
insert(new SalByJob(j,$avg))
end
Comparing to SQL:
SELECT job, avg(sal) FROM Emp GROUP BY job HAVING avg(sal) > 2000;
Exercises
28. Exercise
Find departments with more than 3 workers.
20 5 30 6
Advanced Examples ⌘
Select employees whose salary is greater than the company's average
when Number(avg : longValue) from accumulate (Emp(s: sal),average(s)) e: Emp(sal > avg) then System.out.println(e.name + " earns " + e.sal + " the average is " + avg)
CARNEGIE earns 3000 the average is 2073 TOOSK earns 3000 the average is 2073 MERKEL earns 2450 the average is 2073 BLAIR earns 2850 the average is 2073 PUTIN earns 2975 the average is 2073 BUSH earns 5000 the average is 2073
What if we want to find all the people working in the same position (job) as Blair.
when blr: Emp(name == "BLAIR") e: Emp(job == blr.job) then System.out.println(e.name + " works as " + e.job) end
PUTIN works as MANAGER MERKEL works as MANAGER BLAIR works as MANAGER
We want to find the employees earning the most in department 20.
when Number($max : intValue) from accumulate (Emp(s: sal,deptno == 20 ), max(s)) e : Emp(deptno == 20,sal == $max) then System.out.println(e.name + " " + e.deptno +" " + e.sal )
CARNEGIE 20 3000 TOOSK 20 3000
We want to find out who earns the most in each department.
rule "Sample" when Dept(dn :deptno) Number($max : longValue) from accumulate (Emp(s: sal,deptno == dn ), max(s)) e : Emp(deptno == dn,sal == $max) then System.out.println(e.name + " " + e.deptno +" " + e.sal ) end
CARNEGIE 20 3000 TOOSK 20 3000 BLAIR 30 2850 BUSH 10 5000
Exercises
45. Exercise
Find employees earning more than the manager’s (job=='MANAGER') average.
BUSH 5000.00 BLAIR 2850.00 PUTIN 2975.00 TOOSK 3000.00 CARNEGIE 3000.00
46. Exercise*
Select employees earning the maximum salaries in their positions (jobs).
ELISON CLERK 1300.00 BAROSSO SALESMAN 1600.00 PUTIN MANAGER 2975.00 TOOSK ANALYST 3000.00 CARNEGIE ANALYST 3000.00 BUSH PRESIDENT 5000.00
Forall and Exists ⌘
Does anyone earn less than 6000? In other words, do all people earn less than 6000?
when
forall($e : Emp(sal < 6000) )
then
System.out.println("Everyone earns less than 6000" )
Are there any people earning more than 3000?
when
exists( Emp(sal > 3000))
then
System.out.println("There are people earning more than 3000" )
Let’s find employees who have subordinates.
when m : Emp() exists( Emp(mgr == m.id)) then System.out.println(m.name )
MERKEL CARNEGIE TOOSK PUTIN BLAIR BUSH
Find workerless departments.
when d : Dept() not( Emp(deptno == d.deptno)) then System.out.println(d.dname )
OPERATIONS
Exercises
51. Exercise Find employees earning more than the average salary in their department.
+----------+---------+--------+ | ename | sal | deptno | +----------+---------+--------+ | BUSH | 5000.00 | 10 | | BLAIR | 2850.00 | 30 | | PUTIN | 2975.00 | 20 | | BAROSSO | 1600.00 | 30 | | TOOSK | 3000.00 | 20 | | CARNEGIE | 3000.00 | 20 | +----------+---------+--------+
55. *Exercise
Find the maximum average salary of departments.
10 2916.666667
Collect
Element collect allows rules to reason over a collection of objects obtained from the given source or from the working memory.
rule "Sample1"
when
$emps : ArrayList () from collect (Emp())
then
System.out.println("No of employees: " + $emps.size() )
endThe result pattern of collect can be any concrete class that implements the java.util.Collection interface and provides a default no-arg public constructor e.g. ArrayList, LinkedList, HashSet, etc., or your own class, as long as it implements the java.util.Collection interface and provide a default no-arg public constructor.
Find departments employing more than 3 people.
rule "Sample2"
when
$d : Dept()
$emps : ArrayList (size > 3)
from collect (Emp(deptno == $d.deptno))
then
System.out.println($d.dname )
end
Exercises
56. Exercise
Find job posts having more than 2 employees.
