根据关键字的优先顺序返回匹配的大多数结果

Question

我构建了一个程序来索引文本文件中的关键字并将它们放入数据库。

我的表格很简单：

FILE_ID|Name
------------
1      | a.txt
2      | b.txt
3      | c.txt
KEYWORD_ID|FILE_ID|Hits
-----------------------
1         | 1     | 55
2         | 1     | 10
3         | 1     | 88
1         | 2     | 44
2         | 2     | 15
1         | 3     | 199
2         | 3     | 1
3         | 3     | 4

此表中没有主键。我觉得没有必要。

现在我想搜索哪个文件对某些关键字的点击率最高。

如果我只有一个关键字，这很容易：

select top 10 * 
from words 
where keyword_id=1
order by hits desc

假设我想搜索关键字为 1 和 3 的文件(两者都必须存在并且第一个关键字具有最高重要性(。几个小时后，我带着这个来了：

select top 10 k.* 
from 
(
   select file_id, 
     max(hits) as maxhits 
   from words 
   where keyword_id=3 
   group by file_id
) as x 
inner join keyword as k 
  on (k.file_id = x.file_id 
      and k.keyword=1) 
order by k.hits desc

如何做到这一点？特别是如果我想用N个关键字搜索。使用临时表并使用它会更好吗？

如果使用关键字 1 和 3 进行搜索，我希望按此顺序返回FILE_ID 3 和 1(因为关键字 1 file_id 3 的命中数更高(

Answer 1

不确定，但(根据您的评论(可能是您需要的？

(我使用了@scsimon答案中的表声明(

declare @words table (KEYWORD_ID int, [FILE_ID] int, HITS int)
insert into @words
values
(1,1,55),
(2,1,10),
(3,1,88),
(1,2,44),
(2,2,15),
(1,3,199),
(2,3,1),
(3,3,4)
select [FILE_ID] from (
    select *, row_number() over(partition by KEYWORD_ID order by HITS desc) rn from @words
    where KEYWORD_ID in(1,3)
)t
where rn = 1
order by hits desc

<</div> div class="one_answers">

假设要找到的所有相关关键字都存储在表 KTable 中，该表有两列ID和KEYWORD_ID

那么查询应该是

SELECT 
   FileID,
   SUM(Hits) NetHits,
   SUM(Hits/K.ID) WeightedHits
FROM
   Words w JOIN Ktable K 
    on w.KEYWORD_ID= K.KEYWORD_ID
GROUP BY FileID
HAVING count(1) = (SELECT COUNT(1) FROM Ktable )
ORDER BY 2 DESC,3 DESC

使用窗口函数的相同查询将是

SELECT 
DISTINCT
FileID,
NetHitsPerFile 
FROM 
(
SELECT 
   FileID,
   SUM(Hits) OVER (PARTITION BY FileID ORDER BY K.ID ASC) NetHitsPerFile,
   SUM(FileID) OVER(PARTITION BY K.ID) Files,
   SUM(Hits/K.ID) OVER (PARTITION BY FileID ORDER BY K.ID ASC) weightedHits
FROM
   Words w JOIN Ktable K 
    on w.KEYWORD_ID= K.KEYWORD_ID
)T
   WHERE Files= (SELECT COUNT(1) FROM Ktable)
   ORDER BY NetHitsPerFile, weightedHits

Answer 2

这是一种方式...如果您只想查看具有指定KEYWORD_ID的行，只需在底部添加该WHERE CLAUSE即可。INNER JOIN 通过检查非重复计数是否 = 关键字数，将FILE_ID限制为包含您指定的同时KEYWORD_ID的那些。因此，在下面的示例中，我们将结果集限制为 2 KEYWORD_ID，并检查以确保每个FILE_ID都有 2 个不同的KEYWORD_ID与 HAVING 子句相关联

。

declare @words table (KEYWORD_ID int, [FILE_ID] int, HITS int)
insert into @words
values
(1,1,55),
(2,1,10),
(3,1,88),
(1,2,44),
(2,2,15),
(1,3,199),
(2,3,1),
(3,3,4)
select top 10 w.* 
from @words w
inner join
    (select [FILE_ID]
    from @words
    where KEYWORD_ID in (1,3)
    group by [FILE_ID]
    having count(distinct KEYWORD_ID) = 2
    ) x on x.[FILE_ID] = w.[FILE_ID]
order by HITS desc

Answer 3

您可以在查询中使用带有领带的 top (n(，如下所示：

declare @n int = 10 --10 in your scenario
select top (@n) with ties *
from (
    select w.*, f.name from #words w inner join #files f
        on w.[FILE_ID] = f.[file_id]
    ) a 
    order by (row_number() over (partition by a.[file_id] order by hits desc)-1)/@n +1