diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-03-05 16:33:31 +0100 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-03-05 16:33:31 +0100 |
| commit | bf9743589beb4d91263e1dfaf9c2b74288d7fbd2 (patch) | |
| tree | ed31dc892c1a3a2d2498e509ac26d5f59a9b957c | |
| parent | 66df676e26b9a1e565891384441320d66b589cdf (diff) | |
| download | shrinker-bf9743589beb4d91263e1dfaf9c2b74288d7fbd2.tar.gz shrinker-bf9743589beb4d91263e1dfaf9c2b74288d7fbd2.zip | |
enable shardid and fix timestamp
| -rw-r--r-- | queries/player.sql | 8 |
1 files changed, 6 insertions, 2 deletions
diff --git a/queries/player.sql b/queries/player.sql index 585858d..412bf40 100644 --- a/queries/player.sql +++ b/queries/player.sql @@ -1,12 +1,15 @@ WITH rosters AS (SELECT - match.data->'data'->'attributes'->>'created_at' AS matchdate, + match.data->'data'->'attributes'->>'shardId' AS shardid, + (match.data->'data'->'attributes'->>'createdAt')::timestamp AS matchdate, JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster FROM match WHERE id=$1), participants AS (SELECT + shardid, matchdate, JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant FROM rosters), players AS (SELECT + shardid, matchdate, JSONB_ARRAY_ELEMENTS(participants.participant->'relations') AS player FROM participants) @@ -14,6 +17,7 @@ FROM participants) SELECT players.player->'data'->>'id' AS "api_id", +shardid AS "shard_id", 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", @@ -22,7 +26,7 @@ players.player->'data'->'attributes'->>'name' AS "name", (players.player->'data'->'attributes'->'stats'->>'played')::int - (players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "played_casual", (players.player->'data'->'attributes'->'stats'->>'wins')::int AS "wins", (players.player->'data'->'attributes'->'stats'->>'lifetimeGold')::float AS "lifetime_gold", -CASE WHEN players.player->'data'->'attributes'->>'name'=$2 THEN matchdate ELSE 'epoch'::timestamp::text END AS "last_match_created_date", +CASE WHEN players.player->'data'->'attributes'->>'name'=$2 THEN matchdate ELSE 'epoch'::timestamp END AS "last_match_created_date", 0 AS "streak" |