使用权重因子对查询结果按多个列进行排序以归一化 [英] Sort Query Results by Multiple columns using a Weight Factor to Normalize

问题描述

我知道标题很复杂，但我得到的是这样：

I know the title is kinda complex, but what I'm getting at is this:

我有一个不同的广告横幅存储表，将显示在网页。我想提出一种方式，以最好的方式排序这些广告，以满足网站订阅者的需求。所以，我想到的一个例子是：

I have a table of different advertisement banners stored that will be displayed on a web page. I am trying to come up with a way to sort these ads in the best way possible to cater to the needs of the website subscribers. So, an example I have in mind is this:

表格有广告名称，目标观众，坐标，流行度和激励要显示。假设我想只显示与当前用户登录的目标受众群体相同的广告。我还想仅显示那些至少具有5的流行度和距离会员< 80英里。与会员较低的距离是好的，有较高的人气也是好的。我知道我可以通过降序流行和Ascending距离排序查询的结果。

The table has Ad Name, Target Audience, Coordinates, Popularity, and Incentive to Display. Let's say I want to display only the ads that are equal to the target audience that the current user logged in falls under. I also want to display only those events with at least a Popularity of 5 and a Distance from Member < 80 miles. Having a lower Distance from Member is good and Having a Higher Popularity is also good. I know I could just sort the results of the query by Descending Popularity and Ascending Distance.

但是，这不会是我想要做的理想。如果我刚刚描述的方式，我想我可能有一个结果出来的距离用户6英里的问题，但只有5的受欢迎程度的问题。我不想让这个结果是一个将显示第一个排序的查询结果。

However, this wouldn't be ideal for what I'm trying to do. If I did it the way just described, I think I could potentially have the problem where a result comes up that is 6 miles away from the user, but has a popularity of only 5. I don't want this result to be one of the first sorted query results to appear.

我认为会很酷的是使用归一化加权乘数来得出一个综合得分，通过它可以对查询结果进行排序。说，我想让流行度的权重因子为0.7，距离的权重因子为0.3。现在，查询的结果将按照最大综合得分Desc进行排序。我已经知道，对于这个工作距离，我必须采取最低距离除以所有的距离值在查询的结果，使实际最低距离将有一个分数1.这将是最高分这部分算法。然后，当然，1和所有其他距离值将乘以0.3，以使加权乘数生效。反之亦然的人气

What I think would be cool is to kind of come up with a composite score using a normalizing weighted multiplier by which to sort the query results. Say, I want the Popularity to be given a weight factor of 0.7 and the the Distance to be given a weight factor of 0.3. Now, the results of the query would be sorted by the largest composite score Desc. I already know that for this to work for distances, I have to take the lowest distance divided by all the distances values in the results of the query so that the actual lowest distance will have a score of 1. This will be the highest score for this part of the algorithm. Then, of course, that 1 and all the other distance values would be multiplied by 0.3 for the weighted multiplier to take effect. Vice versa sort of for the Popularity

我知道这听起来真的很复杂，但它必须以某种方式。我已经在Excel电子表格中与解决方案之前做过，但现在我必须将其翻译成SQL。任何和所有的反馈，非常感谢。

I know this sounds really complex, but it has to be possible somehow. I've done it in Excel spreadsheets with solver before, but now I have to translate it into SQL. Any and all feedback is greatly appreciated.

我预期的问题是从Ads表中获取坐标，并将其与登录到我的网页上的用户的当前位置进行比较。这必须在显示查询结果之前完成，因为查询不是询问坐标，而是询问与用户的距离。现在，我想想。这部分不应该太难以代码。

A problem I anticipate is taking the coordinates from the Ads table and comparing that with the current location of the user that is logged onto my webpage. This must be done before the query results can be displayed, since the query is not asking about the coordinates but instead the distance from the user. Now, that I think about it. This part shouldn't be too hard to code. Maybe I just need help with the syntax.

推荐答案

因此，如果你在[0 - 5]
距离，

So if you got rating between [0 - 5] and a distance,

所以你会按此排序？ （（popularity * 0.7）*（（distanceMAX-distance）* 0.3））

so you would sort by this? ((popularity*0.7)*((distanceMAX-distance)*0.3))

甚至可以将这些权重设置为服务器变量，在我的excel文档hehe）

maybe even set these weights to a server variable, this is just a suggestion (worked out in my excel document hehe)

select field1, field2, field3
from stuff
order by ((popularity*0.7)*((distanceMAX-distance)*0.3)) desc

这篇关于使用权重因子对查询结果按多个列进行排序以归一化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！