How to aggregate on the basis of key subset in Pig/Hive?

I have a following data set where emp_id, org_id and res_id are the key columns

Input data is -

emp_id | org_id | res_id | emp_sal
123    | 345    | 678    | 10000
123    |        | 678    | 20000
123    | 345    |        | 30000
       | 345    | 678    | 10000
103    | 305    | 608    | 40000
103    |        |        | 50000

I have a requirement where I need to aggregate emp_sal if the remaining records are the subset of complete key. For example "123 | 345 | 678 |" has 3 more subset in the input data set.

Expected output is -

emp_id | org_id | res_id | emp_sal
123    | 345    | 678    | 70000
103    | 305    | 608    | 90000

How can I calculate this aggregation in Pig?

Solution

SELECT c.emp_id, 
       c.org_id, 
       c.res_id, 
       Sum(d.emp_sal) 
FROM   (SELECT DISTINCT emp_id, 
                        org_id, 
                        res_id 
        FROM   emptest 
        WHERE  emp_id IS NOT NULL 
               AND org_id IS NOT NULL 
               AND res_id IS NOT NULL) AS c, 
       emptest AS d 
WHERE  d.emp_id = c.emp_id 
        OR d.org_id = c.org_id 
        OR d.res_id = c.res_id 
GROUP  BY c.emp_id, 
          c.org_id, 
          c.res_id;

Above Hive query could help you.