SQRT SQL function

1. Usage of SQRT to find product similarity.

SQL Server Query 1

            
WITH ProductData AS (  

    SELECT  

        ProductID,  

        ProductName,  

        UnitPrice,  

        UnitsInStock,		  

        -- Normalize UnitPrice and UnitsInStock to a range of 0 to 1  

        CAST(UnitPrice AS DECIMAL(10, 4)) / (SELECT MAX(UnitPrice) FROM Products) AS NormalizedPrice,  

        CAST(UnitsInStock AS DECIMAL(10, 4)) / (SELECT CAST(MAX(UnitsInStock) AS DECIMAL(10,4)) FROM Products) AS NormalizedStock  

    FROM Products  

    WHERE UnitsInStock > 0 AND UnitPrice > 0  --important to avoid nulls in normalization  

),  

ProductPairs AS (  

    SELECT  

        p1.ProductID AS Product1,  

        p1.ProductName AS ProductName1,  

        p2.ProductID AS Product2,  

        p2.ProductName AS ProductName2,  

        -- Calculate a value similar to cosine similarity (but with our normalized data)  

        (p1.NormalizedPrice * p2.NormalizedPrice + p1.NormalizedStock * p2.NormalizedStock) /  

        (  

            SQRT(p1.NormalizedPrice * p1.NormalizedPrice + p1.NormalizedStock * p1.NormalizedStock) *  

            SQRT(p2.NormalizedPrice * p2.NormalizedPrice + p2.NormalizedStock * p2.NormalizedStock)  

        ) AS SimilarityValue  

    FROM ProductData p1  

    CROSS JOIN ProductData p2  

    WHERE p1.ProductID < p2.ProductID  

),  

AcosResult as (  

    SELECT  

        Product1,  

        ProductName1,  

        Product2,  

        ProductName2,  

        SimilarityValue,  

        -- Ensure the value is within the valid range for ACOS  

        CASE  

            WHEN SimilarityValue > 1 THEN 1  

            WHEN SimilarityValue < -1 THEN -1  

            ELSE SimilarityValue  

        END AS AdjustedSimilarityValue,  

        ACOS(  

            CASE  

            WHEN SimilarityValue > 1 THEN 1  

            WHEN SimilarityValue < -1 THEN -1  

            ELSE SimilarityValue  

            END  

            ) AS SimilarityAngleInRadians  

    FROM ProductPairs  

  

)  

SELECT  

    Product1,  

    ProductName1,  

    Product2,  

    ProductName2,  

    SimilarityAngleInRadians,  

    DEGREES(SimilarityAngleInRadians) AS SimilarityAngleInDegrees  

FROM AcosResult  

