mnNPKVQ9

1CREATE TABLE `genes-g38-201505` (
2  `chr` varchar(2) NOT NULL,
3  `left` bigint(20) NOT NULL,
4  `right` int(11) NOT NULL,
5  `Complement` int(11) NOT NULL,
6  `Name` tinytext NOT NULL,
7  `source` tinytext NOT NULL,
8  `ENSEMBL` tinytext NOT NULL,
9  `gene_version` tinytext NOT NULL,
10  `gene_name` tinytext NOT NULL,
11  `gene_source` tinytext NOT NULL,
12  `gene_biotypeid` tinytext NOT NULL,
13  `id` bigint(20) NOT NULL AUTO_INCREMENT,
14  UNIQUE KEY `id_UNIQUE` (`id`)
15) ENGINE=MyISAM;
16	
17CREATE TABLE `repeats-g38-201505` (
18  `id` int(11) NOT NULL,
19  `chr` varchar(2) DEFAULT NULL,
20  `left` int(11) DEFAULT NULL,
21  `right` int(11) DEFAULT NULL,
22  `name` tinytext,
23  PRIMARY KEY (`id`)
24) ENGINE=MyISAM;
25	
26CREATE TABLE `51k-80-80-ignore-random-noreverse` (
27  `chr` varchar(2) NOT NULL,
28  `left` bigint(20) NOT NULL,
29  `right` bigint(20) NOT NULL,
30  `count` int(11) NOT NULL,
31  `id` bigint(20) NOT NULL AUTO_INCREMENT,
32  UNIQUE KEY `chr_left_right` (`chr`,`left`,`right`),
33  `id` bigint(20) NOT NULL AUTO_INCREMENT
34) ENGINE=MyISAM;
35	
36CREATE TABLE `pk47-pk51-gene-repeat` (
37  `chr` varchar(2) NOT NULL,
38  `left` bigint(20) NOT NULL,
39  `right` bigint(20) NOT NULL,
40  `count_k51` int(11) DEFAULT '0',
41  `count_p51` int(11) DEFAULT '0',
42  `count_p47` int(11) DEFAULT '0',
43  `count_k47` int(11) DEFAULT '0',
44  `name_left` varchar(29) NOT NULL,
45  `name_right` varchar(17) NOT NULL,
46  UNIQUE KEY `pos_name` (`chr`,`left`,`right`,`name_left`,`name_right`)
47) ENGINE=MyISAM;
48	
49INSERT INTO `pk47-pk51-gene-repeat` (
50      `chr`,`left`, `right`,`count_k51`,`name_left`, `name_right`
51)
52SELECT 
53     a.`chr`, a.`left`, a.`right`, a.`count` as `count_k51`,
54     g.`name` as `name_left`, 
55     r.`name` as `name_right` 
56FROM `
57     51k-80-80-ignore-random-noreverse` a, 
58     `genes-g38-201505` g,
59     `repeats-g38-201505` r 
60WHERE
61     a.`chr`=g.`chr` and a.`chr`=r.`chr` and 
62     a.`left` < g.`right` and a.`left` > g.`left` and 
63     a.`right` < r.`right` and a.`right` > r.`left` 
64     on duplicate key 
65     update 
66       `pk47-pk51-gene-repeat`.`count_k51`=a.`count`;
67	
68# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
69'1', 'SIMPLE', 'g', 'ALL', NULL, NULL, NULL, NULL, '60433', NULL
70'1', 'SIMPLE', 'a', 'ref', 'chr_left_right', 'chr_left_right', '4', 'dna_homo_pairs.g.chr', '47216', 'Using index condition'
71'1', 'SIMPLE', 'r', 'ALL', NULL, NULL, NULL, NULL, '5317291', 'Using where; Using join buffer (Block Nested Loop)'
72	
73# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
74'1', 'SIMPLE', 'a', 'ALL', 'chr_left_right', NULL, NULL, NULL, '4721638', NULL
75'1', 'SIMPLE', 'g', 'ref', 'chr_left_right', 'chr_left_right', '4', 'methyl_base.a.chr', '604', 'Using index condition'
76'1', 'SIMPLE', 'r', 'ref', 'chr_left_right', 'chr_left_right', '5', 'methyl_base.a.chr', '53172', 'Using index condition'
77	
78INSERT INTO `pk47-pk51-gene-repeat` (
79      `chr`,`left`, `right`,`count_k51`,`name_left`, `name_right`
80)
81SELECT 
82     "7", a.`left`, a.`right`, a.`count` as `count_k51`,
83     g.`name` as `name_left`, 
84     r.`name` as `name_right` 
85FROM `
86     51k-80-80-ignore-random-noreverse-chr7` a, 
87     `genes-g38-201505-chr7` g,
88     `repeats-g38-201505-chr7` r 
89WHERE
90     a.`left` < g.`right` and a.`left` > g.`left` and 
91     a.`right` < r.`right` and a.`right` > r.`left` 
92     on duplicate key 
93     update 
94       `pk47-pk51-gene-repeat`.`count_k51`=a.`count`;
95	
96create table miss_rows(`left` int not null, `right` int not null,
97                       `count` int not null,`name_left` varchar(100) not null);
98
99drop procedure if exists genjoin;
100delimiter $$
101create procedure genjoin()
102begin
103
104 declare endOfData integer default 0;
105 declare done int default 0;
106 declare cnt int default 0;
107
108 declare g_left int default 0;  -- Переменные для genes
109 declare g_right int default 0;
110 declare g_name varchar(100);
111 declare gen_eof int default 0;  -- Признак конца genes
112
113 declare r_left int default 0;   -- Переменные для repeats
114 declare r_right int default 0;
115 declare r_Oright int default 0; -- Значение правого конца предыдущей записи
116 declare r_name varchar(100);
117 declare rep_eof int default 0;  -- Признак конца repeats
118
119 declare t_left int default 0;   -- Переменные рабочей таблицы
120 declare t_right int default 0;
121 declare t_Oright int default 0; -- Значение правого конца предыдущей записи
122 declare t_count int default 0;
123
124 declare gen_cur cursor for
125  select `left`,`right`,`name` from genes order by 1,2;
126 declare rep_cur cursor for
127  select `left`,`right`,`name` from repeats order by 1,2;
128 declare data_cur cursor for
129  select `left`,`right`,`count` from t47k order by 1,2;
130
131 declare miss_cur cursor for
132  select `left`, `right`, `count`, `name_left` from miss_rows order by 2;
133 declare rep2_cur cursor for
134  select `left`,`right`,`name` from repeats order by 2;
135
136
137 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET endOfData=1;
138
139 open gen_cur;
140 open rep_cur;
141 open data_cur;
142 truncate table miss_rows;
143
144 row: while done=0 do
145    if t_Oright<t_right then set t_Oright=t_right; end if; -- Сохраняем предыдущее значение
146   fetch data_cur into t_left, t_right, t_count;
147   if endOfData=1 then set done=1,endOfData=0; end if;
148
149   while t_left >= g_right and gen_eof=0 do
150     fetch gen_cur into g_left, g_right, g_name;
151     if endOfData=1 then set gen_eof=1,endOfData=0; end if;
152   end while;
153
154   while t_right >= r_right and rep_eof=0 do
155     if r_Oright<r_right then set r_Oright=r_right; end if; -- Сохраняем предыдущее значение
156     fetch rep_cur into r_left, r_right, r_name;
157     if endOfData=1 then set rep_eof=1,endOfData=0; end if;
158   end while;
159
160   if t_left <= g_left or t_left >= g_right then iterate row; end if;
161   if t_Oright > t_right then  -- Концы не по порядку, пересечение интервалов !
162     if t_right < r_Oright then -- Мы могли попасть в предыдущую запись repeats !
163       -- но пропустили ее ...
164       insert into miss_rows values(t_left, t_right, t_count,g_name);
165     end if;
166   end if;
167   if t_right <= r_left or t_right>= r_right then iterate row; end if;
168
169   insert into `pk47-pk51-gene-repeat`
170    (`left`, `right`, `count_k47`,`name_left`,`name_right`)
171    values(t_left, t_right, t_count, g_name, r_name)
172    on duplicate key update `count_k47`=t_count;
173
174 end while;
175 close gen_cur;
176 close rep_cur;
177 close data_cur;
178 open rep2_cur;
179 open miss_cur;
180 set r_left=0,r_right=0,rep_eof=0,done=0;
181 while done=0 do
182   fetch miss_cur into t_left, t_right, t_count, g_name;
183   if endOfData=1 then set done=1,endOfData=0; end if;
184
185   while t_right >= r_right and rep_eof=0 do
186     fetch rep2_cur into r_left, r_right, r_name;
187     if endOfData=1 then set rep_eof=1,endOfData=0; end if;
188   end while;
189
190   if t_right > r_left then
191     insert into `pk47-pk51-gene-repeat`
192      (`left`, `right`, `count_k47`,`name_left`,`name_right`)
193      values(t_left, t_right, t_count, g_name, r_name)
194      on duplicate key update `count_k47`=t_count;
195   end if;
196 end while;
197end$$
198	
199ALTER TABLE `51k-80-80-ignore-random-noreverse` ADD PRIMARY KEY (id);
200	
201SELECT 
202     a.`chr`, a.`left`, a.`right`, a.`count` as `count_k51`,
203     g.`name` as `name_left`, 
204     r.`name` as `name_right` 
205FROM `
206     51k-80-80-ignore-random-noreverse a, 
207     `genes-g38-201505` g,
208     `repeats-g38-201505` r 
209WHERE
210     a.id > 3000 and a.id < 4000 and /* <- лимитирование обрабатываемых данных */
211     a.`chr`=g.`chr` and a.`chr`=r.`chr` and 
212     a.`left` < g.`right` and a.`left` > g.`left` and 
213     a.`right` < r.`right` and a.`right` > r.`left`;
214	
215ALTER TABLE `51k-80-80-ignore-random-noreverse` ADD KEY `BASE` (`chr`, `left`, `right`);
216ALTER TABLE `genes-g38-201505` ADD KEY `BASE` (`chr`, `left`, `right`);
217ALTER TABLE `repeats-g38-201505` ADD KEY `BASE` (`chr`, left`, `right`);