# Is there a way to optimize this aggregate postgreSQL statement by using CASE?

I have a working SQL statement which shows the following:

• Player
• Champion
• Role
• Total Kill/Death Ratio
• Total Kill+Assist/Death Ratio
• Total Win %
• Difference between Ratio from one month ago
• Difference between KA Ratio from one month ago
• Difference between win % from one month ago

However, I don't think it's quite as optimized as it could be:

with CTERatio as (

select tt.playerid, tt.champid, tt.roleid,
sum(tt.words) as kills,
sum(tt.words2) as deaths,
sum(tt.words3) as assists,
sum(tt.words)/sum(tt.words2) as ratio,
(sum(tt.words)+sum(tt.words3))/sum(tt.words2) as kdar,
avg(tt.complete) as win
from schema.records tt
group by tt.playerid, tt.champid, tt.roleid
having count(*) > 2
order by ratio desc

),

hist as (

select tt.playerid, tt.champid, tt.roleid,
sum(tt.words) as kills,
sum(tt.words2) as deaths,
sum(tt.words3) as assists,
sum(tt.words)/sum(tt.words2) as ratio,
(sum(tt.words)+sum(tt.words3))/sum(tt.words2) as kdar,
avg(tt.complete) as win
from schema.records tt
where tt.sys_time < date_trunc('day', NOW() - interval '1 month')
group by tt.playerid, tt.champid, tt.roleid
having count(*) > 2
order by ratio desc

)
select c.playerid as player, c.champid as champion, c.roleid as rolee,
round(c.ratio, 2) as ratio, round(c.kdar, 2) as kdar, round(c.win, 2) as win,
round(c.ratio - h.ratio, 2) as ratiochange,
round(c.kdar - h.kdar, 2) as kdarchange,
round(c.win - h.win, 2) as winchange
from CTERatio c, hist h
where c.playerid = h.playerid
and c.champid = h.champid
and c.roleid = h.roleid
order by c.ratio desc


Is it possible I can incorporate using CASE instead of WITH? Such as:

avg(c.kills) - avg
(
case when tt.sys_time <  date_trunc('day', NOW() - interval '1 month')
then h.words
end
)

• I'll hazard a guess that this hasn't received attention because of how it is asked. I have a few suggestions. First, cut down the stuff you don't need in the SQL. Selecting playerid and winchange could be enough. Second, give us a minimum initial table. Third, put it all into a sqlfiddle.com and let us try it. And, finally, perhaps try stackoverflow.com, not code review. Give us a few different ways you tried to use CASE and ask us what you did wrong. Good luck. – Guy Schalnat Jul 7 '14 at 23:19

# Naming conventions

I think your aliases are pretty cryptic.

This:

from schema.records tt


That:

from CTERatio c, hist h


It's better to use aliases that mean something. tt, c, h... think of Mr. Maintainer going back over your code having to make changes to it.

I will compliment you on re-labeling column names words, words2, words3. Those are pretty bad column names and your re-labeling makes them much more meaningful.

# Indenting/Formatting

Your CTEs are beautiful in how they are formatted. Your final select statement, not so much. How about this:

select
c.playerid as player,
c.champid as champion,
c.roleid as rolee,
round(c.ratio, 2) as ratio,
round(c.kdar, 2) as kdar,
round(c.win, 2) as win,
round(c.ratio - h.ratio, 2) as ratiochange,
round(c.kdar - h.kdar, 2) as kdarchange,
round(c.win - h.win, 2) as winchange


# Performance

This aggregate is expensive, especially used twice:

having count(*) > 2


This means the SQL engine will have to scan the whole table for each column to see if the count is greater than 2. Try to find a unique column to COUNT() on rather than the whole table, like having count(c.playerid) > 2

You have many sum() and round() aggregates, can we cut down on them somehow? It's hard to give specifics since we don't know what the data looks like, but try to limit those as much as you can.

Both of your CTEs seem identical. I'm curious as to why you are selecting the same dataset twice. You could do a self join instead.

Here is how I would write this script, bearing in mind I don't know what the data looks like:

with CTERatio as (
-- calculate ratio
select
rec.playerid,
rec.champid,
rec.roleid,
sum(rec.words) as kills,
sum(rec.words2) as deaths,
sum(rec.words3) as assists,
sum(rec.words)/sum(rec.words2) as ratio,
(sum(rec.words)+sum(rec.words3))/sum(rec.words2) as kdar,
avg(rec.complete) as win
from schema.records as rec
group by rec.playerid, rec.champid, rec.roleid
having count(*) > 2
order by ratio desc

),
-- join ratio to itself to find highest ratio
select
cte_r1.playerid as player,
cte_r1.champid as champion,
cte_r1.roleid as rolee,
round(cte_r1.ratio, 2) as ratio,
round(cte_r1.kdar, 2) as kdar,
round(cte_r1.win, 2) as win,
round(cte_r1.ratio - h.ratio, 2) as ratiochange,
round(cte_r1.kdar - h.kdar, 2) as kdarchange,
round(cte_r1.win - h.win, 2) as winchange
from CTERatio as cte_r1
inner join CTERatio as cte_r2
on cte_r1.playerid = cte_r2.playerid
and cte_r1.champid = te_r2.champid
and cte_r1.roleid = cte_r2.roleid
order by cte_r1.ratio desc
;

• The second WITH is the same data, except with an added 'where tt.sys_time < date_trunc('day', NOW() - interval '1 month')' – Othya Jul 10 '14 at 11:14