Mysql percent based query

Question

Is this kind of mysql query possible?

  SELECT power 
    FROM ".$table."
   WHERE category IN ('convertible') 
     AND type = bwm40% 
     AND type = audi60% 
ORDER BY RAND()

Would go something like this: from all the cars, select the power of the ones that are convertible, but 40% of the selection would be bmw's and the other 60% audi's.

Can this be done with mysql?

Can't seem to make it work with the ideea bellow, gives me an error, here is how I tried it:

    $result = mysql_query("
SELECT power, torque FROM ".$table." 
WHERE category IN ('convertible') 
ORDER BY (case type when 'bmw' then 0.4 when 'audi' then 0.6) * RAND() DESC 
LIMIT ".$offset.", ".$rowsperpage."");

No variation on `ORDER BY RAND()` will work if you're paging as the `RAND()` values will be different each time you run the query to get the next page. — mu is too short, Jul 04 '11 at 00:03
Possible duplicate: [Mysql - LIMIT by Percentage?](http://stackoverflow.com/questions/1309137/mysql-limit-by-percentage) — Orbling, Jul 04 '11 at 00:16

mu is too short · Accepted Answer · 2011-07-04T00:00:27.020

2

You could try adjusting the randomness using a CASE:

SELECT power
FROM table
WHERE category IN ('convertible')
  AND type IN ('bwm', 'audi')
ORDER BY (case type when 'bwm' then Wbwm when 'audi' then Waudi) * RAND() DESC

Where Wbmw and Waudi are weighting factors. Then you'd add a LIMIT clause to chop off the results at your desired size. That won't guarantee your desired proportions but it might be good enough for your purposes.

You'd want to play with the weighting factors (Wbmw and Waudi above) a bit to get the results you want. The weighting factors would depend on frequencies of bwm and audi in your database so 0.2 and 0.8, for example, might work better. As Chris notes in the comments, 0.4 and 0.6 would only work if you have a 50/50 split between BMW and Audi. Putting the weights in a separate table would make this approach easier to maintain and the SQL would be prettier.

edited Jul 04 '11 at 00:00

answered Jul 03 '11 at 23:09

mu is too short

426,620
70
833
800

Ty ty , very interestin. I'm trying to give some proportions cause sometimes I have far too many bmw in my database than audis, and I just want a fair appearence on the site.... – webmasters Jul 03 '11 at 23:10
@webmasters: Sounds like you just want to mix things up a bit rather than achieve an exact 60/40 split (which might not even be possible depending on the bmw/audi distribution in your database). Using a simple weighting to fiddle with a distribution is a fairly common technique. You could also put your weights in a separate table to get better flexibility and avoid an ugly inlined CASE. – mu is too short Jul 03 '11 at 23:19
-1, This does not work unless the proportion of audis and bmws in the database is 50-50. Am I wrong? – Chris Cunningham Jul 03 '11 at 23:21
mu is too short, I'm kind of a newb, and you are right, want to mix things up, can you give me an example? – webmasters Jul 03 '11 at 23:22
@webmasters If you don't actually want an answer to the question you posted and you want to know "how to mix things up," you should modify your question to reflect that... – Chris Cunningham Jul 03 '11 at 23:24
@Chris: I did qualify it with "That won't guarantee your desired proportions but it might be good enough for your purposes." I don't think there is any way to guarantee the desired proportions unless you're willing to limit the size of the list based on the frequency of the `type` values in the database (i.e. 60/40 might not be possible at all). Do you have a better idea? – mu is too short Jul 03 '11 at 23:24
1

@mu is too short No, I don't have a better idea, but that isn't totally relevant to whether I think your post answers the question. If anything I find the answer somewhat misleading since in some situations this method will cause *less* variety rather than more. Others disagree! Sorry to sound argumentative; it's nothing personal! – Chris Cunningham Jul 03 '11 at 23:28
@Chris: I'm not throwing a hissy fit and the down vote gets me closer to a nice OCD friendly multiple of 5 rep anyway. I'm just wondering if there's a better way. I always preferred analysis and (especially) abstract algebra over stats anyway. – mu is too short Jul 03 '11 at 23:34
@mu is too short Agreed, I hope there is an excellent way. Also agreed on algebra vs stats! – Chris Cunningham Jul 03 '11 at 23:35
@mu, I'm trying to make your solution work, posted the sql in my question, can you check it please? ty very much – webmasters Jul 03 '11 at 23:55
@webmasters: (1) See my update on the weighting factors. (2) You might want to include the error message in your question. – mu is too short Jul 04 '11 at 00:01

score 0 · Answer 2 · edited May 23 '17 at 12:14

Doubt this can be done properly in a single statement. Personally I would:

Calculate the COUNT() for each car type, grab them together in a query.
Retrieve both car types separately using sub-queries with LIMIT set to the correct amount and offset based on the percentage desired (so if you want 20 results total, starting at 40, and BMW if 40%, then the limit would be 8 results starting at 16 - they need to be integer values)
Using a UNION to combine the results, ORDER BY RAND() to mix them together.

That's only two actual queries, one for the counts, one combined query for the results, you could combine them in a stored procedure if performance is that much of an issue.

You could combine them using a statement prepare/execute from the results - have a look at this method from a possible duplicate question.

Mysql percent based query

2 Answers2