PostgreSQL 巧用 array 处理 tags

在许多系统中,会使用tag,或是属性.
我们将介绍传统的方式,以及PostgreSQL 的 array 两种方式.

传统方式

会建立三个table,一个用来放标的,一个用来放tag,一个用来放中介.

create table videos (  vid_id int not null generated always as identity primary key, vid_number text not null);create table tags (  tag_id int not null generated always as identity primary key, tag_name text not null, unique(tag_name));create table video_tags (  vid_id int not null, tag_id int not null, unique(vid_id, tag_id));--测试资料insert into videos(vid_number)values ('MEYD-844'), ('WAAA-293');insert into tags (tag_name)values ('熟女'),('人妻'),('巨乳'),('泳装'),       ('单体作品'),('高清'),('独家'),('4K'),       ('女教师'),('痴女'),('大屁股'),('中出');insert into video_tags values(1, 1), (1, 2), (1, 3), (1, 4),(1, 5), (1, 6), (1, 7), (1, 8),(2, 9), (2, 10),(2, 11), (2, 12),(2, 3), (2, 1), (2, 8), (2, 7);

查询的方式

一个 tag 的情况

select v.vid_number  from videos v  join video_tags vt    on (v.vid_id = vt.vid_id)  join tags t    on (vt.tag_id = t.tag_id) where t.tag_name = '女教师';   vid_number ------------ WAAA-293

两个 tag 的情况

select v.vid_number  from videos v  join video_tags vt1    on (v.vid_id = vt1.vid_id)  join video_tags vt2    on (vt1.vid_id = vt2.vid_id)  join tags t1    on (vt1.tag_id = t1.tag_id)  join tags t2    on (vt2.tag_id = t2.tag_id) where t1.tag_name = '熟女'   and t2.tag_name = '巨乳';    vid_number ------------ MEYD-844 WAAA-293(2 rows)

三个 tag 的情况

select v.vid_number  from videos v  join video_tags vt1    on (v.vid_id = vt1.vid_id)  join video_tags vt2    on (vt1.vid_id = vt2.vid_id)  join video_tags vt3    on (vt2.vid_id = vt3.vid_id)  join tags t1    on (vt1.tag_id = t1.tag_id)  join tags t2    on (vt2.tag_id = t2.tag_id)  join tags t3    on (vt3.tag_id = t3.tag_id) where t1.tag_name = '熟女'   and t2.tag_name = '巨乳'   and t3.tag_name = '泳装'; vid_number ------------ MEYD-844

由上面的例子可以看到,查询方式较为繁琐.

使用array的方式

这时只需要两个 table,tags 还是一样,将中介的 table video_tags
合併进 videos 产生新的 videos_array

create table videos_array (  vid_id int not null primary key, vid_number text not null, vid_tags int[]);insert into videos_arrayselect v.vid_id, vid_number, array_agg(tag_id)  from videos v  join video_tags  using (vid_id)  group by v.vid_id, vid_number;  select *  from videos_array; vid_id | vid_number |       vid_tags       --------+------------+----------------------      1 | MEYD-844   | {1,2,3,4,5,6,7,8}      2 | WAAA-293   | {1,3,7,8,9,10,11,12}(2 rows)--可以建立GIN indexcreate index on videos_array using gin (vid_tags);

查询的方式

---三个 tag 的情况with t1 as MATERIALIZED (select array_agg(tag_id) as tag_ids  from tags where tag_name in ('熟女', '巨乳', '泳装'))select va.vid_number  from videos_array va  join t1    on va.vid_tags @> t1.tag_ids; vid_number ------------ MEYD-844

当我们需要查询具有多个tag,只要善用array 的包含运算子 @>
以及 array_agg() 函数,透过 MATERIALIZED 型态 CTE(只计算一次),
即可方便撰写SQL,执行速度又快,因为资料笔数精简又不必透过中介table
多次join.

查询单一 video 的 tag_name

可以使用 unnest() 展开 array

select t.tag_name  from videos_array va  cross join unnest(va.vid_tags) as tag_id  join tags t using (tag_id) where va.vid_number = 'MEYD-844'; tag_name ---------- 熟女 人妻 巨乳 泳装 单体作品 高清 独家 4K(8 rows)

结语

PostgreSQL的Array,是一个成熟并具有弹性,可以让我们精简查询,减少空间,有效的反正规化,提高查询速度的好东西,可以多加利用.

致谢

感谢佐山爱小姐.

同步发表

本文同步发表于 https://hackmd.io/@pgsql-tw/rJSOHh8zT