From 6477b691162a5add0fcd578c9c62002d3fa3cdd3 Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Mon, 1 Dec 2014 21:18:57 -0500 Subject: [PATCH] Partition tables out to 2020, remove old ones. Since these scripts are used to create a new xonstatdb, references to partitions using past dates will just mean empty tables. I'll cut off this pruning at each prior year. --- tables/games.tab | 147 ++++++------ tables/player_game_stats.tab | 261 ++++++++++++++-------- tables/player_weapon_stats.tab | 273 +++++++++++++++-------- tables/team_game_stats.tab | 198 +++++++++++++--- triggers/games_ins_trg.sql | 84 +++++-- triggers/player_game_stats_ins_trg.sql | 86 ++++--- triggers/player_weapon_stats_ins_trg.sql | 83 +++++-- triggers/team_game_stats_ins_trg.sql | 84 +++++-- 8 files changed, 840 insertions(+), 376 deletions(-) diff --git a/tables/games.tab b/tables/games.tab index b90b0ef..06d8079 100755 --- a/tables/games.tab +++ b/tables/games.tab @@ -28,96 +28,115 @@ WITH ( CREATE INDEX games_ix001 on games(create_dt); ALTER TABLE xonstat.games OWNER TO xonstat; --- 2011 -CREATE TABLE xonstat.games_2011Q2 ( - CHECK ( create_dt >= DATE '2011-04-01' AND create_dt < DATE '2011-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2014q1 ( + CHECK ( create_dt >= DATE '2014-01-01' AND create_dt < DATE '2014-04-01' ) ) INHERITS (games); -CREATE INDEX games_2011Q2_ix001 on games_2011Q2(create_dt); -ALTER TABLE xonstat.games_2011Q2 OWNER TO xonstat; -CREATE TABLE xonstat.games_2011Q3 ( - CHECK ( create_dt >= DATE '2011-07-01' AND create_dt < DATE '2011-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2014q2 ( + CHECK ( create_dt >= DATE '2014-04-01' AND create_dt < DATE '2014-07-01' ) ) INHERITS (games); -CREATE INDEX games_2011Q3_ix001 on games_2011Q3(create_dt); -ALTER TABLE xonstat.games_2011Q3 OWNER TO xonstat; -CREATE TABLE xonstat.games_2011Q4 ( - CHECK ( create_dt >= DATE '2011-10-01' AND create_dt < DATE '2012-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2014q3 ( + CHECK ( create_dt >= DATE '2014-07-01' AND create_dt < DATE '2014-10-01' ) ) INHERITS (games); -CREATE INDEX games_2011Q4_ix001 on games_2011Q4(create_dt); -ALTER TABLE xonstat.games_2011Q4 OWNER TO xonstat; --- 2012 -CREATE TABLE xonstat.games_2012Q1 ( - CHECK ( create_dt >= DATE '2012-01-01' AND create_dt < DATE '2012-04-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2014q4 ( + CHECK ( create_dt >= DATE '2014-10-01' AND create_dt < DATE '2015-01-01' ) ) INHERITS (games); -CREATE INDEX games_2012Q1_ix001 on games_2012Q1(create_dt); -ALTER TABLE xonstat.games_2012Q1 OWNER TO xonstat; -CREATE TABLE xonstat.games_2012Q2 ( - CHECK ( create_dt >= DATE '2012-04-01' AND create_dt < DATE '2012-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2015q1 ( + CHECK ( create_dt >= DATE '2015-01-01' AND create_dt < DATE '2015-04-01' ) ) INHERITS (games); -CREATE INDEX games_2012Q2_ix001 on games_2012Q2(create_dt); -ALTER TABLE xonstat.games_2012Q2 OWNER TO xonstat; -CREATE TABLE xonstat.games_2012Q3 ( - CHECK ( create_dt >= DATE '2012-07-01' AND create_dt < DATE '2012-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2015q2 ( + CHECK ( create_dt >= DATE '2015-04-01' AND create_dt < DATE '2015-07-01' ) ) INHERITS (games); -CREATE INDEX games_2012Q3_ix001 on games_2012Q3(create_dt); -ALTER TABLE xonstat.games_2012Q3 OWNER TO xonstat; -CREATE TABLE xonstat.games_2012Q4 ( - CHECK ( create_dt >= DATE '2012-10-01' AND create_dt < DATE '2013-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2015q3 ( + CHECK ( create_dt >= DATE '2015-07-01' AND create_dt < DATE '2015-10-01' ) ) INHERITS (games); -CREATE INDEX games_2012Q4_ix001 on games_2012Q4(create_dt); -ALTER TABLE xonstat.games_2012Q4 OWNER TO xonstat; --- 2013 -CREATE TABLE xonstat.games_2013Q1 ( - CHECK ( create_dt >= DATE '2013-01-01' AND create_dt < DATE '2013-04-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2015q4 ( + CHECK ( create_dt >= DATE '2015-10-01' AND create_dt < DATE '2016-01-01' ) ) INHERITS (games); -CREATE INDEX games_2013Q1_ix001 on games_2013Q1(create_dt); -ALTER TABLE xonstat.games_2013Q1 OWNER TO xonstat; -CREATE TABLE xonstat.games_2013Q2 ( - CHECK ( create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2016q1 ( + CHECK ( create_dt >= DATE '2016-01-01' AND create_dt < DATE '2016-04-01' ) ) INHERITS (games); -CREATE INDEX games_2013Q2_ix001 on games_2013Q2(create_dt); -ALTER TABLE xonstat.games_2013Q2 OWNER TO xonstat; -CREATE TABLE xonstat.games_2013Q3 ( - CHECK ( create_dt >= DATE '2013-07-01' AND create_dt < DATE '2013-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2016q2 ( + CHECK ( create_dt >= DATE '2016-04-01' AND create_dt < DATE '2016-07-01' ) ) INHERITS (games); -CREATE INDEX games_2013Q3_ix001 on games_2013Q3(create_dt); -ALTER TABLE xonstat.games_2013Q3 OWNER TO xonstat; -CREATE TABLE xonstat.games_2013Q4 ( - CHECK ( create_dt >= DATE '2013-10-01' AND create_dt < DATE '2014-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2016q3 ( + CHECK ( create_dt >= DATE '2016-07-01' AND create_dt < DATE '2016-10-01' ) ) INHERITS (games); -CREATE INDEX games_2013Q4_ix001 on games_2013Q4(create_dt); -ALTER TABLE xonstat.games_2013Q4 OWNER TO xonstat; --- 2014 -CREATE TABLE xonstat.games_2014Q1 ( - CHECK ( create_dt >= DATE '2014-01-01' AND create_dt < DATE '2014-04-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2016q4 ( + CHECK ( create_dt >= DATE '2016-10-01' AND create_dt < DATE '2017-01-01' ) ) INHERITS (games); -CREATE INDEX games_2014Q1_ix001 on games_2014Q1(create_dt); -ALTER TABLE xonstat.games_2014Q1 OWNER TO xonstat; -CREATE TABLE xonstat.games_2014Q2 ( - CHECK ( create_dt >= DATE '2014-04-01' AND create_dt < DATE '2014-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2017q1 ( + CHECK ( create_dt >= DATE '2017-01-01' AND create_dt < DATE '2017-04-01' ) ) INHERITS (games); -CREATE INDEX games_2014Q2_ix001 on games_2014Q2(create_dt); -ALTER TABLE xonstat.games_2014Q2 OWNER TO xonstat; -CREATE TABLE xonstat.games_2014Q3 ( - CHECK ( create_dt >= DATE '2014-07-01' AND create_dt < DATE '2014-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2017q2 ( + CHECK ( create_dt >= DATE '2017-04-01' AND create_dt < DATE '2017-07-01' ) ) INHERITS (games); -CREATE INDEX games_2014Q3_ix001 on games_2014Q3(create_dt); -ALTER TABLE xonstat.games_2014Q3 OWNER TO xonstat; -CREATE TABLE xonstat.games_2014Q4 ( - CHECK ( create_dt >= DATE '2014-10-01' AND create_dt < DATE '2015-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.games_2017q3 ( + CHECK ( create_dt >= DATE '2017-07-01' AND create_dt < DATE '2017-10-01' ) ) INHERITS (games); -CREATE INDEX games_2014Q4_ix001 on games_2014Q4(create_dt); -ALTER TABLE xonstat.games_2014Q4 OWNER TO xonstat; + +CREATE TABLE IF NOT EXISTS xonstat.games_2017q4 ( + CHECK ( create_dt >= DATE '2017-10-01' AND create_dt < DATE '2018-01-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2018q1 ( + CHECK ( create_dt >= DATE '2018-01-01' AND create_dt < DATE '2018-04-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2018q2 ( + CHECK ( create_dt >= DATE '2018-04-01' AND create_dt < DATE '2018-07-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2018q3 ( + CHECK ( create_dt >= DATE '2018-07-01' AND create_dt < DATE '2018-10-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2018q4 ( + CHECK ( create_dt >= DATE '2018-10-01' AND create_dt < DATE '2019-01-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2019q1 ( + CHECK ( create_dt >= DATE '2019-01-01' AND create_dt < DATE '2019-04-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2019q2 ( + CHECK ( create_dt >= DATE '2019-04-01' AND create_dt < DATE '2019-07-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2019q3 ( + CHECK ( create_dt >= DATE '2019-07-01' AND create_dt < DATE '2019-10-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2019q4 ( + CHECK ( create_dt >= DATE '2019-10-01' AND create_dt < DATE '2020-01-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2020q1 ( + CHECK ( create_dt >= DATE '2020-01-01' AND create_dt < DATE '2020-04-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2020q2 ( + CHECK ( create_dt >= DATE '2020-04-01' AND create_dt < DATE '2020-07-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2020q3 ( + CHECK ( create_dt >= DATE '2020-07-01' AND create_dt < DATE '2020-10-01' ) +) INHERITS (games); + +CREATE TABLE IF NOT EXISTS xonstat.games_2020q4 ( + CHECK ( create_dt >= DATE '2020-10-01' AND create_dt < DATE '2021-01-01' ) +) INHERITS (games); + diff --git a/tables/player_game_stats.tab b/tables/player_game_stats.tab index e654a76..7b124fe 100755 --- a/tables/player_game_stats.tab +++ b/tables/player_game_stats.tab @@ -47,155 +47,226 @@ CREATE INDEX player_game_stats_ix02 on player_game_stats(game_id); CREATE INDEX player_game_stats_ix03 on player_game_stats(player_id); ALTER TABLE xonstat.player_game_stats OWNER TO xonstat; --- 2011 -CREATE TABLE xonstat.player_game_stats_2011Q2 ( - CHECK ( create_dt >= DATE '2011-04-01' AND create_dt < DATE '2011-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2014q1 ( + CHECK ( create_dt >= DATE '2014-01-01' AND create_dt < DATE '2014-04-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2011Q2_ix01 on player_game_stats_2011Q2(create_dt); -CREATE INDEX player_game_stats_2011Q2_ix02 on player_game_stats_2011Q2(game_id); -CREATE INDEX player_game_stats_2011Q2_ix03 on player_game_stats_2011Q2(player_id); -ALTER TABLE xonstat.player_game_stats_2011Q2 OWNER TO xonstat; +CREATE INDEX player_game_stats_2014q1_ix001 on player_game_stats_2014q1(create_dt); +CREATE INDEX player_game_stats_2014q1_ix002 on player_game_stats_2014q1(game_id); +CREATE INDEX player_game_stats_2014q1_ix003 on player_game_stats_2014q1(player_id); - -CREATE TABLE xonstat.player_game_stats_2011Q3 ( - CHECK ( create_dt >= DATE '2011-07-01' AND create_dt < DATE '2011-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2014q2 ( + CHECK ( create_dt >= DATE '2014-04-01' AND create_dt < DATE '2014-07-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2011Q3_ix01 on player_game_stats_2011Q3(create_dt); -CREATE INDEX player_game_stats_2011Q3_ix02 on player_game_stats_2011Q3(game_id); -CREATE INDEX player_game_stats_2011Q3_ix03 on player_game_stats_2011Q3(player_id); -ALTER TABLE xonstat.player_game_stats_2011Q3 OWNER TO xonstat; +CREATE INDEX player_game_stats_2014q2_ix001 on player_game_stats_2014q2(create_dt); +CREATE INDEX player_game_stats_2014q2_ix002 on player_game_stats_2014q2(game_id); +CREATE INDEX player_game_stats_2014q2_ix003 on player_game_stats_2014q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2014q3 ( + CHECK ( create_dt >= DATE '2014-07-01' AND create_dt < DATE '2014-10-01' ) +) INHERITS (player_game_stats); +CREATE INDEX player_game_stats_2014q3_ix001 on player_game_stats_2014q3(create_dt); +CREATE INDEX player_game_stats_2014q3_ix002 on player_game_stats_2014q3(game_id); +CREATE INDEX player_game_stats_2014q3_ix003 on player_game_stats_2014q3(player_id); -CREATE TABLE xonstat.player_game_stats_2011Q4 ( - CHECK ( create_dt >= DATE '2011-10-01' AND create_dt < DATE '2012-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2014q4 ( + CHECK ( create_dt >= DATE '2014-10-01' AND create_dt < DATE '2015-01-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2011Q4_ix01 on player_game_stats_2011Q4(create_dt); -CREATE INDEX player_game_stats_2011Q4_ix02 on player_game_stats_2011Q4(game_id); -CREATE INDEX player_game_stats_2011Q4_ix03 on player_game_stats_2011Q4(player_id); -ALTER TABLE xonstat.player_game_stats_2011Q4 OWNER TO xonstat; +CREATE INDEX player_game_stats_2014q4_ix001 on player_game_stats_2014q4(create_dt); +CREATE INDEX player_game_stats_2014q4_ix002 on player_game_stats_2014q4(game_id); +CREATE INDEX player_game_stats_2014q4_ix003 on player_game_stats_2014q4(player_id); +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2015q1 ( + CHECK ( create_dt >= DATE '2015-01-01' AND create_dt < DATE '2015-04-01' ) +) INHERITS (player_game_stats); --- 2012 -CREATE TABLE xonstat.player_game_stats_2012Q1 ( - CHECK ( create_dt >= DATE '2012-01-01' AND create_dt < DATE '2012-04-01' ) +CREATE INDEX player_game_stats_2015q1_ix001 on player_game_stats_2015q1(create_dt); +CREATE INDEX player_game_stats_2015q1_ix002 on player_game_stats_2015q1(game_id); +CREATE INDEX player_game_stats_2015q1_ix003 on player_game_stats_2015q1(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2015q2 ( + CHECK ( create_dt >= DATE '2015-04-01' AND create_dt < DATE '2015-07-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2012Q1_ix01 on player_game_stats_2012Q1(create_dt); -CREATE INDEX player_game_stats_2012Q1_ix02 on player_game_stats_2012Q1(game_id); -CREATE INDEX player_game_stats_2012Q1_ix03 on player_game_stats_2012Q1(player_id); -ALTER TABLE xonstat.player_game_stats_2012Q1 OWNER TO xonstat; +CREATE INDEX player_game_stats_2015q2_ix001 on player_game_stats_2015q2(create_dt); +CREATE INDEX player_game_stats_2015q2_ix002 on player_game_stats_2015q2(game_id); +CREATE INDEX player_game_stats_2015q2_ix003 on player_game_stats_2015q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2015q3 ( + CHECK ( create_dt >= DATE '2015-07-01' AND create_dt < DATE '2015-10-01' ) +) INHERITS (player_game_stats); +CREATE INDEX player_game_stats_2015q3_ix001 on player_game_stats_2015q3(create_dt); +CREATE INDEX player_game_stats_2015q3_ix002 on player_game_stats_2015q3(game_id); +CREATE INDEX player_game_stats_2015q3_ix003 on player_game_stats_2015q3(player_id); -CREATE TABLE xonstat.player_game_stats_2012Q2 ( - CHECK ( create_dt >= DATE '2012-04-01' AND create_dt < DATE '2012-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2015q4 ( + CHECK ( create_dt >= DATE '2015-10-01' AND create_dt < DATE '2016-01-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2012Q2_ix01 on player_game_stats_2012Q2(create_dt); -CREATE INDEX player_game_stats_2012Q2_ix02 on player_game_stats_2012Q2(game_id); -CREATE INDEX player_game_stats_2012Q2_ix03 on player_game_stats_2012Q2(player_id); -ALTER TABLE xonstat.player_game_stats_2012Q2 OWNER TO xonstat; +CREATE INDEX player_game_stats_2015q4_ix001 on player_game_stats_2015q4(create_dt); +CREATE INDEX player_game_stats_2015q4_ix002 on player_game_stats_2015q4(game_id); +CREATE INDEX player_game_stats_2015q4_ix003 on player_game_stats_2015q4(player_id); +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2016q1 ( + CHECK ( create_dt >= DATE '2016-01-01' AND create_dt < DATE '2016-04-01' ) +) INHERITS (player_game_stats); + +CREATE INDEX player_game_stats_2016q1_ix001 on player_game_stats_2016q1(create_dt); +CREATE INDEX player_game_stats_2016q1_ix002 on player_game_stats_2016q1(game_id); +CREATE INDEX player_game_stats_2016q1_ix003 on player_game_stats_2016q1(player_id); -CREATE TABLE xonstat.player_game_stats_2012Q3 ( - CHECK ( create_dt >= DATE '2012-07-01' AND create_dt < DATE '2012-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2016q2 ( + CHECK ( create_dt >= DATE '2016-04-01' AND create_dt < DATE '2016-07-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2012Q3_ix01 on player_game_stats_2012Q3(create_dt); -CREATE INDEX player_game_stats_2012Q3_ix02 on player_game_stats_2012Q3(game_id); -CREATE INDEX player_game_stats_2012Q3_ix03 on player_game_stats_2012Q3(player_id); -ALTER TABLE xonstat.player_game_stats_2012Q3 OWNER TO xonstat; +CREATE INDEX player_game_stats_2016q2_ix001 on player_game_stats_2016q2(create_dt); +CREATE INDEX player_game_stats_2016q2_ix002 on player_game_stats_2016q2(game_id); +CREATE INDEX player_game_stats_2016q2_ix003 on player_game_stats_2016q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2016q3 ( + CHECK ( create_dt >= DATE '2016-07-01' AND create_dt < DATE '2016-10-01' ) +) INHERITS (player_game_stats); +CREATE INDEX player_game_stats_2016q3_ix001 on player_game_stats_2016q3(create_dt); +CREATE INDEX player_game_stats_2016q3_ix002 on player_game_stats_2016q3(game_id); +CREATE INDEX player_game_stats_2016q3_ix003 on player_game_stats_2016q3(player_id); -CREATE TABLE xonstat.player_game_stats_2012Q4 ( - CHECK ( create_dt >= DATE '2012-10-01' AND create_dt < DATE '2013-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2016q4 ( + CHECK ( create_dt >= DATE '2016-10-01' AND create_dt < DATE '2017-01-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2012Q4_ix01 on player_game_stats_2012Q4(create_dt); -CREATE INDEX player_game_stats_2012Q4_ix02 on player_game_stats_2012Q4(game_id); -CREATE INDEX player_game_stats_2012Q4_ix03 on player_game_stats_2012Q4(player_id); -ALTER TABLE xonstat.player_game_stats_2012Q4 OWNER TO xonstat; +CREATE INDEX player_game_stats_2016q4_ix001 on player_game_stats_2016q4(create_dt); +CREATE INDEX player_game_stats_2016q4_ix002 on player_game_stats_2016q4(game_id); +CREATE INDEX player_game_stats_2016q4_ix003 on player_game_stats_2016q4(player_id); +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2017q1 ( + CHECK ( create_dt >= DATE '2017-01-01' AND create_dt < DATE '2017-04-01' ) +) INHERITS (player_game_stats); + +CREATE INDEX player_game_stats_2017q1_ix001 on player_game_stats_2017q1(create_dt); +CREATE INDEX player_game_stats_2017q1_ix002 on player_game_stats_2017q1(game_id); +CREATE INDEX player_game_stats_2017q1_ix003 on player_game_stats_2017q1(player_id); --- 2013 -CREATE TABLE xonstat.player_game_stats_2013Q1 ( - CHECK ( create_dt >= DATE '2013-01-01' AND create_dt < DATE '2013-04-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2017q2 ( + CHECK ( create_dt >= DATE '2017-04-01' AND create_dt < DATE '2017-07-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2013Q1_ix01 on player_game_stats_2013Q1(create_dt); -CREATE INDEX player_game_stats_2013Q1_ix02 on player_game_stats_2013Q1(game_id); -CREATE INDEX player_game_stats_2013Q1_ix03 on player_game_stats_2013Q1(player_id); -ALTER TABLE xonstat.player_game_stats_2013Q1 OWNER TO xonstat; +CREATE INDEX player_game_stats_2017q2_ix001 on player_game_stats_2017q2(create_dt); +CREATE INDEX player_game_stats_2017q2_ix002 on player_game_stats_2017q2(game_id); +CREATE INDEX player_game_stats_2017q2_ix003 on player_game_stats_2017q2(player_id); +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2017q3 ( + CHECK ( create_dt >= DATE '2017-07-01' AND create_dt < DATE '2017-10-01' ) +) INHERITS (player_game_stats); -CREATE TABLE xonstat.player_game_stats_2013Q2 ( - CHECK ( create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01' ) +CREATE INDEX player_game_stats_2017q3_ix001 on player_game_stats_2017q3(create_dt); +CREATE INDEX player_game_stats_2017q3_ix002 on player_game_stats_2017q3(game_id); +CREATE INDEX player_game_stats_2017q3_ix003 on player_game_stats_2017q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2017q4 ( + CHECK ( create_dt >= DATE '2017-10-01' AND create_dt < DATE '2018-01-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2013Q2_ix01 on player_game_stats_2013Q2(create_dt); -CREATE INDEX player_game_stats_2013Q2_ix02 on player_game_stats_2013Q2(game_id); -CREATE INDEX player_game_stats_2013Q2_ix03 on player_game_stats_2013Q2(player_id); -ALTER TABLE xonstat.player_game_stats_2013Q2 OWNER TO xonstat; +CREATE INDEX player_game_stats_2017q4_ix001 on player_game_stats_2017q4(create_dt); +CREATE INDEX player_game_stats_2017q4_ix002 on player_game_stats_2017q4(game_id); +CREATE INDEX player_game_stats_2017q4_ix003 on player_game_stats_2017q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2018q1 ( + CHECK ( create_dt >= DATE '2018-01-01' AND create_dt < DATE '2018-04-01' ) +) INHERITS (player_game_stats); +CREATE INDEX player_game_stats_2018q1_ix001 on player_game_stats_2018q1(create_dt); +CREATE INDEX player_game_stats_2018q1_ix002 on player_game_stats_2018q1(game_id); +CREATE INDEX player_game_stats_2018q1_ix003 on player_game_stats_2018q1(player_id); -CREATE TABLE xonstat.player_game_stats_2013Q3 ( - CHECK ( create_dt >= DATE '2013-07-01' AND create_dt < DATE '2013-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2018q2 ( + CHECK ( create_dt >= DATE '2018-04-01' AND create_dt < DATE '2018-07-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2013Q3_ix01 on player_game_stats_2013Q3(create_dt); -CREATE INDEX player_game_stats_2013Q3_ix02 on player_game_stats_2013Q3(game_id); -CREATE INDEX player_game_stats_2013Q3_ix03 on player_game_stats_2013Q3(player_id); -ALTER TABLE xonstat.player_game_stats_2013Q3 OWNER TO xonstat; +CREATE INDEX player_game_stats_2018q2_ix001 on player_game_stats_2018q2(create_dt); +CREATE INDEX player_game_stats_2018q2_ix002 on player_game_stats_2018q2(game_id); +CREATE INDEX player_game_stats_2018q2_ix003 on player_game_stats_2018q2(player_id); +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2018q3 ( + CHECK ( create_dt >= DATE '2018-07-01' AND create_dt < DATE '2018-10-01' ) +) INHERITS (player_game_stats); -CREATE TABLE xonstat.player_game_stats_2013Q4 ( - CHECK ( create_dt >= DATE '2013-10-01' AND create_dt < DATE '2014-01-01' ) +CREATE INDEX player_game_stats_2018q3_ix001 on player_game_stats_2018q3(create_dt); +CREATE INDEX player_game_stats_2018q3_ix002 on player_game_stats_2018q3(game_id); +CREATE INDEX player_game_stats_2018q3_ix003 on player_game_stats_2018q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2018q4 ( + CHECK ( create_dt >= DATE '2018-10-01' AND create_dt < DATE '2019-01-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2013Q4_ix01 on player_game_stats_2013Q4(create_dt); -CREATE INDEX player_game_stats_2013Q4_ix02 on player_game_stats_2013Q4(game_id); -CREATE INDEX player_game_stats_2013Q4_ix03 on player_game_stats_2013Q4(player_id); -ALTER TABLE xonstat.player_game_stats_2013Q4 OWNER TO xonstat; +CREATE INDEX player_game_stats_2018q4_ix001 on player_game_stats_2018q4(create_dt); +CREATE INDEX player_game_stats_2018q4_ix002 on player_game_stats_2018q4(game_id); +CREATE INDEX player_game_stats_2018q4_ix003 on player_game_stats_2018q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2019q1 ( + CHECK ( create_dt >= DATE '2019-01-01' AND create_dt < DATE '2019-04-01' ) +) INHERITS (player_game_stats); +CREATE INDEX player_game_stats_2019q1_ix001 on player_game_stats_2019q1(create_dt); +CREATE INDEX player_game_stats_2019q1_ix002 on player_game_stats_2019q1(game_id); +CREATE INDEX player_game_stats_2019q1_ix003 on player_game_stats_2019q1(player_id); --- 2014 -CREATE TABLE xonstat.player_game_stats_2014Q1 ( - CHECK ( create_dt >= DATE '2014-01-01' AND create_dt < DATE '2014-04-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2019q2 ( + CHECK ( create_dt >= DATE '2019-04-01' AND create_dt < DATE '2019-07-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2014Q1_ix01 on player_game_stats_2014Q1(create_dt); -CREATE INDEX player_game_stats_2014Q1_ix02 on player_game_stats_2014Q1(game_id); -CREATE INDEX player_game_stats_2014Q1_ix03 on player_game_stats_2014Q1(player_id); -ALTER TABLE xonstat.player_game_stats_2014Q1 OWNER TO xonstat; +CREATE INDEX player_game_stats_2019q2_ix001 on player_game_stats_2019q2(create_dt); +CREATE INDEX player_game_stats_2019q2_ix002 on player_game_stats_2019q2(game_id); +CREATE INDEX player_game_stats_2019q2_ix003 on player_game_stats_2019q2(player_id); +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2019q3 ( + CHECK ( create_dt >= DATE '2019-07-01' AND create_dt < DATE '2019-10-01' ) +) INHERITS (player_game_stats); + +CREATE INDEX player_game_stats_2019q3_ix001 on player_game_stats_2019q3(create_dt); +CREATE INDEX player_game_stats_2019q3_ix002 on player_game_stats_2019q3(game_id); +CREATE INDEX player_game_stats_2019q3_ix003 on player_game_stats_2019q3(player_id); -CREATE TABLE xonstat.player_game_stats_2014Q2 ( - CHECK ( create_dt >= DATE '2014-04-01' AND create_dt < DATE '2014-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2019q4 ( + CHECK ( create_dt >= DATE '2019-10-01' AND create_dt < DATE '2020-01-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2014Q2_ix01 on player_game_stats_2014Q2(create_dt); -CREATE INDEX player_game_stats_2014Q2_ix02 on player_game_stats_2014Q2(game_id); -CREATE INDEX player_game_stats_2014Q2_ix03 on player_game_stats_2014Q2(player_id); -ALTER TABLE xonstat.player_game_stats_2014Q2 OWNER TO xonstat; +CREATE INDEX player_game_stats_2019q4_ix001 on player_game_stats_2019q4(create_dt); +CREATE INDEX player_game_stats_2019q4_ix002 on player_game_stats_2019q4(game_id); +CREATE INDEX player_game_stats_2019q4_ix003 on player_game_stats_2019q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2020q1 ( + CHECK ( create_dt >= DATE '2020-01-01' AND create_dt < DATE '2020-04-01' ) +) INHERITS (player_game_stats); +CREATE INDEX player_game_stats_2020q1_ix001 on player_game_stats_2020q1(create_dt); +CREATE INDEX player_game_stats_2020q1_ix002 on player_game_stats_2020q1(game_id); +CREATE INDEX player_game_stats_2020q1_ix003 on player_game_stats_2020q1(player_id); -CREATE TABLE xonstat.player_game_stats_2014Q3 ( - CHECK ( create_dt >= DATE '2014-07-01' AND create_dt < DATE '2014-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2020q2 ( + CHECK ( create_dt >= DATE '2020-04-01' AND create_dt < DATE '2020-07-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2014Q3_ix01 on player_game_stats_2014Q3(create_dt); -CREATE INDEX player_game_stats_2014Q3_ix02 on player_game_stats_2014Q3(game_id); -CREATE INDEX player_game_stats_2014Q3_ix03 on player_game_stats_2014Q3(player_id); -ALTER TABLE xonstat.player_game_stats_2014Q3 OWNER TO xonstat; +CREATE INDEX player_game_stats_2020q2_ix001 on player_game_stats_2020q2(create_dt); +CREATE INDEX player_game_stats_2020q2_ix002 on player_game_stats_2020q2(game_id); +CREATE INDEX player_game_stats_2020q2_ix003 on player_game_stats_2020q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2020q3 ( + CHECK ( create_dt >= DATE '2020-07-01' AND create_dt < DATE '2020-10-01' ) +) INHERITS (player_game_stats); +CREATE INDEX player_game_stats_2020q3_ix001 on player_game_stats_2020q3(create_dt); +CREATE INDEX player_game_stats_2020q3_ix002 on player_game_stats_2020q3(game_id); +CREATE INDEX player_game_stats_2020q3_ix003 on player_game_stats_2020q3(player_id); -CREATE TABLE xonstat.player_game_stats_2014Q4 ( - CHECK ( create_dt >= DATE '2014-10-01' AND create_dt < DATE '2015-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_game_stats_2020q4 ( + CHECK ( create_dt >= DATE '2020-10-01' AND create_dt < DATE '2021-01-01' ) ) INHERITS (player_game_stats); -CREATE INDEX player_game_stats_2014Q4_ix01 on player_game_stats_2014Q4(create_dt); -CREATE INDEX player_game_stats_2014Q4_ix02 on player_game_stats_2014Q4(game_id); -CREATE INDEX player_game_stats_2014Q4_ix03 on player_game_stats_2014Q4(player_id); -ALTER TABLE xonstat.player_game_stats_2014Q4 OWNER TO xonstat; +CREATE INDEX player_game_stats_2020q4_ix001 on player_game_stats_2020q4(create_dt); +CREATE INDEX player_game_stats_2020q4_ix002 on player_game_stats_2020q4(game_id); +CREATE INDEX player_game_stats_2020q4_ix003 on player_game_stats_2020q4(player_id); diff --git a/tables/player_weapon_stats.tab b/tables/player_weapon_stats.tab index 0645fc3..f5bbb03 100755 --- a/tables/player_weapon_stats.tab +++ b/tables/player_weapon_stats.tab @@ -35,141 +35,226 @@ CREATE INDEX player_weap_stats_ix03 on player_weapon_stats(player_id); ALTER TABLE xonstat.player_weapon_stats OWNER TO xonstat; --- 2011 -CREATE TABLE xonstat.player_weapon_stats_2011Q2 ( - CHECK ( create_dt >= DATE '2011-04-01' AND create_dt < DATE '2011-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2014q1 ( + CHECK ( create_dt >= DATE '2014-01-01' AND create_dt < DATE '2014-04-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2011Q2_ix01 on player_weapon_stats_2011Q2(create_dt); -CREATE INDEX player_weap_stats_2011Q2_ix02 on player_weapon_stats_2011Q2(game_id); -CREATE INDEX player_weap_stats_2011Q2_ix03 on player_weapon_stats_2011Q2(player_id); -ALTER TABLE xonstat.player_weapon_stats_2011Q2 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2014q1_ix001 on player_weapon_stats_2014q1(create_dt); +CREATE INDEX player_weapon_stats_2014q1_ix002 on player_weapon_stats_2014q1(game_id); +CREATE INDEX player_weapon_stats_2014q1_ix003 on player_weapon_stats_2014q1(player_id); -CREATE TABLE xonstat.player_weapon_stats_2011Q3 ( - CHECK ( create_dt >= DATE '2011-07-01' AND create_dt < DATE '2011-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2014q2 ( + CHECK ( create_dt >= DATE '2014-04-01' AND create_dt < DATE '2014-07-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2011Q3_ix01 on player_weapon_stats_2011Q3(create_dt); -CREATE INDEX player_weap_stats_2011Q3_ix02 on player_weapon_stats_2011Q3(game_id); -CREATE INDEX player_weap_stats_2011Q3_ix03 on player_weapon_stats_2011Q3(player_id); -ALTER TABLE xonstat.player_weapon_stats_2011Q3 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2014q2_ix001 on player_weapon_stats_2014q2(create_dt); +CREATE INDEX player_weapon_stats_2014q2_ix002 on player_weapon_stats_2014q2(game_id); +CREATE INDEX player_weapon_stats_2014q2_ix003 on player_weapon_stats_2014q2(player_id); -CREATE TABLE xonstat.player_weapon_stats_2011Q4 ( - CHECK ( create_dt >= DATE '2011-10-01' AND create_dt < DATE '2012-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2014q3 ( + CHECK ( create_dt >= DATE '2014-07-01' AND create_dt < DATE '2014-10-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2011Q4_ix01 on player_weapon_stats_2011Q4(create_dt); -CREATE INDEX player_weap_stats_2011Q4_ix02 on player_weapon_stats_2011Q4(game_id); -CREATE INDEX player_weap_stats_2011Q4_ix03 on player_weapon_stats_2011Q4(player_id); -ALTER TABLE xonstat.player_weapon_stats_2011Q4 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2014q3_ix001 on player_weapon_stats_2014q3(create_dt); +CREATE INDEX player_weapon_stats_2014q3_ix002 on player_weapon_stats_2014q3(game_id); +CREATE INDEX player_weapon_stats_2014q3_ix003 on player_weapon_stats_2014q3(player_id); --- 2012 -CREATE TABLE xonstat.player_weapon_stats_2012Q1 ( - CHECK ( create_dt >= DATE '2012-01-01' AND create_dt < DATE '2012-04-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2014q4 ( + CHECK ( create_dt >= DATE '2014-10-01' AND create_dt < DATE '2015-01-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2012Q1_ix01 on player_weapon_stats_2012Q1(create_dt); -CREATE INDEX player_weap_stats_2012Q1_ix02 on player_weapon_stats_2012Q1(game_id); -CREATE INDEX player_weap_stats_2012Q1_ix03 on player_weapon_stats_2012Q1(player_id); -ALTER TABLE xonstat.player_weapon_stats_2012Q1 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2014q4_ix001 on player_weapon_stats_2014q4(create_dt); +CREATE INDEX player_weapon_stats_2014q4_ix002 on player_weapon_stats_2014q4(game_id); +CREATE INDEX player_weapon_stats_2014q4_ix003 on player_weapon_stats_2014q4(player_id); -CREATE TABLE xonstat.player_weapon_stats_2012Q2 ( - CHECK ( create_dt >= DATE '2012-04-01' AND create_dt < DATE '2012-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2015q1 ( + CHECK ( create_dt >= DATE '2015-01-01' AND create_dt < DATE '2015-04-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2012Q2_ix01 on player_weapon_stats_2012Q2(create_dt); -CREATE INDEX player_weap_stats_2012Q2_ix02 on player_weapon_stats_2012Q2(game_id); -CREATE INDEX player_weap_stats_2012Q2_ix03 on player_weapon_stats_2012Q2(player_id); -ALTER TABLE xonstat.player_weapon_stats_2012Q2 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2015q1_ix001 on player_weapon_stats_2015q1(create_dt); +CREATE INDEX player_weapon_stats_2015q1_ix002 on player_weapon_stats_2015q1(game_id); +CREATE INDEX player_weapon_stats_2015q1_ix003 on player_weapon_stats_2015q1(player_id); -CREATE TABLE xonstat.player_weapon_stats_2012Q3 ( - CHECK ( create_dt >= DATE '2012-07-01' AND create_dt < DATE '2012-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2015q2 ( + CHECK ( create_dt >= DATE '2015-04-01' AND create_dt < DATE '2015-07-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2012Q3_ix01 on player_weapon_stats_2012Q3(create_dt); -CREATE INDEX player_weap_stats_2012Q3_ix02 on player_weapon_stats_2012Q3(game_id); -CREATE INDEX player_weap_stats_2012Q3_ix03 on player_weapon_stats_2012Q3(player_id); -ALTER TABLE xonstat.player_weapon_stats_2012Q3 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2015q2_ix001 on player_weapon_stats_2015q2(create_dt); +CREATE INDEX player_weapon_stats_2015q2_ix002 on player_weapon_stats_2015q2(game_id); +CREATE INDEX player_weapon_stats_2015q2_ix003 on player_weapon_stats_2015q2(player_id); -CREATE TABLE xonstat.player_weapon_stats_2012Q4 ( - CHECK ( create_dt >= DATE '2012-10-01' AND create_dt < DATE '2013-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2015q3 ( + CHECK ( create_dt >= DATE '2015-07-01' AND create_dt < DATE '2015-10-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2012Q4_ix01 on player_weapon_stats_2012Q4(create_dt); -CREATE INDEX player_weap_stats_2012Q4_ix02 on player_weapon_stats_2012Q4(game_id); -CREATE INDEX player_weap_stats_2012Q4_ix03 on player_weapon_stats_2012Q4(player_id); -ALTER TABLE xonstat.player_weapon_stats_2012Q4 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2015q3_ix001 on player_weapon_stats_2015q3(create_dt); +CREATE INDEX player_weapon_stats_2015q3_ix002 on player_weapon_stats_2015q3(game_id); +CREATE INDEX player_weapon_stats_2015q3_ix003 on player_weapon_stats_2015q3(player_id); --- 2013 -CREATE TABLE xonstat.player_weapon_stats_2013Q1 ( - CHECK ( create_dt >= DATE '2013-01-01' AND create_dt < DATE '2013-04-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2015q4 ( + CHECK ( create_dt >= DATE '2015-10-01' AND create_dt < DATE '2016-01-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2013Q1_ix01 on player_weapon_stats_2013Q1(create_dt); -CREATE INDEX player_weap_stats_2013Q1_ix02 on player_weapon_stats_2013Q1(game_id); -CREATE INDEX player_weap_stats_2013Q1_ix03 on player_weapon_stats_2013Q1(player_id); -ALTER TABLE xonstat.player_weapon_stats_2013Q1 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2015q4_ix001 on player_weapon_stats_2015q4(create_dt); +CREATE INDEX player_weapon_stats_2015q4_ix002 on player_weapon_stats_2015q4(game_id); +CREATE INDEX player_weapon_stats_2015q4_ix003 on player_weapon_stats_2015q4(player_id); -CREATE TABLE xonstat.player_weapon_stats_2013Q2 ( - CHECK ( create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2016q1 ( + CHECK ( create_dt >= DATE '2016-01-01' AND create_dt < DATE '2016-04-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2013Q2_ix01 on player_weapon_stats_2013Q2(create_dt); -CREATE INDEX player_weap_stats_2013Q2_ix02 on player_weapon_stats_2013Q2(game_id); -CREATE INDEX player_weap_stats_2013Q2_ix03 on player_weapon_stats_2013Q2(player_id); -ALTER TABLE xonstat.player_weapon_stats_2013Q2 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2016q1_ix001 on player_weapon_stats_2016q1(create_dt); +CREATE INDEX player_weapon_stats_2016q1_ix002 on player_weapon_stats_2016q1(game_id); +CREATE INDEX player_weapon_stats_2016q1_ix003 on player_weapon_stats_2016q1(player_id); -CREATE TABLE xonstat.player_weapon_stats_2013Q3 ( - CHECK ( create_dt >= DATE '2013-07-01' AND create_dt < DATE '2013-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2016q2 ( + CHECK ( create_dt >= DATE '2016-04-01' AND create_dt < DATE '2016-07-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2013Q3_ix01 on player_weapon_stats_2013Q3(create_dt); -CREATE INDEX player_weap_stats_2013Q3_ix02 on player_weapon_stats_2013Q3(game_id); -CREATE INDEX player_weap_stats_2013Q3_ix03 on player_weapon_stats_2013Q3(player_id); -ALTER TABLE xonstat.player_weapon_stats_2013Q3 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2016q2_ix001 on player_weapon_stats_2016q2(create_dt); +CREATE INDEX player_weapon_stats_2016q2_ix002 on player_weapon_stats_2016q2(game_id); +CREATE INDEX player_weapon_stats_2016q2_ix003 on player_weapon_stats_2016q2(player_id); -CREATE TABLE xonstat.player_weapon_stats_2013Q4 ( - CHECK ( create_dt >= DATE '2013-10-01' AND create_dt < DATE '2014-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2016q3 ( + CHECK ( create_dt >= DATE '2016-07-01' AND create_dt < DATE '2016-10-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2013Q4_ix01 on player_weapon_stats_2013Q4(create_dt); -CREATE INDEX player_weap_stats_2013Q4_ix02 on player_weapon_stats_2013Q4(game_id); -CREATE INDEX player_weap_stats_2013Q4_ix03 on player_weapon_stats_2013Q4(player_id); -ALTER TABLE xonstat.player_weapon_stats_2013Q4 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2016q3_ix001 on player_weapon_stats_2016q3(create_dt); +CREATE INDEX player_weapon_stats_2016q3_ix002 on player_weapon_stats_2016q3(game_id); +CREATE INDEX player_weapon_stats_2016q3_ix003 on player_weapon_stats_2016q3(player_id); --- 2014 -CREATE TABLE xonstat.player_weapon_stats_2014Q1 ( - CHECK ( create_dt >= DATE '2014-01-01' AND create_dt < DATE '2014-04-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2016q4 ( + CHECK ( create_dt >= DATE '2016-10-01' AND create_dt < DATE '2017-01-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2014Q1_ix01 on player_weapon_stats_2014Q1(create_dt); -CREATE INDEX player_weap_stats_2014Q1_ix02 on player_weapon_stats_2014Q1(game_id); -CREATE INDEX player_weap_stats_2014Q1_ix03 on player_weapon_stats_2014Q1(player_id); -ALTER TABLE xonstat.player_weapon_stats_2014Q1 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2016q4_ix001 on player_weapon_stats_2016q4(create_dt); +CREATE INDEX player_weapon_stats_2016q4_ix002 on player_weapon_stats_2016q4(game_id); +CREATE INDEX player_weapon_stats_2016q4_ix003 on player_weapon_stats_2016q4(player_id); -CREATE TABLE xonstat.player_weapon_stats_2014Q2 ( - CHECK ( create_dt >= DATE '2014-04-01' AND create_dt < DATE '2014-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2017q1 ( + CHECK ( create_dt >= DATE '2017-01-01' AND create_dt < DATE '2017-04-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2014Q2_ix01 on player_weapon_stats_2014Q2(create_dt); -CREATE INDEX player_weap_stats_2014Q2_ix02 on player_weapon_stats_2014Q2(game_id); -CREATE INDEX player_weap_stats_2014Q2_ix03 on player_weapon_stats_2014Q2(player_id); -ALTER TABLE xonstat.player_weapon_stats_2014Q2 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2017q1_ix001 on player_weapon_stats_2017q1(create_dt); +CREATE INDEX player_weapon_stats_2017q1_ix002 on player_weapon_stats_2017q1(game_id); +CREATE INDEX player_weapon_stats_2017q1_ix003 on player_weapon_stats_2017q1(player_id); -CREATE TABLE xonstat.player_weapon_stats_2014Q3 ( - CHECK ( create_dt >= DATE '2014-07-01' AND create_dt < DATE '2014-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2017q2 ( + CHECK ( create_dt >= DATE '2017-04-01' AND create_dt < DATE '2017-07-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2014Q3_ix01 on player_weapon_stats_2014Q3(create_dt); -CREATE INDEX player_weap_stats_2014Q3_ix02 on player_weapon_stats_2014Q3(game_id); -CREATE INDEX player_weap_stats_2014Q3_ix03 on player_weapon_stats_2014Q3(player_id); -ALTER TABLE xonstat.player_weapon_stats_2014Q3 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2017q2_ix001 on player_weapon_stats_2017q2(create_dt); +CREATE INDEX player_weapon_stats_2017q2_ix002 on player_weapon_stats_2017q2(game_id); +CREATE INDEX player_weapon_stats_2017q2_ix003 on player_weapon_stats_2017q2(player_id); -CREATE TABLE xonstat.player_weapon_stats_2014Q4 ( - CHECK ( create_dt >= DATE '2014-10-01' AND create_dt < DATE '2015-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2017q3 ( + CHECK ( create_dt >= DATE '2017-07-01' AND create_dt < DATE '2017-10-01' ) ) INHERITS (player_weapon_stats); -CREATE INDEX player_weap_stats_2014Q4_ix01 on player_weapon_stats_2014Q4(create_dt); -CREATE INDEX player_weap_stats_2014Q4_ix02 on player_weapon_stats_2014Q4(game_id); -CREATE INDEX player_weap_stats_2014Q4_ix03 on player_weapon_stats_2014Q4(player_id); -ALTER TABLE xonstat.player_weapon_stats_2014Q4 OWNER TO xonstat; +CREATE INDEX player_weapon_stats_2017q3_ix001 on player_weapon_stats_2017q3(create_dt); +CREATE INDEX player_weapon_stats_2017q3_ix002 on player_weapon_stats_2017q3(game_id); +CREATE INDEX player_weapon_stats_2017q3_ix003 on player_weapon_stats_2017q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2017q4 ( + CHECK ( create_dt >= DATE '2017-10-01' AND create_dt < DATE '2018-01-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2017q4_ix001 on player_weapon_stats_2017q4(create_dt); +CREATE INDEX player_weapon_stats_2017q4_ix002 on player_weapon_stats_2017q4(game_id); +CREATE INDEX player_weapon_stats_2017q4_ix003 on player_weapon_stats_2017q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2018q1 ( + CHECK ( create_dt >= DATE '2018-01-01' AND create_dt < DATE '2018-04-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2018q1_ix001 on player_weapon_stats_2018q1(create_dt); +CREATE INDEX player_weapon_stats_2018q1_ix002 on player_weapon_stats_2018q1(game_id); +CREATE INDEX player_weapon_stats_2018q1_ix003 on player_weapon_stats_2018q1(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2018q2 ( + CHECK ( create_dt >= DATE '2018-04-01' AND create_dt < DATE '2018-07-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2018q2_ix001 on player_weapon_stats_2018q2(create_dt); +CREATE INDEX player_weapon_stats_2018q2_ix002 on player_weapon_stats_2018q2(game_id); +CREATE INDEX player_weapon_stats_2018q2_ix003 on player_weapon_stats_2018q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2018q3 ( + CHECK ( create_dt >= DATE '2018-07-01' AND create_dt < DATE '2018-10-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2018q3_ix001 on player_weapon_stats_2018q3(create_dt); +CREATE INDEX player_weapon_stats_2018q3_ix002 on player_weapon_stats_2018q3(game_id); +CREATE INDEX player_weapon_stats_2018q3_ix003 on player_weapon_stats_2018q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2018q4 ( + CHECK ( create_dt >= DATE '2018-10-01' AND create_dt < DATE '2019-01-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2018q4_ix001 on player_weapon_stats_2018q4(create_dt); +CREATE INDEX player_weapon_stats_2018q4_ix002 on player_weapon_stats_2018q4(game_id); +CREATE INDEX player_weapon_stats_2018q4_ix003 on player_weapon_stats_2018q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2019q1 ( + CHECK ( create_dt >= DATE '2019-01-01' AND create_dt < DATE '2019-04-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2019q1_ix001 on player_weapon_stats_2019q1(create_dt); +CREATE INDEX player_weapon_stats_2019q1_ix002 on player_weapon_stats_2019q1(game_id); +CREATE INDEX player_weapon_stats_2019q1_ix003 on player_weapon_stats_2019q1(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2019q2 ( + CHECK ( create_dt >= DATE '2019-04-01' AND create_dt < DATE '2019-07-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2019q2_ix001 on player_weapon_stats_2019q2(create_dt); +CREATE INDEX player_weapon_stats_2019q2_ix002 on player_weapon_stats_2019q2(game_id); +CREATE INDEX player_weapon_stats_2019q2_ix003 on player_weapon_stats_2019q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2019q3 ( + CHECK ( create_dt >= DATE '2019-07-01' AND create_dt < DATE '2019-10-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2019q3_ix001 on player_weapon_stats_2019q3(create_dt); +CREATE INDEX player_weapon_stats_2019q3_ix002 on player_weapon_stats_2019q3(game_id); +CREATE INDEX player_weapon_stats_2019q3_ix003 on player_weapon_stats_2019q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2019q4 ( + CHECK ( create_dt >= DATE '2019-10-01' AND create_dt < DATE '2020-01-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2019q4_ix001 on player_weapon_stats_2019q4(create_dt); +CREATE INDEX player_weapon_stats_2019q4_ix002 on player_weapon_stats_2019q4(game_id); +CREATE INDEX player_weapon_stats_2019q4_ix003 on player_weapon_stats_2019q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2020q1 ( + CHECK ( create_dt >= DATE '2020-01-01' AND create_dt < DATE '2020-04-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2020q1_ix001 on player_weapon_stats_2020q1(create_dt); +CREATE INDEX player_weapon_stats_2020q1_ix002 on player_weapon_stats_2020q1(game_id); +CREATE INDEX player_weapon_stats_2020q1_ix003 on player_weapon_stats_2020q1(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2020q2 ( + CHECK ( create_dt >= DATE '2020-04-01' AND create_dt < DATE '2020-07-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2020q2_ix001 on player_weapon_stats_2020q2(create_dt); +CREATE INDEX player_weapon_stats_2020q2_ix002 on player_weapon_stats_2020q2(game_id); +CREATE INDEX player_weapon_stats_2020q2_ix003 on player_weapon_stats_2020q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2020q3 ( + CHECK ( create_dt >= DATE '2020-07-01' AND create_dt < DATE '2020-10-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2020q3_ix001 on player_weapon_stats_2020q3(create_dt); +CREATE INDEX player_weapon_stats_2020q3_ix002 on player_weapon_stats_2020q3(game_id); +CREATE INDEX player_weapon_stats_2020q3_ix003 on player_weapon_stats_2020q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_weapon_stats_2020q4 ( + CHECK ( create_dt >= DATE '2020-10-01' AND create_dt < DATE '2021-01-01' ) +) INHERITS (player_weapon_stats); + +CREATE INDEX player_weapon_stats_2020q4_ix001 on player_weapon_stats_2020q4(create_dt); +CREATE INDEX player_weapon_stats_2020q4_ix002 on player_weapon_stats_2020q4(game_id); +CREATE INDEX player_weapon_stats_2020q4_ix003 on player_weapon_stats_2020q4(player_id); diff --git a/tables/team_game_stats.tab b/tables/team_game_stats.tab index 4ed77f5..fbe2595 100755 --- a/tables/team_game_stats.tab +++ b/tables/team_game_stats.tab @@ -20,60 +20,198 @@ WITH ( CREATE INDEX team_game_stats_ix01 on team_game_stats(game_id); ALTER TABLE xonstat.team_game_stats OWNER TO xonstat; +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2014q1 ( + CHECK ( create_dt >= DATE '2014-01-01' AND create_dt < DATE '2014-04-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2014q1_ix001 on team_game_stats_2014q1(create_dt); +CREATE INDEX team_game_stats_2014q1_ix002 on team_game_stats_2014q1(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2014q2 ( + CHECK ( create_dt >= DATE '2014-04-01' AND create_dt < DATE '2014-07-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2014q2_ix001 on team_game_stats_2014q2(create_dt); +CREATE INDEX team_game_stats_2014q2_ix002 on team_game_stats_2014q2(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2014q3 ( + CHECK ( create_dt >= DATE '2014-07-01' AND create_dt < DATE '2014-10-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2014q3_ix001 on team_game_stats_2014q3(create_dt); +CREATE INDEX team_game_stats_2014q3_ix002 on team_game_stats_2014q3(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2014q4 ( + CHECK ( create_dt >= DATE '2014-10-01' AND create_dt < DATE '2015-01-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2014q4_ix001 on team_game_stats_2014q4(create_dt); +CREATE INDEX team_game_stats_2014q4_ix002 on team_game_stats_2014q4(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2015q1 ( + CHECK ( create_dt >= DATE '2015-01-01' AND create_dt < DATE '2015-04-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2015q1_ix001 on team_game_stats_2015q1(create_dt); +CREATE INDEX team_game_stats_2015q1_ix002 on team_game_stats_2015q1(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2015q2 ( + CHECK ( create_dt >= DATE '2015-04-01' AND create_dt < DATE '2015-07-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2015q2_ix001 on team_game_stats_2015q2(create_dt); +CREATE INDEX team_game_stats_2015q2_ix002 on team_game_stats_2015q2(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2015q3 ( + CHECK ( create_dt >= DATE '2015-07-01' AND create_dt < DATE '2015-10-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2015q3_ix001 on team_game_stats_2015q3(create_dt); +CREATE INDEX team_game_stats_2015q3_ix002 on team_game_stats_2015q3(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2015q4 ( + CHECK ( create_dt >= DATE '2015-10-01' AND create_dt < DATE '2016-01-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2015q4_ix001 on team_game_stats_2015q4(create_dt); +CREATE INDEX team_game_stats_2015q4_ix002 on team_game_stats_2015q4(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2016q1 ( + CHECK ( create_dt >= DATE '2016-01-01' AND create_dt < DATE '2016-04-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2016q1_ix001 on team_game_stats_2016q1(create_dt); +CREATE INDEX team_game_stats_2016q1_ix002 on team_game_stats_2016q1(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2016q2 ( + CHECK ( create_dt >= DATE '2016-04-01' AND create_dt < DATE '2016-07-01' ) +) INHERITS (team_game_stats); --- 2013 -CREATE TABLE xonstat.team_game_stats_2013Q2 ( - CHECK ( create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01' ) +CREATE INDEX team_game_stats_2016q2_ix001 on team_game_stats_2016q2(create_dt); +CREATE INDEX team_game_stats_2016q2_ix002 on team_game_stats_2016q2(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2016q3 ( + CHECK ( create_dt >= DATE '2016-07-01' AND create_dt < DATE '2016-10-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2016q3_ix001 on team_game_stats_2016q3(create_dt); +CREATE INDEX team_game_stats_2016q3_ix002 on team_game_stats_2016q3(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2016q4 ( + CHECK ( create_dt >= DATE '2016-10-01' AND create_dt < DATE '2017-01-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2016q4_ix001 on team_game_stats_2016q4(create_dt); +CREATE INDEX team_game_stats_2016q4_ix002 on team_game_stats_2016q4(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2017q1 ( + CHECK ( create_dt >= DATE '2017-01-01' AND create_dt < DATE '2017-04-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2017q1_ix001 on team_game_stats_2017q1(create_dt); +CREATE INDEX team_game_stats_2017q1_ix002 on team_game_stats_2017q1(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2017q2 ( + CHECK ( create_dt >= DATE '2017-04-01' AND create_dt < DATE '2017-07-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2017q2_ix001 on team_game_stats_2017q2(create_dt); +CREATE INDEX team_game_stats_2017q2_ix002 on team_game_stats_2017q2(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2017q3 ( + CHECK ( create_dt >= DATE '2017-07-01' AND create_dt < DATE '2017-10-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2017q3_ix001 on team_game_stats_2017q3(create_dt); +CREATE INDEX team_game_stats_2017q3_ix002 on team_game_stats_2017q3(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2017q4 ( + CHECK ( create_dt >= DATE '2017-10-01' AND create_dt < DATE '2018-01-01' ) ) INHERITS (team_game_stats); -CREATE INDEX team_game_stats_2013Q2_ix01 on team_game_stats_2013Q2(game_id); -ALTER TABLE xonstat.team_game_stats_2013Q2 OWNER TO xonstat; +CREATE INDEX team_game_stats_2017q4_ix001 on team_game_stats_2017q4(create_dt); +CREATE INDEX team_game_stats_2017q4_ix002 on team_game_stats_2017q4(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2018q1 ( + CHECK ( create_dt >= DATE '2018-01-01' AND create_dt < DATE '2018-04-01' ) +) INHERITS (team_game_stats); +CREATE INDEX team_game_stats_2018q1_ix001 on team_game_stats_2018q1(create_dt); +CREATE INDEX team_game_stats_2018q1_ix002 on team_game_stats_2018q1(game_id); -CREATE TABLE xonstat.team_game_stats_2013Q3 ( - CHECK ( create_dt >= DATE '2013-07-01' AND create_dt < DATE '2013-10-01' ) +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2018q2 ( + CHECK ( create_dt >= DATE '2018-04-01' AND create_dt < DATE '2018-07-01' ) ) INHERITS (team_game_stats); -CREATE INDEX team_game_stats_2013Q3_ix01 on team_game_stats_2013Q3(game_id); -ALTER TABLE xonstat.team_game_stats_2013Q3 OWNER TO xonstat; +CREATE INDEX team_game_stats_2018q2_ix001 on team_game_stats_2018q2(create_dt); +CREATE INDEX team_game_stats_2018q2_ix002 on team_game_stats_2018q2(game_id); +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2018q3 ( + CHECK ( create_dt >= DATE '2018-07-01' AND create_dt < DATE '2018-10-01' ) +) INHERITS (team_game_stats); + +CREATE INDEX team_game_stats_2018q3_ix001 on team_game_stats_2018q3(create_dt); +CREATE INDEX team_game_stats_2018q3_ix002 on team_game_stats_2018q3(game_id); -CREATE TABLE xonstat.team_game_stats_2013Q4 ( - CHECK ( create_dt >= DATE '2013-10-01' AND create_dt < DATE '2014-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2018q4 ( + CHECK ( create_dt >= DATE '2018-10-01' AND create_dt < DATE '2019-01-01' ) ) INHERITS (team_game_stats); -CREATE INDEX team_game_stats_2013Q4_ix01 on team_game_stats_2013Q4(game_id); -ALTER TABLE xonstat.team_game_stats_2013Q4 OWNER TO xonstat; +CREATE INDEX team_game_stats_2018q4_ix001 on team_game_stats_2018q4(create_dt); +CREATE INDEX team_game_stats_2018q4_ix002 on team_game_stats_2018q4(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2019q1 ( + CHECK ( create_dt >= DATE '2019-01-01' AND create_dt < DATE '2019-04-01' ) +) INHERITS (team_game_stats); +CREATE INDEX team_game_stats_2019q1_ix001 on team_game_stats_2019q1(create_dt); +CREATE INDEX team_game_stats_2019q1_ix002 on team_game_stats_2019q1(game_id); --- 2014 -CREATE TABLE xonstat.team_game_stats_2014Q1 ( - CHECK ( create_dt >= DATE '2014-01-01' AND create_dt < DATE '2014-04-01' ) +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2019q2 ( + CHECK ( create_dt >= DATE '2019-04-01' AND create_dt < DATE '2019-07-01' ) ) INHERITS (team_game_stats); -CREATE INDEX team_game_stats_2014Q1_ix01 on team_game_stats_2014Q1(game_id); -ALTER TABLE xonstat.team_game_stats_2014Q1 OWNER TO xonstat; +CREATE INDEX team_game_stats_2019q2_ix001 on team_game_stats_2019q2(create_dt); +CREATE INDEX team_game_stats_2019q2_ix002 on team_game_stats_2019q2(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2019q3 ( + CHECK ( create_dt >= DATE '2019-07-01' AND create_dt < DATE '2019-10-01' ) +) INHERITS (team_game_stats); +CREATE INDEX team_game_stats_2019q3_ix001 on team_game_stats_2019q3(create_dt); +CREATE INDEX team_game_stats_2019q3_ix002 on team_game_stats_2019q3(game_id); -CREATE TABLE xonstat.team_game_stats_2014Q2 ( - CHECK ( create_dt >= DATE '2014-04-01' AND create_dt < DATE '2014-07-01' ) +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2019q4 ( + CHECK ( create_dt >= DATE '2019-10-01' AND create_dt < DATE '2020-01-01' ) ) INHERITS (team_game_stats); -CREATE INDEX team_game_stats_2014Q2_ix01 on team_game_stats_2014Q2(game_id); -ALTER TABLE xonstat.team_game_stats_2014Q2 OWNER TO xonstat; +CREATE INDEX team_game_stats_2019q4_ix001 on team_game_stats_2019q4(create_dt); +CREATE INDEX team_game_stats_2019q4_ix002 on team_game_stats_2019q4(game_id); +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2020q1 ( + CHECK ( create_dt >= DATE '2020-01-01' AND create_dt < DATE '2020-04-01' ) +) INHERITS (team_game_stats); -CREATE TABLE xonstat.team_game_stats_2014Q3 ( - CHECK ( create_dt >= DATE '2014-07-01' AND create_dt < DATE '2014-10-01' ) +CREATE INDEX team_game_stats_2020q1_ix001 on team_game_stats_2020q1(create_dt); +CREATE INDEX team_game_stats_2020q1_ix002 on team_game_stats_2020q1(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2020q2 ( + CHECK ( create_dt >= DATE '2020-04-01' AND create_dt < DATE '2020-07-01' ) ) INHERITS (team_game_stats); -CREATE INDEX team_game_stats_2014Q3_ix01 on team_game_stats_2014Q3(game_id); -ALTER TABLE xonstat.team_game_stats_2014Q3 OWNER TO xonstat; +CREATE INDEX team_game_stats_2020q2_ix001 on team_game_stats_2020q2(create_dt); +CREATE INDEX team_game_stats_2020q2_ix002 on team_game_stats_2020q2(game_id); + +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2020q3 ( + CHECK ( create_dt >= DATE '2020-07-01' AND create_dt < DATE '2020-10-01' ) +) INHERITS (team_game_stats); +CREATE INDEX team_game_stats_2020q3_ix001 on team_game_stats_2020q3(create_dt); +CREATE INDEX team_game_stats_2020q3_ix002 on team_game_stats_2020q3(game_id); -CREATE TABLE xonstat.team_game_stats_2014Q4 ( - CHECK ( create_dt >= DATE '2014-10-01' AND create_dt < DATE '2015-01-01' ) +CREATE TABLE IF NOT EXISTS xonstat.team_game_stats_2020q4 ( + CHECK ( create_dt >= DATE '2020-10-01' AND create_dt < DATE '2021-01-01' ) ) INHERITS (team_game_stats); -CREATE INDEX team_game_stats_2014Q4_ix01 on team_game_stats_2014Q4(game_id); -ALTER TABLE xonstat.team_game_stats_2014Q4 OWNER TO xonstat; +CREATE INDEX team_game_stats_2020q4_ix001 on team_game_stats_2020q4(create_dt); +CREATE INDEX team_game_stats_2020q4_ix002 on team_game_stats_2020q4(game_id); diff --git a/triggers/games_ins_trg.sql b/triggers/games_ins_trg.sql index 0490d18..937ff09 100644 --- a/triggers/games_ins_trg.sql +++ b/triggers/games_ins_trg.sql @@ -1,29 +1,67 @@ CREATE OR REPLACE FUNCTION games_ins() RETURNS TRIGGER AS $$ BEGIN - -- 2013 - IF (NEW.create_dt >= DATE '2013-04-01' AND NEW.create_dt < DATE '2013-07-01') THEN - INSERT INTO games_2013Q2 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2013-07-01' AND NEW.create_dt < DATE '2013-10-01') THEN - INSERT INTO games_2013Q3 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2013-10-01' AND NEW.create_dt < DATE '2014-01-01') THEN - INSERT INTO games_2013Q4 VALUES (NEW.*); - - -- 2014 - ELSIF (NEW.create_dt >= DATE '2014-01-01' AND NEW.create_dt < DATE '2014-04-01') THEN - INSERT INTO games_2014Q1 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-04-01' AND NEW.create_dt < DATE '2014-07-01') THEN - INSERT INTO games_2014Q2 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-07-01' AND NEW.create_dt < DATE '2014-10-01') THEN - INSERT INTO games_2014Q3 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-10-01' AND NEW.create_dt < DATE '2015-01-01') THEN - INSERT INTO games_2014Q4 VALUES (NEW.*); - - ELSE - RAISE EXCEPTION 'Date out of range. Fix the games_ins() trigger!'; - END IF; - RETURN NULL; -END; + IF (NEW.create_dt >= DATE '2014-01-01' AND NEW.create_dt < DATE '2014-04-01') THEN + INSERT INTO games_2014Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-04-01' AND NEW.create_dt < DATE '2014-07-01') THEN + INSERT INTO games_2014Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-07-01' AND NEW.create_dt < DATE '2014-10-01') THEN + INSERT INTO games_2014Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-10-01' AND NEW.create_dt < DATE '2015-01-01') THEN + INSERT INTO games_2014Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-01-01' AND NEW.create_dt < DATE '2015-04-01') THEN + INSERT INTO games_2015Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-04-01' AND NEW.create_dt < DATE '2015-07-01') THEN + INSERT INTO games_2015Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-07-01' AND NEW.create_dt < DATE '2015-10-01') THEN + INSERT INTO games_2015Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-10-01' AND NEW.create_dt < DATE '2016-01-01') THEN + INSERT INTO games_2015Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-01-01' AND NEW.create_dt < DATE '2016-04-01') THEN + INSERT INTO games_2016Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-04-01' AND NEW.create_dt < DATE '2016-07-01') THEN + INSERT INTO games_2016Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-07-01' AND NEW.create_dt < DATE '2016-10-01') THEN + INSERT INTO games_2016Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-10-01' AND NEW.create_dt < DATE '2017-01-01') THEN + INSERT INTO games_2016Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-01-01' AND NEW.create_dt < DATE '2017-04-01') THEN + INSERT INTO games_2017Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-04-01' AND NEW.create_dt < DATE '2017-07-01') THEN + INSERT INTO games_2017Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-07-01' AND NEW.create_dt < DATE '2017-10-01') THEN + INSERT INTO games_2017Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-10-01' AND NEW.create_dt < DATE '2018-01-01') THEN + INSERT INTO games_2017Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-01-01' AND NEW.create_dt < DATE '2018-04-01') THEN + INSERT INTO games_2018Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-04-01' AND NEW.create_dt < DATE '2018-07-01') THEN + INSERT INTO games_2018Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-07-01' AND NEW.create_dt < DATE '2018-10-01') THEN + INSERT INTO games_2018Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-10-01' AND NEW.create_dt < DATE '2019-01-01') THEN + INSERT INTO games_2018Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-01-01' AND NEW.create_dt < DATE '2019-04-01') THEN + INSERT INTO games_2019Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-04-01' AND NEW.create_dt < DATE '2019-07-01') THEN + INSERT INTO games_2019Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-07-01' AND NEW.create_dt < DATE '2019-10-01') THEN + INSERT INTO games_2019Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-10-01' AND NEW.create_dt < DATE '2020-01-01') THEN + INSERT INTO games_2019Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-01-01' AND NEW.create_dt < DATE '2020-04-01') THEN + INSERT INTO games_2020Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-04-01' AND NEW.create_dt < DATE '2020-07-01') THEN + INSERT INTO games_2020Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-07-01' AND NEW.create_dt < DATE '2020-10-01') THEN + INSERT INTO games_2020Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-10-01' AND NEW.create_dt < DATE '2021-01-01') THEN + INSERT INTO games_2020Q4 VALUES (NEW.*); + ELSE + RAISE EXCEPTION 'Date out of range. Fix the games_ins() trigger!'; + END IF; + RETURN NULL; +END $$ LANGUAGE plpgsql; diff --git a/triggers/player_game_stats_ins_trg.sql b/triggers/player_game_stats_ins_trg.sql index a74f2ea..28a808d 100644 --- a/triggers/player_game_stats_ins_trg.sql +++ b/triggers/player_game_stats_ins_trg.sql @@ -1,31 +1,67 @@ CREATE OR REPLACE FUNCTION player_game_stats_ins() RETURNS TRIGGER AS $$ BEGIN - -- 2013 - IF (NEW.create_dt >= DATE '2013-01-01' AND NEW.create_dt < DATE '2013-04-01') THEN - INSERT INTO player_game_stats_2013Q1 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2013-04-01' AND NEW.create_dt < DATE '2013-07-01') THEN - INSERT INTO player_game_stats_2013Q2 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2013-07-01' AND NEW.create_dt < DATE '2013-10-01') THEN - INSERT INTO player_game_stats_2013Q3 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2013-10-01' AND NEW.create_dt < DATE '2014-01-01') THEN - INSERT INTO player_game_stats_2013Q4 VALUES (NEW.*); - - -- 2014 - ELSIF (NEW.create_dt >= DATE '2014-01-01' AND NEW.create_dt < DATE '2014-04-01') THEN - INSERT INTO player_game_stats_2014Q1 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-04-01' AND NEW.create_dt < DATE '2014-07-01') THEN - INSERT INTO player_game_stats_2014Q2 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-07-01' AND NEW.create_dt < DATE '2014-10-01') THEN - INSERT INTO player_game_stats_2014Q3 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-10-01' AND NEW.create_dt < DATE '2015-01-01') THEN - INSERT INTO player_game_stats_2014Q4 VALUES (NEW.*); - - ELSE - RAISE EXCEPTION 'Date out of range. Fix the player_game_stats_ins() trigger!'; - END IF; - RETURN NULL; -END; + IF (NEW.create_dt >= DATE '2014-01-01' AND NEW.create_dt < DATE '2014-04-01') THEN + INSERT INTO player_game_stats_2014Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-04-01' AND NEW.create_dt < DATE '2014-07-01') THEN + INSERT INTO player_game_stats_2014Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-07-01' AND NEW.create_dt < DATE '2014-10-01') THEN + INSERT INTO player_game_stats_2014Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-10-01' AND NEW.create_dt < DATE '2015-01-01') THEN + INSERT INTO player_game_stats_2014Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-01-01' AND NEW.create_dt < DATE '2015-04-01') THEN + INSERT INTO player_game_stats_2015Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-04-01' AND NEW.create_dt < DATE '2015-07-01') THEN + INSERT INTO player_game_stats_2015Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-07-01' AND NEW.create_dt < DATE '2015-10-01') THEN + INSERT INTO player_game_stats_2015Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-10-01' AND NEW.create_dt < DATE '2016-01-01') THEN + INSERT INTO player_game_stats_2015Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-01-01' AND NEW.create_dt < DATE '2016-04-01') THEN + INSERT INTO player_game_stats_2016Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-04-01' AND NEW.create_dt < DATE '2016-07-01') THEN + INSERT INTO player_game_stats_2016Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-07-01' AND NEW.create_dt < DATE '2016-10-01') THEN + INSERT INTO player_game_stats_2016Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-10-01' AND NEW.create_dt < DATE '2017-01-01') THEN + INSERT INTO player_game_stats_2016Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-01-01' AND NEW.create_dt < DATE '2017-04-01') THEN + INSERT INTO player_game_stats_2017Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-04-01' AND NEW.create_dt < DATE '2017-07-01') THEN + INSERT INTO player_game_stats_2017Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-07-01' AND NEW.create_dt < DATE '2017-10-01') THEN + INSERT INTO player_game_stats_2017Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-10-01' AND NEW.create_dt < DATE '2018-01-01') THEN + INSERT INTO player_game_stats_2017Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-01-01' AND NEW.create_dt < DATE '2018-04-01') THEN + INSERT INTO player_game_stats_2018Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-04-01' AND NEW.create_dt < DATE '2018-07-01') THEN + INSERT INTO player_game_stats_2018Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-07-01' AND NEW.create_dt < DATE '2018-10-01') THEN + INSERT INTO player_game_stats_2018Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-10-01' AND NEW.create_dt < DATE '2019-01-01') THEN + INSERT INTO player_game_stats_2018Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-01-01' AND NEW.create_dt < DATE '2019-04-01') THEN + INSERT INTO player_game_stats_2019Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-04-01' AND NEW.create_dt < DATE '2019-07-01') THEN + INSERT INTO player_game_stats_2019Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-07-01' AND NEW.create_dt < DATE '2019-10-01') THEN + INSERT INTO player_game_stats_2019Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-10-01' AND NEW.create_dt < DATE '2020-01-01') THEN + INSERT INTO player_game_stats_2019Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-01-01' AND NEW.create_dt < DATE '2020-04-01') THEN + INSERT INTO player_game_stats_2020Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-04-01' AND NEW.create_dt < DATE '2020-07-01') THEN + INSERT INTO player_game_stats_2020Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-07-01' AND NEW.create_dt < DATE '2020-10-01') THEN + INSERT INTO player_game_stats_2020Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-10-01' AND NEW.create_dt < DATE '2021-01-01') THEN + INSERT INTO player_game_stats_2020Q4 VALUES (NEW.*); + ELSE + RAISE EXCEPTION 'Date out of range. Fix the player_game_stats_ins() trigger!'; + END IF; + RETURN NULL; +END $$ LANGUAGE plpgsql; diff --git a/triggers/player_weapon_stats_ins_trg.sql b/triggers/player_weapon_stats_ins_trg.sql index 3b9e268..e0c256e 100644 --- a/triggers/player_weapon_stats_ins_trg.sql +++ b/triggers/player_weapon_stats_ins_trg.sql @@ -1,28 +1,67 @@ CREATE OR REPLACE FUNCTION player_weapon_stats_ins() RETURNS TRIGGER AS $$ BEGIN - IF (NEW.create_dt >= DATE '2013-04-01' AND NEW.create_dt < DATE '2013-07-01') THEN - INSERT INTO player_weapon_stats_2013Q2 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2013-07-01' AND NEW.create_dt < DATE '2013-10-01') THEN - INSERT INTO player_weapon_stats_2013Q3 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2013-10-01' AND NEW.create_dt < DATE '2014-01-01') THEN - INSERT INTO player_weapon_stats_2013Q4 VALUES (NEW.*); - - -- 2014 - ELSIF (NEW.create_dt >= DATE '2014-01-01' AND NEW.create_dt < DATE '2014-04-01') THEN - INSERT INTO player_weapon_stats_2014Q1 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-04-01' AND NEW.create_dt < DATE '2014-07-01') THEN - INSERT INTO player_weapon_stats_2014Q2 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-07-01' AND NEW.create_dt < DATE '2014-10-01') THEN - INSERT INTO player_weapon_stats_2014Q3 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-10-01' AND NEW.create_dt < DATE '2015-01-01') THEN - INSERT INTO player_weapon_stats_2014Q4 VALUES (NEW.*); - - ELSE - RAISE EXCEPTION 'Date out of range. Fix the player_weapon_stats_ins() trigger!'; - END IF; - RETURN NULL; -END; + IF (NEW.create_dt >= DATE '2014-01-01' AND NEW.create_dt < DATE '2014-04-01') THEN + INSERT INTO player_weapon_stats_2014Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-04-01' AND NEW.create_dt < DATE '2014-07-01') THEN + INSERT INTO player_weapon_stats_2014Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-07-01' AND NEW.create_dt < DATE '2014-10-01') THEN + INSERT INTO player_weapon_stats_2014Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-10-01' AND NEW.create_dt < DATE '2015-01-01') THEN + INSERT INTO player_weapon_stats_2014Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-01-01' AND NEW.create_dt < DATE '2015-04-01') THEN + INSERT INTO player_weapon_stats_2015Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-04-01' AND NEW.create_dt < DATE '2015-07-01') THEN + INSERT INTO player_weapon_stats_2015Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-07-01' AND NEW.create_dt < DATE '2015-10-01') THEN + INSERT INTO player_weapon_stats_2015Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-10-01' AND NEW.create_dt < DATE '2016-01-01') THEN + INSERT INTO player_weapon_stats_2015Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-01-01' AND NEW.create_dt < DATE '2016-04-01') THEN + INSERT INTO player_weapon_stats_2016Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-04-01' AND NEW.create_dt < DATE '2016-07-01') THEN + INSERT INTO player_weapon_stats_2016Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-07-01' AND NEW.create_dt < DATE '2016-10-01') THEN + INSERT INTO player_weapon_stats_2016Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-10-01' AND NEW.create_dt < DATE '2017-01-01') THEN + INSERT INTO player_weapon_stats_2016Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-01-01' AND NEW.create_dt < DATE '2017-04-01') THEN + INSERT INTO player_weapon_stats_2017Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-04-01' AND NEW.create_dt < DATE '2017-07-01') THEN + INSERT INTO player_weapon_stats_2017Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-07-01' AND NEW.create_dt < DATE '2017-10-01') THEN + INSERT INTO player_weapon_stats_2017Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-10-01' AND NEW.create_dt < DATE '2018-01-01') THEN + INSERT INTO player_weapon_stats_2017Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-01-01' AND NEW.create_dt < DATE '2018-04-01') THEN + INSERT INTO player_weapon_stats_2018Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-04-01' AND NEW.create_dt < DATE '2018-07-01') THEN + INSERT INTO player_weapon_stats_2018Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-07-01' AND NEW.create_dt < DATE '2018-10-01') THEN + INSERT INTO player_weapon_stats_2018Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-10-01' AND NEW.create_dt < DATE '2019-01-01') THEN + INSERT INTO player_weapon_stats_2018Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-01-01' AND NEW.create_dt < DATE '2019-04-01') THEN + INSERT INTO player_weapon_stats_2019Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-04-01' AND NEW.create_dt < DATE '2019-07-01') THEN + INSERT INTO player_weapon_stats_2019Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-07-01' AND NEW.create_dt < DATE '2019-10-01') THEN + INSERT INTO player_weapon_stats_2019Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-10-01' AND NEW.create_dt < DATE '2020-01-01') THEN + INSERT INTO player_weapon_stats_2019Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-01-01' AND NEW.create_dt < DATE '2020-04-01') THEN + INSERT INTO player_weapon_stats_2020Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-04-01' AND NEW.create_dt < DATE '2020-07-01') THEN + INSERT INTO player_weapon_stats_2020Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-07-01' AND NEW.create_dt < DATE '2020-10-01') THEN + INSERT INTO player_weapon_stats_2020Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-10-01' AND NEW.create_dt < DATE '2021-01-01') THEN + INSERT INTO player_weapon_stats_2020Q4 VALUES (NEW.*); + ELSE + RAISE EXCEPTION 'Date out of range. Fix the player_weapon_stats_ins() trigger!'; + END IF; + RETURN NULL; +END $$ LANGUAGE plpgsql; diff --git a/triggers/team_game_stats_ins_trg.sql b/triggers/team_game_stats_ins_trg.sql index 6a0974c..0649a39 100644 --- a/triggers/team_game_stats_ins_trg.sql +++ b/triggers/team_game_stats_ins_trg.sql @@ -1,29 +1,67 @@ CREATE OR REPLACE FUNCTION team_game_stats_ins() RETURNS TRIGGER AS $$ BEGIN - -- 2013 - IF (NEW.create_dt >= DATE '2013-04-01' AND NEW.create_dt < DATE '2013-07-01') THEN - INSERT INTO team_game_stats_2013Q2 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2013-07-01' AND NEW.create_dt < DATE '2013-10-01') THEN - INSERT INTO team_game_stats_2013Q3 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2013-10-01' AND NEW.create_dt < DATE '2014-01-01') THEN - INSERT INTO team_game_stats_2013Q4 VALUES (NEW.*); - - -- 2014 - ELSIF (NEW.create_dt >= DATE '2014-01-01' AND NEW.create_dt < DATE '2014-04-01') THEN - INSERT INTO team_game_stats_2014Q1 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-04-01' AND NEW.create_dt < DATE '2014-07-01') THEN - INSERT INTO team_game_stats_2014Q2 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-07-01' AND NEW.create_dt < DATE '2014-10-01') THEN - INSERT INTO team_game_stats_2014Q3 VALUES (NEW.*); - ELSIF (NEW.create_dt >= DATE '2014-10-01' AND NEW.create_dt < DATE '2015-01-01') THEN - INSERT INTO team_game_stats_2014Q4 VALUES (NEW.*); - - ELSE - RAISE EXCEPTION 'Date out of range. Fix the team_game_stats_ins() trigger!'; - END IF; - RETURN NULL; -END; + IF (NEW.create_dt >= DATE '2014-01-01' AND NEW.create_dt < DATE '2014-04-01') THEN + INSERT INTO team_game_stats_2014Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-04-01' AND NEW.create_dt < DATE '2014-07-01') THEN + INSERT INTO team_game_stats_2014Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-07-01' AND NEW.create_dt < DATE '2014-10-01') THEN + INSERT INTO team_game_stats_2014Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2014-10-01' AND NEW.create_dt < DATE '2015-01-01') THEN + INSERT INTO team_game_stats_2014Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-01-01' AND NEW.create_dt < DATE '2015-04-01') THEN + INSERT INTO team_game_stats_2015Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-04-01' AND NEW.create_dt < DATE '2015-07-01') THEN + INSERT INTO team_game_stats_2015Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-07-01' AND NEW.create_dt < DATE '2015-10-01') THEN + INSERT INTO team_game_stats_2015Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2015-10-01' AND NEW.create_dt < DATE '2016-01-01') THEN + INSERT INTO team_game_stats_2015Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-01-01' AND NEW.create_dt < DATE '2016-04-01') THEN + INSERT INTO team_game_stats_2016Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-04-01' AND NEW.create_dt < DATE '2016-07-01') THEN + INSERT INTO team_game_stats_2016Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-07-01' AND NEW.create_dt < DATE '2016-10-01') THEN + INSERT INTO team_game_stats_2016Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2016-10-01' AND NEW.create_dt < DATE '2017-01-01') THEN + INSERT INTO team_game_stats_2016Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-01-01' AND NEW.create_dt < DATE '2017-04-01') THEN + INSERT INTO team_game_stats_2017Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-04-01' AND NEW.create_dt < DATE '2017-07-01') THEN + INSERT INTO team_game_stats_2017Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-07-01' AND NEW.create_dt < DATE '2017-10-01') THEN + INSERT INTO team_game_stats_2017Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2017-10-01' AND NEW.create_dt < DATE '2018-01-01') THEN + INSERT INTO team_game_stats_2017Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-01-01' AND NEW.create_dt < DATE '2018-04-01') THEN + INSERT INTO team_game_stats_2018Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-04-01' AND NEW.create_dt < DATE '2018-07-01') THEN + INSERT INTO team_game_stats_2018Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-07-01' AND NEW.create_dt < DATE '2018-10-01') THEN + INSERT INTO team_game_stats_2018Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-10-01' AND NEW.create_dt < DATE '2019-01-01') THEN + INSERT INTO team_game_stats_2018Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-01-01' AND NEW.create_dt < DATE '2019-04-01') THEN + INSERT INTO team_game_stats_2019Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-04-01' AND NEW.create_dt < DATE '2019-07-01') THEN + INSERT INTO team_game_stats_2019Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-07-01' AND NEW.create_dt < DATE '2019-10-01') THEN + INSERT INTO team_game_stats_2019Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-10-01' AND NEW.create_dt < DATE '2020-01-01') THEN + INSERT INTO team_game_stats_2019Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-01-01' AND NEW.create_dt < DATE '2020-04-01') THEN + INSERT INTO team_game_stats_2020Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-04-01' AND NEW.create_dt < DATE '2020-07-01') THEN + INSERT INTO team_game_stats_2020Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-07-01' AND NEW.create_dt < DATE '2020-10-01') THEN + INSERT INTO team_game_stats_2020Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-10-01' AND NEW.create_dt < DATE '2021-01-01') THEN + INSERT INTO team_game_stats_2020Q4 VALUES (NEW.*); + ELSE + RAISE EXCEPTION 'Date out of range. Fix the team_game_stats_ins() trigger!'; + END IF; + RETURN NULL; +END $$ LANGUAGE plpgsql; -- 2.39.2