資料庫百科---實現多標籤篩選

一、背景

在一些視訊網站（如：優酷、愛奇藝等），都會有篩選功能。

但當我使用這個篩選功能時，發現其只能進行單型別篩選，例如電影型別只能按某一種型別進行篩選。如果想看既是科幻電影又是戰爭的電影呢？顯然單個標籤型別篩選是滿足不了這個需求的，接下來簡要談談我的實現方法。

二、SQL實現

1）建表

這裡我們需要建立三張表：標籤表、電影表、標籤電影關聯表。

-- 標籤表
create table TEST_LABEL
(
	LABEL_ID VARCHAR2(32) NOT NULL,
	LABEL_NAME VARCHAR2(64) NOT NULL
);
insert into TEST_LABEL(LABEL_ID, LABEL_NAME) values('L10001', '喜劇');
insert into TEST_LABEL(LABEL_ID, LABEL_NAME) values('L10002', '愛情');
insert into TEST_LABEL(LABEL_ID, LABEL_NAME) values('L10003', '戰爭');
insert into TEST_LABEL(LABEL_ID, LABEL_NAME) values('L10004', '驚悚');
insert into TEST_LABEL(LABEL_ID, LABEL_NAME) values('L10005', '恐怖');
insert into TEST_LABEL(LABEL_ID, LABEL_NAME) values('L10006', '冒險');
	
-- 視訊表
create table TEST_VIDEO
(
	VIDEO_ID INTEGER NOT NULL,
	VIDEO_NAME VARCHAR2(64) NOT NULL,
	VIDEO_DESC VARCHAR2(2000) NULL
);
insert into TEST_VIDEO(VIDEO_ID, VIDEO_NAME, VIDEO_DESC) values(1001, '視訊一', '視訊描述內容');
insert into TEST_VIDEO(VIDEO_ID, VIDEO_NAME, VIDEO_DESC) values(1002, '視訊二', '視訊描述內容');
insert into TEST_VIDEO(VIDEO_ID, VIDEO_NAME, VIDEO_DESC) values(1003, '視訊三', '視訊描述內容');
insert into TEST_VIDEO(VIDEO_ID, VIDEO_NAME, VIDEO_DESC) values(1004, '視訊四', '視訊描述內容');
insert into TEST_VIDEO(VIDEO_ID, VIDEO_NAME, VIDEO_DESC) values(1005, '視訊五', '視訊描述內容');


-- 視訊標籤關聯表
create table TEST_RELATION
(
	VIDEO_ID INTEGER NOT NULL,
	LABEL_ID VARCHAR2(32) NOT NULL
);
insert into TEST_RELATION(VIDEO_ID, LABEL_ID) values(1001, 'L10001');
insert into TEST_RELATION(VIDEO_ID, LABEL_ID) values(1001, 'L10002');
insert into TEST_RELATION(VIDEO_ID, LABEL_ID) values(1002, 'L10003');
insert into TEST_RELATION(VIDEO_ID, LABEL_ID) values(1002, 'L10007');
insert into TEST_RELATION(VIDEO_ID, LABEL_ID) values(1003, 'L10004');
insert into TEST_RELATION(VIDEO_ID, LABEL_ID) values(1003, 'L10005');
insert into TEST_RELATION(VIDEO_ID, LABEL_ID) values(1003, 'L10006');
insert into TEST_RELATION(VIDEO_ID, LABEL_ID) values(1004, 'L10004');
insert into TEST_RELATION(VIDEO_ID, LABEL_ID) values(1004, 'L10005');

2）SQL實現

我用的資料庫為oracle，這裡使用oracle的交集intersect實現該功能。

-- 按視訊ID排序
select f.VIDEO_ID, f.VIDEO_NAME, f.VIDEO_DESC, e.LABEL_NAME
  from (select c.VIDEO_ID, to_char(wm_concat(d.LABEL_NAME)) LABEL_NAME
          from (select a.VIDEO_ID
                  from TEST_RELATION a
                 where a.LABEL_ID = 'L10004'
                intersect
                select a.VIDEO_ID
                  from TEST_RELATION a
                 where a.LABEL_ID = 'L10005') b
          left join TEST_RELATION c
            on c.VIDEO_ID = b.VIDEO_ID
          left join TEST_LABEL d
            on d.LABEL_ID = c.LABEL_ID
         group by c.VIDEO_ID) e
  left join TEST_VIDEO f
    on f.VIDEO_ID = e.VIDEO_ID
 order by f.VIDEO_ID desc

-- 按視訊標籤數排序
select f.VIDEO_ID, f.VIDEO_NAME, f.VIDEO_DESC, e.LABEL_NAME
  from (select c.VIDEO_ID,
               to_char(wm_concat(d.LABEL_NAME)) LABEL_NAME,
               count(c.VIDEO_ID) TOTAL
          from (select a.VIDEO_ID
                  from TEST_RELATION a
                 where a.LABEL_ID = 'L10004'
                intersect
                select a.VIDEO_ID
                  from TEST_RELATION a
                 where a.LABEL_ID = 'L10005') b
          left join TEST_RELATION c
            on c.VIDEO_ID = b.VIDEO_ID
          left join TEST_LABEL d
            on d.LABEL_ID = c.LABEL_ID
         group by c.VIDEO_ID) e
  left join TEST_VIDEO f
    on f.VIDEO_ID = e.VIDEO_ID
 order by e.TOTAL desc

注：取交集部分可以使用Java根據選擇標籤數進行拼接。

3）結果

三、總結

通過使用取交集的方法獲得命中標籤的所有視訊ID，這樣我們就可以根據這些視訊ID很容易獲取其相關資訊了。SQL語句中使用了wm_concat函式，這裡可以換成listagg來實現同樣的功能。