topic Re: [resolved] How to compute weighted average in Talend in Talend Studio

[resolved] How to compute weighted average in Talend

Anonymous — Sat, 16 Nov 2024 13:18:51 GMT

Hello.
I need to compute weighted average at product leve basedon units and price.
Here is sample table:
ProdurctID Units Price
222 10 $15
333 15 $20
444 37 $5

It is very easy in SQL:
SELECT ProdurctID, SUM(units * price) / SUM(units) AS wavg FROM table GROUP BY ProdurctID;
or
SELECT ProdurctID, SUM(units * price) / SUM(units) over (partition by product) AS wavg FROM table GROUP BY ProdurctID;
Please explain how to implement it in Talend as I the tool does not let me use SQL functions.
I use the following components:
tMysqlInput -> tMap -> tAggregateRow -> tMysqlOutput
Thank you,
Pit.

Re: [resolved] How to compute weighted average in Talend

Anonymous — Sat, 14 Aug 2010 00:23:08 GMT

If you are using a DB as your source, you can simply use SQL to calculate these averages in your input components.
if you have a file, I would use a tAggregateRow to compute all the SUM's for each row, and then a tmap to do the division.

Re: [resolved] How to compute weighted average in Talend

Anonymous — Sat, 14 Aug 2010 01:15:46 GMT

What component should I use to calculate all in SQL? I don't see any references to SQL in tMySQLOutput or Input.

Re: [resolved] How to compute weighted average in Talend

Anonymous — Mon, 16 Aug 2010 17:45:56 GMT

Any ideas, folks?

Re: [resolved] How to compute weighted average in Talend

Anonymous — Mon, 16 Aug 2010 19:55:27 GMT

Peter,
You can put custom queries into a tMysqlInput component in the "Query" text box. Just remember that when you write a query manually, you must define the schema manually as well. The schema is positional, so column 1 in the query needs to be schema element 1.

Re: [resolved] How to compute weighted average in Talend

Anonymous — Tue, 17 Aug 2010 00:17:01 GMT

Thanks a lot!