forked from AMPATH/etl
-
Notifications
You must be signed in to change notification settings - Fork 0
/
flat_table_template.sql
145 lines (109 loc) · 5.42 KB
/
flat_table_template.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
# This is the ETL table for flat_table
# obs concept_ids:
# encounter types: 1,2,3,4,10,13,14,15,17,19,22,23,26,43,47,21
# 1. Replace flat_table with flat_table_name
# 2. Replace concept_id in () with concept_id in (obs concept_ids)
# 3. Add column definitions
# 4. Add obs_set column definitions
select @last_update := (select max(date_updated) from flat_log where table_name="flat_table");
# then use the max_date_created from amrs.encounter. This takes about 10 seconds and is better to avoid.
select @last_update :=
if(@last_update is null,
(select max(date_created) from amrs.encounter e join flat_table using (encounter_id)),
@last_update);
#otherwise set to a date before any encounters had been created (i.g. we will get all encounters)
select @last_update := if(@last_update,@last_update,'1900-01-01');
select @now := now();
#select @last_update := "2015-03-10";
#delete from flat_log where table_name="flat_table";
#drop table if exists flat_table;
create table if not exists flat_table
(encounter_id int,
person_id int,
#COLUMN DEFINITIONS GO HERE ##########################################################################################
index encounter_id (encounter_id)
);
drop table if exists voided_obs;
create table voided_obs (index encounter_id (encounter_id), index obs_id (obs_id))
(select person_id, encounter_id, obs_id, obs_datetime, date_voided, concept_id, date_created
from amrs.obs where voided=1 and date_voided > @last_update and date_created <= @last_update and concept_id in ());
drop temporary table if exists enc;
create temporary table enc (encounter_id int, person_id int, primary key encounter_id (encounter_id), index person_id (person_id))
(select e.encounter_id, e.patient_id as person_id
from amrs.encounter e
where e.voided=0
and e.date_created > @last_update
and encounter_type in (1,2,3,4,10,13,14,15,17,19,22,23,26,43,47,21)
);
insert ignore into enc
(select e.encounter_id, e.patient_id as person_id
from amrs.encounter e
join voided_obs v using (encounter_id)
where e.date_created <= @last_update and e.voided=0 and v.encounter_id is not null and e.encounter_type in (1,2,3,4,10,13,14,15,17,19,22,23,26,43,47,21)
);
# add in encounters which have new relevant obs attached to them
insert ignore into enc
(select e.encounter_id, e.patient_id as person_id
from amrs.encounter e
join amrs.obs o
force index for join (date_created)
using (encounter_id)
where o.date_created > @last_update and o.voided=0 and e.date_created <= @last_update and e.voided=0 and concept_id in () and encounter_type in (1,2,3,4,10,13,14,15,17,19,22,23,26,43,47,21)
);
# remove test patients
delete t1
from enc t1
join amrs.person_attribute t2 using (person_id)
where t2.person_attribute_type_id=28 and value='true';
# create a dataset of the new obs.
drop table if exists obs_subset;
create temporary table obs_subset (primary key obs_id (obs_id), index encounter_id (encounter_id))
(select * from amrs.obs o use index (date_created) where concept_id in () and o.voided=0 and date_created > @last_update);
# add obs of encounters with voided obs
insert ignore into obs_subset
(select t1.* from amrs.obs t1 join voided_obs t2 using (encounter_id) where t1.voided=0 and t1.date_created <= @last_update and t2.encounter_id is not null and t1.concept_id in ());
# add obs for encounters which have new obs
insert ignore into obs_subset
(select o.* from amrs.encounter e join amrs.obs o use index (date_created) using (encounter_id) where o.date_created > @last_update and o.voided=0 and e.date_created <= @last_update and e.voided=0 and o.concept_id in ());
drop temporary table if exists n_obs;
create temporary table n_obs (index encounter_id (encounter_id))
(select
encounter_id,
# flattened column definitions go here##############################################################################################3
from obs_subset
where encounter_id is not null and voided=0
group by encounter_id
);
#remove any encounters that have a voided obs.
drop table if exists encounters_to_be_removed;
create temporary table encounters_to_be_removed (primary key encounter_id (encounter_id))
(select distinct encounter_id from voided_obs);
#remove any encounters that have been voided.
insert ignore into encounters_to_be_removed
(select encounter_id from amrs.encounter where voided=1 and date_created <= @last_update and date_voided > @last_update and encounter_type in (1,2,3,4,10,13,14,15,17,19,22,23,26,43,47,21));
# remove any encounters that will be (re)inserted
insert ignore into encounters_to_be_removed
(select encounter_id from enc);
delete t1
from flat_table t1
join encounters_to_be_removed t2 using (encounter_id);
#will inner join to avoid having any encounters which have no obs.
insert into flat_table
(select *
from enc e1
inner join n_obs n1 using (encounter_id)
order by e1.person_id,e1.encounter_id
);
## UPDATE data for derived tables
#remove any encounters that have been voided.
insert ignore into flat_new_person_data
(select distinct person_id from voided_obs);
insert ignore into flat_new_person_data
(select patient_id as person_id from amrs.encounter where voided=1 and date_created <= @last_update and date_voided > @last_update);
#remove any encounters with new obs as the entire encounter will be rebuilt and added back
insert ignore into flat_new_person_data
(select person_id from obs_subset);
insert ignore into flat_new_person_data
(select person_id from enc);
drop table voided_obs;
insert into flat_log values (@now,"flat_table");