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/player.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/player.sql')
| -rw-r--r-- | queries/player.sql | 34 |
1 files changed, 24 insertions, 10 deletions
diff --git a/queries/player.sql b/queries/player.sql index 992cb44..0619ee6 100644 --- a/queries/player.sql +++ b/queries/player.sql @@ -1,15 +1,29 @@ +WITH rosters AS (SELECT + match.data->'data'->'attributes'->>'createdAt' AS matchdate, + JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster +FROM match WHERE id=$1), +participants AS (SELECT + matchdate, + JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant +FROM rosters), +players AS (SELECT + matchdate, + JSONB_ARRAY_ELEMENTS(participants.participant->'relations') AS player +FROM participants) + SELECT -id AS "apiId", -attributes->>'name' AS "name", -(attributes->'stats'->>'level')::int AS "level", -(attributes->'stats'->>'xp')::int AS "xp", -(attributes->'stats'->>'played')::int AS "played", -(attributes->'stats'->>'played_ranked')::int AS "playedRanked", -(attributes->'stats'->>'played')::int - (attributes->'stats'->>'played_ranked')::int AS "playedCasual", -(attributes->'stats'->>'wins')::int AS "wins", -(attributes->'stats'->>'lifetimeGold')::float AS "lifetimeGold", +players.player->'data'->>'id' AS "apiId", +players.player->'data'->'attributes'->>'name' AS "name", +(players.player->'data'->'attributes'->'stats'->>'level')::int AS "level", +(players.player->'data'->'attributes'->'stats'->>'xp')::int AS "xp", +(players.player->'data'->'attributes'->'stats'->>'played')::int AS "played", +(players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "playedRanked", +(players.player->'data'->'attributes'->'stats'->>'played')::int - (players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "playedCasual", +(players.player->'data'->'attributes'->'stats'->>'wins')::int AS "wins", +(players.player->'data'->'attributes'->'stats'->>'lifetimeGold')::float AS "lifetimeGold", +CASE WHEN players.player->'data'->'attributes'->>'name'=$2 THEN matchdate ELSE 'epoch'::timestamp::text END AS "lastMatchCreatedDate", 0 AS "streak" -FROM "player" +FROM players |