Re: slow count(CASE) query
От | Gerardo Herzig |
---|---|
Тема | Re: slow count(CASE) query |
Дата | |
Msg-id | 4AE9DB6C.6000203@fmed.uba.ar обсуждение исходный текст |
Ответ на | slow count(CASE) query (Grant Masan <grant.massan@gmail.com>) |
Список | pgsql-sql |
Grant Masan wrote: > Hi all, > > I have this kind of query that I need to do, yes my query is giving right > answers now but it is long and slow. I am now asking you that if > you have another solution for my query to make that more smarter ! Hope you > can help me with this ! > > > select '000100' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length <100 > GROUP BY type > ORDER BY type) as koo > > UNION ALL > > select '100200' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length between 100 and 200 > GROUP BY type > ORDER BY type) as koo > > UNION ALL > > select '200300' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length between 200 and 300 > GROUP BY type > ORDER BY type) as koo > > UNION ALL > > select '300999' as length, sum(ship1) as ship1 ,sum(ship2) as > ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM > (select > count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, > count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, > count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, > count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, > count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 > FROM school_proj_boat where length >300 > GROUP BY type > ORDER BY type) as koo > Well, it looks like this will read school_proj_boat 4 times. What about 1) A plsql function that iterates *one time* on school_proj_boat, with anested CASE, or a par of IF's 2) Could be a good place for using window functions http://www.postgresql.org/docs/current/static/tutorial-window.html HTH Gerardo
В списке pgsql-sql по дате отправления: