Aggregate using the same set
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.
Find more posts tagged with
Sort by:
1 - 9 of
91
Altair Employee@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
Since your join seems includes the =< operator you may need
Database Envy operator available at the marketplace extension developed by @BalazsBarany
@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.
@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
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,
Balázs
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
@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.
anyone with better solution here please?
Hi,
joining in the database *is* the better solution, you mentioned it yourself.
Regards,
Balázs
joining in the database *is* the better solution, you mentioned it yourself.
Regards,
Balázs
Closing this call
Sort by:
1 - 1 of
11
Hi,
joining in the database *is* the better solution, you mentioned it yourself.
Regards,
Balázs
joining in the database *is* the better solution, you mentioned it yourself.
Regards,
Balázs
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.