ViewVC Help
View File | Revision Log | Show Annotations | View Changeset | Root Listing
root/owl/trunk/modelit/scripts/queries_benchmark_plots.sql
Revision: 1299
Committed: Tue Jan 11 17:38:33 2011 UTC (10 years, 5 months ago) by hstehr
File size: 5114 byte(s)
Log Message:
adding Model-It server files to repository
Line User Rev File contents
1 hstehr 1299 CREATE TABLE tmp_nopp
2     SELECT target,method,gdt
3     FROM benchmark
4     WHERE cct=4 and max=10 and phipsi='-' and omega='-' and gdt!=0
5     ORDER BY target,method;
6    
7     CREATE TABLE tmp_pp20
8     SELECT target,method,gdt
9     FROM benchmark
10     WHERE cct=4 and max=10 and phipsi='pp20' and omega='-' and gdt!=0
11     ORDER BY target,method;
12    
13     CREATE TABLE tmp_pp20mm0
14     SELECT target,method,gdt
15     FROM benchmark
16     WHERE cct=4 and max=10 and phipsi='pp20mm0' and omega='-' and gdt!=0
17     ORDER BY target,method;
18    
19     CREATE TABLE tmp_pp30
20     SELECT target,method,gdt
21     FROM benchmark
22     WHERE cct=4 and max=10 and phipsi='pp30' and omega='-' and gdt!=0
23     ORDER BY target,method;
24    
25     CREATE TABLE tmp_pp20mm2_om178-5
26     SELECT target,method,gdt
27     FROM benchmark
28     WHERE cct=4 and max=10 and phipsi='pp20mm2' and omega='om178-5' and gdt!=0
29     ORDER BY target,method;
30    
31     CREATE TABLE tmp_targets_sorted
32     SELECT substring(target,1,5) as target, avg(gdt_ts) as gdt
33     FROM results_raw
34     GROUP by substring(target,1,5)
35     ORDER BY gdt desc;
36    
37    
38     -- to plot nopp vs pp20 vs pp30 vs pp20mm0
39     CREATE TABLE tmp
40     SELECT a.target as target,a.method as method, a.gdt as gdt_nopp, b.gdt as gdt_pp20, c.gdt as gdt_pp30, d.gdt as gdt_pp20mm0
41     FROM tmp_nopp as a
42     INNER JOIN tmp_pp20 as b
43     INNER JOIN tmp_pp30 as c
44     INNER JOIN tmp_pp20mm0 as d
45     INNER JOIN tmp_targets_sorted as e
46     ON (a.target=b.target AND a.target=c.target AND a.target=d.target AND a.method=b.method AND a.method=c.method AND a.method=d.method AND a.target=e.target)
47     ORDER BY e.gdt desc;
48    
49     -- same as above only for blast
50     CREATE TABLE tmp_bla
51     SELECT a.target as target,a.method as method, a.gdt as gdt_nopp, b.gdt as gdt_pp20, c.gdt as gdt_pp30, d.gdt as gdt_pp20mm0
52     FROM tmp_nopp as a
53     INNER JOIN tmp_pp20 as b
54     INNER JOIN tmp_pp30 as c
55     INNER JOIN tmp_pp20mm0 as d
56     INNER JOIN tmp_targets_sorted as e
57     ON (a.target=b.target AND a.target=c.target AND a.target=d.target AND a.method=b.method AND a.method=c.method AND a.method=d.method AND a.target=e.target)
58     WHERE a.method='bla'
59     ORDER BY e.gdt desc;
60    
61     -- same only for psi-blast
62     CREATE TABLE tmp_pb3
63     SELECT a.target as target,a.method as method, a.gdt as gdt_nopp, b.gdt as gdt_pp20, c.gdt as gdt_pp30, d.gdt as gdt_pp20mm0
64     FROM tmp_nopp as a
65     INNER JOIN tmp_pp20 as b
66     INNER JOIN tmp_pp30 as c
67     INNER JOIN tmp_pp20mm0 as d
68     INNER JOIN tmp_targets_sorted as e
69     ON (a.target=b.target AND a.target=c.target AND a.target=d.target AND a.method=b.method AND a.method=c.method AND a.method=d.method AND a.target=e.target)
70     WHERE a.method='pb3'
71     ORDER BY e.gdt desc;
72    
73     -- same only for gtg
74     CREATE TABLE tmp_gtg
75     SELECT a.target as target,a.method as method, a.gdt as gdt_nopp, b.gdt as gdt_pp20, c.gdt as gdt_pp30, d.gdt as gdt_pp20mm0
76     FROM tmp_nopp as a
77     INNER JOIN tmp_pp20 as b
78     INNER JOIN tmp_pp30 as c
79     INNER JOIN tmp_pp20mm0 as d
80     INNER JOIN tmp_targets_sorted as e
81     ON (a.target=b.target AND a.target=c.target AND a.target=d.target AND a.method=b.method AND a.method=c.method AND a.method=d.method AND a.target=e.target)
82     WHERE a.method='gtg'
83     ORDER BY e.gdt desc;
84    
85     -- blast vs psi-blast vs gtg (using pp20, max10, cct4)
86     SELECT a.target as target, a.method as method, a.gdt_pp20 as gdt_bla, b.gdt_pp20 as gdt_pb3, c.gdt_pp20 as gdt_gtg
87     FROM tmp AS a
88     JOIN tmp AS b
89     JOIN tmp AS c
90     ON (a.target=b.target AND a.target=c.target)
91     WHERE a.method='bla' AND b.method='pb3' AND c.method='gtg';
92    
93     -- psi-blast vs gtg (using pp20, max10, cct4)
94     SELECT b.target as target, b.method as method, b.gdt_pp20 as gdt_pb3, c.gdt_pp20 as gdt_gtg
95     FROM tmp AS b
96     JOIN tmp AS c
97     ON (b.target=c.target)
98     WHERE b.method='pb3' AND c.method='gtg';
99    
100    
101     -- nopp vs pp20mm0 vs pp20mm2_om178-5 (blast)
102     SELECT a.target as target,a.method as method, a.gdt as gdt_nopp, b.gdt as gdt_pp20, c.gdt as gdt_pp20mm0, d.gdt as gdt_pp20mm2_om178_5
103     FROM tmp_nopp as a
104     INNER JOIN tmp_pp20 as b
105     INNER JOIN tmp_pp20mm0 as c
106     INNER JOIN tmp_pp20mm2_om178_5 as d
107     INNER JOIN tmp_targets_sorted as e
108     ON (a.target=b.target AND a.target=c.target AND a.target=d.target AND a.method=b.method AND a.method=c.method AND a.method=d.method AND a.target=e.target)
109     WHERE a.method='bla'
110     ORDER BY e.gdt desc;
111    
112     -- nopp vs pp20mm0 vs pp20mm2_om178-5 (psi-blast)
113     SELECT a.target as target,a.method as method, a.gdt as gdt_nopp, b.gdt as gdt_pp20, c.gdt as gdt_pp20mm0, d.gdt as gdt_pp20mm2_om178_5
114     FROM tmp_nopp as a
115     INNER JOIN tmp_pp20 as b
116     INNER JOIN tmp_pp20mm0 as c
117     INNER JOIN tmp_pp20mm2_om178_5 as d
118     INNER JOIN tmp_targets_sorted as e
119     ON (a.target=b.target AND a.target=c.target AND a.target=d.target AND a.method=b.method AND a.method=c.method AND a.method=d.method AND a.target=e.target)
120     WHERE a.method='pb3'
121     ORDER BY e.gdt desc;
122    
123     -- nopp vs pp20mm0 vs pp20mm2_om178-5 (gtg)
124     SELECT a.target as target,a.method as method, a.gdt as gdt_nopp, b.gdt as gdt_pp20, c.gdt as gdt_pp20mm0, d.gdt as gdt_pp20mm2_om178_5
125     FROM tmp_nopp as a
126     INNER JOIN tmp_pp20 as b
127     INNER JOIN tmp_pp20mm0 as c
128     INNER JOIN tmp_pp20mm2_om178_5 as d
129     INNER JOIN tmp_targets_sorted as e
130     ON (a.target=b.target AND a.target=c.target AND a.target=d.target AND a.method=b.method AND a.method=c.method AND a.method=d.method AND a.target=e.target)
131     WHERE a.method='gtg'
132     ORDER BY e.gdt desc;