-
Notifications
You must be signed in to change notification settings - Fork 0
/
figure4.sql
204 lines (175 loc) · 13 KB
/
figure4.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
/* revision queries */
/* select all 1335 genes from ALN or ZOL (original and repeat sequencing) CRISPRi/a screen with absolute value rho phenotype > 0.1 and p value <= 0.05 */
select a.gene, (abs(a.`r avg3 average phenotype of strongest 3`) + abs(z.`r avg3 average phenotype of strongest 3`) + abs(a2.`r avg3 average phenotype of strongest 3`) + abs(z2.`r avg3 average phenotype of strongest 3`)) sumall, a.`r avg3 Mann-Whitney p-value` as crispri_aln_pval, a.`r avg3 average phenotype of strongest 3` as crispri_aln_rho,
z.`r avg3 Mann-Whitney p-value` as crispri_zol_pval, z.`r avg3 average phenotype of strongest 3` as crispri_zol_rho,
a2.`r avg3 average phenotype of strongest 3` crispra_rho, z2.`r avg3 average phenotype of strongest 3` z2_rho
from crispri.`aln` a
join
crispri.`zol_digested_copy` z
on a.gene=z.gene
join
crispri.`zol-10ug-v3` z2
on a.gene=z2.gene
join
crispra.`aln` a2
on a.gene=a2.gene
where
(abs(a.`r avg3 average phenotype of strongest 3`) > 0.3 or abs(z.`r avg3 average phenotype of strongest 3`) > 0.3 or (abs(a2.`r avg3 average phenotype of strongest 3`) > 0.3 or abs(z2.`r avg3 average phenotype of strongest 3`) > 0.3)) and (a.`r avg3 Mann-Whitney p-value` <= 0.05 or z.`r avg3 Mann-Whitney p-value` <= 0.05 or a2.`r avg3 Mann-Whitney p-value` <= 0.05 or z2.`r avg3 Mann-Whitney p-value` <= 0.05) and (a.gene not like "%pseudo_%" and z.gene not like "%pseudo_%" and a2.gene not like "%pseudo_%" and z2.gene not like "%pseudo_%") group by a.gene order by (abs(a.`r avg3 average phenotype of strongest 3`) + abs(z.`r avg3 average phenotype of strongest 3`) + abs(a2.`r avg3 average phenotype of strongest 3`) + abs(z2.`r avg3 average phenotype of strongest 3`)) desc
/* select all 774 statistically significant differentially expressed genes in both ONJ and DTC patients */
select * from (select o.symbol as gene, o.logFC as o_fold, o.`AveExpr` as o_avg, o.`P.Value` as o_pval from tbone.cheng_onj o where o.symbol !='.' and o.`P.Value` <= 0.05 group by symbol) o
join
(select c.symbol as gene, c.logFC as c_fold, c.`AveExpr` as c_avg, c.`P.Value` as c_pval from tbone.cheng_cancer_her2_1000vs2500days c where c.symbol !='.' and c.`P.Value` <= 0.05 group by symbol) c
on o.gene=c.gene
group by o.gene
/* 76 genes CRISPRia- ONJ/DTC gene expr. intersection */
select crispria.gene from
(select a.gene, (abs(a.`r avg3 average phenotype of strongest 3`) + abs(z.`r avg3 average phenotype of strongest 3`) + abs(a2.`r avg3 average phenotype of strongest 3`) + abs(z2.`r avg3 average phenotype of strongest 3`)) sumall, a.`r avg3 Mann-Whitney p-value` as crispri_aln_pval, a.`r avg3 average phenotype of strongest 3` as crispri_aln_rho,
z.`r avg3 Mann-Whitney p-value` as crispri_zol_pval, z.`r avg3 average phenotype of strongest 3` as crispri_zol_rho,
a2.`r avg3 average phenotype of strongest 3` crispra_rho, z2.`r avg3 average phenotype of strongest 3` z2_rho
from crispri.`aln` a
join
crispri.`zol_digested_copy` z
on a.gene=z.gene
join
crispri.`zol-10ug-v3` z2
on a.gene=z2.gene
join
crispra.`aln` a2
on a.gene=a2.gene
where
(abs(a.`r avg3 average phenotype of strongest 3`) > 0.3 or abs(z.`r avg3 average phenotype of strongest 3`) > 0.3 or (abs(a2.`r avg3 average phenotype of strongest 3`) > 0.3 or abs(z2.`r avg3 average phenotype of strongest 3`) > 0.3)) and (a.`r avg3 Mann-Whitney p-value` <= 0.05 or z.`r avg3 Mann-Whitney p-value` <= 0.05 or a2.`r avg3 Mann-Whitney p-value` <= 0.05 or z2.`r avg3 Mann-Whitney p-value` <= 0.05) and (a.gene not like "%pseudo_%" and z.gene not like "%pseudo_%" and a2.gene not like "%pseudo_%" and z2.gene not like "%pseudo_%") group by a.gene order by (abs(a.`r avg3 average phenotype of strongest 3`) + abs(z.`r avg3 average phenotype of strongest 3`) + abs(a2.`r avg3 average phenotype of strongest 3`) + abs(z2.`r avg3 average phenotype of strongest 3`)) desc) crispria
join
(select o.* from (select o.symbol as gene, o.logFC as o_fold, o.`AveExpr` as o_avg, o.`P.Value` as o_pval from tbone.cheng_onj o where o.symbol !='.' and o.`P.Value` <= 0.05 group by symbol) o
join
(select c.symbol as gene, c.logFC as c_fold, c.`AveExpr` as c_avg, c.`P.Value` as c_pval from tbone.cheng_cancer_her2_1000vs2500days c where c.symbol !='.' and c.`P.Value` <= 0.05 group by symbol) c
on o.gene=c.gene
group by o.gene) gene_expr
on crispria.gene = gene_expr.gene
/* 49 genes WES- ONJ/DTC gene expr. intersection */
select c.genec from (select a.gene as genec from (select * from Annotated_ONJ_WES20 where carriercount > 0 /*and carriercount/20 > gnomadexomeMaxAF /*and (carriercount/20)/gnomadexomeMaxAF > 3*/ /*and gnomadexomeMaxAF !="."*/ and RefGeneFunction_ExonicFunction like "%nonsy%" group by gene) a
join
aff_multiple_case_only_variants_v5 b
on
a.`gene`=b.gene) c
join
(select o.gene as gened from (select o.symbol as gene, o.logFC as o_fold, o.`AveExpr` as o_avg, o.`P.Value` as o_pval from tbone.cheng_onj o where o.symbol !='.' and o.`P.Value` <= 0.05 group by symbol) o
join
(select c.symbol as gene, c.logFC as c_fold, c.`AveExpr` as c_avg, c.`P.Value` as c_pval from tbone.cheng_cancer_her2_1000vs2500days c where c.symbol !='.' and c.`P.Value` <= 0.05 group by symbol) c
on o.gene=c.gene
group by o.gene) d
on
c.genec=d.gened
/* 64 genes CRISPRia- WES intersection */
select crispria.gene from
(select a.gene, (abs(a.`r avg3 average phenotype of strongest 3`) + abs(z.`r avg3 average phenotype of strongest 3`) + abs(a2.`r avg3 average phenotype of strongest 3`) + abs(z2.`r avg3 average phenotype of strongest 3`)) sumall, a.`r avg3 Mann-Whitney p-value` as crispri_aln_pval, a.`r avg3 average phenotype of strongest 3` as crispri_aln_rho,
z.`r avg3 Mann-Whitney p-value` as crispri_zol_pval, z.`r avg3 average phenotype of strongest 3` as crispri_zol_rho,
a2.`r avg3 average phenotype of strongest 3` crispra_rho, z2.`r avg3 average phenotype of strongest 3` z2_rho
from crispri.`aln` a
join
crispri.`zol_digested_copy` z
on a.gene=z.gene
join
crispri.`zol-10ug-v3` z2
on a.gene=z2.gene
join
crispra.`aln` a2
on a.gene=a2.gene
where
(abs(a.`r avg3 average phenotype of strongest 3`) > 0.3 or abs(z.`r avg3 average phenotype of strongest 3`) > 0.3 or (abs(a2.`r avg3 average phenotype of strongest 3`) > 0.3 or abs(z2.`r avg3 average phenotype of strongest 3`) > 0.3)) and (a.`r avg3 Mann-Whitney p-value` <= 0.05 or z.`r avg3 Mann-Whitney p-value` <= 0.05 or a2.`r avg3 Mann-Whitney p-value` <= 0.05 or z2.`r avg3 Mann-Whitney p-value` <= 0.05) and (a.gene not like "%pseudo_%" and z.gene not like "%pseudo_%" and a2.gene not like "%pseudo_%" and z2.gene not like "%pseudo_%") group by a.gene order by (abs(a.`r avg3 average phenotype of strongest 3`) + abs(z.`r avg3 average phenotype of strongest 3`) + abs(a2.`r avg3 average phenotype of strongest 3`) + abs(z2.`r avg3 average phenotype of strongest 3`)) desc) crispria
join
(select a.gene as genec from (select * from tbone.Annotated_ONJ_WES20 where carriercount > 0 /*and carriercount/20 > gnomadexomeMaxAF /*and (carriercount/20)/gnomadexomeMaxAF > 3*/ /*and gnomadexomeMaxAF !="."*/ and RefGeneFunction_ExonicFunction like "%nonsy%" group by gene) a
join
tbone.aff_multiple_case_only_variants_v5 b
on
a.`gene`=b.gene) WES
on crispria.gene = WES.genec
/* 3 genes ONJ/DTC gene expression-WES-CRISPRi/a intersection */
select * from (select o.*, c_fold, c_avg, c_pval from
(select o.symbol as gene, o.logFC as o_fold, o.`AveExpr` as o_avg, o.`P.Value` as o_pval from tbone.cheng_onj o where o.symbol !='.' and o.`P.Value` <= 0.05 group by symbol) o
join
(select c.symbol as gene, c.logFC as c_fold, c.`AveExpr` as c_avg, c.`P.Value` as c_pval from tbone.cheng_cancer_her2_1000vs2500days c where c.symbol !='.' and c.`P.Value` <= 0.05 group by symbol) c
on o.gene=c.gene
group by o.gene
) RNA
join
(select a.gene as crispri_gene, (abs(a.`r avg3 average phenotype of strongest 3`) + abs(z.`r avg3 average phenotype of strongest 3`) + abs(a2.`r avg3 average phenotype of strongest 3`) + abs(z2.`r avg3 average phenotype of strongest 3`)) sumall, a.`r avg3 Mann-Whitney p-value` as crispri_aln_pval, a.`r avg3 average phenotype of strongest 3` as crispri_aln_rho,
z.`r avg3 Mann-Whitney p-value` as crispri_zol_pval, z.`r avg3 average phenotype of strongest 3` as crispri_zol_rho,
a2.`r avg3 average phenotype of strongest 3` crispra_rho, a2.`r avg3 Mann-Whitney p-value` crispra_pval, z2.`r avg3 average phenotype of strongest 3` z2_rho, z2.`r avg3 Mann-Whitney p-value` as z2_pval
from crispri.`aln` a
join
crispri.`zol_digested_copy` z
on a.gene=z.gene
join
crispri.`zol-10ug-v3` z2
on a.gene=z2.gene
join
crispra.`aln` a2
on a.gene=a2.gene
where
(abs(a.`r avg3 average phenotype of strongest 3`) > 0.3 or abs(z.`r avg3 average phenotype of strongest 3`) > 0.3 or (abs(a2.`r avg3 average phenotype of strongest 3`) > 0.3 or abs(z2.`r avg3 average phenotype of strongest 3`) > 0.3)) and (a.`r avg3 Mann-Whitney p-value` <= 0.05 or z.`r avg3 Mann-Whitney p-value` <= 0.05 or a2.`r avg3 Mann-Whitney p-value` <= 0.05 or z2.`r avg3 Mann-Whitney p-value` <= 0.05) and (a.gene not like "%pseudo_%" and z.gene not like "%pseudo_%" and a2.gene not like "%pseudo_%" and z2.gene not like "%pseudo_%") group by a.gene order by (abs(a.`r avg3 average phenotype of strongest 3`) + abs(z.`r avg3 average phenotype of strongest 3`) + abs(a2.`r avg3 average phenotype of strongest 3`) + abs(z2.`r avg3 average phenotype of strongest 3`)) desc
) CRISPRI
on RNA.gene=CRISPRI.crispri_gene
join
(select a.gene as WES_gene from (select * from Annotated_ONJ_WES20 where carriercount > 0 and (RefGeneFunction_ExonicFunction like "%nonsy%" or RefGeneFunction_ExonicFunction like "%,frameshift_deletion%") group by gene) a
join
aff_multiple_case_only_variants_v5 b
on
a.`gene`=b.gene) WES
on RNA.gene=WES.WES_gene
/* first draft queries */
/* select all 2357 genes from ALN or ZOL CRISPRi screen with absolute value rho phenotype > 0.1 and p value <= 0.05 */
select a.gene, a.`r avg3 Mann-Whitney p-value` as crispri_aln_pval, a.`r avg3 average phenotype of strongest 3` as crispri_aln_rho,
z.`r avg3 Mann-Whitney p-value` as crispri_zol_pval, z.`r avg3 average phenotype of strongest 3` as crispri_zol_rho
from crispri.`aln` a
join
crispri.`zol_digested_copy` z
on a.gene=z.gene
where
(abs(a.`r avg3 average phenotype of strongest 3`) > 0.1 or abs(z.`r avg3 average phenotype of strongest 3`) > 0.1) and (a.`r avg3 Mann-Whitney p-value` <= 0.05 or z.`r avg3 Mann-Whitney p-value` <= 0.05) and a.gene not like "%pseudo_%" and z.gene not like "%pseudo_%"
/* 165 genes CRISPRi-AFF intersection */
select * from (select * from aff_multiple_case_only_variants_v5) AFF
join
(select a.gene, a.`r avg3 Mann-Whitney p-value` as crispri_aln_pval, a.`r avg3 average phenotype of strongest 3` as crispri_aln_rho,
z.`r avg3 Mann-Whitney p-value` as crispri_zol_pval, z.`r avg3 average phenotype of strongest 3` as crispri_zol_rho
from crispri.`aln_copy` a
join
crispri.`zol_digested_copy` z
on a.gene=z.gene
where
(abs(a.`r avg3 average phenotype of strongest 3`) > 0.1 or abs(z.`r avg3 average phenotype of strongest 3`) > 0.1) and (a.`r avg3 Mann-Whitney p-value` <= 0.05 or z.`r avg3 Mann-Whitney p-value` <= 0.05) and a.gene not like "%pseudo_%" and z.gene not like "%pseudo_%") CRISPRI
on AFF.gene=CRISPRI.gene
group by AFF.gene
/* 68 genes ONJ/DTC gene expression-AFF intersection */
select * from (select * from aff_multiple_case_only_variants_v5) AFF
join
(select o.* from
(select o.symbol as gene, o.logFC as o_fold, o.`AveExpr` as o_avg, o.`P.Value` as o_pval from tbone.cheng_onj o where o.symbol !='.' and o.`P.Value` <= 0.05 group by symbol) o
join
(select c.symbol as gene, c.logFC as c_fold, c.`AveExpr` as c_avg, c.`P.Value` as c_pval from tbone.cheng_cancer_her2_1000vs2500days c where c.symbol !='.' and c.`P.Value` <= 0.05 group by symbol) c
on o.gene=c.gene
group by o.gene) RNA
on AFF.gene=RNA.gene
group by AFF.gene
/* 5 genes ONJ/DTC gene expression-AFF-CRISPRi intersection */
select * from (select * from (select o.*, c_fold, c_avg, c_pval from
(select o.symbol as gene, o.logFC as o_fold, o.`AveExpr` as o_avg, o.`P.Value` as o_pval from tbone.cheng_onj o where o.symbol !='.' and o.`P.Value` <= 0.05 group by symbol) o
join
(select c.symbol as gene, c.logFC as c_fold, c.`AveExpr` as c_avg, c.`P.Value` as c_pval from tbone.cheng_cancer_her2_1000vs2500days c where c.symbol !='.' and c.`P.Value` <= 0.05 group by symbol) c
on o.gene=c.gene
group by o.gene) RNA
join
(select a.gene as crispri_gene, a.`r avg3 Mann-Whitney p-value` as crispri_aln_pval, a.`r avg3 average phenotype of strongest 3` as crispri_aln_rho,
z.`r avg3 Mann-Whitney p-value` as crispri_zol_pval, z.`r avg3 average phenotype of strongest 3` as crispri_zol_rho
from crispri.`aln` a
join
crispri.`zol_digested_copy` z
on a.gene=z.gene
where
(abs(a.`r avg3 average phenotype of strongest 3`) > 0.1 or abs(z.`r avg3 average phenotype of strongest 3`) > 0.1) and (a.`r avg3 Mann-Whitney p-value` <= 0.05 or z.`r avg3 Mann-Whitney p-value` <= 0.05) and a.gene not like "%pseudo_%" and z.gene not like "%pseudo_%") CRISPRI
on RNA.gene=CRISPRI.crispri_gene
join
(select gene as aff_gene, patient1,patient2,patient3,patient4 from aff_multiple_case_only_variants_v5) AFF
on AFF.aff_gene=RNA.gene
group by RNA.gene) a
join aff_all_variants_v3_copy b
on b.gene like concat( '%', a.gene, '%')