MYSQL Weighing Results

Question

What is the typical way to weigh results in MYSQL?

I have a table with a column called 'category_id' which goes from 1-10.

How do I weigh results so a higher frequency of results with one category_id will show up vs. another?

If you have already thought of `GROUP BY` then I didn't understand your question at all. — hjpotter92, Apr 02 '12 at 19:26
@safarov No I meant GROUP BY. The user wants results regarding the frequency of occurrences. — hjpotter92, Apr 02 '12 at 19:28
I mean I want to return 100 results, but 50% of them should be category_id 1, 25% should be category_id and rest category_id 3-10 depending on what percentages I use — user1124535, Apr 02 '12 at 19:30

score 2 · Answer 1 · answered Apr 02 '12 at 19:30

2

if you want to favor, say, category 5, you can do something in the lines of:

select id, description
from your table
where yourcondition='conditionvalue'
order by 
    case category
       when 5 then 0
       else 1
    end, 
    category

answered Apr 02 '12 at 19:30

Adriano Carneiro

57,693
12
90
123

jeffery_the_wind · Accepted Answer · 2012-04-02T20:56:42.303

1

select category_id, count(*) 
from table 
group by category_id order by count(*) desc

assuming you are using a table named table

To get the weights like you were saying, you can use two queries:

set @a := (select count(*) from table);

select category_id, count(*)/@a from table group by category_id order by count(*) desc;

maybe this is what you are looking for:

(select * from table where category_id = 1 order by rand() limit 50)
union all
(select * from table where category_id = 2 order by rand() limit 25)
union all
(select * from table where category_id = 3 order by rand() limit 25)

this will give you 100 rows, split up 50/25/25 by category ids 1,2,3.

edited Apr 02 '12 at 20:56

answered Apr 02 '12 at 19:29

jeffery_the_wind

17,048
34
98
160

Thanks Jeffrey. I was actually not referring to finding the frequency. I was trying to figure out a way to write a query that would generate a certain frequency. – user1124535 Apr 02 '12 at 19:35
Jeff - this gives you the weights from the results. – user1124535 Apr 02 '12 at 19:38
Is there a way to write it such that 25% of results will always have category_id=1? – user1124535 Apr 02 '12 at 19:38
The last part I wrote there is not so robust I abmit, maybe gives you something to work with. Do you mean you want to RANDOMLY return results with 25% from category 1, 30% from category 2 ... – jeffery_the_wind Apr 02 '12 at 19:40
Thanks Jeff. Looks like may need to do 3 unions. – user1124535 Apr 02 '12 at 19:41
OK you can use the `order by rand()` clause to get random data, see answer. – jeffery_the_wind Apr 02 '12 at 19:47
@user1124535 - check my update with the `union all` syntax combines your results into to result set. – jeffery_the_wind Apr 02 '12 at 20:57

MYSQL Weighing Results

2 Answers2