classify a float column into 10 equispaced bins

I have a column returning floating point data (+-ve and -ive)

Currently I was using a snippet of this sort to classify it into five
classes (denoted by "colorbit" taking integer values -2 through +2)

CASE
WHEN
( b.BE - a.BE ) < -0.2
THEN -2
WHEN
( b.BE - a.BE ) < 0
THEN -1
WHEN
( b.BE - a.BE ) < 0.2
THEN 1
ELSE
+2
END AS colorbit,

I wanted to make this more generic by, say, always having 10 equal-width
bins between MIN and MAX. So that colorbit takes (-5 through +5) depending
on what the value of ( b.BE - a.BE ) is. MIN and MAX, of course, are the
min and max of the ( b.BE - a.BE ) column.

How can I do this via a mySQL querey?

For the curious the "colorbit" is used by a plotting script down the
pipeline to color different records with different shades.



--
Rahul

>Currently I was using a snippet of this sort to classify it into five
>classes (denoted by "colorbit" taking integer values -2 through +2)
>
> CASE
> WHEN
> ( b.BE - a.BE ) < -0.2
> THEN -2
> WHEN
> ( b.BE - a.BE ) < 0
> THEN -1
> WHEN
> ( b.BE - a.BE ) < 0.2
> THEN 1
> ELSE
> +2
> END AS colorbit,

I don't see where you end up with a value of 0 here.

>I wanted to make this more generic by, say, always having 10 equal-width
>bins between MIN and MAX.

Are you sure you don't want 11 bins, so the middle one can be centered
around zero?

You can do a linear map of [MIN,MAX) to [0,9). Assuming that b.BE
- a.BE always ends up in the interval [MIN, MAX), (not including
the MAX endpoint) this formula should translate the values to an
integer in the range 0 .. 9 .

FLOOR(((b.BE - a.BE)-MIN)*10/(MAX-MIN))
This formula does have a problem that it might return 10 if b.BE -
a.BE is exactly MAX (depending on rounding error or lack thereof).
If you need to range-check for out-of-bounds values, two nested
calls to if(), one for each bound, will work.

if((b.BE-a.BE) >= MAX, 9, if((b.BE-a.BE) <= MIN, 0,
FLOOR(((b.BE - a.BE)-MIN)*10/(MAX-MIN))
))

(I hope I haven't messed up the parentheses here.)


If you want something else, like a range -5 .. +4, subtract 5.