diff options
Diffstat (limited to 'queries')
| -rw-r--r-- | queries/match.sql | 15 | ||||
| -rw-r--r-- | queries/participant.sql | 40 | ||||
| -rw-r--r-- | queries/player.sql | 34 | ||||
| -rw-r--r-- | queries/roster.sql | 20 |
4 files changed, 0 insertions, 109 deletions
diff --git a/queries/match.sql b/queries/match.sql deleted file mode 100644 index fc8ab7e..0000000 --- a/queries/match.sql +++ /dev/null @@ -1,15 +0,0 @@ -SELECT - -match.id AS "api_id", -(match.data->'data'->'attributes'->>'createdAt')::timestamp as "created_at", -(match.data->'data'->'attributes'->>'duration')::int AS "duration", -match.data->'data'->'attributes'->>'gameMode' AS "game_mode", -COALESCE(NULLIF(match.data->'data'->'attributes'->>'patch_version', ''), '2.2') AS "patch_version", -match.data->'data'->'attributes'->>'shardId' AS "shard_id", -match.data->'data'->'attributes'->'stats'->>'endGameReason' AS "end_game_reason", -match.data->'data'->'attributes'->'stats'->>'queue' AS "queue", - -match.data->'relations'->0->'data'->>'id' as "roster_1", -match.data->'relations'->1->'data'->>'id' as "roster_2" - -FROM match WHERE id=$1 diff --git a/queries/participant.sql b/queries/participant.sql deleted file mode 100644 index 8e86e3d..0000000 --- a/queries/participant.sql +++ /dev/null @@ -1,40 +0,0 @@ -WITH rosters AS (SELECT - (match.data->'data'->'attributes'->>'createdAt')::timestamp AS matchdate, - JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster -FROM match WHERE id=$1), -participants AS (SELECT - matchdate, - rosters.roster->'data'->>'id' AS rosterid, - JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant -FROM rosters) -SELECT - -participant->'data'->>'id' AS "api_id", -rosterid AS "roster_api_id", -participant->'relations'->0->'data'->>'id' AS "player_api_id", -matchdate AS "created_at", - -participant->'data'->'attributes'->>'actor' AS "hero", -(participant->'data'->'attributes'->'stats'->>'assists')::int AS "assists", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystal_mine_captures", -(participant->'data'->'attributes'->'stats'->>'deaths')::int AS "deaths", -(participant->'data'->'attributes'->'stats'->>'farm')::float AS "farm", -(participant->'data'->'attributes'->'stats'->>'firstAfkTime')::float AS "first_afk_time", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'goldMineCaptures', ''), '0')::int AS "gold_mine_captures", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'jungleKills', ''), '0')::int AS "jungle_kills", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'karmaLevel', ''), '0')::int AS "karma_level", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'kills', ''), '0')::int AS "kills", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'krakenCaptures', ''), '0')::int AS "kraken_captures", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'level', ''), '0')::int AS "level", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'minionKills', ''), '0')::int AS "minion_kills", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'skillTier', ''), '0')::int AS "skill_tier", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'skinKey', ''), '0') AS "skin_key", -COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'turretCaptures', ''), '0')::int AS "turret_kills", -(participant->'data'->'attributes'->'stats'->>'wentAfk')::bool AS "went_afk", -(participant->'data'->'attributes'->'stats'->>'winner')::bool AS "winner", -participant->'data'->'attributes'->'stats'->'itemGrants' AS "item_grants", -participant->'data'->'attributes'->'stats'->'itemSells' AS "item_sells", -participant->'data'->'attributes'->'stats'->'itemUses' AS "item_uses", -participant->'data'->'attributes'->'stats'->'items' AS "items" - -FROM participants diff --git a/queries/player.sql b/queries/player.sql deleted file mode 100644 index d718391..0000000 --- a/queries/player.sql +++ /dev/null @@ -1,34 +0,0 @@ -WITH rosters AS (SELECT - match.data->'data'->'attributes'->>'shardId' AS shardid, - (match.data->'data'->'attributes'->>'createdAt')::timestamp AS matchdate, - JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster -FROM match WHERE id=$1), -participants AS (SELECT - shardid, - matchdate, - JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant -FROM rosters), -players AS (SELECT - participants.participant->'data'->'attributes'->'stats'->>'skillTier' AS skill_tier, - shardid, - matchdate, - JSONB_ARRAY_ELEMENTS(participants.participant->'relations') AS player -FROM participants) - -SELECT - -players.player->'data'->>'id' AS "api_id", -shardid AS "shard_id", -players.player->'data'->'attributes'->>'name' AS "name", -(players.player->'data'->'attributes'->'stats'->>'level')::int AS "level", -(players.player->'data'->'attributes'->'stats'->>'xp')::float::int AS "xp", -(players.player->'data'->'attributes'->'stats'->>'played')::int AS "played", -(players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "played_ranked", -(players.player->'data'->'attributes'->'stats'->>'played')::int - (players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "played_casual", -(players.player->'data'->'attributes'->'stats'->>'wins')::int AS "wins", -(players.player->'data'->'attributes'->'stats'->>'lifetimeGold')::float AS "lifetime_gold", -matchdate AS "last_match_created_date", -skill_tier AS "skill_tier", -0 AS "streak" - -FROM players diff --git a/queries/roster.sql b/queries/roster.sql deleted file mode 100644 index f6500fb..0000000 --- a/queries/roster.sql +++ /dev/null @@ -1,20 +0,0 @@ -WITH rosters AS (SELECT - id AS matchid, - JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster -FROM match WHERE id=$1) -SELECT -roster->'data'->>'id' AS "api_id", -matchid AS "match_api_id", -(roster->'data'->'attributes'->'stats'->>'acesEarned')::int AS "aces_earned", -(roster->'data'->'attributes'->'stats'->>'gold')::int AS "gold", -(roster->'data'->'attributes'->'stats'->>'heroKills')::int AS "hero_kills", -(roster->'data'->'attributes'->'stats'->>'krakenCaptures')::int AS "kraken_captures", -roster->'data'->'attributes'->'stats'->>'side' AS "side", -roster->'data'->'attributes'->'stats'->>'side' AS "team_color", -(roster->'data'->'attributes'->'stats'->>'turretKills')::int AS "turret_kills", -(roster->'data'->'attributes'->'stats'->>'turretsRemaining')::int AS "turrets_remaining", - -roster->'relations'->0->'data'->>'id' AS "participant_1", -roster->'relations'->1->'data'->>'id' AS "participant_2", -roster->'relations'->2->'data'->>'id' AS "participant_3" -FROM rosters |