Get a user's rank from an equation, and then store it in the table

Question

I'm working on a project for my database class, and I have a table called "accounts". It looks like

What I want to do is have a stored procedure where it calculates the new ranks for each account. The ranks will be based off a value (higher the better) that will be computed with an equation I am working on. How would I be able to get the rank, and store the rank in my table?

Ex: Account_id||Games_won||Games_lost||(calculated value) not in table||Account_rank

         1        200           157              1856                    1

EDIT: Here is my CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS league_of_legends.accounts (

account_id INT NOT NULL AUTO_INCREMENT,

account_name VARCHAR(45) NOT NULL,

account_password VARCHAR(45) NOT NULL,

subscriber_first_name VARCHAR(45) NOT NULL,

subscriber_last_name VARCHAR(45) NOT NULL,

subscriber_email_address VARCHAR(45) NOT NULL,

subscriber_dob DATE NOT NULL,

server VARCHAR(45) NOT NULL,

account_balance INT NULL,

games_won INT NULL,

games_lost INT NULL,

account_rank INT NULL,

PRIMARY KEY (account_id, account_name, subscriber_email_address));

My INSERT sample statements: INSERT INTO accounts (account_id, account_name, account_password, subscriber_first_name, subscriber_last_name, subscriber_email_address, subscriber_dob, server, account_balance, games_won, games_lost, account_rank)

VALUES (DEFAULT, 'Bwomp', 'asdfqwer', 'Nile', 'Williams', '[email protected]', '2003-10-09', 'NA', 1250, 100, 100, NULL),

(DEFAULT, 'Dirty Dan', '1234567890', 'Daniel', 'Brown', '[email protected]', '1990-07-16', 'EU', 0, 3, 20, NULL),

(DEFAULT, 'Alfy', 'doodcool', 'Alfonso', 'Riggleman', '[email protected]', '1992-12-27', 'KR', 50, 400, 410, NULL),

(DEFAULT, 'Justaguy1', 'retrodonkey', 'Nick', 'Chetta', '[email protected]', '1996-06-28', 'NA', 3, 0, 5, NULL);

Sample equation:

(Games_won / total_games) * games_won = value

Answer 1

OK - this should be easy for the first part, if you're willing to be a bit adventurous :-).

MariaDB has had computed columns (otherwise known as virtual or generated columns) for quite a while now. Otherwise, the latest milestone release of MySQL (5.7.7) also has them.

If you want to do this with a current GA version, you'll have to use TRIGGERs.

However, if you want to update the RANK (see a lovely post here) on each update of games_won/games_lost, then you'll have to call a PROCEDURE which will update RANK. MySQL's Stored Procedure programming language isn't sophisticated enough for this on its own, but see here for some ideas. It says that there can be rollback issues, but since you're updating only one table, it should be easier than most - good luck :-). BTW, I'd use float/double for games_won/_lost - otherwise, you might have problems with integer division.

Answer 2

My $0.02; perform the calculation app side. It will scale better. Its possible to perform the calculation in an update or an insert on duplicate key update statement.