src/ports/postgres/modules/linalg/test/linalg.ic.sql_in

/* ----------------------------------------------------------------------- *//**
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 *//* ----------------------------------------------------------------------- */

/* -----------------------------------------------------------------------------
 * Test linear-algebra operations.
 * -------------------------------------------------------------------------- */

\set p 3

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
        );

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 matrix_column(matrix, 0), matrix_column(matrix, 1)
FROM (
    SELECT ARRAY[ARRAY[1,2],ARRAY[3,4]]::DOUBLE PRECISION[][] AS matrix
) ignored;

SELECT index_2d_array(ARRAY[ARRAY[1,2],ARRAY[3,4]]::float8[], 2);
---------------------------------------------------------------------------
SELECT * FROM deconstruct_2d_array(array[[1,2],[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,
    norm1(a),
    norm2(a)
FROM two_vectors
WHERE id = 2;

SELECT
    id,
    dist_norm1(a, b),
    dist_norm2(a, b),
    dist_pnorm(a, b, 5),
    dist_inf_norm(a, b),
    squared_dist_norm2(a, b),
    dist_angle(a, b),
    dist_tanimoto(a, b)
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 dist_jaccard(ARRAY['a', 'b', 'c']::TEXT[], ARRAY['a', 'b', 'c']::TEXT[]);

-------------------------------------------------------------------------