Find cumulative sum of last 3 months' row values in Talend

Question

Performing ETL on a dataset using Talend, I have obtained the "yyyyMM" column as "MonthYear", and total "CB" in that month.

Now, I want to find out total CB of previous 3 months, for each month.

Respective (self-join) query:

SELECT
    t1.MonthYear, t1.CB
    SUM(t2.CB) CB_last3months
FROM
    Table1 t1
    JOIN Table1 t2 
        ON t2.MonthYear <= t1.MonthYear
        AND t2.MonthYear >= t1.MonthYear-2
GROUP BY t1.Month

My data is like:

MonthYear   CB
-------+-------
201601  7000
201602  5000
201603  7000
201604  6000
201605  7000
201606  4000

I want my output schema to be like:

MonthYear CB    CB_last3months
------+-------+-------------------
201601  7000    7000
201602  5000    12000
201603  7000    19000
201604  6000    18000
201605  7000    20000
201606  4000    17000

In SQL, I can do that by a nested subquery, or using self-join. How do I execute this query on the current Talend job without having to store the rows as a table in a (MySQL) DB?

My other option is to use Talend components to carry out the remaining steps.

But are there any components in Talend where I can extract rows iteratively and perform aggregation on them? Or some way to perform join and aggregation both in tMap?

I have attempted this so far ... but how do I get the "test" expression to be evaluated iteratively?

tMap component Expression

Answer 1

Please try this way it may help you

DECLARE @t TABLE(ColumnA INT, ColumnB VARCHAR(50));

INSERT INTO @t VALUES
(2,           'a'),
(3  ,         'b'),
(4   ,        'c'),
(5    ,       'd'),
(1     ,      'a');

;WITH cte
AS
(
   SELECT ColumnB, SUM(ColumnA) asum
   FROM @t 
   gROUP BY ColumnB

), cteRanked AS
(
   SELECT asum, ColumnB, ROW_NUMBER() OVER(ORDER BY ColumnB) rownum
   FROM cte
) 
SELECT asum AS Amount, (SELECT SUM(asum) FROM cteRanked c2 WHERE c2.rownum <= c1.rownum) AS TotalAMount, ColumnB
FROM cteRanked c1;

Output this