Aggregate using the same set

DivyemDivyem Member Posts: 17 Contributor II
I am trying to aggregate on the set itself on the basis of intra day amount happening. 
May be below query will help you understand my requirement more 

SELECT B.time, B.date, B.att1, SUM(A.COST)
WHERE  A.time <= B.time
AND A.DATE = B.DATE AND A.att1 = B.att1
GROUP BY A.att1,A.att2,A.time, A.date;

I am not able to achieve it with any operator or multiple operators. Can anyone help me out on this.

Best Answer


  • Options
    hbajpaihbajpai Member Posts: 102 Unicorn
    Hey @Divyem ,

    The process you describe can be achieved using Pivot operator. Check out the operator and for sample usage you can refer to the help window in Studio.
  • Options
    MarcoBarradasMarcoBarradas Administrator, Employee, RapidMiner Certified Analyst, Member Posts: 272 Unicorn
    @Divyem I don´t know if your example set is already joined or not.
    Since your join seems includes the =< operator you may need 
    Database Envy operator available at the marketplace extension developed by @BalazsBarany
  • Options
    DivyemDivyem Member Posts: 17 Contributor II
    @hbajpai I am trying to achieve something with =< which cannot be done on the pivot. I tried pivot but couldn't achieve the desired output.
  • Options
    DivyemDivyem Member Posts: 17 Contributor II
    @MarcoBarradas that's an amazing finding for me, but there is one issue this operator gives me function to compare two attributes at once. I want to compare multiple attributes and merge the set together. taking some inputs from one set and aggregated inputs from other with some conditions as per the query above. I tried but couldn't come to a solution. Could you help me out in this
  • Options
    BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn
    Hi @Divyem,

    the manual way to do this is to preaggregate the second example set (self-joining the result back if necessary), then Cartesian Join with the first example set, then using Generate Attributes to calculate a "keep" column (true/false) using arbitrary complex expressions, and then filtering the result.

    This is of course inefficient in memory and CPU terms but you have full control on the processing. It's good to avoid this approach if possible, but if it's not, this is how you have to do it.

    Best regards,

  • Options
    DivyemDivyem Member Posts: 17 Contributor II
    @BalazsBarany so inshort if I have a set of 3 million rows this is impossible to do in rapidminer. Will have to link directly from the database with the respected query instead. Thanks for the inputs anyway sir. 
  • Options
    DivyemDivyem Member Posts: 17 Contributor II
    anyone with better solution here please?
  • Options
    DivyemDivyem Member Posts: 17 Contributor II
    Closing this call 
Sign In or Register to comment.