Liknande med hjälp av nyckelord
Förord
Använd nyckelord för att hitta liknande entiteterInnehåll
»»
»
»
Problemet som ska lösas
Det finns många situationer där man har en entitet och vill hitta andra entiteter som liknar denna entitet. Som exempel kan vi ta artiklar. Varje artikel innehåller en massa text, och ska man hitta andra artiklar som liknar en viss artikel kan man förstås använda olika algoritmer för att jämföra texterna mot varandra och ranka hur lika de är. Men detta är förstås ingen enkel uppgift, och ofta kan det vara lite 'overkill' att implementera en sådan lösning. Här ska jag istället beskriva en enklare lösning, där man markerar varje artikel med ett eller flera nyckelord. För att sedan hitta liknande artiklar behöver man bara jämföra nyckelord.
Struktur
Exemplet handlar som sagt om artiklar. Därför har jag följande enkla tabeller (observera att här saknas alla primärnycklar, främmande nycklar, index och andra constraints man bör ha på tabeller):
CREATE TABLE ARTICLES
(
articleid int NOT NULL
, body varchar(8000) NOT NULL
)
CREATE TABLE KEYWORDS
(
keywordid int NOT NULL
, keyword varchar(25)
)
CREATE TABLE ARTICLEKEYWORDS
(
articleid int NOT NULL
, keywordid int NOT NULL
)
INSERT INTO ARTICLES (articleid,body) VALUES (1,'En artikel om någonting')
INSERT INTO ARTICLES (articleid,body) VALUES (2,'En annan artikel')
INSERT INTO ARTICLES (articleid,body) VALUES (3,'Ytterligare en traktor')
INSERT INTO ARTICLES (articleid,body) VALUES (4,'Artiklar, artiklar, artiklar')
INSERT INTO ARTICLES (articleid,body) VALUES (5,'Quidquid Latine dictum sit, altum viditur')
INSERT INTO KEYWORDS (keywordid,keyword) VALUES (1,'foo')
INSERT INTO KEYWORDS (keywordid,keyword) VALUES (2,'bar')
INSERT INTO KEYWORDS (keywordid,keyword) VALUES (3,'foobar')
INSERT INTO KEYWORDS (keywordid,keyword) VALUES (4,'barfoo')
INSERT INTO KEYWORDS (keywordid,keyword) VALUES (5,'chili')
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (1,1)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (1,3)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (1,5)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (2,2)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (3,2)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (3,3)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (3,4)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (3,5)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (4,1)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (4,5)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (5,2)
INSERT INTO ARTICLEKEYWORDS (articleid,keywordid) VALUES (5,3)
Med dessa tabeller på plats och 'fyllda' med data kan vi nu börja fundera på att hitta sätt att jämföra artiklar via nyckelord.
SQL-sats för att hitta liknande
Hur menar vi då att en artikel liknar en annan? Ett sätt är att ranka artiklarna efter hur många av deras nyckelord som även är nyckelord för den artikel vi utgår från. Om vi t ex utgår från artikel 4 (nyckelord 1 och 5) och vill hitta de artiklar som har så många som möjligt av dessa nyckelord kopplade till sig kan vi använda följande SELECT-sats:
DECLARE @articleid int
SET @articleid = 4
SELECT a.articleid, COUNT(ak.articleid) AS hitrate
FROM ARTICLES a
INNER JOIN ARTICLEKEYWORDS ak ON a.articleid = ak.articleid
WHERE ak.keywordid IN (SELECT keywordid FROM ARTICLEKEYWORDS WHERE articleid = @articleid)
AND a.articleid <> @articleid
GROUP BY a.articleid
ORDER BY hitrate DESC
Om vi bryter ner frågan i dess olika delar och tittar på dem så som man bör göra när man ska skriva en klurig SQL-sats får vi följande:
1. SELECT: Vilken data är vi ute efter? Vi vill ha artiklarnas id, samt hur många nyckelord de har gemensamt med den artikel vi utgår från. Vi kallar den andra kolumnen för hitrate eftersom ju fler gemensamma nyckelord, desto mer lik artikel.
2. FROM: Var får vi datan vi söker från? Först och främst behöver vi förstås artikeltabellen. Sedan måste vi joina mot tabellen med artiklar och nyckelord kombinerat för att kunna titta på nyckelord per artikel.
3. WHERE: Vilka rader är vi intresserade av? Nu ska vi se till att endast returnera de artiklar som liknar den artikel vi utgår från. Det gör vi genom filtrera bort alla nyckelord som ej är kopplade till utgångsartikeln. Nu har vi kvar de nyckelord som hör till utgångsartikeln samt de artiklar som har dessa nyckelord kopplade till sig. Dessutom måste vi naturligtvis filtrera bort den artikel vi utgick från, eftersom denna annars kommer med då den förstås har alla nyckelord vi jämför med.
4. GROUP BY: Istället för att returnera varje kombination av artikel och nyckelord på varsin rad så vill vi slå ihop alla rader för varje artikel, och bara räkna hur många rader det är. Det gör vi genom att gruppera på artikelid och använda en aggregerande funktion (COUNT) på resultatet för att räkna hur många rader som ingår i gruppen.
5. ORDER BY: Slutligen sorterar vi resultaten i fallande hitrate, dvs de med flest nyckelord gemensamma med utgångsartikeln kommer först.
Resultatet blir:
articleid hitrate
----------- -----------
1 2
3 1
Naturligtvis finns det andra varianter man kan använda för att säga att en artikel liknar en annan. Man kan ju t ex för varje artikel ge den en hitrate som inte enbart beror på hur många keywords den har gemensamt med utgångsartikeln, utan även räknar in antalet keywords som inte stämmer in och låter dessa sänka hitrate för en artikel. Man kan titta på både de nyckelord som finns på utgångsartikeln och/eller de nyckelord som ej finns på utgångsartikeln men finns till den artikel man jämför mot den. Men detta låter jag er själva testa er fram till, kom gärna med förslag så kan jag lägga till dem i artikeln senare.
0 Kommentarer