Re: SQL Count Magic Required.... Second Iteration...
От | |
---|---|
Тема | Re: SQL Count Magic Required.... Second Iteration... |
Дата | |
Msg-id | 20060601183722.7326.qmail@web33303.mail.mud.yahoo.com обсуждение исходный текст |
Список | pgsql-novice |
> > On 5/31/06 7:32 PM, > "operationsengineer1@yahoo.com" > > <operationsengineer1@yahoo.com> wrote: > > > > > 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. > > for those following on (probably just yours truly > ;-), > the first iteration (tested and works): > > SELECT a.total, b.passed, > b.passed/(a.total::numeric) > as yield > FROM (SELECT count(inspect_result_pass) as total > FROM t_inspect_result) > AS a, > (SELECT count(inspect_result_pass) as passed > FROM t_inspect_result > WHERE inspect_result_pass = 't') > AS b > > this generates results based on the whole table (not > limited to first entry and not limited by unique > product/serial combo. > > in my test case, i have 5 passes and 9 total and 5/9 > is displayed as > > 0.55555555555555... the second iteration is working, too. it counts the passes and total by serial_number_id (i will eventually use the product id and the serial number to determine the serial_number_id, but i've excluded that "noise" to keep this simpler). again, it works on the whole table, not just those passes and fails that were the first inspection for a given inspection (remember, this is first pass yield, not second, third or fourth pass yield). eg, inspect #1: *pass* inspect #2: *fail*, fail, fail, fail, fail, pass inspect #3: *fail*, pass first pass yield = 1 / 3 = 33.3%. in my previous query, i resolved this by doing: SELECT DISTINCT ON (t_inspect_result.inspect_id)... ... ORDER BY t_inspect_result.inspect_id DESC, t_inspect_result.inspect_result_timestamp ASC i have no clue how to implement something similar given the different SQL structure below... SELECT a.passed, b.total, a.passed/(b.total::numeric) as yield FROM (SELECT count(inspect_result_pass) as passed FROM t_inspect_result, t_inspect, t_inspect_area, t_serial_number, t_link_contract_number_job_number, t_job_number, t_product WHERE inspect_result_pass = 't' AND t_inspect.inspect_id = t_inspect_result.inspect_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_area_id = t_inspect_area.inspect_area_id AND t_inspect.serial_number_id = '200' ) AS a, (SELECT count(inspect_result_pass) as total 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_id = t_inspect_result.inspect_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_area_id = t_inspect_area.inspect_area_id AND t_inspect.serial_number_id = '200' ) AS b __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
В списке pgsql-novice по дате отправления: