From: | <operationsengineer1(at)yahoo(dot)com> |
---|---|
To: | Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: SQL Count Magic Required.... |
Date: | 2006-06-01 15:47:53 |
Message-ID: | 20060601154753.72513.qmail@web33301.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
> > i have the following query that yields a series of
> > true or false results:
> >
> > -- distinct on is pgsql extension --
> > SELECT DISTINCT ON (t_inspect_result.inspect_id)
> > t_inspect_result.inspect_result_id,
> >
> > t_inspect_result.inspect_result_pass,
> > t_inspect_area.inspect_area,
> >
> > t_inspect_result.inspect_result_timestamp
> > --,t_inspect.serial_number_id,
> > t_inspect.inspect_id
> > FROM t_inspect_result, t_inspect, t_inspect_area,
> > t_serial_number,
> > t_link_contract_number_job_number, t_job_number,
> > t_product
> > WHERE t_inspect.inspect_area_id =
> > t_inspect_area.inspect_area_id
> > AND t_inspect.serial_number_id =
> > t_serial_number.serial_number_id
> > AND
> t_serial_number.link_contract_number_job_number_id
> > =
> >
> >
>
t_link_contract_number_job_number.link_contract_number_job_number_id
> > AND
> t_link_contract_number_job_number.job_number_id =
> > t_job_number.job_number_id
> > AND t_product.product_id =
> > t_job_number.product_id
> > AND t_inspect.inspect_id =
> > t_inspect_result.inspect_id
> > AND t_inspect.serial_number_id = '200'
> > ORDER BY t_inspect_result.inspect_id DESC,
> > t_inspect_result.inspect_result_timestamp ASC
> > -- used to get first pass yield pass / fail (true
> /
> > false) data.
> > -- inspect_id desc impacts end result. time desc
> > impacts the groups prior to being distinctly
> listed
> >
> > the simplified output may look like
> >
> > f,t,t,f,f,f,t,f,t,f
> >
> > the COUNT magic comes into play b/c i want to
> count
> > the result set's "t"s and total, but i have no
> clue
> > how to get this done.
> >
> > #ts: 4
> > #total: 10
> >
> > when i have this data, i can apply some math and
> come
> > up with a 40% yield.
>
> You can do a query like (untested, and needs to be
> translated into your
> monster query):
>
> select
> a.id,a.total,b.failed,(a.total::numeric)/b.total as
> yield
> from (select count(test_result) as total from
> table) as a,
> (select count(test_result) as failed from
> table where
> test_result='f') as b where a.id = b.id;
>
> The point is to do the queries separately as
> subqueries and join them on
> some primary key so that you get the count "total"
> and the count "failed".
> Then you can do the math as above. Note that you
> have to cast at least one
> of the integers to numeric if you want a numeric
> result.
>
> Sean
Sean, t_test_result can get quite large quite quickly.
iow, this query could be running on a million or more
rows in a year or two.
is this a performance killer query?
the reason i ask is i could add a column to t_inspect
called first_pass and store the pass/fail data there
and then just complete a much simpler query counting
the t_inspect.first_pass true values and then the
getting the total.
i'd imagine that would be much faster and much simpler
to conceptualize. the downside is that i would have
repeating information.
tia...
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2006-06-01 16:03:00 | Re: SQL Count Magic Required.... |
Previous Message | Christian Hofmann | 2006-06-01 15:40:26 | Re: Postgresql.conf - which param for in memory keys and tables? |