diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-02-26 17:21:00 +0100 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-02-26 17:21:00 +0100 |
| commit | 4c05d4522bb559394f99147cae56ab34b077f0d8 (patch) | |
| tree | b5c8df31ad2fce3fedcfc564297496d191d3b11f /queries/player.sql | |
| parent | 9ae212a23754035be50e9125a7966544e2d525fb (diff) | |
| download | processor-4c05d4522bb559394f99147cae56ab34b077f0d8.tar.gz processor-4c05d4522bb559394f99147cae56ab34b077f0d8.zip | |
rewrite to use joblib (fixes #20), use updated raw schema, check for duplicates
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 |