sql-server-2008 - 在存储过程中计算加权(贝叶斯)平均分数/索引？

Question

我有一个 MS SQL Server 2008 数据库，用于存储提供食物的地方(咖啡馆、餐馆、小餐馆等)。在连接到该数据库的网站上，人们可以从 1 到 3 的等级对地点进行评分。

在网站上有一个页面，人们可以在其中查看某个城市中排名前 25(评分最高)的地方的顶级列表。数据库结构看起来像这样(表中存储了更多信息，但这是相关信息):


一个地方位于一个城市中，投票被放置在一个地方。

到目前为止，我刚刚计算了每个地方的平均投票分数，我将某个地方的所有投票总和除以该地方的投票数，如下所示(伪代码):

vote_count = total number of votes for the place
vote_sum = total sum of all the votes for the place

vote_score = vote_sum/vote_count

如果一个地方没有选票，我还必须处理除以零。所有这些都是在存储过程中完成的，该存储过程获取我想要显示在顶部列表中的其他数据。这是当前获取投票得分最高的前 25 个位置的存储过程:

ALTER PROCEDURE [dbo].[GetTopListByCity]
    (
    @city_id Int
    )
AS
    SELECT TOP 25 dbo.Places.place_id, 
           dbo.Places.city_id,
           dbo.Places.place_name,
           dbo.Places.place_alias,
           dbo.Places.place_street_address,
           dbo.Places.place_street_number,
           dbo.Places.place_zip_code,
           dbo.Cities.city_name,
           dbo.Cities.city_alias,
           dbo.Places.place_phone,
           dbo.Places.place_lat,
           dbo.Places.place_lng,
           ISNULL(SUM(dbo.Votes.vote_score),0) AS vote_sum,
           (SELECT COUNT(*) FROM dbo.Votes WHERE dbo.Votes.place_id = dbo.Places.place_id) AS vote_count,
           COALESCE((CONVERT(FLOAT,SUM(dbo.Votes.vote_score))/(CONVERT(FLOAT,(SELECT COUNT(*) FROM dbo.Votes WHERE dbo.Votes.place_id = dbo.Places.place_id)))),0) AS vote_score

    FROM dbo.Places INNER JOIN dbo.Cities ON dbo.Places.city_id = dbo.Cities.city_id
    LEFT OUTER JOIN dbo.Votes ON dbo.Places.place_id = dbo.Votes.place_id
    WHERE dbo.Places.city_id = @city_id
    AND dbo.Places.hidden = 0
    GROUP BY dbo.Places.place_id,
             dbo.Places.city_id,
             dbo.Places.place_name,
             dbo.Places.place_alias,
             dbo.Places.place_street_address,
             dbo.Places.place_street_number,
             dbo.Places.place_zip_code,
             dbo.Cities.city_name,
             dbo.Cities.city_alias,
             dbo.Places.place_phone,
             dbo.Places.place_lat,
             dbo.Places.place_lng
    ORDER BY vote_score DESC, vote_count DESC, place_name ASC

    RETURN

正如您所看到的，它获取的不仅仅是投票分数 - 我需要有关该地点、所在城市等的数据。这很好用，但有一个大问题:投票分数太简单了，因为它没有考虑到投票数。用简单的计算方法，一个得一票得分为 3 的地方最终会比一个有十四票得分为 3 和一票得分为 2 的地方在列表中排名靠前:

3/1 = 3
(14*3 + 1*2) = 44/15 = 2.933333333333

为了解决这个问题，我一直在研究使用某种形式的加权平均/加权指数。我发现了一个看起来很有希望的真正贝叶斯估计的例子。它看起来像这样:

weighted rating (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C

where:

R = average for the place (mean) = (Rating)
v = number of votes for the place = (votes)
m = minimum number of votes required to be listed in the Top 25 (unsure how many, but somewhere between 2-5 seems realistic)
C = the mean vote across the whole database

当我尝试在存储过程中实现这个加权评级时，问题就开始了——它很快变得复杂，我陷入了括号和存储过程的松散跟踪。

现在我需要一些帮助来解决两个问题:

这是为我的网站计算加权指数的合适方法吗？

当在存储过程中实现时，这个(或其他合适的计算方法)会是什么样子？

Answer 1

我看不出你的计算有什么问题。但我可以看到你在做同样的事情很多次。我的建议将帮助您在一个地方进行聚合，然后选择非常容易。

;WITH CTE
(
    SELECT
        SUM(dbo.Votes.vote_score) AS SumOfVoteScore,
        COUNT(*) AS CountOfVotes,
        Votes.place_id
    FROM
        Votes
    GROUP BY
        Votes.place_id
)
 SELECT TOP 25 
    dbo.Places.place_id, 
    dbo.Places.city_id,
    dbo.Places.place_name,
    dbo.Places.place_alias,
    dbo.Places.place_street_address,
    dbo.Places.place_street_number,
    dbo.Places.place_zip_code,
    dbo.Cities.city_name,
    dbo.Cities.city_alias,
    dbo.Places.place_phone,
    dbo.Places.place_lat,
    dbo.Places.place_lng,
    ISNULL(CTE.SumOfVoteScore,0) AS vote_sum,
    CTE.CountOfVotes AS vote_count,
    COALESCE((CONVERT(FLOAT,CTE.SumOfVoteScore)/
    (CONVERT(FLOAT,CTE.CountOfVotes))),0) AS vote_score

FROM dbo.Places INNER JOIN dbo.Cities ON dbo.Places.city_id = dbo.Cities.city_id
LEFT JOIN CTE ON dbo.Places.place_id=CTE.place_id
WHERE dbo.Places.city_id = @city_id
AND dbo.Places.hidden = 0
GROUP BY dbo.Places.place_id,
         dbo.Places.city_id,
         dbo.Places.place_name,
         dbo.Places.place_alias,
         dbo.Places.place_street_address,
         dbo.Places.place_street_number,
         dbo.Places.place_zip_code,
         dbo.Cities.city_name,
         dbo.Cities.city_alias,
         dbo.Places.place_phone,
         dbo.Places.place_lat,
         dbo.Places.place_lng
ORDER BY vote_score DESC, vote_count DESC, place_name ASC

CTE 函数帮助我们重用计算。这样我们就不必使用 SUM(vote_score)和 SELECT COUNT(*) FROM Votes WHERE...多次。因此，当您选择计算时，很容易遵循。

我希望这有帮助

编辑

您不必在 CTE 中定义表列。此 CTE (SumOfVoteScore, CountOfVotes, place_id) AS效果和这个一样好 CTE AS .如果您使用递归 cte，则需要定义列。因为你是 union与另一部分。

供引用 here和 here你会发现一些关于 CTE 函数的信息