SQL-SRA-QRY-anno-distribution.txt

create table study_exp_count as
select
   stu.study_accession as stu_id, count(*) as total from study as stu
join
  experiment as exp
on exp.study_accession = stu.study_accession
group by stu_id;


create table study_anno_distrib as
select
   stu.study_accession id, exp_count.total as total, count(library_construction_protocol) as c from study as stu
join
  experiment as exp
on exp.study_accession = stu.study_accession
join
  study_exp_count as exp_count
on exp_count.stu_id = stu.study_accession
where
(
   library_construction_protocol like "% shear%"
or library_construction_protocol like "% restriction%"
or library_construction_protocol like "% digest%"
or library_construction_protocol like "% fragment%"
or library_construction_protocol like "% breaks %"
or library_construction_protocol like "% nebulis%"
or library_construction_protocol like "% nebuliz%"
or library_construction_protocol like "% acoustic%"
or library_construction_protocol like "% sonic%"
) and
(
   library_construction_protocol like "% adapter%"
or library_construction_protocol like "% ligat%"
or library_construction_protocol like "% blunt%"
or library_construction_protocol like "% phosphorylat%"
or library_construction_protocol like "% overhang"
or library_construction_protocol like "% t4-PNK%"
or library_construction_protocol like "% t4%"
or library_construction_protocol like "% pnk%"
or library_construction_protocol like "% kinase%"
or library_construction_protocol like "% a-tail%"
) and
(
   library_construction_protocol like "% clone%"
or library_construction_protocol like "% clonin%"
or library_construction_protocol like "% pcr %"
or library_construction_protocol like "% amplif%"
or library_construction_protocol like "% polymerase%"
or library_construction_protocol like "% taq%"
or library_construction_protocol like "% phusion%"
or library_construction_protocol like "% temperat%"
or library_construction_protocol like "% thermal%"
or library_construction_protocol like "% anneal%"
or library_construction_protocol like "% denature%"
)
group by id
order by total, c;




-- Table of experiment record count for each study and frequency of this experiment record count

create table study_exp_distrib as
select total, count(*) as freq from study_exp_count group by total;

-- First we need a look-up table containing
-- All studies and corresponding total experiment record counts for that study


-- Next we create an intermediate results table containing the annotation type
-- the study and the proportion of experiment records annotated for the study
-- which is then grouped by the number of studies that have experiment records
-- that are annotated to that % level

-- NB: KLUDGE - Do this as two separate queries as SQLite doesn't like the single
-- unioned query it breaks for some reason even though the queries are the same
-- and have matching column results.
--
-- First execute one unioned query for frag, ligation, enrichment
-- insert the results into new table (anno_distrib), then perform another query
-- for all annotations and insert the results into anno_distrib.

create table anno_distrib as
select 'fragmentation' as anno, *, count(score) as score_c from
(
select id, total, c, cast (c as real)/cast (total as real) * 100 as score from
(
select
   stu.study_accession id, exp_count.total as total, count(library_construction_protocol) as c from study as stu
join
  experiment as exp
on exp.study_accession = stu.study_accession
join
  study_exp_count as exp_count
on exp_count.stu_id = stu.study_accession
where
   library_construction_protocol like "% shear%"
or library_construction_protocol like "% restriction%"
or library_construction_protocol like "% digest%"
or library_construction_protocol like "% fragment%"
or library_construction_protocol like "% breaks %"
or library_construction_protocol like "% nebulis%" or library_construction_protocol like "% nebuliz%"
or library_construction_protocol like "% acoustic%"
or library_construction_protocol like "% sonic%"
group by id
) 
) group by score
union all
select 'ligation' as anno, *, count(score) as score_c from
(
select id, total, c, cast (c as real)/cast (total as real) * 100 as score from
(
select
   stu.study_accession id, exp_count.total as total, count(library_construction_protocol) as c from study as stu
join
  experiment as exp
on exp.study_accession = stu.study_accession
join
  study_exp_count as exp_count
on exp_count.stu_id = stu.study_accession
where
   library_construction_protocol like "% adapter%"
or library_construction_protocol like "% ligat%"
or library_construction_protocol like "% blunt%"
or library_construction_protocol like "% phosphorylat%"
or library_construction_protocol like "% overhang"
or library_construction_protocol like "% t4-PNK%"
or library_construction_protocol like "% t4%"
or library_construction_protocol like "% pnk%"
or library_construction_protocol like "% kinase%"
or library_construction_protocol like "% a-tail%"
group by id
)
) group by score
union all
select 'enrichment' as anno, *, count(score) as score_c from
(
select id, total, c, cast (c as real)/cast (total as real) * 100 as score from
(
select
   stu.study_accession id, exp_count.total as total, count(library_construction_protocol) as c from study as stu
join
  experiment as exp
on exp.study_accession = stu.study_accession
join
  study_exp_count as exp_count
on exp_count.stu_id = stu.study_accession
where
   library_construction_protocol like "% clone%"
or library_construction_protocol like "% clonin%"
or library_construction_protocol like "% pcr %"
or library_construction_protocol like "% amplif%"
or library_construction_protocol like "% polymerase%"
or library_construction_protocol like "% taq%"
or library_construction_protocol like "% phusion%"
or library_construction_protocol like "% temperat%"
or library_construction_protocol like "% thermal%"
or library_construction_protocol like "% anneal%"
or library_construction_protocol like "% denature%"
group by id
)
) group by score;

