diff options
| -rw-r--r-- | queries/match_participation.sql | 24 | ||||
| -rw-r--r-- | queries/match_results.sql | 22 | ||||
| -rw-r--r-- | queries/participant.sql | 28 |
3 files changed, 28 insertions, 46 deletions
diff --git a/queries/match_participation.sql b/queries/match_participation.sql deleted file mode 100644 index 43785c6..0000000 --- a/queries/match_participation.sql +++ /dev/null @@ -1,24 +0,0 @@ -SELECT - -relationships->'player'->'data'->>'id' AS player_id, -0 AS "match_id", -0 AS "rosterId", -0 AS "fk_player_id", -0 AS "fk_match_results_id", -attributes->>'actor' AS "hero", -COALESCE(NULLIF(attributes->'stats'->>'kills', ''), '0')::int AS "kills", -(attributes->'stats'->>'deaths')::int AS "deaths", -(attributes->'stats'->>'assists')::int AS "assists", -0.0 AS "KD", -0.0 AS "KDA", -0 AS "killParticipation", -COALESCE(NULLIF(attributes->'stats'->>'nonJungleMinionKills', ''), '0')::int AS "laneMinionsSlayed", -COALESCE(NULLIF(attributes->'stats'->>'minionKills', ''), '0')::int AS "jungleMinionsSlayed", -COALESCE(NULLIF(attributes->'stats'->>'turretCaptures', ''), '0')::int AS "turretsDestroyed", -COALESCE(NULLIF(attributes->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures", -COALESCE(NULLIF(attributes->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures", -COALESCE(NULLIF(attributes->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures", -(attributes->'stats'->>'wentAfk')::bool::int AS "wentAfk", -FALSE AS "perfectGame" - -FROM participant diff --git a/queries/match_results.sql b/queries/match_results.sql deleted file mode 100644 index c493d5c..0000000 --- a/queries/match_results.sql +++ /dev/null @@ -1,22 +0,0 @@ -SELECT - -attributes->'stats'->>'side' AS "team", -FALSE AS "winner", -FALSE AS "surrender", -0 AS "teamSize", -'' AS "hero_1", -'' AS "hero_2", -'' AS "hero_3", -0 AS "laneMinionsSlayed", -0 AS "jungleMinionsSlayed", -(attributes->'stats'->>'turretKills')::int AS "turretsDestroyed", -(attributes->'stats'->>'heroKills')::int AS "kills", -0 AS "assists", -0 AS "deaths", -(attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures", -0 AS "goldMineCaptures", -0 AS "crystalMineCaptures", -0 AS "afkCount", -0 AS "afkTime" - -FROM roster diff --git a/queries/participant.sql b/queries/participant.sql new file mode 100644 index 0000000..a84274d --- /dev/null +++ b/queries/participant.sql @@ -0,0 +1,28 @@ +SELECT + +id AS "apiId", +relationships->'player'->'data'->>'id' AS "playerId", + +attributes->>'actor' AS "hero", +(attributes->'stats'->>'assists')::int AS "assists", +COALESCE(NULLIF(attributes->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures", +(attributes->'stats'->>'deaths')::int AS "deaths", +(attributes->'stats'->>'farm')::float AS "farm", +(attributes->'stats'->>'firstAfkTime')::float AS "firstAfkTime", +COALESCE(NULLIF(attributes->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures", +COALESCE(NULLIF(attributes->'stats'->>'jungleKills', ''), '0')::int AS "jungleKills", +COALESCE(NULLIF(attributes->'stats'->>'karmaLevel', ''), '0')::int AS "karmaLevel", +COALESCE(NULLIF(attributes->'stats'->>'kills', ''), '0')::int AS "kills", +COALESCE(NULLIF(attributes->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures", +COALESCE(NULLIF(attributes->'stats'->>'level', ''), '0')::int AS "level", +COALESCE(NULLIF(attributes->'stats'->>'minionKills', ''), '0')::int AS "minionKills", +COALESCE(NULLIF(attributes->'stats'->>'skillTier', ''), '0')::int AS "skillTier", +COALESCE(NULLIF(attributes->'stats'->>'skinKey', ''), '0') AS "skinKey", +(attributes->'stats'->>'wentAfk')::bool::bool AS "wentAfk", +(attributes->'stats'->>'winner')::bool::bool AS "winner", +attributes->'stats'->'itemGrants' AS "itemGrants", +attributes->'stats'->'itemSells' AS "itemSells", +attributes->'stats'->'itemUses' AS "itemUses", +attributes->'stats'->'items' AS "items" + +FROM participant |