User:Lindenb/Notebook/UMR915/20100705

20100702        Top        20100706       

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"
 	;