From 1a7f0eb91529148b08eec58ce9cbebf514a9af25 Mon Sep 17 00:00:00 2001 From: schneefux Date: Wed, 15 Mar 2017 14:47:08 +0100 Subject: calculate kda ratio (fix vainglorygame/meta#51) --- queries/participant/stats.sql | 10 +++++++--- 1 file 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) -- cgit v1.3.1