User:Lindenb/Notebook/UMR915/20100705
From OpenWetWare
compute the difference between SLS2 and me
create temporary table user_anonymous.T1 ( id int unsigned primary key auto_increment, chrom varchar(20) not null, position int unsigned, sample_id int unsigned,index(sample_id), index (chrom),index(position),index(chrom,position) ); insert into user_anonymous.T1(chrom,position,sample_id) select distinct V1.chrom,V1.position,C1.sample_id from variation as V1 , vcf_call as C1, vcf_input as I1 where V1.ref!='*' and length(V1.alt)=1 and C1.variation_id=V1.id and C1.input_id=I1.id and I1.creator="sls2" ; create temporary table user_anonymous.T2 ( id int unsigned primary key auto_increment, chrom varchar(20) not null, position int unsigned, sample_id int unsigned,index(sample_id), index (chrom),index(position),index(chrom,position) ); insert into user_anonymous.T2(chrom,position,sample_id) select V1.chrom,V1.position,C1.sample_id from variation as V1 , vcf_call as C1, vcf_input as I1 where C1.snp_quality>=25 and C1.depth>4 and C1.depth<=1200 and C1.rms_quality >=25 and V1.ref!='*' and length(V1.alt)=1 and C1.variation_id=V1.id and C1.input_id=I1.id and I1.creator="lindenb" ; create temporary table user_anonymous.T3 ( chrom varchar(20) not null, position int unsigned, sample_id int unsigned,index(sample_id), prediction varchar(20),index(prediction), t1 int, t2 int, index (chrom),index(position),index(chrom,position) ); insert into user_anonymous.T3(chrom,position,sample_id,prediction,t1,t2) select V.chrom, V.position, C.sample_id, P.prediction, user_anonymous.T1.id, user_anonymous.T2.id from ( variation as V, polyphen as P, vcf_call as C ) left join user_anonymous.T1 on (T1.chrom=V.chrom and user_anonymous.T1.position=V.position and C.sample_id=T1.sample_id) left join user_anonymous.T2 on (T2.chrom=V.chrom and user_anonymous.T2.position=V.position and C.sample_id=T2.sample_id) where V.ref!='*' and length(P.alt)=1 and C.variation_id=V.id and P.variation_id=V.id and P.library='HumDiv' group by 1,2,3,4 ; delete from user_anonymous.T3 where t1 is null and t2 is null; select * from user_anonymous.T3 limit 200; select user_anonymous.T3.prediction as "pph2.HumDiv", concat("XXX",user_anonymous.T3.sample_id) as "Sample", COUNT(t1) as "sls2", COUNT(t2) as "pierre", COUNT(t1)-COUNT(t2) as "diff(sls2-pierr)" from ( user_anonymous.T3 ) group by user_anonymous.T3.sample_id, user_anonymous.T3.prediction order by 1,2 ;
compute data found by me but not by sls2
create temporary table user_anonymous.T1 ( id int unsigned primary key auto_increment, chrom varchar(20) not null, position int unsigned, sample_id int unsigned,index(sample_id), index (chrom),index(position),index(chrom,position) ); insert into user_anonymous.T1(chrom,position,sample_id) select distinct V1.chrom,V1.position,C1.sample_id from variation as V1 , vcf_call as C1, vcf_input as I1 where V1.ref!='*' and length(V1.alt)=1 and C1.variation_id=V1.id and C1.input_id=I1.id and I1.creator="sls2" ; select S.name, V1.chrom, V1.position+1, V1.ref, V1.alt from ( variation as V1 , vcf_call as C1, vcf_input as I1, sample as S ) left join user_anonymous.T1 on (T1.chrom=V1.chrom and T1.position=V1.position and S.id=user_anonymous.T1.sample_id) where T1.id is null and C1.sample_id=S.id and C1.snp_quality>=25 and C1.depth>4 and C1.depth<=1200 and C1.rms_quality >=25 and V1.ref!='*' and length(V1.alt)=1 and C1.variation_id=V1.id and C1.input_id=I1.id and I1.creator="MOIMOIMOI" ;