From 6dd1f9706a065952a2d34c0008d1eba2cd157f89 Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Sat, 15 Dec 2012 09:58:10 -0500 Subject: [PATCH] Add a small script to update the summary stats table. This script is intended to be run from cron on a semi-frequent basis, like hourly or daily. One would adjust that depending on the volume of new games/players/servers. --- scripts/update_summary_stats.sql | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) create mode 100644 scripts/update_summary_stats.sql diff --git a/scripts/update_summary_stats.sql b/scripts/update_summary_stats.sql new file mode 100644 index 0000000..30cc97d --- /dev/null +++ b/scripts/update_summary_stats.sql @@ -0,0 +1,32 @@ +begin; + -- get rid of the existing summary stats since we're about to refresh + delete from summary_stats; + + insert into summary_stats(total_players, total_servers, total_games, + total_dm_games, total_duel_games, total_ctf_games) + with total_games as ( + select game_type_cd, count(*) total_games + from games + where game_type_cd in ('duel', 'dm', 'ctf') + group by game_type_cd + ), + total_players as ( + select count(*) total_players + from players + where active_ind = true + ), + total_servers as ( + select count(*) total_servers + from servers + where active_ind = true + ) + select tp.total_players, ts.total_servers, dm.total_games+duel.total_games+ctf.total_games total_games, + dm.total_games dm_games, duel.total_games duel_games, ctf.total_games ctf_games + from total_games dm, total_games duel, total_games ctf, total_players tp, total_servers ts + where dm.game_type_cd = 'dm' + and ctf.game_type_cd = 'ctf' + and duel.game_type_cd = 'duel'; + + commit; + +end; -- 2.39.2