champion_competitor_stats.cql

champion_competitor_stats.cql

-- Save champion stats by day
CREATE TABLE champion_stats_by_day (
    champion_ids FROZEN<SET<INT>>,
    competing_champion_ids FROZEN<SET<INT>>,
    competition_day DATE,
    win_ratio DECIMAL,
    loss_ratio DECIMAL,
    draw_ratio DECIMAL,
    wins INT,
    draws INT,
    losses INT,
    matches INT,
    PRIMARY KEY(champion_ids, competition_day, competing_champion_ids)
) WITH CLUSTERING ORDER BY(competition_day DESC, competing_champion_ids ASC);

-- Inserting some sample data
insert into champion_stats_by_day
	(champion_ids, competing_champion_ids, competition_day, win_ratio, draw_ratio, loss_ratio, wins, draws, losses, matches)
	values ({1,2,3,4}, {21,2,9,33}, '2017-10-28', 0.71, 0.23, 0.04, 121, 40, 8, 169);
insert into champion_stats_by_day
	(champion_ids, competing_champion_ids, competition_day, win_ratio, draw_ratio, loss_ratio, wins, draws, losses, matches)
	values ({1,2,3,4}, {8,42,3,21}, '2017-10-29', 0.007, 0, 0.992, 1, 0, 128, 129);
insert into champion_stats_by_day
	(champion_ids, competing_champion_ids, competition_day, win_ratio, draw_ratio, loss_ratio, wins, draws, losses, matches)
	values ({1,2,3,4}, {21,9,3,4}, '2017-10-13', 0, 0.13, 0.86, 0, 11, 68, 79);
insert into champion_stats_by_day
	(champion_ids, competing_champion_ids, competition_day, win_ratio, draw_ratio, loss_ratio, wins, draws, losses, matches)
	values ({1,2,3,4}, {93,5,34,8}, '2017-10-05', 0.63, 0.15, 0.21, 12, 3, 4, 19);
insert into champion_stats_by_day
	(champion_ids, competing_champion_ids, competition_day, win_ratio, draw_ratio, loss_ratio, wins, draws, losses, matches)
	values ({1,2,3,4}, {44,12,23,7}, '2017-10-22', 0.4, 0.57, 0.02, 45, 64, 3, 112);
insert into champion_stats_by_day
	(champion_ids, competing_champion_ids, competition_day, win_ratio, draw_ratio, loss_ratio, wins, draws, losses, matches)
	values ({1,2,3,4}, {32,22,6,5}, '2017-11-01', 0.43, 0.008, 0.55, 99, 2, 128, 229);


-- Operations in application code: Update field
SELECT * FROM champion_stats_by_day WHERE champion_ids = {1,2,3,4} AND competing_champion_ids = {21,2,9,33} AND competition_day = '2017-11-01';
UPDATE champion_stats_by_day
	SET win_ratio = 0.38, draw_ratio = 0.04, loss_ratio = 0.57, wins = 32, draws = 4, losses = 48, matches = 84
	WHERE champion_ids = {1,2,3,4} AND competing_champion_ids = {21,2,9,33} AND competition_day = '2017-11-01';

-- Getting stats from a specified date
-- You have to do the sorting / aggregates in client-side
SELECT *
  FROM champion_stats_by_day
  WHERE champion_ids = {1,2,3,4}
  AND competition_day > '2017-10-17';