Re: Query to return normalized floats

On Sun, 2016-02-28 at 09:21 +0100, Andreas Kretschmer wrote:
> Kip Warner <kip(at)thevertigo(dot)com> wrote:
>
> > The some_value_* fields contain floating point data and are at
> > least a
> > dozen in number.
>
> maybe a wrong table design, but that's another question.

Hey Andreas. I am very sorry for taking so long to acknowledge and
respond to your thoughtful answer. The software I am working on had
another aspect of it that sucked me away for the last few weeks before
I could return to the database schema.

> > 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.
>
> simple example:
>
> test=*# select * from bla;
> id | col1 | col2
> ----+-------------------+--------------------
> 1 | -27.3061781190336 | 9.23637737520039
> 2 | -34.9188138730824 | 4.02728125452995
> 3 | 27.7425193693489 | -1.71850152313709
> 4 | 18.2173402048647 | 1.78571328520775
> 5 | -49.3932147044688 | 3.25902994722128
> 6 | -21.3868645019829 | 0.0395399890840054
> 7 | 48.6888256389648 | -0.219368590041995
> 8 | -26.943267416209 | -2.84633947536349
> 9 | -47.2060812171549 | 1.46993971429765
> 10 | -16.008263733238 | 5.15772333368659
> 11 | 12.651920504868 | 4.91552650928497
> 12 | 38.5760291945189 | 6.94333815015852
> 13 | -47.87487979047 | -4.18941779062152
> 14 | -2.24363747984171 | 0.686697596684098
> 15 | -2.6916132774204 | 7.83255377784371
> 16 | -4.97196828946471 | 4.0004417207092
> 17 | 35.3446557652205 | 2.0218435768038
> 18 | -9.86138512380421 | 2.61743502691388
> 19 | -48.4832897316664 | -8.32880898378789
> 20 | -27.9842584393919 | -9.57530088722706
> (20 rows)
>
> test=*# create view my_factor as select max(col1) - min(col1) as c1,
> max(col2) - min(col2) as c2 from bla;
> CREATE VIEW
> test=*# select id, col1 / f.c1, col2 / f.c2 from bla cross join
> my_factor f;
> id | ?column? | ?column?
> ----+---------------------+---------------------
> 1 | -0.278401407876724 | 0.490991672638172
> 2 | -0.356016389451263 | 0.214084102351125
> 3 | 0.282850145370229 | -0.0913529085052159
> 4 | 0.185735738582485 | 0.094925782819407
> 5 | -0.503590815724457 | 0.173245039690613
> 6 | -0.218050770835282 | 0.00210188525087517
> 7 | 0.496409184275543 | -0.0116612982096414
> 8 | -0.274701334942333 | -0.151307046381315
> 9 | -0.481291794622778 | 0.078139743503564
> 10 | -0.163212996764598 | 0.274176671625737
> 11 | 0.128993243417117 | 0.261301859446679
> 12 | 0.393303698204536 | 0.369097219997987
> 13 | -0.48811056155476 | -0.222703032242957
> 14 | -0.0228751101831245 | 0.0365037923307268
> 15 | -0.0274424682438878 | 0.416366560632055
> 16 | -0.0506919337327751 | 0.212657353847013
> 17 | 0.360358080250589 | 0.107478107407462
> 18 | -0.100542210268818 | 0.139138836546109
> 19 | -0.494313633381836 | -0.442746727197808
> 20 | -0.285314807291989 | -0.509008327361828
> (20 rows)
>
> test=*#
>
> is that okay?

Almost. I realized that the normalization is not calculated correctly
and should be calculated as...

norm(x) = (x - min)/(max - min)

This also ensures the values are in the range of [0.0, 1.0].

It would also be nice if the view contained the fully normalized
results. I've started with this...

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

The problem is when I try to see what it's populated with via...

SELECT * FROM my_view;

I get an ERROR: division by zero. I can see how that would happen if
MAX(x) - MIN(x) was zero, but I've made sure it wasn't from the sample
rows in my_table. I suspect my_view schema is not declared correctly.

> (you should define aliases for the columns...)

Yes. Hopefully I'm using the AS keyword correctly here.

> create indexes on the columns, so it should use indexes for the
> min/max.

Done.

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