# Drools Expert - mvel - LHS - advanced

Title

Drools Expert - mvel - RHS - advanced
Author
Bernard Szlachta (NobleProg Ltd)
Subfooter

Drools Expert - mvel - RHS - advanced          Bernard Szlachta (NobleProg Ltd)

## Selecting data from many different Fact Types 从多个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 只选择那些在两个fact types中都匹配deptno的数据

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
e :Emp(sal >= s.losal && <= s.hisal)
then
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
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. 选择员工的名字和工作城市（DEPT表中的LOC列）

+----------+----------+
| ename    | loc      |
+----------+----------+
| 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. 选择员工的名字以及部门的编号和名称

+----------+------------+--------+
| ename    | dname      | deptno |
+----------+------------+--------+
| 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. 选择员工的名字、工资和工资等级，但只选择工资高于2000的员工

+----------+---------+-------+
| ename    | sal     | grade |
+----------+---------+-------+
| 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. 选择在伦敦工作的雇员

+----------+--------+
| ename    | loc    |
+----------+--------+
| PUTIN    | LONDON |
| TOOSK    | LONDON |
| THATCHER | LONDON |
| CARNEGIE | LONDON |
| FORD     | LONDON |
+----------+--------+


#### 35. Exercise

Select employees and their salary grades except those from London. 选择不在伦敦的雇员和工资等级

+---------+----------+-------+
| ename   | loc      | grade |
+---------+----------+-------+
| 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 寻找同一类型的facts 。⌘

We need to report who is whose boss 一份谁是谁上级的报告

rule "Sample"
when
e :Emp()
m :Emp(m.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


### 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 employees 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. 寻找10号部门有，但是20号部门没有的岗位

PRESIDENT


## Accumulate Functions 累计函数 。⌘

Drools has a couple of built in accumulate functions:
Drools 有一些 累计accumulate）函数:

• average
• min
• max
• count
• sum

Find the average salary in the whole company. 寻找整个公司的平均工资

when
accumulate (e :Emp(), avg : average(e.sal))
then
System.out.println(avg)

2073


Which can be also written this way: 还可以写成:

when
avg: Number() from accumulate (Emp(s: sal),average(s))
then
System.out.println(avg)

2073

accumulate (e: Emp(), avg: average(e.sal))
e:Emp(sal >
(Double)avg
)
then
System.out.println(e.name + "\t" + e.sal + " average is "  + avg)
end
Multi-function Accumulates

You can also use many aggregation functions (e.g. min, max, etc...) in simple accumulate statement: 你可以一次使用多个累计函数:

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


### 23. Exercise

Find the minimal, maximal and average salaries of people employed in 1981. 寻找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? 公司有多少经理?

+---------+-----------+
| job     | How many? |
+---------+-----------+
| MANAGER | 3         |
+---------+-----------+


## Groups 组 。⌘

A group contains all facts which have the same values in a property

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:这里有五个job组:
ANALYST, CLERK, MANAGER, PRESIDENT and SALESMAN.

### 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 声明和插入新的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 below does:

not SalByJob(j == job)

Method 1

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  Method 2 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 + "\n" ) 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  Method 3 declare 岗位 工作名字 : String end rule "Get all position" when Emp(j: job) not 岗位(工作名字 == j) then insert (new 岗位(j)) end rule "Exercise 1" when 岗位(j: 工作名字) accumulate ($e : Emp(job == j), s: sum($e.sal)) then  What is missing in the rules above? ### 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


### 25. Exercise

Find the average salary for every post. 计算每个岗位的平均工资

+-----------+-------------+
| job       | Average     |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN  | 1400.000000 |
+-----------+-------------+


### 27. Exercise

Find the average annual salaries in departments. 计算部门的平均年薪

+--------+-----------------+
| deptno | Annual Salaries |
+--------+-----------------+
| 10     | 35000.000000    |
| 20     | 26100.000000    |
| 30     | 18800.000000    |
+--------+-----------------+


### 43. *Exercise

Find post filled in 1982 and 1983. 寻找在1982和1983年填满的职位

CLERK


### Filtering down results of an accumulate function 过滤累计函数的结果 。⌘

Find job positions with an average salary greater than 2000

rule "Find avarage in position"
when
Emp(j: job)
accumulate(Emp(s: sal,job == j), $avgSal: average(s)) eval($avgSal > 2000)
//   Number(doubleValue > 2000) from $avgSal then System.out.println(j + " " +$avgSal)
end

PRESIDENT 5000.0
MANAGER 2758.3333333333335
MANAGER 2758.3333333333335
MANAGER 2758.3333333333335
ANALYST 3000.0
ANALYST 3000.0


We can eliminate duplicates this way：

declare Position
job : String
avergeSalary : Double
end

rule "Find avarage in position"
when
Emp(j: job)
accumulate(Emp(s: sal,job == j), $avgSal: average(s)) not Position(job == j) then insert(new Position(j,$avgSal))
end

rule "Get position where average salary > 2000"
when
$p:Position(avergeSalary > 2000) then System.out.println($p.job + " " + $p.avergeSalary) end  ### Exercises ### 28. Exercise Find departments with more than 3 workers. 寻找雇员多于3人的部门 +--------+----------+ | deptno | count(*) | +--------+----------+ | 20 | 5 | | 30 | 6 | +--------+----------+  ## Advanced Examples 。⌘ Select employees whose salary is greater than the company's average 选择工资高于公司平均工资的雇员 Let us try pure accumulate when accumulate (Emp(s : sal),avg: average(s)) e: Emp(sal > avg) then  This will return "incompatible types" error . To convert you can use follow code: 这个将返回类型不匹配"incompatible types"错误。我们可以转换： 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  Explenation: 1. sal属性是long类型（private long sal） 2. average函数返回Double类型 3. Java不能自动转换Double到long类型 4. Number是Java类 5. Number是Double, Long等的父类 What if we want to find all the people working in the same position (job) as Blair. 如果我们想寻找所有和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. 我们想找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. 寻找工资比经理 (job=='MANAGER') 的平均工资高的雇员 +----------+---------+ | ename | sal | +----------+---------+ | 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). 寻找在同一个职位工资最高的雇员 +----------+-----------+---------+ | ename | job | sal | +----------+-----------+---------+ | 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? 有谁工资低于6000？ In other words, do all people earn less than 6000? 或者说，是否所有人工资都低于6000？ when forall($e : Emp(sal < 6000) )
then
System.out.println("Everyone earns less than 6000" )


Are there any people earning more than 3000? 有谁工资高于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


### 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     |
+----------+---------+--------+


54. *Exercise Find employees who have no boss.寻找没有上级的员工

  BUSH


### 55. *Exercise

Find the department with maximum average salary. 寻找平均工资最高的部门

10     2916.666667


### 29. *Exercise

Check that every id is unique. 检查每个id是否唯一

### 30. *Exercise

For each of the managers find a minimum salary of their subordinates. 为每个经理寻找其下属的最低工资

+------+----------+
| mgr  | min(sal) |
+------+----------+
| 0    | 5000.00  |
| 7566 | 3000.00  |
| 7698 | 950.00   |
| 7782 | 1300.00  |
| 7788 | 1100.00  |
| 7839 | 2450.00  |
| 7902 | 800.00   |
+------+----------+


### 30a. Exercise

• Find a job which has the lowest discrepancy between lowest and highest salary
• 寻找工资差异最低的职位