Fstachecki: /* Accumulate Functions ⌘ */

2019-12-16T20:29:49Z

Accumulate Functions ⌘

New page

{{Cat|Drools Filip|060}}

== Drools Expert - mvel - RHS - advanced Training Materials ==
{{Can I use your material}}

== 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
<syntaxhighlight lang="java">
accumulate( <source pattern>; <functions> [;<constraints>] )
</syntaxhighlight>

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:

<source>
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))
end
</source>

===Exercises ===

'''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.
<source>
rule "Sample1"
when
$emps : ArrayList () from collect (Emp())
then
System.out.println("No of employees: " + $emps.size() )
end
</source>

The 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.<br/>

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.<br/>

MVEL advanced - Revision history

Fstachecki: /* Accumulate Functions ⌘ */