Useful sql function

1. We have two tests with the same query set. One 17294 was fetched two weekly ago and the other one 17374 is fetched just now.

   •  Find  how many URLs in the new test for the same query are not covered in the old test:
       
   

select m.query_name_old query_name, m.url_num_in_old_test-n.url_num_in_both_old_new url_number_not_in_old from
(select b.query query_name_old,count(*) url_num_in_old_test from rel_url_score a, rel_query b
where a.test_id=17294 and b.test_id=17294 and a.query_id=b.id group by b.query) m,
(select query_name query_name_all, count (*) url_num_in_both_old_new from(
                          select query_name,count(*) from
((select b.query query_name,a.url url,a.test_id from rel_url_score a, rel_query b where a.test_id=17294 and b.test_id=17294 and a.query_id=b.id)
union all
(select d.query query_name,c.url url,c.test_id from rel_url_score c, rel_query d where c.test_id=17374 and d.test_id=17374 and c.query_id=d.id))
group by query_name,url
having count(*) >1
order by query_name ) group by query_name
) n
where m.query_name_old=n.query_name_all


• Get the total number of unique URLs:

select (select count(*) from rel_url_score where test_id = 17374) - cnt from
(
select count(*) cnt from
 (
select query, url, count(*) cnt from
(
select b.query ,a.url url,a.test_id from rel_url_score a, rel_query b where a.test_id=17294 and b.test_id=17294 and a.query_id=b.id
union all
select d.query ,c.url url,c.test_id from rel_url_score c, rel_query d where c.test_id=17374 and d.test_id=17374 and c.query_id=d.id
)
group by query,url having count(*) >1
)
)


• Get all unique URLs in test 17374:

select url from
(
  select query, url, count(*) cnt, sum(decode(test_id, 17294, 1, 0)) test1, sum(decode(test_id, 17374, 1, 0)) test2 from
  (
    select b.query ,a.url url,a.test_id from rel_url_score a, rel_query b where a.test_id=17294 and b.test_id=17294 and a.query_id=b.id
    union all
    select d.query ,c.url url,c.test_id from rel_url_score c, rel_query d where c.test_id=17374 and d.test_id=17374 and c.query_id=d.id
  )
  group by query,url having count(*) =1
)
where test2 !=0