CREATE TABLE POST (
ID DECIMAL(20,0), -- AUTOINCREMENT
CONTENT VARCHAR2(1024)
);
CREATE TABLE TAG (
ID DECIMAL(20,0), -- AUTOINCREMENT
TAG VARCHAR2(100)
);
CREATE TABLE POST_TAG (
POST_ID DECIMAL(20,0),
TAG_ID DECIMAL(20,0)
);
-- FOREIGN KEY FK_POST_TAG_POST ON POST(ID);
-- FOREIGN KEY FK_POST_TAG_TAG ON TAG(ID);
CREATE UNIQUE INDEX UQ_POST ON POST(ID);
CREATE UNIQUE INDEX UQ_TAG ON TAG(ID);
CREATE INDEX IDX_POST_TAG ON POST_TAG(POST_ID, TAG_ID);
CREATE INDEX IDX_POST_TAG_R ON POST_TAG(TAG_ID, POST_ID);
INSERT INTO TAG(ID, TAG) VALUES(1, 'foo');
INSERT INTO TAG(ID, TAG) VALUES(2, 'boo');
INSERT INTO TAG(ID, TAG) VALUES(3, 'goo');
INSERT INTO TAG(ID, TAG) VALUES(4, 'doo');
INSERT INTO TAG(ID, TAG) VALUES(5, 'too');
INSERT INTO POST(ID, CONTENT) VALUES(1, '...');
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(1, 1 /* foo */);
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(1, 2 /* boo */);
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(1, 3 /* goo */);
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(1, 4 /* doo */);
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(1, 5 /* too */);
INSERT INTO POST(ID, CONTENT) VALUES(2, '...');
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(2, 2 /* boo */);
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(2, 3 /* goo */);
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(2, 5 /* too */);
INSERT INTO POST(ID, CONTENT) VALUES(3, '...');
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(3, 1 /* foo */);
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(3, 5 /* too */);
INSERT INTO POST(ID, CONTENT) VALUES(4, '...');
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(4, 4 /* doo */);
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(4, 5 /* too */);
INSERT INTO POST(ID, CONTENT) VALUES(5, '...');
INSERT INTO POST_TAG(POST_ID, TAG_ID) VALUES(5, 5 /* too */);
SELECT POST.ID, TAG.TAG
FROM POST, TAG, POST_TAG
WHERE POST.ID = POST_TAG.POST_ID AND TAG.ID = POST_TAG.TAG_ID
ORDER BY POST.ID, TAG.ID;
SELECT DISTINCT ID, POST_TAG_COUNT, POST_TAGS_MATCHED_COUNT, POST_TAG_COUNT - POST_TAGS_MATCHED_COUNT POST_TAGS_EXTRA
FROM (
SELECT
POST.ID,
( -- количество тегов всего
SELECT COUNT(POST_TAG.TAG_ID) CNT
FROM POST_TAG
WHERE POST_TAG.POST_ID = POST.ID
) POST_TAG_COUNT,
( -- количество найденных по точному совпадению
SELECT COUNT(POST_TAG.TAG_ID) CNT
FROM POST_TAG
JOIN TAG ON POST_TAG.TAG_ID = TAG.ID
WHERE TAG.TAG IN ('boo', 'too') AND POST_TAG.POST_ID = POST.ID
) POST_TAGS_MATCHED_COUNT
FROM POST
JOIN POST_TAG ON POST_TAG.POST_ID = POST.ID
JOIN TAG ON TAG.ID = POST_TAG.TAG_ID
WHERE TAG.TAG IN ('boo', 'too')
)
WHERE POST_TAGS_MATCHED_COUNT > 0
ORDER BY POST_TAGS_MATCHED_COUNT DESC, POST_TAG_COUNT - POST_TAGS_MATCHED_COUNT ASC;
Смысл запроса на выборку:
Найти общее количество тегов у постов и количество совпавших тегов, вывести в порядке убывания количества совпадений, затем в порядке возрастания «лишних тегов».
Если нужен вывод именно в таком порядке, то практически полный перебор
Базы в 100 гигов текста тоже под рукой пока нет. Не замерял. Вес (оценка сложности исполнения) запроса с индексами небольшой, вполне возможно — миллисекунды.
Это только поиск ID постов.
И надо на всякий случай лимит на запрос поставить.
Средний
Простой
Простой