-- This is the remainder part of the results for the above unioned query

insert into anno_distrib
select 'all' as anno, *, count(score) as score_c from
(
select id, total, c, cast (c as real)/cast (total as real) * 100 as score from
(
select
   stu.study_accession id, exp_count.total as total, count(library_construction_protocol) as c from study as stu
join
  experiment as exp
on exp.study_accession = stu.study_accession
join
  study_exp_count as exp_count
on exp_count.stu_id = stu.study_accession
where
(
   library_construction_protocol like "% shear%"
or library_construction_protocol like "% restriction%"
or library_construction_protocol like "% digest%"
or library_construction_protocol like "% fragment%"
or library_construction_protocol like "% breaks %"
or library_construction_protocol like "% nebulis%"
or library_construction_protocol like "% nebuliz%"
or library_construction_protocol like "% acoustic%"
or library_construction_protocol like "% sonic%"
) and
(
   library_construction_protocol like "% adapter%"
or library_construction_protocol like "% ligat%"
or library_construction_protocol like "% blunt%"
or library_construction_protocol like "% phosphorylat%"
or library_construction_protocol like "% overhang"
or library_construction_protocol like "% t4-PNK%"
or library_construction_protocol like "% t4%"
or library_construction_protocol like "% pnk%"
or library_construction_protocol like "% kinase%"
or library_construction_protocol like "% a-tail%"
) and
(
   library_construction_protocol like "% clone%"
or library_construction_protocol like "% clonin%"
or library_construction_protocol like "% pcr %"
or library_construction_protocol like "% amplif%"
or library_construction_protocol like "% polymerase%"
or library_construction_protocol like "% taq%"
or library_construction_protocol like "% phusion%"
or library_construction_protocol like "% temperat%"
or library_construction_protocol like "% thermal%"
or library_construction_protocol like "% anneal%"
or library_construction_protocol like "% denature%"
)
group by id
)
) group by score;




-- Final break-down of proportions of experiment records annotated
-- for a study and annotation type, but binned into percentages

select anno, '> 0% <= 25%', sum(score_c) from anno_distrib
where (score <= 25) and anno = 'fragmentation'
union all
select anno, '> 25% <= 50%', sum(score_c) from anno_distrib
where (score > 25 and score <= 50) and anno = 'fragmentation'
union all
select anno, '> 50% <= 75%', sum(score_c) from anno_distrib
where (score > 50 and score <= 75) and anno = 'fragmentation'
union all
select anno, '> 75% <= 100%', sum(score_c) from anno_distrib
where (score > 75 and score <= 100) and anno = 'fragmentation'

union all

select anno, '> 0% <= 25%', sum(score_c) from anno_distrib
where (score <= 25) and anno = 'ligation'
union all
select anno, '> 25% <= 50%', sum(score_c) from anno_distrib
where (score > 25 and score <= 50) and anno = 'ligation'
union all
select anno, '> 50% <= 75%', sum(score_c) from anno_distrib
where (score > 50 and score <= 75) and anno = 'ligation'
union all
select anno, '> 75% <= 100%', sum(score_c) from anno_distrib
where (score > 75 and score <= 100) and anno = 'ligation'

union all

select anno, '> 0% <= 25%', sum(score_c) from anno_distrib
where (score <= 25) and anno = 'enrichment'
union all
select anno, '> 25% <= 50%', sum(score_c) from anno_distrib
where (score > 25 and score <= 50) and anno = 'enrichment'
union all
select anno, '> 50% <= 75%', sum(score_c) from anno_distrib
where (score > 50 and score <= 75) and anno = 'enrichment'
union all
select anno, '> 75% <= 100%', sum(score_c) from anno_distrib
where (score > 75 and score <= 100) and anno = 'enrichment'

union all

select anno, '> 0% <= 25%', sum(score_c) from anno_distrib
where (score <= 25) and anno = 'all'
union all
select anno, '> 25% <= 50%', sum(score_c) from anno_distrib
where (score > 25 and score <= 50) and anno = 'all'
union all
select anno, '> 50% <= 75%', sum(score_c) from anno_distrib
where (score > 50 and score <= 75) and anno = 'all'
union all
select anno, '> 75% <= 100%', sum(score_c) from anno_distrib
where (score > 75 and score <= 100) and anno = 'all';