Sql To Merge Rows
Update How I can select the rows from a table that when ordered the first element matches some row the second element matches the next row the third element the next row after th
Solution 1:
After answering most of your recent questions I have a vague idea of what you are doing. So I had a closer look at your solution and optimized quite a bit. Mostly I simplified the code, but there are some substantial improvements, too.
Some points:
- Don't use the undocumented assignment operator
=in plpgsql. Use:=instead. See this related question for more info. - Why
LOOP BEGIN? A separate code block only slows down if you don't need it. Removed it. - Many more, I added a few comments
Please have a look at the code side-by-side for some hints. Test the two versions to see which performs faster.
For your consideration:
CREATEOR REPLACE FUNCTION merge_tokens(words varchar[], separator varchar)
RETURNS VOID AS
$body$
DECLARE
r record;
current_id integer;
ids integer[];
generated_word varchar :=''; -- you can initialize variables at declaration time. Saves additional assignment.BEGIN-- get the ids and generate the word
RAISE NOTICE 'Getting ids and generating words';
generated_word := array_to_string(words, separator); -- 1 assignment is much cheaper. Also: no trim() needed.
ids :=ARRAY
( SELECT t.id
FROM (
SELECTrow_number() OVER () AS rn, text
FROM (SELECTunnest(words) AS text) x) y
JOIN token t USING (text)
ORDERBY rn);
RAISE NOTICE 'Generated word: %', generated_word;
-- check if the don't exists to insert itSELECTINTO current_id t.id FROM token t WHERE t.text = generated_word;
IF NOT FOUND THEN
RAISE NOTICE 'Word don''t exists';
INSERTINTO token(text) VALUES(generated_word)
RETURNING id
INTO current_id; --get the last value without additional query.END IF;
RAISE NOTICE 'Word id: %', current_id;
-- select the records that will be updated
RAISE NOTICE 'Getting words to be updated.';
FOR r INSELECT textblockid, sentence, position, tokenid, rn
FROM
( -- select the rows that are completeSELECT textblockid, sentence, position, tokenid, rn, count(*) OVER (PARTITIONBY grp) AS counting
FROM
( -- match source with lookup tableSELECT source.textblockid, source.sentence, source.position, source.tokenid, source.rn, source.grp
FROM
( -- select textblocks where words appears with row number to matchingSELECT tb.textblockid, tb.sentence, tb.position, tb.tokenid, grp
,CASEWHEN grp >0THENrow_number() OVER (PARTITIONBY grp ORDERBY tb.textblockid, tb.sentence, tb.position)
ENDAS rn
FROM
( -- create the groups to be used in partition by to generate the row numbersSELECT tb.textblockid, tb.sentence, tb.position, tb.tokenid
,SUM(CASEWHEN tb.tokenid = ids[1] THEN1ELSE0END) OVER (ORDERBY tb.textblockid, tb.sentence, tb.position) AS grp
FROM textblockhastoken tb
JOIN
( --select the textblocks where the word appearsSELECT textblockid, sentence
FROM textblockhastoken tb
WHERE tb.tokenid = ids[1]
) res USING (textblockid, sentence)
) tb
) source
-- create the lookup table to match positionsJOIN (SELECTrow_number() OVER () as rn, id FROMunnest(ids) AS id) lookup USING (rn)
WHERE source.tokenid = lookup.id
) merged
) g
WHERE g.counting = array_length(ids,1)
ORDERBY g.rn --order by row number to update first, delete and change positions after
LOOP
--check if update or delete
IF (r.rn =1) THEN
RAISE NOTICE 'Updating word in T:% S:% P:%', r.textblockid, r.sentence, r.position;
UPDATE textblockhastoken tb SET tokenid = current_id
WHERE (tb.textblockid, tb.sentence, tb.position)
= ( r.textblockid, r.sentence, r.position);
ELSE
RAISE NOTICE 'Deleting word in T:% S:% P:%', r.textblockid, r.sentence, r.position;
DELETEFROM textblockhastoken tb
WHERE (tb.textblockid, tb.sentence, tb.position)
= ( r.textblockid, r.sentence, r.position);
END IF;
--check if is the last word to update the positions
IF (r.rn = array_length(ids,1)) THEN
RAISE NOTICE 'Changing positions in T:% S:%', r.textblockid, r.sentence;
UPDATE textblockhastoken tb SET position = new_position
FROM
( SELECT textblockid, sentence, position
,row_number() OVER (PARTITIONBY tb.textblockid, tb.sentence ORDERBY tb.position) as new_position
FROM textblockhastoken tb
WHERE tb.textblockid = r.textblockid AND tb.sentence = r.sentence
) np
WHERE (tb.textblockid, tb.sentence, tb.position)
= (np.textblockid, np.sentence, np.position)
AND tb.position <> np.new_position;
END IF;
END LOOP;
END;
$body$ LANGUAGE plpgsql;
Solution 2:
This fragment does not use arrays. (I don't like arrays)
set search_path='tmp';
DROPTABLE wordlist;
CREATETABLE wordlist
( id INTEGERNOTNULLPRIMARY KEY
, word varchar
, textblockid INTEGERNOTNULL
, sentence INTEGERNOTNULL
, postion INTEGERNOTNULL
, UNIQUE (textblockid,sentence,postion)
);
INSERTINTO wordlist(id,word,textblockid,sentence,postion) VALUES
(5 , 'Fear', 5 , 1 , 1 )
,(8 , 'of', 5 , 1 , 2 )
,(6 , 'the', 5 , 1 , 3 )
,(7 , 'Dark', 5 , 1 , 4 )
,(9 , 'is', 5 , 1 , 5 )
;
WITHRECURSIVE meuk AS (
SELECT0AS lev
, id,word AS words
, textblockid,sentence,postion AS lastpos
FROM wordlist
UNIONSELECT1+ mk.lev AS lev
, wl.id
, mk.words ||' '::text || wl.word AS words
, wl.textblockid,wl.sentence
, wl.postion AS lastpos
FROM meuk mk
JOIN wordlist wl ON (wl.textblockid = mk.textblockid
AND wl.sentence = mk.sentence
AND wl.postion = mk.lastpos+1)
)
SELECT*FROM meuk
WHERE lev =3
;
results:
SETDROPTABLE
NOTICE: CREATETABLE/PRIMARY KEY will create implicit index "wordlist_pkey" fortable "wordlist"
NOTICE: CREATETABLE/UNIQUE will create implicit index "wordlist_textblockid_sentence_postion_key" fortable "wordlist"
CREATETABLEINSERT05
lev | id | words | textblockid | sentence | lastpos
-----+----+------------------+-------------+----------+---------3|7| Fear of the Dark |5|1|43|9|of the Dark is|5|1|5
(2rows)
Solution 3:
Best do this in one transaction:
UPDATE token
SET word = (
SELECT string_agg(word, ' 'ORDERBY position)
FROM token
WHERE id =ANY('{5,8,6,7}'::int[])
)
,id = nextval('token_id_seq')
WHERE id = ('{5,8,6,7}'::int[])[1];
DELETEFROM token
WHERE id =ANY('{5,8,6,7}'::int[])
AND id <> ('{5,8,6,7}'::int[])[1];
Replace '{5,8,6,7}'::int[] with your integer array parameter.
I get the new id from the sequence I assume exists.
I further assume that the ordering in array concurs with the ordering by position. Alternative version follows below.
id to be updated is the first element of the array.
Ordering of the words can be done inside the aggregate function (since PostgreSQL 9.0). Read about that in the manual.
Answer to additional question
Order selected rows according to sequence of array elements:
SELECT rn, t.*FROM (
SELECT id
,row_number() OVER () AS rn
FROM (SELECTunnest('{5,8,6,7}'::int[]) id) x
) x
JOIN token t USING (id)
ORDERBY rn;
Or ... does the same with different techniques, works in older versions of Postgres, too:
SELECT rn, t.*FROM (
SELECT rn
,a[rn] AS id
FROM (SELECT'{5,8,6,7}'::int[] AS a
,generate_series(1, array_upper('{5,8,6,7}'::int[], 1)) rn) x
) x
JOIN token t USING (id)
ORDERBY rn;
Combination
Use that in the UPDATE statement:
UPDATE token
SET word = (
SELECT string_agg(word, ' 'ORDERBY rn)
FROM (
SELECT rn
,a[rn] AS id
FROM (
SELECT'{5,8,6,7}'::int[] AS a
,generate_series(1, array_upper('{5,8,6,7}'::int[], 1)) rn) x
) x
JOIN token t USING (id)
)
,id = nextval('token_id_seq')
WHERE id = ('{5,8,6,7}'::int[])[1];
Solution 4:
Is this something you could do as part of your merge_tokens function? Seems like you could just have that function keep track of which records need to be updated/deleted, simply based on the provided array (first element updated, the rest deleted).
Solution 5:
This answer is for my particular case. I don't know if is the best way, but works for me.
I build this procedure with answer from these questions: Is possible have different conditions for each row in a query? and How create a WINDOW in PostgreSQL until the same value appears again?
The FOREARCH is only working in PostgreSQL 9.1.
CREATEOR REPLACE FUNCTION merge_tokens(words VARCHAR[], separator VARCHAR)
RETURNS VOID
AS $$
DECLARE
r RECORD;
current_id INTEGER;
current_word VARCHAR;
ids INTEGER[];
generated_word VARCHAR;
BEGIN-- get the ids and generate the word
RAISE NOTICE 'Getting ids and generating words';
generated_word ='';
FOREACH current_word INARRAY words
LOOP BEGIN
generated_word = generated_word || current_word;
generated_word = generated_word || separator;
SELECT t.id INTO current_id FROM token t WHERE t.text = current_word;
ids = ids || current_id;
END;
END LOOP;
-- remove lead and ending spacing in word
RAISE NOTICE 'Generated word: %', generated_word;
generated_word =TRIM(generated_word);
-- check if the don't exists to insert itSELECT t.id INTO current_id FROM token t WHERE t.text = generated_word;
IF (current_id ISNULL) THEN
RAISE NOTICE 'Word don''t exists';
INSERTINTO token(id,text) VALUES(nextval('tokenidsqc'),generated_word);
current_id = lastval(); --get the last value from the sequence END IF;
RAISE NOTICE 'Word id: %', current_id;
-- select the records that will be updated
RAISE NOTICE 'Getting words to be updated.';
FOR r INSELECT grouping.textblockid, grouping.sentence, grouping.position, grouping.tokenid, grouping.row_number
FROM
(
-- select the rows that are completeSELECT merged.textblockid, merged.sentence, merged.position, merged.tokenid,merged.row_number,count(*) OVER w as counting
FROM
(
-- match source with lookup tableSELECT source.textblockid, source.sentence, source.position, source.tokenid,source.row_number, source.grp
FROM
( -- select textblocks where words appears with row number to matchingSELECT tb.textblockid, tb.sentence, tb.position, tb.tokenid, grp,
CASEWHEN grp >0THENrow_number() OVER (PARTITIONBY grp ORDERBY tb.textblockid,tb.sentence,tb.position)
ENDAS row_number
FROM
( -- create the groups to be used in partition by to generate the row numbersSELECT tb.textblockid, tb.sentence, tb.position, tb.tokenid,
SUM(CASEWHEN tb.tokenid = ids[1] THEN1ELSE0END) OVER (ORDERBY tb.textblockid,tb.sentence,tb.position) AS grp
FROM textblockhastoken tb,
( --select the textblocks where the word appearsSELECT textblockid, sentence
FROM textblockhastoken tb
WHERE tb.tokenid = ids[1]
)res
WHERE tb.textblockid = res.textblockid
AND tb.sentence = res.sentence
)tb
)source,
-- create the lookup table to match positions
(
SELECTrow_number() OVER () as row_number,id FROMunnest(ids::INTEGER[]) as id
)lookup
WHERE source.tokenid = lookup.id
AND source.row_number = lookup.row_number
)merged
WINDOW w AS (PARTITIONBY grp)
) groupingWHERE grouping.counting = array_length(ids,1)
ORDERBY grouping.row_number --order by row number to update first, delete and change positions after-- end of query and start of iterations actions
LOOP BEGIN--check if update or delete
IF (r.row_number =1) THEN
RAISE NOTICE 'Updating word in T:% S:% P:%', r.textblockid, r.sentence, r.position;
UPDATE textblockhastoken tb SET tokenid = current_id
WHERE tb.textblockid = r.textblockid
AND tb.sentence = r.sentence
AND tb.position = r.position;
ELSE
RAISE NOTICE 'Deleting word in T:% S:% P:%', r.textblockid, r.sentence, r.position;
DELETEFROM textblockhastoken tb
WHERE tb.textblockid = r.textblockid
AND tb.sentence = r.sentence
AND tb.position = r.position;
END IF;
--check if is the last word to update the positions
IF (r.row_number = array_length(ids,1)) THEN
RAISE NOTICE 'Changing positions in T:% S:%', r.textblockid, r.sentence;
UPDATE textblockhastoken tb SET position = new_position
FROM
(
SELECT textblockid, sentence, position, row_number() OVER w as new_position
FROM textblockhastoken tb
WHERE tb.textblockid = r.textblockid AND tb.sentence = r.sentence
WINDOW w AS (PARTITIONBY tb.textblockid, tb.sentence ORDERBY tb.position)
)new_positioning
WHERE tb.textblockid = new_positioning.textblockid
AND tb.sentence = new_positioning.sentence
AND tb.position = new_positioning.position
AND tb.position <> new_positioning.new_position;
END IF;
END;
END LOOP;
END
$$
LANGUAGE plpgsql;
Post a Comment for "Sql To Merge Rows"