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 (8 years, 8 months ago) by hstehr
File size: 5114 byte(s)
Log Message:
adding Model-It server files to repository
Line File contents
1 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;