Hey list,
I am new to PostgreSQL, and it's been a while since I had to do some
complicated SQL queries. I have a table like so...
CREATE TABLE my_table
(
id SERIAL NOT NULL,
PRIMARY KEY(id),
some_value_1 float NOT NULL,
...
some_value_N float NOT NULL
);
The some_value_* fields contain floating point data and are at least a
dozen in number.
I would like to be able to perform queries on the table on the
normalized versions of these values. By normalized I don't mean in the
database nomenclature, but where all some_value_i's are in the range of
[0,1].
To do this, I must find the min() and max() of each row's some_value_i
within the table and divide each some_value_i by the absolute
difference of these two values.
As an example, if a row contained the lowest some_value_3 of -4.0 and
the largest row 1.5, then the normalized version of any some_value_3
field is some_value_3 / (1.5 - -4.0) or some_value_3 / 5.5.
I am having difficulty expressing this as a query to just list every
row in the table for starters (e.g. SELECT * FROM my_table;). I
considered creating a VIEW, my_table_normalized, but I'm not sure if
that is the appropriate strategy here.
The my_table table also contains a large number of rows. I am therefore
worried about every query on the normalized variant taking a while to
find the min() and the max() before it can do anything else.
Any help appreciated.
--
Kip Warner -- Senior Software Engineer
OpenPGP encrypted/signed mail preferred
http://www.thevertigo.com
