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'
No comments:
Post a Comment