Difference between revisions of "Postgres status"

From WormBaseWiki
Jump to navigationJump to search
(Created page with "Back to .ace examples")
 
Line 1: Line 1:
Back to [[.ace examples]]
+
objects in Exp_pattern that have:
 +
 
 +
<pre>
 +
1- only life stage (exp_lifestage) 726 objects
 +
2- only anatomy (exp_anatomy) 2749 objects
 +
3- only GO (exp_goid) 952 objects
 +
4- Life stage + anatomy 4568
 +
5- Life stage + GO 78
 +
6- Anatomy + GO 779
 +
7- Life stage+ Anatomy + GO 854
 +
8- No Life_stage NO GO NO Anatomy 652
 +
 
 +
</pre>
 +
 
 +
 
 +
total number of Expr objects in postgres 11683 (the sum of the above is 11358, talk to J to pull out the remaining)
 +
 
 +
 
 +
1) objects that have only life stage: 726 results -725 without the duplicated objects -> Check out the discrepancy
 +
 
 +
SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
 +
 
 +
query to filter out duplicates
 +
SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
 +
 
 +
 
 +
2) objects that have only anatomy:  3514 results, 2749 w/o the duplicated objects
 +
 
 +
SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
 +
 
 +
query to filter out duplicates
 +
SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE  exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
 +
 
 +
3) objects that have only go:  952 results, 951 w/o duplicates -> Check out the discrepancy
 +
SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL);
 +
 
 +
query to filter out duplicates
 +
SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE  exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) ORDER BY exp_name;
 +
 
 +
4) objects that have life stage and anatomy but not GO: 6169 results - 4568 without the duplicated objects
 +
 
 +
SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
 +
 
 +
query to filter out duplicates
 +
SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
 +
 
 +
5) objects that have life stage and GO but not anatomy: 78 results - 78 without the duplicated objects
 +
 
 +
SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
 +
 
 +
query to filter out duplicates
 +
SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
 +
 
 +
6) objects that have anatomy and GO but not life_stage: 929 results - 779 without the duplicated objects
 +
 
 +
SELECT * FROM exp_name WHERE joinkey  NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
 +
 
 +
query to filter out duplicates
 +
SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
 +
 
 +
 
 +
7) objects that have anatomy and GO and life_stage: 1178 results - 854 without the duplicated objects
 +
 
 +
SELECT * FROM exp_name WHERE joinkey  IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
 +
 
 +
query to filter out duplicates
 +
SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
 +
 
 +
8) objects that have no anatomy no GO and no life_stage: 652 results
 +
 
 +
SELECT * FROM exp_name WHERE joinkey  NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
 +
 
 +
 
 +
 
 +
 
 +
2610 remainingBack to [[.ace examples]]

Revision as of 13:02, 5 May 2014

objects in Exp_pattern that have:

1- only life stage (exp_lifestage) 726 objects
2- only anatomy (exp_anatomy) 2749 objects
3- only GO (exp_goid) 952 objects
4- Life stage + anatomy 4568
5- Life stage + GO 78
6- Anatomy + GO 779
7- Life stage+ Anatomy + GO 854
8- No Life_stage NO GO NO Anatomy 652


total number of Expr objects in postgres 11683 (the sum of the above is 11358, talk to J to pull out the remaining)


1) objects that have only life stage: 726 results -725 without the duplicated objects -> Check out the discrepancy

SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);

query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;


2) objects that have only anatomy: 3514 results, 2749 w/o the duplicated objects

SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);

query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;

3) objects that have only go: 952 results, 951 w/o duplicates -> Check out the discrepancy SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL);

query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) ORDER BY exp_name;

4) objects that have life stage and anatomy but not GO: 6169 results - 4568 without the duplicated objects

SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);

query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;

5) objects that have life stage and GO but not anatomy: 78 results - 78 without the duplicated objects

SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);

query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;

6) objects that have anatomy and GO but not life_stage: 929 results - 779 without the duplicated objects

SELECT * FROM exp_name WHERE joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);

query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;


7) objects that have anatomy and GO and life_stage: 1178 results - 854 without the duplicated objects

SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);

query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;

8) objects that have no anatomy no GO and no life_stage: 652 results

SELECT * FROM exp_name WHERE joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);



2610 remainingBack to .ace examples