Options
Aggregate using the same set
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)
FROM TBL1 B, TBL1 A
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.
May be below query will help you understand my requirement more
SELECT B.time, B.date, B.att1, SUM(A.COST)
FROM TBL1 B, TBL1 A
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.
Tagged:
0
Best Answer
-
Options
BalazsBarany
Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 
Unicorn
Hi,
joining in the database *is* the better solution, you mentioned it yourself.
Regards,
Balázs5
Answers
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.
Harshit
Since your join seems includes the =< operator you may need
Database Envy operator available at the marketplace extension developed by @BalazsBarany
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,
Balázs