diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-03-15 14:47:08 +0100 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-03-15 14:47:08 +0100 |
| commit | 1a7f0eb91529148b08eec58ce9cbebf514a9af25 (patch) | |
| tree | b015cf6c0bbc01078b0e31470c9e15b4289c4636 | |
| parent | d18b4c4a5f7f8a2d1505bd03c91a3c2c989dadad (diff) | |
| download | compiler-1a7f0eb91529148b08eec58ce9cbebf514a9af25.tar.gz compiler-1a7f0eb91529148b08eec58ce9cbebf514a9af25.zip | |
calculate kda ratio (fix vainglorygame/meta#51)
| -rw-r--r-- | queries/participant/stats.sql | 10 |
1 files changed, 7 insertions, 3 deletions
diff --git a/queries/participant/stats.sql b/queries/participant/stats.sql index 6a6d540..fe4025f 100644 --- a/queries/participant/stats.sql +++ b/queries/participant/stats.sql @@ -5,13 +5,17 @@ WITH stats AS ( CASE WHEN roster.hero_kills=0 THEN 0 ELSE (kills+assists)/roster.hero_kills::float - END AS kills_participation + END AS kills_participation, + CASE WHEN participant.deaths=0 + THEN 0 + ELSE (kills+assists)/deaths::float + END AS kda FROM participant JOIN roster ON roster.api_id=participant.roster_api_id JOIN match ON match.api_id=roster.match_api_id WHERE participant.api_id = $1 ) -INSERT INTO participant_stats(participant_api_id, patch_version, kills_participation) +INSERT INTO participant_stats(participant_api_id, patch_version, kills_participation, kda) SELECT * FROM stats ON CONFLICT(participant_api_id) DO -UPDATE SET(participant_api_id, patch_version, kills_participation) = (SELECT * FROM stats) +UPDATE SET(participant_api_id, patch_version, kills_participation, kda) = (SELECT * FROM stats) |