Drools Expert - mvel - LHS - advanced

From Training Material
Jump to navigation Jump to search
Category:Drools
title
Drools Expert - mvel - RHS - advanced
author
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
   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. 选择员工的名字和工作城市（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

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

Exercises

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
一个组包含了属性值相同的facts。
Also known as factor levels in statistics.
在统计学里也叫做factor levels.

| 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).
上面的解决方法的问题是它重复了 (循环) 14 次 (雇员的数量).

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:
要解决这个问题，我们可以标记“已经处理过的组”。我们可以通过“插入一个新的fact SalByJob"实现这个，然后我们检查是否已经计算出了平均值 

not SalByJob(j == job)
Method 1

The code below will return exactly 5 averages.
下面的代码返回5个平均值 

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

Exercises

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
寻找平均工资高于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

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


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

  BUSH

55. *Exercise

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

10     2916.666667

Additional Exercises

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
  • 寻找工资差异最低的职位

Next Module

Drools Expert - mvel - LHS - advanced part 2

Retrieved from ‘https://training-course-material.com/index.php?title=Drools_Expert_-_mvel_-_LHS_-_advanced&oldid=52228’