Best query for getting success/failure ratio from warehouse fact table
I'm trying to fine tune a query and would like some feedback. I have a job_fact
warehouse table with an unit of measure for the job's final event (final_event_type
). I'm trying to run a query on the fact that will give me a success/failure ratio. Here's what I have so far:
SELECT
CASE WHEN jf.final_event_type IN (开发者_如何学JAVA4,6,8,9) THEN count(final_event_type) END as num_failures,
CASE WHEN jf.final_event_type IN (5,7,10) THEN count(final_event_type) END as num_successes
FROM job_fact jf
GROUP BY jf.final_event_type;
This query only gives me the raw succes and failure values in a two-row result:
+----------------------+-----------------------+
| num_failures | num_successes |
+----------------------+-----------------------+
| [NULL] | 6 |
| 14 | [NULL] |
+----------------------+-----------------------+
Does anyone know if there's a way to a) get the results on one line, and b) be able to calculate the ratio between the two (e.g. the failure percentage). I'm assuming someone will tell me that I'm better off writing a procedure for this, but I'd like to avoid it if possible. I know there's an elegant way to do this, but my sql-foo is lacking today I guess.
I'm running PostgreSQL 9.0.1. Thanks for any assistance you may offer.
UPDATE
Based off of the chosen answer (from @Ronnis), here is my final query, in case you were wondering:
select
sum(case when final_event_type in(4,6,8,9) then 1 else 0 end) as failures,
sum(case when final_event_type in(5,7,10) then 1 else 0 end) as successes,
count(final_event_type) as total_events,
sum(case when final_event_type in(4,6,8,9) then 1 else 0 end) / count(final_event_type)::decimal as failure_percentage,
sum(case when final_event_type in(5,7,10) then 1 else 0 end) / count(final_event_type)::decimal as success_percentage
from job_fact;
If where final_event_type in(4,5,6,7,8,9,10)
would hit most of the table, I think the following would be pretty performant:
select sum(case when final_event_type in(4,6,8,9) then 1 else 0 end) as failures
,sum(case when final_event_type in(5,7,10) then 1 else 0 end) as successes
from job_fact;
Edit
I don't know how postgresq executes the above query. In Oracle, there is an access path called Index Fast Full scan, which basically treats the index as a table. No traversal (slow), just a full scan (efficient). The benefit is that an index on {final_event_type}
could be significantly smaller to scan than the whole table.
(I'm not mentioning bitmap indexes, because that would be faster still).
SELECT num_failures, num_successesc, (num_failures/Total), (num_successesc/Total)
FROM (
SELECT
(SELECT COUNT(*) FROM job_fact WHERE final_event_type IN (4,6,8,9)) AS "num_failures"
(SELECT COUNT(*) FROM job_fact WHERE final_event_type IN (5,7,10)) AS "num_successesc"
(SELECT COUNT(*) FROM job_fact WHERE final_event_type IN (4,6,8,9,5,7,10)) AS "Total"
) PQ
精彩评论