I have a task where I need to read a big data file and process the data in it. Every row in the file looks like this:
CustomerId ItemId Amount Price
I need to calculate the total cost for the customer, but first I need to determine the most expensive item purchased. "Most expensive" means the item with the highest total price sum (per customer). I then have to subtract the most expensive item from the total cost.
My idea is to first make this table:
CustomerId ItemId Total_Cost
Next, I sort the table, find the highest cost, and store this in a variable.
Then I make this table:
Then I subtract the highest cost from each row.
But I feel that this is a brute force approach, and I'd like to get advice on a more clever and efficient way to do this.
Make Map with CustomerId key, i.e. Map[CustId, TotalCost] - calling this M1 Make another Map, i.e. Map[(CustId, ItemId), SubTotalCost] - calling this M2 // Open file and process line by line for each line in file M1(CustomerId) = running_total_cost M2((CustomerId, ItemId) = running_subtotal_cost // When finished processing the file for each entery in M1 get highest subtotal in M2 substracting highest total in M2 from M1's running_total_cost