diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-02-07 20:46:16 +0100 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-02-07 20:46:16 +0100 |
| commit | f72d816f8d5bb6f363b1ae43debdcff4dafef074 (patch) | |
| tree | 3d95b058efced83297b881e648a5c816f1eaacc6 /queries | |
| parent | da71062508f53d2674e1b9ea90764b7ba650c412 (diff) | |
| download | apigrabber-f72d816f8d5bb6f363b1ae43debdcff4dafef074.tar.gz apigrabber-f72d816f8d5bb6f363b1ae43debdcff4dafef074.zip | |
remove code not related to API data fetching
Diffstat (limited to 'queries')
| -rw-r--r-- | queries/hero-winrates.sql | 18 | ||||
| -rw-r--r-- | queries/index.sql | 4 | ||||
| -rw-r--r-- | queries/recent-matches.sql | 30 |
3 files changed, 0 insertions, 52 deletions
diff --git a/queries/hero-winrates.sql b/queries/hero-winrates.sql deleted file mode 100644 index 51f40e1..0000000 --- a/queries/hero-winrates.sql +++ /dev/null @@ -1,18 +0,0 @@ -SELECT ARRAY_TO_JSON(ARRAY( -SELECT - JSONB_BUILD_OBJECT( - 'actor', actors.actor, - 'winrate', (SELECT - SUM((CASE WHEN (participant.data->'attributes'->'stats'->>'winner')='true' THEN 1 ELSE 0 END))::float - / - COUNT(*)::float - FROM participant - WHERE (participant.data->'attributes'->>'actor') = actors.actor) - ) -FROM -(SELECT - DISTINCT participant.data->'attributes'->>'actor' - AS actor - FROM participant) -AS actors) -) AS data;
\ No newline at end of file diff --git a/queries/index.sql b/queries/index.sql deleted file mode 100644 index 5af21b5..0000000 --- a/queries/index.sql +++ /dev/null @@ -1,4 +0,0 @@ -CREATE INDEX ON match((data->>'id')); -CREATE INDEX ON roster((data->>'id')); -CREATE INDEX ON participant((data->>'id')); -CREATE INDEX ON match((data->'attributes'->>'createdAt')); diff --git a/queries/recent-matches.sql b/queries/recent-matches.sql deleted file mode 100644 index 1bde01b..0000000 --- a/queries/recent-matches.sql +++ /dev/null @@ -1,30 +0,0 @@ -SELECT ARRAY_TO_JSON(ARRAY( -SELECT - JSONB_BUILD_OBJECT( - 'id', (match.data->'attributes'->>'shardId') || (match.data->>'id'), - 'date', match.data->'attributes'->>'createdAt', - 'duration', CAST( - match.data->'attributes'->>'duration' - AS INTEGER), - 'teams', ARRAY( - SELECT( - SELECT - JSONB_BUILD_OBJECT( - 'id', roster.data->>'id', - 'side', roster.data->'attributes'->'stats'->>'side', - 'kills', roster.data->'attributes'->'stats'->>'heroKills', - 'players', ARRAY( - SELECT( - SELECT - participant.data->'attributes'->>'actor' - FROM participant WHERE relparticipant->>'id' = participant.data->>'id') - FROM JSONB_ARRAY_ELEMENTS(roster.data->'relationships'->'participants'->'data') relparticipant - ) - ) - FROM roster WHERE relroster->>'id' = roster.data->>'id') - FROM JSONB_ARRAY_ELEMENTS(match.data->'relationships'->'rosters'->'data') relroster - ) -) -FROM match -ORDER BY match.data->'attributes'->>'createdAt' DESC -)) AS data; |