CODEWITHSUNDEEP
phpsqldatabasesorting
Connor
Connor

Reputation: 73

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:

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.

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.

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

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.

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.

Upvotes: 3

Views: 1507

Answers (2)

Christopher Bonitz
Christopher Bonitz

Reputation: 856

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

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

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

Upvotes: 2

Hass
Hass

Reputation: 1636

You could try something like this 

SELECT ad.*, ((ad.popularity * 0.7) + (ad.distance * 0.3)) AS relevance
FROM ad
WHERE popularity >= 5
ORDER BY relevance, popularity DESC

Untested but maybe it might help you experiment a little more?

Upvotes: 1

Related Questions