-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries
79 lines (56 loc) · 2.18 KB
/
queries
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
apop_text_to_db 07_08_HH_subset.csv tan07 bust.db
apop_text_to_db 11_12_HH_subset.csv tan11 bust.db
apop_text_to_db 07_08_hiv.csv 07_08_hiv bust.db
apop_text_to_db 11_12_hiv.csv 11_12_hiv bust.db
weights=v005
hhweights=hv005
muni=hv024
testresult=hiv03
#join the `07 HIV data
sqlite3 --column bust.db "create index hiv0703 on hiv07(hiv03)"
sqlite3 --column bust.db "create index tan07xx on tan07(ha62)"
sqlite3 --column bust.db "
create table hivxtab07 as
select $testresult, $muni, tan07.$hhweights weights, count(*) ct
from tan07, hiv07 where
`for i in $(seq 1 9); do
echo hiv07.hiv01 = tan07.ha62_$i or
done`
0
group by hv024, hiv03"
apop_plot_query -n bust.db "
create table fxtab07 as
select g.$muni, pos*g.weights, neg*b.weights
, pos*g.weights/(pos*g.weights+neg*b.weights+0.0) ratio, sqrt((pos/(pos+neg+0.0))*(neg/(pos+neg+0.0))/(pos+neg)) σ
from (select hv024, weights, ct as pos from hivxtab07 where hiv03='hiv positive' group by hv024) g,
(select hv024, weights, ct as neg from hivxtab07 where hiv03='hiv negative' group by hv024) b
where g.hv024=b.hv024
"
bloodid=ha62
sqlite3 --column bust.db "drop table hivxtab"
sqlite3 --column bust.db "
create table hivxtab as
select hiv03, $muni , $hhweights as weights, count(*) ct
from tan11, hiv11 where
`for i in $(seq 1 9); do
echo hiv11.hiv01 = tan11.ha62_0$i or
done`
0
group by $muni , hiv03"
apop_plot_query -n bust.db "
create table fxtab11 as
select g.$muni, pos*g.weights, neg*b.weights
, pos*g.weights/(pos*g.weights+neg*b.weights+0.0) ratio, sqrt((pos/(pos+neg+0.0))*(neg/(pos+neg+0.0))/(pos+neg)) σ
from (select hv024, weights, ct as pos from hivxtab where hiv03='hiv positive' group by hv024) g,
(select hv024, weights, ct as neg from hivxtab where hiv03='hiv negative' group by hv024) b
where g.hv024=b.hv024
"
#now combine to get diffs.
sqlite3 --column bust.db "create index hiv1103 on hiv11(hiv03)"
sqlite3 --column bust.db "create index tan11 on tan11(s816)"
apop_plot_query -n bust.db "create table summary as
select fxtab07.hv024, (fxtab11.ratio - fxtab07.ratio) diff ,
sqrt (fxtab11.σ *fxtab11.σ + fxtab07.σ *fxtab07.σ)
from
fxtab07, fxtab11
where fxtab11.$muni==fxtab07.hv024 order by diff"