TSQL 匹配尽可能多的逗号分隔标签

2021-09-10 00:00:00 tsql sql-server

一个表包含一个 Title 字段和一个 Tags 字段.标签是通过潜在狄利克雷分配(LDA)从文档中生成的，可以是例如鱼、烤箱、时间"、烧烤、啤酒"或肉、烧烤".标签的长度不固定.

A table contains a Title field and a Tags field. The tags are generated via latent Dirichlet allocation (LDA) from documents and can be e.g. 'fish, oven, time', 'BBQ, beer' or ' meat, BBQ'. The length of the tags is not fixed.

给定一组标签，无论标签的顺序如何，如何找到匹配的标签数量最多的记录?

Given a set of tags, how to find the record with the maximum amount of tags matching no matter the order of the tags?

因此，如果给出BBQ, meat"，最好的结果应该是meat, BBQ".如果给出'BBQ,fish, cream'，则可以返回所有三个记录(它们都有一个匹配的标签).

So, if 'BBQ, meat' is given the best result should be 'meat, BBQ'. If 'BBQ, fish, cream' is given all three records can be returned (they all have one matching tag).

推荐答案

使用这个函数并创建这个>

Use this function and Create this one

    CREATE FUNCTION dbo.getCountOfMatch ( @mainString VARCHAR(MAX),  @searchString nvarchar(max))
    RETURNS
      INT
    AS
    BEGIN
        DECLARE @returnCount INT

        SELECT 
            @returnCount = COUNT(1)
        FROM 
            splitstring(@mainString) A INNER JOIN 
            splitstring(@searchString) B ON A.Name = B.Name

        RETURN @returnCount
    END

和

    SELECT TOP 1 // What you want
      Title,
      Tags
    FROM
    (
        SELECT
            A.Title,
            A.Tags,
            dbo.getCountOfMatch(A.Tags, @search) CountTags -- The number of matches.
        FROM
            TABLE A
    ) B
    ORDER BY B.CountTags DESC

更新

DECLARE @searchText NVARCHAR(MAX) = 'BBQ, meat'
DECLARE @query NVARCHAR(MAX) = '
        SELECT
            *
        FROM
            Table
        WHERE '

SELECT
    @query += 
    (
        SELECT
            'Tags like ''%' + A.Name + '%'' AND ' -- Dont forget trim!
        FROM 
            splitstring(@searchText) A
        FOR XML PATH ('')
    )

SELECT @query = LEFT(@query, LEN(@query) - 4) + 'ORDER BY LEN(Tags)' -- For exactly matching: LEN(Tags) = LEN(@searchText)

EXEC sp_executesql  @query

查询的样子；

    SELECT
        *
    FROM
        Table
    WHERE 
        Tags like '%BBQ%' AND 
        Tags like '%meat%'
    ORDER BY LEN(Tags)

相关文章