BinBase - generate histograms

sql statements to generate histograms, to learn more about your data

similarity


select count("range"),"range" from
(
select
case
when match > 500 and match <= 550 then '501 - 550' when match > 550 and match <= 600 then '550 - 600' when match > 600 and match <= 650 then '600 - 650' when match > 650 and match <= 700 then '650 - 700' when match > 700 and match <= 750 then '700 - 750' when match > 750 and match <= 800 then '750 - 800' when match > 800 and match <= 850 then '800 - 850' when match > 850 and match <= 900 then '850 - 900' when match > 900 and match <= 950 then '900 - 950' when match > 950 and match <= 1000 then '950 - 1000' end AS "range" from spectra a, bin b where a.bin_id = b.bin_id and b.export = 'FALSE' ) a group by "range"

purity


select count("range"),"range" from
(
select
case
when a.purity > 0 and a.purity <= 0.1 then '0 - 0.1' when a.purity > 0.1 and a.purity <= 0.5 then '0.1 - 0.5' when a.purity > 0.5 and a.purity <= 0.9 then '0.5 - 0.9' when a.purity > 0.9 and a.purity <= 1.3 then '0.9 - 1.3' when a.purity > 1.3 and a.purity <= 1.7 then '1.3 - 1.7' when a.purity > 1.7 and a.purity <= 2.1 then '1.7 - 2.1' when a.purity > 2.1 and a.purity <= 2.5 then '2.1 - 2.5' when a.purity > 2.5 then '> 2.5'
end
AS "range" from spectra a, bin b where a.bin_id = b.bin_id and b.export = 'TRUE'
)
a group by "range"


signal noise




BinBase - simulate the sifter algorithm directly on the database

currently I'm generating statistics for the BinBase system and so needed to simulate the annotation algorithm in sql.

It was rather simple....



-- 0.05 settings
select
count(*) as "count",
lower('0 < 0.05') as purity, lower('> 500') as signalNoise,
lower('> 800') as similarity

from spectra where purity > 0 and purity < 0.05 and signal_noise > 500 and match > 800

UNION

select
count(*) as "count",
lower('0 < 0.05') as purity, lower('> 50 && < 500') as signalNoise, lower('> 700') as similarity

from spectra where purity > 0 and purity < 0.05 and signal_noise > 50 and signal_noise <= 500 and match > 700


UNION

select
count(*) as "count",
lower('0 < 0.05') as purity, lower('> 5 && < 50') as signalNoise, lower('> 600') as similarity

from spectra where purity > 0 and purity < 0.05 and signal_noise > 5 and signal_noise <= 50 and match > 600

-- 0.3 settings

UNION

select
count(*) as "count",
lower('0 < 0.3') as purity, lower('> 500') as signalNoise,
lower('> 700') as similarity

from spectra where purity > 0 and purity < 0.3 and signal_noise > 500 and match > 700

UNION

select
count(*) as "count",
lower('0 < 0.3') as purity, lower('> 50 && < 500') as signalNoise, lower('> 600') as similarity

from spectra where purity > 0 and purity < 0.3 and signal_noise > 50 and signal_noise <= 500 and match > 600

UNION

select
count(*) as "count",
lower('0 < 0.3') as purity, lower('> 5 && < 50') as signalNoise, lower('> 500') as similarity

from spectra where purity > 0 and purity < 0.03 and signal_noise > 5 and signal_noise <= 50 and match > 500

-- all ither settings

UNION

select
count(*) as "count",
lower('> 0.3') as purity,
lower('> 500') as signalNoise,
lower('> 600') as similarity

from spectra where purity > 0.3 and signal_noise > 500 and match > 600

UNION

select
count(*) as "count",
lower('> 0.3') as purity,
lower('> 50 && < 500') as signalNoise, lower('> 500') as similarity

from spectra where purity > 0.3 and signal_noise > 50 and signal_noise <= 500 and match > 500

UNION

select
count(*) as "count",
lower('> 0.3') as purity,
lower('> 5 && < 50') as signalNoise, lower('> 450') as similarity

from spectra where purity > 0.03 and signal_noise > 5 and signal_noise <= 50 and match > 450

-- total --

union

select
count(*) as "count",
lower('no filter') as purity,
lower('no filter') as signalNoise,
lower('no filter') as similarity

from spectra



leco 2.22 settings


-- 0.1 settings
select
count(*) as "count",
lower('0 < 0.1') as purity, lower('> 250') as signalNoise,
lower('> 800') as similarity

