如何编写等效于np.linalg.norm的SQL函数？数据库场景下的替代方案咨询

Hey there! Let's figure out how to replicate the functionality of NumPy's np.linalg.norm in your SQL database. First, a quick recap: np.linalg.norm calculates different types of vector norms—most commonly the L2 (Euclidean) norm, L1 (Manhattan) norm, and infinity norm. Below are step-by-step ways to implement this with custom SQL functions, plus alternative approaches that might fit your database better.

The exact implementation will vary slightly depending on your database system (PostgreSQL, MySQL, etc.), but here are examples for the most common ones.

For PostgreSQL (Array-Based Vectors) #

PostgreSQL has robust array support, so we can easily unnest vectors to compute norms:

L2 (Euclidean) Norm #

This calculates the square root of the sum of squared elements:

CREATE OR REPLACE FUNCTION vector_l2_norm(v NUMERIC[])
RETURNS NUMERIC AS $$
BEGIN
    RETURN SQRT(SUM(x^2)) FROM UNNEST(v) AS x;
END;
$$ LANGUAGE plpgsql;

Usage: SELECT vector_l2_norm(ARRAY[3,4]); → Returns 5

L1 (Manhattan) Norm #

This sums the absolute values of elements:

CREATE OR REPLACE FUNCTION vector_l1_norm(v NUMERIC[])
RETURNS NUMERIC AS $$
BEGIN
    RETURN SUM(ABS(x)) FROM UNNEST(v) AS x;
END;
$$ LANGUAGE plpgsql;

Usage: SELECT vector_l1_norm(ARRAY[3,4]); → Returns 7

Infinity Norm #

This returns the maximum absolute value in the vector:

CREATE OR REPLACE FUNCTION vector_inf_norm(v NUMERIC[])
RETURNS NUMERIC AS $$
BEGIN
    RETURN MAX(ABS(x)) FROM UNNEST(v) AS x;
END;
$$ LANGUAGE plpgsql;

Usage: SELECT vector_inf_norm(ARRAY[-3,4]); → Returns 4

For MySQL (JSON Array-Based Vectors) #

MySQL doesn't have native array types, so we'll use JSON arrays to store vectors:

L2 (Euclidean) Norm #

DELIMITER //
CREATE FUNCTION vector_l2_norm(v JSON)
RETURNS DECIMAL(18,6)
BEGIN
    DECLARE sum_squared DECIMAL(18,6) DEFAULT 0;
    DECLARE index INT DEFAULT 0;
    DECLARE element DECIMAL(18,6);
    
    WHILE index < JSON_LENGTH(v) DO
        SET element = JSON_EXTRACT(v, CONCAT('$[', index, ']'));
        SET sum_squared = sum_squared + (element * element);
        SET index = index + 1;
    END WHILE;
    
    RETURN SQRT(sum_squared);
END //
DELIMITER ;

Usage: SELECT vector_l2_norm('[3,4]'); → Returns 5.000000

If you don't want to create custom functions, you can compute norms directly in queries, depending on how your vectors are stored.

For Array/JSON Vectors #

PostgreSQL #

• L2 Norm: SELECT SQRT(SUM(x^2)) AS l2_norm FROM UNNEST(ARRAY[3,4]) AS x;
• L1 Norm: SELECT SUM(ABS(x)) AS l1_norm FROM UNNEST(ARRAY[3,4]) AS x;
• Infinity Norm: SELECT MAX(ABS(x)) AS inf_norm FROM UNNEST(ARRAY[-3,4]) AS x;

BigQuery #

BigQuery supports array operations natively:

-- L2 Norm
SELECT SQRT(SUM(POW(x, 2))) AS l2_norm FROM UNNEST([3,4]) AS x;

-- L1 Norm
SELECT SUM(ABS(x)) AS l1_norm FROM UNNEST([3,4]) AS x;

-- Infinity Norm
SELECT MAX(ABS(x)) AS inf_norm FROM UNNEST([-3,4]) AS x;

For Column-Based Vectors #

If your vector elements are stored in separate columns (e.g., x1, x2, x3), you can compute norms directly without unnesting:

• L2 Norm: SQRT(POW(x1,2) + POW(x2,2) + POW(x3,2))
• L1 Norm: ABS(x1) + ABS(x2) + ABS(x3)
• Infinity Norm: GREATEST(ABS(x1), ABS(x2), ABS(x3))

• SQL Server: Use STRING_SPLIT if your vectors are stored as comma-separated strings, or JSON functions for JSON arrays. For example, to compute L2 norm from a JSON array: SELECT SQRT(SUM(POW(CAST(value AS FLOAT),2))) FROM OPENJSON('[3,4]');
• Oracle: Use the TABLE function to unnest collections, or JSON path expressions for JSON vectors. For L2 norm: SELECT SQRT(SUM(POWER(column_value,2))) FROM TABLE(SYS.ODCINUMBERLIST(3,4));

Hope these solutions fit your use case! Feel free to tweak them if you're working with a less common database system.

内容的提问来源于stack exchange，提问作者Muhammad Hasan Alasady