Re: Query to return normalized floats

Hey Andreas,

I figured it out with the help of some folks on IRC. It turns out the
view schema was almost correct. It needed some adjustments, in
particular safe handling of divide by zero errors.

CREATE VIEW my_view AS
SELECT
id,
COALESCE((col1 - (SELECT MIN(col1) FROM my_table)) / NULLIF((SELECT MAX(col1) FROM my_table) - (SELECT MIN(col1) FROM my_table), 0), 0) AS col1_norm,
COALESCE((col2 - (SELECT MIN(col2) FROM my_table)) / NULLIF((SELECT MAX(col2) FROM my_table) - (SELECT MIN(col2) FROM my_table), 0), 0) AS col2_norm,
COALESCE((col3 - (SELECT MIN(col3) FROM my_table)) / NULLIF((SELECT MAX(col3) FROM my_table) - (SELECT MIN(col3) FROM my_table), 0), 0) AS col3_norm
FROM my_table GROUP_BY id;

Since my_table contains hundreds of thousands of rows, it was also
suggested to me to create indices for every column in my_table. e.g.

CREATE INDEX col1_index ON my_table(col1);
CREATE INDEX col2_index ON my_table(col2);
CREATE INDEX col3_index ON my_table(col3);

Hopefully that will not only work, but will also be efficient too.

--
Kip Warner -- Senior Software Engineer
OpenPGP encrypted/signed mail preferred
http://www.thevertigo.com