from spectra where purity > 0 and purity < 0.1 and signal_noise > 250 and match > 800

UNION

select
count(*) as "count",
lower('0 < 0.1') as purity, lower('> 25 && < 250') as signalNoise, lower('> 700') as similarity

from spectra where purity > 0 and purity < 0.1 and signal_noise > 25 and signal_noise <= 250 and match > 700


UNION

select
count(*) as "count",
lower('0 < 0.1') as purity, lower('>35 && < 25') as signalNoise, lower('> 600') as similarity

from spectra where purity > 0 and purity < 0.1 and signal_noise > 3 and signal_noise <= 25 and match > 600

-- 1.5 settings

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 250') as signalNoise,
lower('> 700') as similarity

from spectra where purity > 0 and purity < 1.5 and signal_noise > 250 and match > 700

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 25 && < 250') as signalNoise, lower('> 600') as similarity

from spectra where purity > 0 and purity < 1.5 and signal_noise > 25 and signal_noise <= 250 and match > 600

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 3 && < 25') as signalNoise, lower('> 500') as similarity

from spectra where purity > 0 and purity < 1.5 and signal_noise > 3 and signal_noise <= 25 and match > 500

-- all other settings

UNION

select
count(*) as "count",
lower('> 1.5') as purity,
lower('> 250') as signalNoise,
lower('> 600') as similarity

from spectra where purity > 1.5 and signal_noise > 250 and match > 600

UNION

select
count(*) as "count",
lower('> 1.5') as purity,
lower('> 3 && < 250') as signalNoise, lower('> 500') as similarity

from spectra where purity > 1.5 and signal_noise > 3 and signal_noise <= 250 and match > 500

UNION

-- total --

select
count(*) as "count",
lower('no filter') as purity,
lower('no filter') as signalNoise,
lower('no filter') as similarity

from spectra


and as query to filter polysiloxanes out of the result for leco 2.22




-- 0.1 settings
select
count(*) as "count",
lower('0 < 0.1') as purity, lower('> 250') as signalNoise,
lower('> 800') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 0.1 and a.signal_noise > 250 and match > 800 and a.bin_id = b.bin_id and b.export = 'TRUE'

UNION

select
count(*) as "count",
lower('0 < 0.1') as purity, lower('> 25 && < 250') as signalNoise, lower('> 700') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 0.1 and a.signal_noise > 25 and a.signal_noise <= 250 and match > 700 and a.bin_id = b.bin_id and b.export = 'TRUE'


UNION

select
count(*) as "count",
lower('0 < 0.1') as purity, lower('>35 && < 25') as signalNoise, lower('> 600') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 0.1 and a.signal_noise > 3 and a.signal_noise <= 25 and match > 600 and a.bin_id = b.bin_id and b.export = 'TRUE'

-- 1.5 settings

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 250') as signalNoise,
lower('> 700') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 1.5 and a.signal_noise > 250 and match > 700 and a.bin_id = b.bin_id and b.export = 'TRUE'

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 25 && < 250') as signalNoise, lower('> 600') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 1.5 and a.signal_noise > 25 and a.signal_noise <= 250 and match > 600 and a.bin_id = b.bin_id and b.export = 'TRUE'

UNION

select
count(*) as "count",
lower('0 < 1.5') as purity, lower('> 3 && < 25') as signalNoise, lower('> 500') as similarity

from spectra a, bin b where a.purity > 0 and a.purity < 1.5 and a.signal_noise > 3 and a.signal_noise <= 25 and match > 500 and a.bin_id = b.bin_id and b.export = 'TRUE'

-- all other settings

UNION

select
count(*) as "count",
lower('> 1.5') as purity,
lower('> 250') as signalNoise,
lower('> 600') as similarity

from spectra a, bin b where a.purity > 1.5 and a.signal_noise > 250 and match > 600 and a.bin_id = b.bin_id and b.export = 'TRUE'

UNION

select
count(*) as "count",
lower('> 1.5') as purity,
lower('> 3 && < 250') as signalNoise, lower('> 500') as similarity

from spectra a, bin b where a.purity > 1.5 and a.signal_noise > 3 and a.signal_noise <= 250 and match > 500 and a.bin_id = b.bin_id and b.export = 'TRUE'

UNION

-- total --

select
count(*) as "count",
lower('no filter') as purity,
lower('no filter') as signalNoise,
lower('no filter') as similarity

from spectra a, bin b where a.bin_id = b.bin_id and b.export = 'TRUE'