开发者

MySQL help, updating fields based on a calculation

I've been trying to work out this particular problem and though I can easily think of a sort of brute force PHP + MySQL solution, I want some guidance on solving this particular problem without iterating through fields with PHP.

So.. with that, here's the problem. I want to precalculate each room's size relative to all other rooms with a single query (or 3), such that rooms that are bigger than 66% of all other rooms can have a category filled in as Large, while rooms within a 33%-66% range are given Medium and the rest are considered Small.

I have a general idea of how to complete this, but I'm hoping that someone more adept as SQL queries could at least point me in the right direction.

The hardest part for me comes with being able to simultaneously update every field that fits the criteria of falling within a certain range :(.

Here's an example of the table

Rooms
    ID | Length | Width | Relative Size [Expected Values]
    ------------------------------------
    1  | 15     | 12    | Large
    2  | 15     | 12    | Large
    3  | 10     | 10    | Medium
    4  | 10     | 10    | Medium
    5  | 8      | 9     | Small
    6  | 8      | 8     | Small
    7  | 8      | 7     | Small
    8  | 10     | 9     | Medium

I'd be perfectly happ开发者_运维技巧y with a resources or clues that can assist me with this, but I've been going in circles.

Thanks for any attempts at helping me out.

Edit: I ended up going with a standard deviation approach, since it makes a lot more sense in this case, I'll post it as an answer.


I couldnt think of a way to do it in 1 query but here is my attempt to do it in 3, assuming size is Null before populating

Update table set size = 'Large' where ID in (select TOP 33 PERCENT ID from table order by length*width Desc)

Update table set size = 'Small' where ID in (select TOP 33 PERCENT ID from table order by length*width Asc)

Update table set size = 'Medium' where size = null
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