Weighting results

Thread replies: 11
Thread images: 1

Anonymous
Weighting results 2016-09-09 02:46:32 Post No. 56506015
[Report] Image search: [Google]

File: mysql-logo.jpg (75KB, 1020x426px) Image search: [Google]

Weighting results Anonymous 2016-09-09 02:46:32 Post No. 56506015 [Report]

Hey /g/,

I'm working on a system that pulls and ranks certain YouTube videos in order to find content with a high approval from the people watching but that isn't very popular.

I use the query
SELECT * FROM `videos` ORDER BY LIKECOUNT / VIEWS * (VIEWS / SUBSCRIBERS) * LIKERATIO DESC
, which does the job fairly well in terms of finding relatively underappreciated videos.

What I'd like to do is add some sort of small multiplier that gives larger channels preference over smaller ones.

I'd like a channel with, for example, 20k subs and 98% approval to be on the same scale as a channel with 1M with 96% and a channel with 30 subscribers with 100% overwhelming approval.

Is there some sort of math or something MySQL that would allow me to weight these results properly?

Anonymous 2016-09-09 02:49:53 Post No.56506058
[Report]

Anonymous 2016-09-09 02:49:53 Post No.56506058 [Report]

Also, I had no idea how impractical using the code tag is for one line

Anonymous 2016-09-09 02:51:53 Post No.56506083
[Report]

Anonymous 2016-09-09 02:51:53 Post No.56506083 [Report]

>>56506015
What about just dividing everything by some number?

Anonymous 2016-09-09 02:52:11 Post No.56506086
[Report]

Anonymous 2016-09-09 02:52:11 Post No.56506086 [Report]

>>56506015
you need to use column aliaseses
SELECT name, LIKECOUNT / VIEWS * (VIEWS / SUBSCRIBERS) * LIKERATIO AS secretsauce FROM `videos` ORDER BY secretsauce DESC
or something similar to this

Anonymous 2016-09-09 02:56:46 Post No.56506143
[Report]

Anonymous 2016-09-09 02:56:46 Post No.56506143 [Report]

>>56506086
Is there any way I could take that and weight it based on subscriber count? Also, all results are relative, so something like
(secretsauce*.90)+(subscribers*.10)
wouldn't work

Anonymous 2016-09-09 03:04:19 Post No.56506237
[Report]

Anonymous 2016-09-09 03:04:19 Post No.56506237 [Report]

>>56506143
what about (secretsauce * subscribers) / subscribers

Anonymous 2016-09-09 03:11:56 Post No.56506343
[Report]

Anonymous 2016-09-09 03:11:56 Post No.56506343 [Report]

>>56506237
They'd just cancel each other out, like (2 * 5) / 5, but diving it by subs times some sort of number to weight the sub count very lightly would be perfect

Anonymous 2016-09-09 03:24:27 Post No.56506511
[Report]

Anonymous 2016-09-09 03:24:27 Post No.56506511 [Report]

>>56506343
so like a weighted t-test or a ANOVA of some kind?

Anonymous 2016-09-09 05:11:21 Post No.56507678
[Report]

Anonymous 2016-09-09 05:11:21 Post No.56507678 [Report]

I don't have a solution, but I think what you're doing is cool, and when you've completed the software, I'd like to be able to use it if you wish to make it available. Do you have a github account or something I could watch for progress?

Anonymous 2016-09-09 05:31:50 Post No.56507892
[Report]

Anonymous 2016-09-09 05:31:50 Post No.56507892 [Report]

>>56507678
THIS

Anonymous 2016-09-09 05:33:36 Post No.56507913
[Report]

Anonymous 2016-09-09 05:33:36 Post No.56507913 [Report]

>>56506015
Each video should have a creator ID field of some sort. Then it becomes a matter of finding the ID in a list of users and getting the relevant channel stats. Probably need at least 2-3 tables for this and some JOINing.

I'm aware that Imgur.com will stop allowing adult images since 15th of May. I'm taking actions to backup as much data as possible. Read more on this topic here - https://archived.moe/talk/thread/1694/

I'm aware that Imgur.com will stop allowing adult images since 15th of May. I'm taking actions to backup as much data as possible.
Read more on this topic here - https://archived.moe/talk/thread/1694/