| /* ----------------------------------------------------------------------------- |
| * Test linear-algebra operations. |
| * -------------------------------------------------------------------------- */ |
| |
| \set p 3 |
| |
| SELECT assert( |
| relative_error( |
| dist_pnorm( |
| ARRAY[-1.2, 0, 10, 5, 3, 9], |
| ARRAY[10.1, 43, 5.2, 13, 3, -10], |
| :p), |
| ((10.1 + 1.2)^(:p) + 43^(:p) + (10 - 5.2)^(:p) + (13 - 5)^(:p) + (9 + 10)^(:p))^(1./:p) |
| ) < 1e-5, |
| 'Incorrect 3-norm' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| dist_pnorm( |
| ARRAY[-1.2, 0, 10, 5, 3, 9], |
| ARRAY[10.1, 43, 5.2, 13, 3, -10], |
| 'Inf'::float8), |
| 43 |
| ) < 1e-5, |
| 'Incorrect infinity-norm' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| dist_inf_norm( |
| ARRAY[-1.2, 0, 10, 5, 3, 9], |
| ARRAY[10.1, 43, 5.2, 13, 3, -10]), |
| 43 |
| ) < 1e-5, |
| 'Incorrect infinity-norm' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| norm2(ARRAY[-1.2, 0, 10, 5, 3, 9]), |
| sqrt( 1.2^2 + 10^2 + 5^2 + 3^2 + 9^2) |
| ) < 1e-5, |
| 'Incorrect 2-norm' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| norm1(ARRAY[-1.2, 0, 10, 5, 3, 9]), |
| ( 1.2 + 10 + 5 + 3 + 9) |
| ) < 1e-5, |
| 'Incorrect 1-norm' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| dist_norm1( |
| ARRAY[-1.2, 0, 10, 5, 3, 9], |
| ARRAY[10.1, 43, 5.2, 13, 3, -10]), |
| (10.1 + 1.2) + 43 + (10 - 5.2) + (13 - 5) + (9 + 10) |
| ) < 1e-5, |
| 'Incorrect distance w.r.t. 1-norm' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| dist_norm2( |
| ARRAY[-1.2, 0, 10, 5, 3, 9], |
| ARRAY[10.1, 43, 5.2, 13, 3, -10]), |
| sqrt((10.1 + 1.2)^2 + 43^2 + (10 - 5.2)^2 + (13 - 5)^2 + (9 + 10)^2) |
| ) < 1e-5, |
| 'Incorrect distance w.r.t. 2-norm' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| cosine_similarity( x ,y), |
| ((10.1 * -1.2) + (10 * 5.2) + (13 * 5) + (3 * 3) + (9 * -10)) / |
| (norm2(x) * norm2(y)) |
| ) < 1e-5, |
| 'Incorrect cosine similarity score' |
| ) FROM ( |
| SELECT ARRAY[-1.2, 0, 10, 5, 3, 9] AS x, |
| ARRAY[10.1, 43, 5.2, 13, 3, -10] AS y |
| ) AS ignored; |
| |
| SELECT assert( |
| relative_error( |
| squared_dist_norm2( |
| ARRAY[-1.2, 0, 10, 5, 3, 9], |
| ARRAY[10.1, 43, 5.2, 13, 3, -10]), |
| (10.1 + 1.2)^2 + 43^2 + (10 - 5.2)^2 + (13 - 5)^2 + (9 + 10)^2 |
| ) < 1e-5, |
| 'Incorrect squared distance w.r.t. 2-norm' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| dist_angle(x, y), |
| acos( |
| ((10.1 * -1.2) + (10 * 5.2) + (13 * 5) + (3 * 3) + (9 * -10)) / |
| (norm2(x) * norm2(y)) |
| ) |
| ) < 1e-5, |
| 'Incorrect angle' |
| ) FROM ( |
| SELECT ARRAY[-1.2, 0, 10, 5, 3, 9] AS x, |
| ARRAY[10.1, 43, 5.2, 13, 3, -10] AS y |
| ) AS ignored; |
| |
| SELECT assert( |
| relative_error( |
| dist_tanimoto(x, y), |
| ( |
| 1. - dot / (norm2(x)^2 + norm2(y)^2 - dot) |
| ) |
| ) < 1e-5, |
| 'Incorrect Tanimoto distance' |
| ) FROM ( |
| SELECT ARRAY[-1.2, 0, 10, 5, 3, 9] AS x, |
| ARRAY[10.1, 43, 5.2, 13, 3, -10] AS y, |
| ((10.1 * -1.2) + (10 * 5.2) + (13 * 5) + (3 * 3) + (9 * -10)) AS dot |
| ) AS ignored; |
| |
| SELECT assert( |
| dist1 = dist2 AND dist2 = dist3, |
| 'Incorrect closest column.' |
| ) FROM ( |
| SELECT |
| squared_dist_norm2(matrix[column_id + lb:column_id + lb], x) AS dist1, |
| distance AS dist2, |
| least AS dist3, |
| * |
| FROM ( |
| SELECT |
| (closest_column(matrix, x)).*, |
| least( |
| squared_dist_norm2(a, x), |
| squared_dist_norm2(b, x), |
| squared_dist_norm2(c, x), |
| squared_dist_norm2(d, x) |
| ), |
| array_lower(matrix, 2) AS lb, |
| * |
| FROM ( |
| SELECT |
| ARRAY[a, b, c, d] AS matrix, |
| * |
| FROM ( |
| SELECT |
| ARRAY[ 1.2, 4.5, -1.6, 9.2, 100.3, 34.3] AS a, |
| ARRAY[-3.1, -5.4, 6.2, 10.2, 59.2, -8.2] AS b, |
| ARRAY[42 , 32 , 3.1, 8.1, 24.3, 10.3] AS c, |
| ARRAY[-5.1, 12.2, 3.9, -6.4, 39.9, -4.9] AS d, |
| ARRAY[1.2, 5, 6.4, -5, 56, 0] AS x |
| ) AS ignored |
| ) AS ignored |
| ) AS ignored |
| ) AS ignored; |
| |
| SELECT |
| closest_columns( |
| ARRAY[ |
| ARRAY[0,0], |
| ARRAY[0,1], |
| ARRAY[1,0], |
| ARRAY[0,1] |
| ]::DOUBLE PRECISION[][], |
| ARRAY[.5,.5]::DOUBLE PRECISION[], |
| 2::INTEGER |
| ); |
| |
| /* All values in the following test are exact sums of powers of 2, so |
| * floating-point arithmetic needs to be exact. */ |
| SELECT assert( |
| (c1).column_ids = ARRAY[0,1]::INTEGER[] AND |
| (c1).distances = ARRAY[0.5,0.5]::DOUBLE PRECISION[] AND |
| |
| (c2).column_ids = ARRAY[0,1]::INTEGER[] AND |
| (c2).distances = ARRAY[0.125,0.625]::DOUBLE PRECISION[] AND |
| |
| (c3).column_ids = ARRAY[1,0]::INTEGER[] AND |
| (c3).distances = ARRAY[0.125,0.625]::DOUBLE PRECISION[] AND |
| |
| (c4).column_ids = ARRAY[3,0]::INTEGER[] AND |
| (c4).distances = ARRAY[0.125,0.625]::DOUBLE PRECISION[] AND |
| |
| (c5).column_ids = ARRAY[0,3]::INTEGER[] AND |
| (c5).distances = ARRAY[0.3125,0.3125]::DOUBLE PRECISION[], |
| |
| 'Incorrect closest columns.') |
| FROM ( |
| SELECT |
| closest_columns(matrix, ARRAY[.5,.5]::DOUBLE PRECISION[], num) AS c1, |
| closest_columns(matrix, ARRAY[.25,.25]::DOUBLE PRECISION[], num) AS c2, |
| closest_columns(matrix, ARRAY[.75,.25]::DOUBLE PRECISION[], num) AS c3, |
| closest_columns(matrix, ARRAY[.25,.75]::DOUBLE PRECISION[], num) AS c4, |
| closest_columns(matrix, ARRAY[.25,.5]::DOUBLE PRECISION[], num) AS c5 |
| FROM ( |
| SELECT |
| ARRAY[ |
| ARRAY[0,0], |
| ARRAY[1,0], |
| ARRAY[1,1], |
| ARRAY[0,1] |
| ]::DOUBLE PRECISION[][] AS matrix, |
| 2 AS num, |
| 'squared_dist_norm2'::REGPROC AS metric |
| ) AS ignored |
| ) AS ignored; |
| |
| |
| CREATE TABLE some_vectors ( |
| id SERIAL, |
| x FLOAT8[] |
| ); |
| |
| INSERT INTO some_vectors(x) VALUES |
| (ARRAY[1,0,0,0]), |
| (ARRAY[0,1,0,0]), |
| (ARRAY[0,0,1,0]), |
| (ARRAY[0,0,0,2]); |
| |
| SELECT assert( |
| relative_error(avg(x), ARRAY[1./4., 1./4., 1./4., 2./4.]) < 1e-5, |
| 'Incorrect average of vectors' |
| ) |
| FROM some_vectors; |
| |
| SELECT assert( |
| relative_error(normalized_avg(x), ARRAY[1./2., 1./2., 1./2., 1./2.]) < 1e-5, |
| 'Incorrect normalized average of vectors' |
| ) |
| FROM some_vectors; |
| |
| SELECT assert( |
| matrix_column(matrix, 0) = ARRAY[1,2]::DOUBLE PRECISION[] AND |
| matrix_column(matrix, 1) = ARRAY[3,4]::DOUBLE PRECISION[], |
| 'Vector returned does not match column in matrix.') |
| FROM ( |
| SELECT ARRAY[ARRAY[1,2],ARRAY[3,4]]::DOUBLE PRECISION[][] AS matrix |
| ) ignored; |
| |
| SELECT assert(array_dims(a) = '[1:2]' AND a = ARRAY[3,4]::float8[], |
| 'Wrong results from index_2d_array') |
| FROM ( |
| SELECT index_2d_array(ARRAY[ARRAY[1,2],ARRAY[3,4]]::float8[], 2) AS a |
| ) subq; |
| |
| --------------------------------------------------------------------------- |
| SELECT * FROM deconstruct_2d_array(array[[1,2],[5,6]]) as k(id int, a float8, b float8); |
| SELECT * FROM deconstruct_2d_array(array[[2],[5]]) as k(id int, a float8, b float8); |
| SELECT * FROM deconstruct_2d_array(array[[5,6]]) as k(id int, a float8, b float8); |
| |
| --------------------------------------------------------------------------- |
| -- user doc examples |
| --------------------------------------------------------------------------- |
| CREATE TABLE two_vectors( |
| id integer, |
| a float8[], |
| b float8[]); |
| INSERT INTO two_vectors VALUES |
| (1, '{3,4}', '{4,5}'), |
| (2, '{1,1,0,-4,5,3,4,106,14}', '{1,1,0,6,-3,1,2,92,2}'); |
| |
| SELECT |
| id, |
| assert(abs(norm1(a) - 138) < 1e-6, 'Wrong answer: norm1() example'), |
| assert(abs(norm2(a) - 107.238052947636) < 1e-6, 'Wrong answer: norm2() example') |
| FROM two_vectors |
| WHERE id = 2; |
| |
| SELECT |
| id, |
| assert(abs(dist_norm1(a, b) - 48) < 1e-6, 'Wrong answer: dist_norm1() example'), |
| assert(abs(dist_norm2(a, b) - 22.6274169979695) < 1e-6, 'Wrong answer: dist_norm2() example'), |
| assert(abs(dist_pnorm(a, b, 5) - 15.585086360695) < 1e-6, 'Wrong answer: dist_pnorm() example'), |
| assert(abs(dist_inf_norm(a, b) - 14) < 1e-6, 'Wrong answer: dist_inf_norm() example'), |
| assert(abs(squared_dist_norm2(a, b) - 512) < 1e-6, 'Wrong answer: squared_dist_norm2() example'), |
| assert(abs(dist_angle(a, b) - 0.17106899659286) < 1e-6, 'Wrong answer: dist_angle() example'), |
| assert(abs(dist_tanimoto(a, b) - 0.0498733684005455) < 1e-6, 'Wrong answer: dist_tanimoto() example') |
| FROM two_vectors |
| WHERE id = 2; |
| |
| --------------------------------------------------------------------------- |
| CREATE TABLE matrix( |
| id integer, |
| m float8[]); |
| INSERT INTO matrix VALUES |
| (1, '{{4,5},{3,5},{9,0}}'); |
| |
| SELECT |
| get_row(m, 1) AS row_1, |
| get_row(m, 2) AS row_2, |
| get_row(m, 3) AS row_3, |
| get_col(m, 1) AS col_1, |
| get_col(m, 2) AS col_2 |
| FROM matrix; |
| |
| --------------------------------------------------------------------------- |
| CREATE TABLE vector( |
| id integer, |
| v float8[]); |
| INSERT INTO vector VALUES |
| (1, '{4,3}'), |
| (2, '{8,6}'), |
| (3, '{12,9}'); |
| |
| SELECT |
| avg(v), |
| normalized_avg(v), |
| matrix_agg(v) |
| FROM vector; |
| ------------------------------------------------------------------------- |
| |
| -- adding 1.0 on left and right to avoid division by zero |
| SELECT assert( |
| relative_error( |
| 1.0 + dist_jaccard(ARRAY['a', 'b', 'c']::TEXT[], ARRAY['a', 'b', 'c']::TEXT[]), |
| 1.0 + 0.0) < 1e-5, |
| 'Incorrect Jaccard distance' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| 1.0 + dist_jaccard(ARRAY['a', 'a', 'a', 'b', 'c']::TEXT[], |
| ARRAY['a', 'b', 'c']::TEXT[]), |
| 1.0 + 0.0) < 1e-5, |
| 'Incorrect Jaccard distance' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| dist_jaccard(ARRAY['a', 'a', 'e', 'e', 'f', 'b', 'c']::TEXT[], |
| ARRAY['a', 'b', 'c']::TEXT[]), |
| 0.4) < 1e-5, |
| 'Incorrect Jaccard distance' |
| ); |
| |
| -- Jaccard distance is not suitable for sets with floating point values |
| SELECT assert( |
| relative_error( |
| dist_jaccard(ARRAY[0.4, 1, 0.5]::TEXT[], |
| ARRAY[0.4, 0.1, 0.5]::TEXT[]), |
| 0.5) < 1e-5, |
| 'Incorrect Jaccard distance' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| dist_jaccard(ARRAY[1, 2, 3, 4]::TEXT[], |
| '{}'::TEXT[]), |
| 1.0) < 1e-5, |
| 'Incorrect Jaccard distance' |
| ); |
| |
| SELECT assert( |
| relative_error( |
| 1.0 + dist_jaccard('{}'::TEXT[], |
| '{}'::TEXT[]), |
| 1.0 + 0.0) < 1e-5, |
| 'Incorrect Jaccard distance' |
| ); |
| |
| SELECT assert( |
| dist_jaccard(ARRAY[1, 2, 3, 4]::TEXT[], |
| NULL::TEXT[]) is NULL, |
| 'Incorrect Jaccard distance' |
| ); |
| ------------------------------------------------------------------------- |
