BinBase queries

short overview of binbase queries to generate statistics



count of spectra

select count(*) from spectra a, samples b where a.sample_id = b.sample_id and visible = 'TRUE'

count of spectra with no annotations

select count(*) from spectra a, samples b where a.sample_id = b.sample_id and visible = 'TRUE' and bin_id is null

count of samples

select count(*) from samples where visibile = 'TRUE'

known species

select distinct value from sample_info a, meta_key b where a.key_id = b.key_id and b.key = 'Species'

count of species

select count(distinct value) from sample_info a, meta_key b where a.key_id = b.key_id and b.key = 'Species'

sample count for species

select count(a.sample_id),a.value from sample_info a, meta_key b,samples c where a.key_id = b.key_id and b.key = 'Species' and a.sample_id = c.sample_id and c.visible = 'TRUE' group by a.value

bin generation by species

select a.value,count(d.spectra_id) from sample_info a, meta_key b,samples c,bin d where a.key_id = b.key_id and b.key = 'Species' and a.sample_id = c.sample_id and d.sample_id = c.sample_id group by a.value

ri correction successful

select count(*) from samples where visible = 'TRUE' and correction_failed = 'FALSE'

ri correction successful

select count(*) from samples where visible = 'TRUE' and correction_failed = 'FALSE'

named bins

select count(bin_id) from bin where name = TO_CHAR(bin_id,'FM9999999999')

distribution of similarities


select count("range"),"range" from (
select
case
when match >= 0 and match <= 100 then '0 - 100' when match > 100 and match <= 200 then '101 - 200' when match > 200 and match <= 300 then '201 - 300' when match > 300 and match <= 400 then '301 - 400' when match > 400 and match <= 500 then '401 - 500' when match > 500 and match <= 600 then '501 - 600' when match > 600 and match <= 700 then '601 - 700' when match > 700 and match <= 800 then '701 - 800' when match > 800 and match <= 900 then '801 - 900' when match > 900 and match <= 1000 then '901 - 1000' end AS "range" from spectra ) a group by "range"