Postgresql Array Functions 应用

在 PTT Database 版看到这篇问题: https://www.ptt.cc/bbs/Database/M.1554802071.A.872.html试解答如下:create table ithelp190410 (  id int not null primary key, tagstr text not null);insert into ithelp190410 values(1, 'tag1;tag2;tag3'),(2, 'tag2;tag3;tag4'),(3, 'tag3;tag4;tag5;tag1');-- string_to_array()select id     , tagstr     , string_to_array(tagstr, ';') as stary  from ithelp190410;+----+---------------------+-----------------------+| id |       tagstr        |         stary         |+----+---------------------+-----------------------+|  1 | tag1;tag2;tag3      | {tag1,tag2,tag3}      ||  2 | tag2;tag3;tag4      | {tag2,tag3,tag4}      ||  3 | tag3;tag4;tag5;tag1 | {tag3,tag4,tag5,tag1} |+----+---------------------+-----------------------+(3 rows)-- unnest()select unnest(string_to_array(tagstr, ';')) as un_nest  from ithelp190410;+---------+| un_nest |+---------+| tag1    || tag2    || tag3    || tag2    || tag3    || tag4    || tag3    || tag4    || tag5    || tag1    |+---------+(10 rows)-- group byselect elem     , count(elem)  from (select unnest(string_to_array(tagstr, ';')) as elem          from ithelp190410) a group by elem order by elem; +------+-------+| elem | count |+------+-------+| tag1 |     2 || tag2 |     2 || tag3 |     3 || tag4 |     2 || tag5 |     1 |+------+-------+(5 rows)