0

I do have two data streams q1 and q2. I want to merge q2.SUM into q1 if they both have the same value in MaxMon.

first

It looks so obvious, but I am lost in between all this cogroup and union magic. This is how I load those two data streams:

q = load "RetentionDATA1";
q1 = group q by 'Month_of_Retention';
q1 = foreach q1 generate q1.'Month_of_Retention' as 'q1.RM', sum(q1.'MONTHLY_FEE') as 'q1.SUM', toString(toDate(max(q1.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q1.MaxMon';

enter image description here

q2 = filter q by 'Month_of_Retention' == "1";
q2 = group q2 by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month');
q2 = foreach q2 generate toString(toDate(max(q2.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q2.MaxMon', sum(sum(q2.'MONTHLY_FEE')) over([.. 0] partition by all order by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month')) as 'q2.SUM';

enter image description here

So far so good. Now I want to do the ‘merging’. Below are some examples and outcomes of my attempts:

result = cogroup q1 by ('q1.MaxMon') , q2 by ('q2.MaxMon');
result = foreach result generate first(q1.'q1.RM') as 'q1.RM', sum(q1.'q1.SUM') as 'q1.SUM', sum(q2.'q2.SUM') as 'q2.SUM)';

enter image description here

The results with q1.RM=3,4,8 are missing. I might understand 3 and 4, as those have a MaxMon matching two times. But why 8? (Same result if I do it with the keyword left for left join.)

Any other idea, how to join those tables, to get to the result described above? I need a table like this: q1.RM | q1. Sum | q2.SUM where q1.MaxMon equals q2. MaxMon

This is the full query:

q = load "RetentionDATA1";
q1 = group q by 'Month_of_Retention';
q1 = foreach q1 generate q1.'Month_of_Retention' as 'q1.RM', sum(q1.'MONTHLY_FEE') as 'q1.SUM', toString(toDate(max(q1.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q1.MaxMon';


q2 = filter q by 'Month_of_Retention' == "1";
q2 = group q2 by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month');
q2 = foreach q2 generate toString(toDate(max(q2.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q2.MaxMon', sum(sum(q2.'MONTHLY_FEE')) over([.. 0] partition by all order by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month')) as 'q2.SUM';

result = cogroup q1 by ('q1.MaxMon') left, q2 by ('q2.MaxMon');
result = foreach result generate first(q1.'q1.RM') as 'q1.RM', sum(q1.'q1.SUM') as 'q1.SUM', sum(q2.'q2.SUM') as 'q2.SUM)';
0

the 3 and 4 are missing, because you group by MaxMonth which is the same for both. (8 should however be there, it is in my test...) You have to use a little trick: Before cogrouping, you group query 1 by MaxMon and RM. That way you preserve All distinct rows.

q = load "RetentionDATA1";
q1 = group q by 'Month_of_Retention';
q1 = foreach q1 generate q1.'Month_of_Retention' as 'q1.RM',
    sum(q1.'MONTHLY_FEE') as 'q1.SUM',
    toString(toDate(max(q1.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q1.MaxMon';
q1 = group q1 by ( 'q1.MaxMon', 'q1.RM');

Then generate the second query and cogroup. Since we grouped query 1 by MaxMon and RM we get the Rows with RM 3 and 4, too.

q2 = filter q by 'Month_of_Retention' == "1";
q2 = group q2 by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month');
q2 = foreach q2 generate toString(toDate(max(q2.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q2.MaxMon',
    sum(sum(q2.'MONTHLY_FEE')) over([.. 0] partition by all order by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month')) as 'q2.SUM';

result = cogroup q1 by ('q1.MaxMon'), q2 by ('q2.MaxMon');
result = foreach result generate q1.'q1.MaxMon' as 'q1.MaxMon',
    q2.'q2.MaxMon' as 'q2.MaxMon',
    string_to_number(first(q1.'q1.RM')) as 'q1.RM',
    sum(q1.'q1.SUM') as 'q1.SUM',
    sum(q2.'q2.SUM') as 'q2.SUM';
result = order result by 'q1.RM';

Voilá, that should do the trick!

| improve this answer | |
  • Patrick, you cannot imagine how thankful I am. It works!!! And the solution is so obviously but I really spent days here going back and forth with this issue. Thank you so much! – Mario Z Mar 25 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.