Originally posted by: BLAIS

It made a long time that I 'am coding in OPL but I just realize how to do a JOIN between tuple sets in OPL this week, I share that with you. Most of the time, I work my data in SQL before I feed OPL but something you need to do that directly in OPL and it work well. You have to know that the "|" symbol is like the FROM and that the ":" symbol is like the WHERE keyword in SQL. You can do any kind of join in the WHERE clause. Here are some simple examples (Project, Restrict, Product, Join, Intersect, Union, Difference, SymDiff) :

<code>

tuplerv2DPoint {

      key float X;

      float Y;

};

 

tuplerv3DPoint {

      key float X;

      key float Y;

      float Z;

};

 

tuplervPerson {

  key string Name;

  string StateCode;

}

 

tuplervState {

  key string StateCode;

  string StateName;

}

 

tuplervPersonState {

  key string PersonName;

  string StateName;

}

 

setof(rv3DPoint)relPoints3D = {<60.0, 0.0000, 0.000000>,

                                         <4.1,417.721000, 139.198000>,

                                         <10.5,835.442000, 278.397000>,

                                         <7.8,1131.390000, 371.160000>};

                                        

setof(rv3DPoint)relPoints3D_2 = {<60.0, 0.0000, 0.000000>,

                                         <4.1,418.0, 139.198000>,

                                         <10.5,835.442000, 278.397000>,

                                         <8.0,1131.390000, 371.160000>};

 

setof(rvPerson)relPersons = {<"Mark", "BC">, <"Joe", "VT">, <"Bill", "VT">};

 

setof(rvState)relStates = {<"BC", "Britsh-Columbia">, <"VT", "Vermont">};

 

setof(float)relZs = {t*0.5| t in 1..10};

 

//Project (like a select of some column. The "|" symbol is like the FROM )

{rv2DPoint}relProjectionResult ={<p3D.X,p3D.Y>| p3D in relPoints3D};

 

//Restrict (the ":" symbol is like the WHERE keyword in SQL)

{rv3DPoint}relRestrictionResult ={p3D | p3D in relPoints3D: p3D.X > 10};

 

//Product (cross join)

{rv3DPoint}relProductResult ={<z, p2D.X, p2D.Y> | p2D in relProjectionResult, z in relZs};

 

//Join (like if you did the join in the WHERE clause)

{rvPersonState}relPersonsStates ={<p.Name, s.StateName> | p in relPersons, s in relStates : p.StateCode==s.StateCode};

 

//For other operation, OPL help is clear

 

//Intersect

{rv3DPoint}relIntersectResult = relPoints3D_2 inter relPoints3D;

 

//Union

{rv3DPoint}relUnionResult = relPoints3D_2 union relPoints3D;

 

//Difference

{rv3DPoint}relDifferenceResult = relPoints3D_2 diff relPoints3D;

 

//Symmetric difference

{rv3DPoint}relSymDiffResult = relPoints3D_2 symdiff relPoints3D;

 

dvar float+var;

minimizevar;

subject to

{

     

     

 }

<code>

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: BLAIS

Summarization in OPL. How to aggregate value in a tupleset. Equivalent of the GROUP BY in SQL.

tuple rvPersonWithAge {

   key string Name;

   string StateCode;

   float Age;

}

 

setof(rvPersonWithAge) relPersonsWithAge = {<"Mark", "BC", 30.0>, <"Joe", "VT", 42.0>, <"Bill", "VT", 37.0>};

 

//Summarization

tuple rvAgeSumByState {

   key string StateCode;

   float SumAge;

}

setof(rvAgeSumByState) relAgeSumByState = {<p.StateCode, sum(pa in relPersonsWithAge: p.StateCode==pa.StateCode) pa.Age > | p in relPersonsWithAge};

 

dvar float+ var;

minimize var;

subject to

{

 

}

 

Unfortunately, I can't calculate age average because I don't know how to convert int to float  (no itof function) to convert card(pa) to float.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

setof(rvAgeSumByState) relAgeAverageByState = {<p.StateCode, 1/card({pa | pa in relPersonsWithAge: p.StateCode==pa.StateCode})*sum(pa in relPersonsWithAge: p.StateCode==pa.StateCode) pa.Age > | p in relPersonsWithAge};

 

would do the job:

tuple rvPersonWithAge {
  key string Name;
  string StateCode;
  float Age;
}
 
setof(rvPersonWithAge) relPersonsWithAge = {<"Mark",  "BC", 30.0>, <"Joe", "VT", 42.0>, <"Bill", "VT", 37.0>};

//Summarization
tuple rvAgeSumByState {
  key string StateCode;
  float SumAge;
}

setof(rvAgeSumByState) relAgeAverageByState = {<p.StateCode, 1/card({pa | pa in relPersonsWithAge: p.StateCode==pa.StateCode})*sum(pa in relPersonsWithAge: p.StateCode==pa.StateCode) pa.Age > | p in relPersonsWithAge};

execute
{
writeln(relAgeAverageByState);
}

dvar float+ var;

minimize var;

subject to

{

      

      

 }

gives

{<"BC" 30> <"VT" 39.5>}

regards

 

Alex Fleischer

PS:

Many how to with OPL at https://www.linkedin.com/pulse/how-opl-alex-fleischer/

Many examples from a very good book : https://www.linkedin.com/pulse/model-building-oplcplex-alex-fleischer/

Making optimization simple : https://www.linkedin.com/pulse/making-decision-optimization-simple-alex-fleischer/

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: BLAIS

Nice! Thank you Alex.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: BLAIS

Restriction on set:

 

//Restrict on set (WHERE (x) not in (SELECT a FROM table) )

setof(rvPersonWithAge) relRestrictionOnSetResult = {pa

| pa in relPersonsWithAge : <pa.Name> not in relPersons};

 

I add this operation on the attach file.

#DecisionOptimization
#OPLusingCPLEXOptimizer