From 4c05d4522bb559394f99147cae56ab34b077f0d8 Mon Sep 17 00:00:00 2001 From: schneefux Date: Sun, 26 Feb 2017 17:21:00 +0100 Subject: rewrite to use joblib (fixes #20), use updated raw schema, check for duplicates --- queries/player.sql | 34 ++++++++++++++++++++++++---------- 1 file changed, 24 insertions(+), 10 deletions(-) (limited to 'queries/player.sql') 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 -- cgit v1.3.1