CODEWITHSUNDEEP
mysqlsql
mesibo
mesibo

Reputation: 4323

Update oldest rows based on sum of column

We are having a following MySQL table to maintain user credits.

id   user    credits      expiry        status
-----------------------------------------
1     A       1.2        somedatetime    0
2     A       4.4        somedatetime    0  
3     A       5.0        somedatetime    0  
4     B       1.0        somedatetime    0  
5     B       2.4        somedatetime    0  
6     C       7.8        somedatetime    0

Whenever user makes a purchase, we deduct the amount from the available credits. To be fair to user, the credits with nearest expiry will be consumed first and so on. We will also update the status to mark row as consumed.

For example, if user A makes a purchase of $2, $1.2 will be debited from id 1 and remaining $0.8 from id 2 and so on. So Table will now look like

id   user    credits      expiry        status
-----------------------------------------
1     A       0.0        somedatetime    1
2     A       3.6        somedatetime    1  
3     A       5.0        somedatetime    0  
4     B       1.0        somedatetime    0  
5     B       2.4        somedatetime    0  
6     C       7.8        somedatetime    0

So far we have been doing it with brute-force approach. Any idea suggestion how to do it more efficiently in minimum or single query.

Update: since someone asked about our current brute-force approach, it's iterating each row from the oldest and updating till the purchase amount is covered, which is very inefficient.

Thanks

Upvotes: 1

Views: 84

Answers (2)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/485673/1

SET @amount = 2;
UPDATE t1
JOIN (
  SELECT t2.id, 
    IF(@amount=0,t2.credits, IF(@amount>t2.credits,0,t2.credits-@amount)) credits,
    IF(@amount>=t2.credits,@amount := @amount-t2.credits, 0)
  FROM (
    SELECT id, credits
    FROM t1
    WHERE credits>0 AND `user`='A'
    ORDER BY expiry ASC
  ) t2
           ) t
ON t1.id = t.id
SET t1.credits=t.credits
WHERE t1.user = 'A';

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Using variables you calculate the totals credits. Run the inner query so you learn what is beign calculate first.

Fiddle Demo

UPDATE customer c
JOIN (
        SELECT cu.`id`, 
               cu.`user`, 
               `credits`, `expiry`, `status`,        
               @total := IF(@customer = cu.`user`, @total := @total  + `credits`, `credits`) as cumulative_total,
               @customer := cu.`user` as user_current,
               `credit_used`
        FROM customer cu 
        CROSS JOIN  (SELECT @customer := '', @total := 0 ) t
        JOIN credits
          ON cu.`user` = credits.`user`
        ORDER BY cu.`id`
     ) t
   ON c.`id` = t.`id`
SET c.credits = CASE WHEN c.credits <= t.credit_used THEN 0
                                                     ELSE t.cumulative_total - credit_used
                END,
    c.status =  CASE WHEN c.credits <= t.credit_used THEN 1
                                                     ELSE 0
                END;

My test Setup:

CREATE TABLE customer
    (`id` int, `user` varchar(1), `credits` double, `expiry` int, `status` int)
;

INSERT INTO customer
    (`id`, `user`, `credits`, `expiry`, `status`)
VALUES
    (1, 'A', 1.2, 1, 0),
    (2, 'A', 4.4, 2, 0),
    (3, 'A', 5.0, 3, 0),
    (4, 'B', 1.0, 4, 0),
    (5, 'B', 2.4, 5, 0),
    (6, 'C', 7.8, 6, 0)
;

CREATE TABLE credits
    (`id` int, `user` varchar(1), `credit_used` double)
;

INSERT INTO credits
    (`id`, `user`, `credit_used`)
VALUES
    (1, 'A', 2.0),
    (2, 'B', 3.4)
;

Upvotes: 3

Related Questions