diff options
| author | Kapil Viren Ahuja <kvahuja@users.noreply.github.com> | 2017-02-27 19:12:59 +0530 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2017-02-27 19:12:59 +0530 |
| commit | 65f7c3cddaa7b4a495c283ccf7c9b415bb365f4b (patch) | |
| tree | b5c8df31ad2fce3fedcfc564297496d191d3b11f /queries/participant.sql | |
| parent | 2309c313b648e06b3c441ab8aafd845939dedc48 (diff) | |
| parent | 3575da2783ba62ddf3784ae814c8fec862c0eb1a (diff) | |
| download | processor-65f7c3cddaa7b4a495c283ccf7c9b415bb365f4b.tar.gz processor-65f7c3cddaa7b4a495c283ccf7c9b415bb365f4b.zip | |
Merge pull request #22 from vainglorygame/rewrite
rewrite to use joblib (fixes #20), use updated raw schema, check for …
Diffstat (limited to 'queries/participant.sql')
| -rw-r--r-- | queries/participant.sql | 54 |
1 files changed, 30 insertions, 24 deletions
diff --git a/queries/participant.sql b/queries/participant.sql index b7685a9..e11179c 100644 --- a/queries/participant.sql +++ b/queries/participant.sql @@ -1,28 +1,34 @@ +WITH rosters AS (SELECT + JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster +FROM match WHERE id=$1), +participants AS (SELECT + JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant +FROM rosters) SELECT -id AS "apiId", -relationships->'player'->'data'->>'id' AS "player_apiId", +participant->'data'->>'id' AS "apiId", +participant->'relations'->0->'data'->>'id' AS "player_apiId", -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" +participant->'data'->'attributes'->>'actor' AS "hero", +(participant->'data'->'attributes'->'stats'->>'assists')::int AS "assists", +COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures", +(participant->'data'->'attributes'->'stats'->>'deaths')::int AS "deaths", +(participant->'data'->'attributes'->'stats'->>'farm')::float AS "farm", +(participant->'data'->'attributes'->'stats'->>'firstAfkTime')::float AS "firstAfkTime", +COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures", +COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'jungleKills', ''), '0')::int AS "jungleKills", +COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'karmaLevel', ''), '0')::int AS "karmaLevel", +COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'kills', ''), '0')::int AS "kills", +COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures", +COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'level', ''), '0')::int AS "level", +COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'minionKills', ''), '0')::int AS "minionKills", +COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'skillTier', ''), '0')::int AS "skillTier", +COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'skinKey', ''), '0') AS "skinKey", +(participant->'data'->'attributes'->'stats'->>'wentAfk')::bool AS "wentAfk", +(participant->'data'->'attributes'->'stats'->>'winner')::bool AS "winner", +participant->'data'->'attributes'->'stats'->'itemGrants' AS "itemGrants", +participant->'data'->'attributes'->'stats'->'itemSells' AS "itemSells", +participant->'data'->'attributes'->'stats'->'itemUses' AS "itemUses", +participant->'data'->'attributes'->'stats'->'items' AS "items" -FROM participant +FROM participants |