1:03 p.m.
Hi
i have an object customer (purchaseDate, CustNo, Amount)
i need to find the customers who purchased more than X amount in each date)
ie group by customer then group by date and then sum of amount
how do you write a rule for this
thanks
Vijay
--
View this message in context:
http://drools-java-rules-engine.46999.n3.nabble.com/Disting-and-group-by-...
Sent from the Drools - User mailing list archive at Nabble.com.
3:01 a.m.
With an additional class PurchaseSum
List<Customer> customers;
... purchaseDate;
... custNo;
... sum;
}
you can write these rules;
rule "insert PurchaseSum"
when
Customer( $pd : purchaseDate, $cn : custNo )
not( PurchaseSum( purchaseDate == $pd, custNo == $cn ) )
then
insert( new PurchaseSum( $cn, $pd );
end
rule "accumulate amounts"
when
$cu : Customer( $pd : purchaseDate, $cn : custNo, $am : amount )
$ps : PurchaseSum( purchaseDate == $pd, custNo == $cn, customers not
contains $cu )
then
modify( $ps ){
setSum( $ps.getSum() + amount ),
setCustomers( $ps.getCustomers().add( $cu )
}
end
rule "show amounts > X"
salience -100
when
$ps : PurchaseSum( amount > X )
thne
System.out.println( $ps... );
retract( $ps );
end
rule "clean up"
salience -101
when
$ps : PurchaseSum()
thne
retract( $ps );
end
On 10 August 2010 20:03, vijrams <mailrvijay(a)gmail.com> wrote:
Hi
i have an object customer (purchaseDate, CustNo, Amount)
i need to find the customers who purchased more than X amount in each date)
ie group by customer then group by date and then sum of amount
how do you write a rule for this
thanks
Vijay
--
View this message in context:
http://drools-java-rules-engine.46999.n3.nabble.com/Disting-and-group-by-...
Sent from the Drools - User mailing list archive at Nabble.com.
_______________________________________________
rules-users mailing list
rules-users(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/rules-users
9:04 a.m.
"Group by" is implicit in rules, just by writing patterns in order. So,
if you write a rule that starts with Customer(), it will automatically do a
"group by" customer:
when
Customer(...)
...
then
"Distinct" is a bit problematic, as we have no explicit distinct
functionality (I have an open ticket for that:
https://jira.jboss.org/browse/JBRULES-2640) but in some cases you can use
collectSet for similar results.
In your case, you can go with Wolfgang's suggestion, or using
accumulate:
declare ReportForDate
custNo : String
date : Date
end
rule "insert report dates"
salience 10
dialect "mvel"
when
Customer( $pd : purchaseDate, $custNo : custNo )
not( ReportForDate( custNo == $custNo, date == $pd ) )
then
with( $r = new ReportDate() ) {
custNo = $custNo,
date == $pd
}
insert( $r );
end
rule "customers that purchased more than 100 on a given date"
when
ReportDate( $pd: date, $cn : custNo )
Number( $total : doubleValue > 100 ) from accumulate(
Customer( $custNo == $cn, purchaseDate == $pd, $am : amount ),
sum( $am ) )
then
// do something as the customer $cn purchased $total on $pd
end
Edson
2010/8/10 vijrams <mailrvijay(a)gmail.com>
Hi
i have an object customer (purchaseDate, CustNo, Amount)
i need to find the customers who purchased more than X amount in each date)
ie group by customer then group by date and then sum of amount
how do you write a rule for this
thanks
Vijay
--
View this message in context:
http://drools-java-rules-engine.46999.n3.nabble.com/Disting-and-group-by-...
Sent from the Drools - User mailing list archive at Nabble.com.
_______________________________________________
rules-users mailing list
rules-users(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/rules-users
--
Edson Tirelli
JBoss Drools Core Development
JBoss by Red Hat @ www.jboss.com
9:25 a.m.
2010/8/11 Edson Tirelli <tirelli(a)post.com>:
"Group by" is implicit in rules, just by writing patterns in order. So,
if you write a rule that starts with Customer(), it will automatically do a
"group by" customer:
when
Customer(...)
...
then
Hmm, I think vijrams meant "grouping of the accumulated sums" which will
require the PurchaseSum facts I proposed. Only then it is possible to obtain
all sums-per-day grouped by customer.
I don't see why "customers that purchased more than 100 on a given date"
should fire in Customer order.
Even with my solution some additional quirks will be necessary to get
the data grouped by Customer.
-W
3:11 p.m.
Hi Wolfgang,
I believe both suggestions (mine and yours) are equivalent, just
different ways of writing down the solution for the problem. If you identify
any significant difference in the results, please let me know... :)
Edson
2010/8/11 Wolfgang Laun <wolfgang.laun(a)gmail.com>
2010/8/11 Edson Tirelli <tirelli(a)post.com>:
>
> "Group by" is implicit in rules, just by writing patterns in order.
So,
> if you write a rule that starts with Customer(), it will automatically do
a
> "group by" customer:
> when
> Customer(...)
> ...
> then
Hmm, I think vijrams meant "grouping of the accumulated sums" which will
require the PurchaseSum facts I proposed. Only then it is possible to
obtain
all sums-per-day grouped by customer.
I don't see why "customers that purchased more than 100 on a given date"
should fire in Customer order.
Even with my solution some additional quirks will be necessary to get
the data grouped by Customer.
-W
_______________________________________________
rules-users mailing list
rules-users(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/rules-users
--
Edson Tirelli
JBoss Drools Core Development
JBoss by Red Hat @ www.jboss.com
3:04 a.m.
2010/8/11 Edson Tirelli <tirelli(a)post.com>:
Hi Wolfgang,
I believe both suggestions (mine and yours) are equivalent, just
different ways of writing down the solution for the problem. If you identify
any significant difference in the results, please let me know... :)
No, both will produce the sums required to decide >X, but both were
incomplete w.r.t. what (I think) is meant by "grouping by customer".
-W
Edson
2010/8/11 Wolfgang Laun <wolfgang.laun(a)gmail.com>
>
> 2010/8/11 Edson Tirelli <tirelli(a)post.com>:
> >
> > "Group by" is implicit in rules, just by writing patterns in
order.
> > So,
> > if you write a rule that starts with Customer(), it will automatically
> > do a
> > "group by" customer:
> > when
> > Customer(...)
> > ...
> > then
>
> Hmm, I think vijrams meant "grouping of the accumulated sums" which will
> require the PurchaseSum facts I proposed. Only then it is possible to
> obtain
> all sums-per-day grouped by customer.
>
> I don't see why "customers that purchased more than 100 on a given
date"
> should fire in Customer order.
>
> Even with my solution some additional quirks will be necessary to get
> the data grouped by Customer.
>
> -W
>
> _______________________________________________
> rules-users mailing list
> rules-users(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/rules-users
--
Edson Tirelli
JBoss Drools Core Development
JBoss by Red Hat @ www.jboss.com
_______________________________________________
rules-users mailing list
rules-users(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/rules-users
11:55 p.m.
Thanks Wolfgang and Edson for your help
i was able to make it work
below is my rule
declare ReportForDate
custNo : String
chargeDate : Date
end
rule "insert report dates"
salience 10
dialect "mvel"
when
$lineItems : Customer (person != null, $custNo : person.ID, $ChargeDate
: lineItemDate)
not( ReportForDate( custNo == $custNo, chargeDate == $ChargeDate ) )
then
with( $r = new ReportForDate() ) {
custNo = $custNo,
chargeDate = $ChargeDate
}
insert( $r );
end
rule "customers that purchased more than 100 on a given date"
when
$CustDateInfo : ReportForDate($custNo : custNo, $ChargeDate : chargeDate)
$dayTotal : Number(doubleValue > 100) from accumulate( Customer ( person.ID
== $custNo, lineItemDate == $ChargeDate
, $amt : amount )
, sum( $amt ) )
then
System.out.println("********* customer " + $custNo +" billed over 100
per day");
end
--
View this message in context:
http://drools-java-rules-engine.46999.n3.nabble.com/Distinct-and-group-by...
Sent from the Drools - User mailing list archive at Nabble.com.
5015
days inactive
5018
days old
6 comments
3 participants
participants (3)
-
Edson Tirelli -
vijrams -
Wolfgang Laun