ORDER BY SimilarityAngleInRadians;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .WithCTETable(new Table("ProductData"), new SqlQueryBuilder()  

        .Select()  

        .Columns("ProductID", "ProductName", "UnitPrice", "UnitsInStock")  

        .Column(new ColumnArithmatic(new CAST(new Column("UnitPrice"), SqlDataType.DECIMAL, new Tuple<int, int>(10, 4)))  

            .DIVIDE(new SqlQueryBuilder().Select()  

                .Column(new MAX(new Column("UnitPrice")), "NormalizedPrice")  

                .From("Products")  

            )  

        , "NormalizedPrice")  

        .Column(new ColumnArithmatic(new CAST(new Column("UnitsInStock"), SqlDataType.DECIMAL, new Tuple<int, int>(10, 4)))  

            .DIVIDE(new SqlQueryBuilder().Select()  

                .Column(new CAST(new MAX(new Column("UnitsInStock")), SqlDataType.DECIMAL, new Tuple<int, int>(10, 4)), "NormalizedStock")  

                .From("Products"))  

        , "NormalizedStock")  

        .From("Products")  

        .Where(new Where(new Column("UnitsInStock").GreaterThan(0))  

            .AND(new Column("UnitPrice").GreaterThan(0)))  

    )  

    .WithCTETable(new Table("ProductPairs"), new SqlQueryBuilder()  

        .Select()  

        .Column("p1.ProductID", "Product1")  

        .Column("p1.ProductName", "ProductName1")  

        .Column("p2.ProductID", "Product2")  

        .Column("p2.ProductName", "ProductName2")  

        .Column(new ColumnArithmatic()  

            .StartBracket("p1.NormalizedPrice").MULTIPLY("p2.NormalizedPrice")  

                .ADD("p1.NormalizedStock").MULTIPLY("p2.NormalizedStock")  

            .EndBracket().DIVIDE()  

            .StartBracket(new SQRT(new ColumnArithmatic().Column("p1.NormalizedPrice").MULTIPLY().Column("p1.NormalizedPrice").ADD()  

                    .Column("p1.NormalizedStock").MULTIPLY().Column("p1.NormalizedStock")  

                )).MULTIPLY(new SQRT(new ColumnArithmatic("p2.NormalizedPrice").MULTIPLY("p2.NormalizedPrice")  

                    .ADD("p2.NormalizedStock").MULTIPLY("p2.NormalizedStock").EndBracket()  

            ))  

        , "SimilarityValue")  

        .From("ProductData", "p1")  

        .Join(new List<IJoin>()  

        {  

            new CROSSJOIN().TableName(new Table("ProductData","p2"))  

        })  

        .Where(new Where(new Column("p1.ProductID").LessThan(new Column("p2.ProductID"))))  

    )  

    .WithCTETable(new Table("AcosResult"), new SqlQueryBuilder()  

        .Select()  

        .Columns("Product1", "ProductName1", "Product2", "ProductName2", "SimilarityValue")  

        .Column(new CASE()  

            .When(new Column("SimilarityValue").GreaterThan(1))  

                .Then(1)  

            .When(new Column("SimilarityValue").LessThan(-1))  

                .Then(-1)  

            .Else(new Column("SimilarityValue"))  

        , "AdjustedSimilarityValue")  

        .Column(new ACOS(new CASE()  

            .When(new Column("SimilarityValue").GreaterThan(1))  

                .Then(1)  

            .When(new Column("SimilarityValue").LessThan(-1))  

                .Then(-1)  

            .Else(new Column("SimilarityValue"))  

        )  

        , "SimilarityAngleInRadians")  

        .From("ProductPairs")  

    )  

    .Select()  

    .Columns("Product1", "ProductName1", "Product2", "ProductName2", "SimilarityAngleInRadians")  

    .Column(new DEGREES(new Column("SimilarityAngleInRadians")), "SimilarityAngleInDegrees")  

    .From("AcosResult")  

    .OrderBy(new OrderBy().SetColumnAscending("SimilarityAngleInRadians"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH ProductData
AS (SELECT ProductID,
           ProductName,
           UnitPrice,
           UnitsInStock,
           CAST (UnitPrice AS DECIMAL (10, 4)) / (SELECT MAX(UnitPrice) AS NormalizedPrice
                                                  FROM Products) AS NormalizedPrice,
           CAST (UnitsInStock AS DECIMAL (10, 4)) / (SELECT CAST (MAX(UnitsInStock) AS DECIMAL (10, 4)) AS NormalizedStock
                                                     FROM Products) AS NormalizedStock
    FROM Products
    WHERE UnitsInStock > @pMAIN_2507200145499894180
          AND UnitPrice > @pMAIN_2507200145499894181),
 ProductPairs
AS (SELECT p1.ProductID AS Product1,
           p1.ProductName AS ProductName1,
           p2.ProductID AS Product2,
           p2.ProductName AS ProductName2,
           (p1.NormalizedPrice * p2.NormalizedPrice + p1.NormalizedStock * p2.NormalizedStock) / (SQRT(p1.NormalizedPrice * p1.NormalizedPrice + p1.NormalizedStock * p1.NormalizedStock) * SQRT(p2.NormalizedPrice * p2.NormalizedPrice + p2.NormalizedStock * p2.NormalizedStock)) AS SimilarityValue
    FROM ProductData AS p1 CROSS JOIN ProductData AS p2
    WHERE p1.ProductID < p2.ProductID),
 AcosResult
AS (SELECT Product1,
           ProductName1,
           Product2,
           ProductName2,
           SimilarityValue,
           CASE WHEN SimilarityValue > @pMAIN_2507200145499894182 THEN @pMAIN_2507200145499894183 WHEN SimilarityValue < @pMAIN_2507200145499894184 THEN @pMAIN_2507200145499894185 ELSE SimilarityValue END AS AdjustedSimilarityValue,
           ACOS(CASE WHEN SimilarityValue > @pMAIN_2507200145499894186 THEN @pMAIN_2507200145499894187 WHEN SimilarityValue < @pMAIN_2507200145499894188 THEN @pMAIN_2507200145499894189 ELSE SimilarityValue END) AS SimilarityAngleInRadians
    FROM ProductPairs)
SELECT Product1,
       ProductName1,
       Product2,
       ProductName2,
       SimilarityAngleInRadians,
       DEGREES(SimilarityAngleInRadians) AS SimilarityAngleInDegrees
FROM AcosResult
ORDER BY SimilarityAngleInRadians ASC;


            
        

Parameters (If used)

Query Results 1: