· 8 years ago · Aug 12, 2018, 05:14 PM
1SQL to merge rows
2id | word | textblockid |sentence |position
3 5 | Fear | 5 | 1 | 1
4 8 | of | 5 | 1 | 2
5 6 | the | 5 | 1 | 3
6 7 | Dark | 5 | 1 | 4
7 9 | is | 5 | 1 | 5
8
9id | word | textblockid | sentence |position
10 10 | Fear of the Dark | 5 | 1 | 1
11 9 | is | 5 | 1 | 2
12
13id | word | textblockid |sentence |position
14 6 | the | 8 | 3 | 10
15 11 | sound | 8 | 3 | 21
16 8 | of | 8 | 3 | 12
17 6 | the | 8 | 3 | 13
18 7 | mountain | 8 | 3 | 14
19
20CREATE OR REPLACE FUNCTION merge_tokens(words varchar[], separator varchar)
21 RETURNS VOID AS
22$body$
23DECLARE
24 r record;
25 current_id integer;
26 ids integer[];
27 generated_word varchar := ''; -- you can initialize variables at declaration time. Saves additional assignment.
28
29BEGIN
30 -- get the ids and generate the word
31 RAISE NOTICE 'Getting ids and generating words';
32 generated_word := array_to_string(words, separator); -- 1 assignment is much cheaper. Also: no trim() needed.
33 ids := ARRAY
34 ( SELECT t.id
35 FROM (
36 SELECT row_number() OVER () AS rn, text
37 FROM (SELECT unnest(words) AS text) x) y
38 JOIN token t USING (text)
39 ORDER BY rn);
40 RAISE NOTICE 'Generated word: %', generated_word;
41
42 -- check if the don't exists to insert it
43 SELECT INTO current_id t.id FROM token t WHERE t.text = generated_word;
44 IF NOT FOUND THEN
45 RAISE NOTICE 'Word don''t exists';
46 INSERT INTO token(text) VALUES(generated_word)
47 RETURNING id
48 INTO current_id; --get the last value without additional query.
49 END IF;
50 RAISE NOTICE 'Word id: %', current_id;
51
52 -- select the records that will be updated
53 RAISE NOTICE 'Getting words to be updated.';
54 FOR r IN
55 SELECT textblockid, sentence, position, tokenid, rn
56 FROM
57 ( -- select the rows that are complete
58 SELECT textblockid, sentence, position, tokenid, rn, count(*) OVER (PARTITION BY grp) AS counting
59 FROM
60 ( -- match source with lookup table
61 SELECT source.textblockid, source.sentence, source.position, source.tokenid, source.rn, source.grp
62 FROM
63 ( -- select textblocks where words appears with row number to matching
64 SELECT tb.textblockid, tb.sentence, tb.position, tb.tokenid, grp
65 ,CASE WHEN grp > 0 THEN
66 row_number() OVER (PARTITION BY grp ORDER BY tb.textblockid, tb.sentence, tb.position)
67 END AS rn
68 FROM
69 ( -- create the groups to be used in partition by to generate the row numbers
70 SELECT tb.textblockid, tb.sentence, tb.position, tb.tokenid
71 ,SUM(CASE WHEN tb.tokenid = ids[1] THEN 1 ELSE 0 END) OVER (ORDER BY tb.textblockid, tb.sentence, tb.position) AS grp
72 FROM textblockhastoken tb
73 JOIN
74 ( --select the textblocks where the word appears
75 SELECT textblockid, sentence
76 FROM textblockhastoken tb
77 WHERE tb.tokenid = ids[1]
78 ) res USING (textblockid, sentence)
79 ) tb
80 ) source
81 -- create the lookup table to match positions
82 JOIN (SELECT row_number() OVER () as rn, id FROM unnest(ids) AS id) lookup USING (rn)
83 WHERE source.tokenid = lookup.id
84 ) merged
85 ) g
86 WHERE g.counting = array_length(ids,1)
87 ORDER BY g.rn --order by row number to update first, delete and change positions after
88 LOOP
89 --check if update or delete
90 IF (r.rn = 1) THEN
91 RAISE NOTICE 'Updating word in T:% S:% P:%', r.textblockid, r.sentence, r.position;
92 UPDATE textblockhastoken tb SET tokenid = current_id
93 WHERE (tb.textblockid, tb.sentence, tb.position)
94 = ( r.textblockid, r.sentence, r.position);
95 ELSE
96 RAISE NOTICE 'Deleting word in T:% S:% P:%', r.textblockid, r.sentence, r.position;
97 DELETE FROM textblockhastoken tb
98 WHERE (tb.textblockid, tb.sentence, tb.position)
99 = ( r.textblockid, r.sentence, r.position);
100 END IF;
101 --check if is the last word to update the positions
102 IF (r.rn = array_length(ids,1)) THEN
103 RAISE NOTICE 'Changing positions in T:% S:%', r.textblockid, r.sentence;
104 UPDATE textblockhastoken tb SET position = new_position
105 FROM
106 ( SELECT textblockid, sentence, position
107 ,row_number() OVER (PARTITION BY tb.textblockid, tb.sentence ORDER BY tb.position) as new_position
108 FROM textblockhastoken tb
109 WHERE tb.textblockid = r.textblockid AND tb.sentence = r.sentence
110 ) np
111 WHERE (tb.textblockid, tb.sentence, tb.position)
112 = (np.textblockid, np.sentence, np.position)
113 AND tb.position <> np.new_position;
114 END IF;
115 END LOOP;
116END;
117$body$ LANGUAGE plpgsql;
118
119set search_path='tmp';
120
121DROP TABLE wordlist;
122CREATE TABLE wordlist
123 ( id INTEGER NOT NULL PRIMARY KEY
124 , word varchar
125 , textblockid INTEGER NOT NULL
126 , sentence INTEGER NOT NULL
127 , postion INTEGER NOT NULL
128 , UNIQUE (textblockid,sentence,postion)
129 );
130
131INSERT INTO wordlist(id,word,textblockid,sentence,postion) VALUES
132 (5 , 'Fear', 5 , 1 , 1 )
133,(8 , 'of', 5 , 1 , 2 )
134,(6 , 'the', 5 , 1 , 3 )
135,(7 , 'Dark', 5 , 1 , 4 )
136,(9 , 'is', 5 , 1 , 5 )
137 ;
138
139WITH RECURSIVE meuk AS (
140 SELECT 0 AS lev
141 , id,word AS words
142 , textblockid,sentence,postion AS lastpos
143 FROM wordlist
144 UNION
145 SELECT 1+ mk.lev AS lev
146 , wl.id
147 , mk.words || ' '::text || wl.word AS words
148 , wl.textblockid,wl.sentence
149 , wl.postion AS lastpos
150 FROM meuk mk
151 JOIN wordlist wl ON (wl.textblockid = mk.textblockid
152 AND wl.sentence = mk.sentence
153 AND wl.postion = mk.lastpos+1)
154 )
155SELECT * FROM meuk
156WHERE lev = 3
157 ;
158
159SET
160DROP TABLE
161NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "wordlist_pkey" for table "wordlist"
162NOTICE: CREATE TABLE / UNIQUE will create implicit index "wordlist_textblockid_sentence_postion_key" for table "wordlist"
163CREATE TABLE
164INSERT 0 5
165 lev | id | words | textblockid | sentence | lastpos
166-----+----+------------------+-------------+----------+---------
167 3 | 7 | Fear of the Dark | 5 | 1 | 4
168 3 | 9 | of the Dark is | 5 | 1 | 5
169(2 rows)
170
171UPDATE token
172SET word = (
173 SELECT string_agg(word, ' ' ORDER BY position)
174 FROM token
175 WHERE id = ANY('{5,8,6,7}'::int[])
176 )
177 ,id = nextval('token_id_seq')
178WHERE id = ('{5,8,6,7}'::int[])[1];
179
180DELETE FROM token
181WHERE id = ANY('{5,8,6,7}'::int[])
182AND id <> ('{5,8,6,7}'::int[])[1];
183
184SELECT rn, t.*
185FROM (
186 SELECT id
187 ,row_number() OVER () AS rn
188 FROM (SELECT unnest('{5,8,6,7}'::int[]) id) x
189 ) x
190JOIN token t USING (id)
191ORDER BY rn;
192
193SELECT rn, t.*
194FROM (
195 SELECT rn
196 ,a[rn] AS id
197 FROM (SELECT '{5,8,6,7}'::int[] AS a
198 ,generate_series(1, array_upper('{5,8,6,7}'::int[], 1)) rn) x
199 ) x
200JOIN token t USING (id)
201ORDER BY rn;
202
203UPDATE token
204SET word = (
205 SELECT string_agg(word, ' ' ORDER BY rn)
206 FROM (
207 SELECT rn
208 ,a[rn] AS id
209 FROM (
210 SELECT '{5,8,6,7}'::int[] AS a
211 ,generate_series(1, array_upper('{5,8,6,7}'::int[], 1)) rn) x
212 ) x
213 JOIN token t USING (id)
214 )
215 ,id = nextval('token_id_seq')
216WHERE id = ('{5,8,6,7}'::int[])[1];
217
218CREATE OR REPLACE FUNCTION merge_tokens(words VARCHAR[], separator VARCHAR)
219RETURNS VOID
220AS $$
221DECLARE
222 r RECORD;
223 current_id INTEGER;
224 current_word VARCHAR;
225 ids INTEGER[];
226 generated_word VARCHAR;
227
228BEGIN
229 -- get the ids and generate the word
230 RAISE NOTICE 'Getting ids and generating words';
231 generated_word = '';
232 FOREACH current_word IN ARRAY words
233 LOOP BEGIN
234 generated_word = generated_word || current_word;
235 generated_word = generated_word || separator;
236 SELECT t.id INTO current_id FROM token t WHERE t.text = current_word;
237 ids = ids || current_id;
238 END;
239 END LOOP;
240
241 -- remove lead and ending spacing in word
242 RAISE NOTICE 'Generated word: %', generated_word;
243 generated_word = TRIM(generated_word);
244
245 -- check if the don't exists to insert it
246 SELECT t.id INTO current_id FROM token t WHERE t.text = generated_word;
247 IF (current_id IS NULL) THEN
248 RAISE NOTICE 'Word don''t exists';
249 INSERT INTO token(id,text) VALUES(nextval('tokenidsqc'),generated_word);
250 current_id = lastval(); --get the last value from the sequence
251 END IF;
252 RAISE NOTICE 'Word id: %', current_id;
253
254 -- select the records that will be updated
255 RAISE NOTICE 'Getting words to be updated.';
256 FOR r IN SELECT grouping.textblockid, grouping.sentence, grouping.position, grouping.tokenid, grouping.row_number
257 FROM
258 (
259 -- select the rows that are complete
260 SELECT merged.textblockid, merged.sentence, merged.position, merged.tokenid,merged.row_number,count(*) OVER w as counting
261 FROM
262 (
263 -- match source with lookup table
264 SELECT source.textblockid, source.sentence, source.position, source.tokenid,source.row_number, source.grp
265 FROM
266 ( -- select textblocks where words appears with row number to matching
267 SELECT tb.textblockid, tb.sentence, tb.position, tb.tokenid, grp,
268 CASE WHEN grp > 0 THEN
269 row_number() OVER (PARTITION BY grp ORDER BY tb.textblockid,tb.sentence,tb.position)
270 END AS row_number
271 FROM
272 ( -- create the groups to be used in partition by to generate the row numbers
273 SELECT tb.textblockid, tb.sentence, tb.position, tb.tokenid,
274 SUM(CASE WHEN tb.tokenid = ids[1] THEN 1 ELSE 0 END) OVER (ORDER BY tb.textblockid,tb.sentence,tb.position) AS grp
275 FROM textblockhastoken tb,
276 ( --select the textblocks where the word appears
277 SELECT textblockid, sentence
278 FROM textblockhastoken tb
279 WHERE tb.tokenid = ids[1]
280 )res
281 WHERE tb.textblockid = res.textblockid
282 AND tb.sentence = res.sentence
283 )tb
284 )source,
285 -- create the lookup table to match positions
286 (
287 SELECT row_number() OVER () as row_number,id FROM unnest(ids::INTEGER[]) as id
288 )lookup
289 WHERE source.tokenid = lookup.id
290 AND source.row_number = lookup.row_number
291 )merged
292 WINDOW w AS (PARTITION BY grp)
293 ) grouping
294 WHERE grouping.counting = array_length(ids,1)
295 ORDER BY grouping.row_number --order by row number to update first, delete and change positions after
296 -- end of query and start of iterations actions
297 LOOP BEGIN
298 --check if update or delete
299 IF (r.row_number = 1) THEN
300 RAISE NOTICE 'Updating word in T:% S:% P:%', r.textblockid, r.sentence, r.position;
301 UPDATE textblockhastoken tb SET tokenid = current_id
302 WHERE tb.textblockid = r.textblockid
303 AND tb.sentence = r.sentence
304 AND tb.position = r.position;
305 ELSE
306 RAISE NOTICE 'Deleting word in T:% S:% P:%', r.textblockid, r.sentence, r.position;
307 DELETE FROM textblockhastoken tb
308 WHERE tb.textblockid = r.textblockid
309 AND tb.sentence = r.sentence
310 AND tb.position = r.position;
311 END IF;
312 --check if is the last word to update the positions
313 IF (r.row_number = array_length(ids,1)) THEN
314 RAISE NOTICE 'Changing positions in T:% S:%', r.textblockid, r.sentence;
315 UPDATE textblockhastoken tb SET position = new_position
316 FROM
317 (
318 SELECT textblockid, sentence, position, row_number() OVER w as new_position
319 FROM textblockhastoken tb
320 WHERE tb.textblockid = r.textblockid AND tb.sentence = r.sentence
321 WINDOW w AS (PARTITION BY tb.textblockid, tb.sentence ORDER BY tb.position)
322 )new_positioning
323 WHERE tb.textblockid = new_positioning.textblockid
324 AND tb.sentence = new_positioning.sentence
325 AND tb.position = new_positioning.position
326 AND tb.position <> new_positioning.new_position;
327 END IF;
328 END;
329 END LOOP;
330END
331$$
332LANGUAGE plpgsql;
333
334DELETE FROM token WHERE id IN (5,8,6,7)