Difference between revisions of "Postgres status"
(Created page with "Back to .ace examples") |
|||
Line 1: | Line 1: | ||
− | + | 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