Other ACOS SQL function
1. Usage of ACOS to show hypothetical similiarity angles in degress of products
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 TOP 10
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()
.SqlFunction(new CAST(new Column("UnitPrice"), SqlDataType.DECIMAL, new Tuple<int, int>(10, 4)))
.DIVIDE()
.Column(new SqlQueryBuilder().Select()
.Column(new MAX(new Column("UnitPrice")), "NormalizedPrice")
.From("Products")
)
, "NormalizedPrice")
.Column(new ColumnArithmatic()
.SqlFunction(new CAST(new Column("UnitsInStock"), SqlDataType.DECIMAL, new Tuple<int, int>(10, 4)))
.DIVIDE()
.Column(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()
.Column("p1.NormalizedPrice").MULTIPLY().Column("p2.NormalizedPrice")
.ADD().Column("p1.NormalizedStock").MULTIPLY().Column("p2.NormalizedStock")
.EndBracket().DIVIDE()
.StartBracket()
.SqlFunction(new SQRT(new ColumnArithmatic().Column("p1.NormalizedPrice").MULTIPLY().Column("p1.NormalizedPrice").ADD()
.Column("p1.NormalizedStock").MULTIPLY().Column("p1.NormalizedStock")
)).MULTIPLY()
.SqlFunction(new SQRT(new ColumnArithmatic().Column("p2.NormalizedPrice").MULTIPLY().Column("p2.NormalizedPrice")
.ADD().Column("p2.NormalizedStock").MULTIPLY().Column("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").GreaterThan(-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().Top(10)
.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_2507192031229020940
AND UnitPrice > @pMAIN_2507192031229020941),
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_2507192031229020942 THEN @pMAIN_2507192031229020943 WHEN SimilarityValue > @pMAIN_2507192031229020944 THEN @pMAIN_2507192031229020945 ELSE SimilarityValue END AS AdjustedSimilarityValue,
ACOS(CASE WHEN SimilarityValue > @pMAIN_2507192031229020946 THEN @pMAIN_2507192031229020947 WHEN SimilarityValue < @pMAIN_2507192031229020948 THEN @pMAIN_2507192031229020949 ELSE SimilarityValue END) AS SimilarityAngleInRadians
FROM ProductPairs)
SELECT TOP 10 Product1,
ProductName1,
Product2,
ProductName2,
SimilarityAngleInRadians,
DEGREES(SimilarityAngleInRadians) AS SimilarityAngleInDegrees
FROM AcosResult
ORDER BY SimilarityAngleInRadians ASC;
Parameters (If used)
| Name |
Value |
| @pMAIN_2507192031229020940 |
0 |
| @pMAIN_2507192031229020941 |
0 |
| @pMAIN_2507192031229020942 |
1 |
| @pMAIN_2507192031229020943 |
1 |
| @pMAIN_2507192031229020944 |
-1 |
| @pMAIN_2507192031229020945 |
-1 |
| @pMAIN_2507192031229020946 |
1 |
| @pMAIN_2507192031229020947 |
1 |
| @pMAIN_2507192031229020948 |
-1 |
| @pMAIN_2507192031229020949 |
-1 |
Query Results 1:
| |
Product1 |
ProductName1 |
Product2 |
ProductName2 |
SimilarityAngleInRadians |
SimilarityAngleInDegrees |
| 1 |
10
|
Ikura
|
70
|
Outback Lager
|
0
|
0
|
| 2 |
13
|
Konbu
|
50
|
Valkoinen suklaa
|
0
|
0
|
| 3 |
25
|
NuNuCa Nuß-Nougat-Creme
|
52
|
Filo Mix
|
0
|
0
|
| 4 |
7
|
Uncle Bob's Organic Dried Pears
|
49
|
Maxilaku
|
0.000000021073424255447
|
0.00000120741826972573
|
| 5 |
34
|
Sasquatch Ale
|
46
|
Spegesild
|
0.0000896516979850368
|
0.00513666392072411
|
| 6 |
6
|
Grandma's Boysenberry Spread
|
55
|
Pâté chinois
|
0.000170212591084587
|
0.00975246308913292
|
| 7 |
26
|
Gumbär Gummibärchen
|
68
|
Scottish Longbreads
|
0.00032007904335472
|
0.0183391782948104
|
| 8 |
23
|
Tunnbröd
|
73
|
Röd Kaviar
|
0.000459719716187663
|
0.0263399994965051
|
| 9 |
40
|
Boston Crab Meat
|
73
|
Röd Kaviar
|
0.000509145312503459
|
0.0291718775653176
|
| 10 |
23
|
Tunnbröd
|
40
|
Boston Crab Meat
|
0.000968865028495687
|
0.0555118770506251
|
2. Usage of ACOS to show hypothetical relationship angles in radians of products
SQL Server Query 2
WITH CustomerProductCounts AS (
SELECT
c.CustomerID,
c.CompanyName AS CustomerName,
COUNT(DISTINCT od.ProductID) AS CustomerProductCount,
CAST(COUNT(DISTINCT od.ProductID) AS DECIMAL(10, 8)) / (SELECT MAX(sub.ProductCount) from (SELECT COUNT(DISTINCT od.ProductID) as ProductCount from Customers c join Orders o on c.CustomerID = o.CustomerID join [Order Details] od on o.OrderID = od.OrderID GROUP BY c.CustomerID) as sub) AS NormalizedCustomerProductCount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN [Order Details] od ON o.OrderID = od.OrderID
GROUP BY c.CustomerID, c.CompanyName
),
SupplierProductCounts AS (
SELECT
s.SupplierID,
s.CompanyName AS SupplierName,
COUNT(DISTINCT p.ProductID) AS SupplierProductCount,
CAST(COUNT(DISTINCT p.ProductID) AS DECIMAL(10, 8)) / (SELECT MAX(SupplierProductCount) FROM (SELECT s.SupplierID, COUNT(DISTINCT p.ProductID) AS SupplierProductCount from Suppliers s JOIN Products p ON s.SupplierID = p.SupplierID GROUP BY s.SupplierID) as sub2) AS NormalizedSupplierProductCount
FROM Suppliers s
JOIN Products p ON s.SupplierID = p.SupplierID
GROUP BY s.SupplierID, s.CompanyName
),
CustomerSupplierPairs AS (
SELECT
cp.CustomerID,
cp.CustomerName,
sp.SupplierID,
sp.SupplierName,
-- Calculate a similarity value
(cp.NormalizedCustomerProductCount * sp.NormalizedSupplierProductCount) AS SimilarityValue
FROM CustomerProductCounts cp
CROSS JOIN SupplierProductCounts sp
WHERE cp.NormalizedCustomerProductCount > 0 AND sp.NormalizedSupplierProductCount > 0
),
AcosResult as (
SELECT
CustomerID,
CustomerName,
SupplierID,
SupplierName,
SimilarityValue,
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 RelationshipAngleInRadians
FROM CustomerSupplierPairs
)
SELECT TOP 10
CustomerID,
CustomerName,
SupplierID,
SupplierName,
RelationshipAngleInRadians,
DEGREES(RelationshipAngleInRadians) AS RelationshipAngleInDegrees
FROM AcosResult
ORDER BY RelationshipAngleInRadians;
Create SQL query with SqlQueryBuilder 2
var (sql2, parameters2) = new SqlQueryBuilder()
.WithCTETable(new Table("CustomerProductCounts"), new SqlQueryBuilder()
.Select()
.Column("c.CustomerID", "CustomerID")
.Column("c.CompanyName", "CustomerName")
.Column(new COUNT(new Column("od.ProductID"), true), "CustomerProductCount")
.Column(new ColumnArithmatic()
.SqlFunction(new CAST(new COUNT(new Column("od.ProductID"), true), SqlDataType.DECIMAL, new Tuple<int, int>(10, 8)))
.DIVIDE()
.Column(new SqlQueryBuilder()
.Select()
.Column(new MAX(new Column("sub.ProductCount")), "NormalizedCustomerProductCount")
.From(new SqlQueryBuilder()
.Select()
.Column(new COUNT(new Column("od.ProductID"), true), "ProductCount")
.From("Customers", "c")
.Join(new List<IJoin>()
{
new INNERJOIN().TableName(new Table("Orders","o"))
.On(new Column("c.CustomerID").Equale(new Column("o.CustomerID"))),
new INNERJOIN().TableName(new Table("[Order Details]","od"))
.On(new Column("o.OrderID").Equale(new Column("od.OrderID")))
})
.GroupBy(new GroupBy(new Column("c.CustomerID")))
, "sub")
)
, "NormalizedCustomerProductCount")
.From("Customers", "c")
.Join(new List<IJoin>()
{
new INNERJOIN().TableName(new Table("Orders","o"))
.On(new Column("c.CustomerID").Equale(new Column("o.CustomerID"))),
new INNERJOIN().TableName(new Table("[Order Details]","od"))
.On(new Column("o.OrderID").Equale(new Column("od.OrderID")))
})
.GroupBy(new GroupBy(new Column("c.CustomerID"), new Column("c.CompanyName")))
)
.WithCTETable(new Table("SupplierProductCounts"), new SqlQueryBuilder()
.Select()
.Column("s.SupplierID", "SupplierID")
.Column("s.CompanyName", "SupplierName")
.Column(new COUNT(new Column("p.ProductID"), true), "SupplierProductCount")
.Column(new ColumnArithmatic()
.SqlFunction(new CAST(new COUNT(new Column("p.ProductID"), true), SqlDataType.DECIMAL, new Tuple<int, int>(10, 8)))
.DIVIDE()
.Column(new SqlQueryBuilder().Select()
.Column(new MAX(new Column("SupplierProductCount")), "NormalizedSupplierProductCount")
.From(new SqlQueryBuilder()
.Select()
.Column("s.SupplierID", "SupplierID")
.Column(new COUNT(new Column("p.ProductID"), true), "SupplierProductCount")
.From("Suppliers", "s")
.Join(new List<IJoin>()
{
new INNERJOIN().TableName(new Table("Products","p"))
.On(new Column("s.SupplierID").Equale(new Column("p.SupplierID")))
})
.GroupBy(new GroupBy(new Column("s.SupplierID")))
, "sub2")
)
, "NormalizedSupplierProductCount")
.From("Suppliers", "s")
.Join(new List<IJoin>()
{
new INNERJOIN().TableName(new Table("Products","p"))
.On(new Column("s.SupplierID").Equale(new Column("p.SupplierID")))
})
.GroupBy(new GroupBy(new Column("s.SupplierID"), new Column("s.CompanyName")))
)
.WithCTETable(new Table("CustomerSupplierPairs"), new SqlQueryBuilder()
.Select()
.Columns("cp.CustomerID", "cp.CustomerName", "sp.SupplierID", "sp.SupplierName")
.Column(new ColumnArithmatic().StartBracket().Column("cp.NormalizedCustomerProductCount").MULTIPLY()
.Column("sp.NormalizedSupplierProductCount").EndBracket()
, "SimilarityValue")
.From("CustomerProductCounts", "cp")
.Join(new List<IJoin>()
{
new CROSSJOIN().TableName(new Table("SupplierProductCounts","sp"))
})
.Where(new Where(new Column("cp.NormalizedCustomerProductCount").GreaterThan(0))
.AND(new Column("sp.NormalizedSupplierProductCount").GreaterThan(0)))
)
.WithCTETable(new Table("AcosResult"), new SqlQueryBuilder()
.Select()
.Columns("CustomerID", "CustomerName", "SupplierID", "SupplierName", "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")))
, "RelationshipAngleInRadians")
.From("CustomerSupplierPairs")
)
.Select().Top(10)
.Columns("CustomerID", "CustomerName", "SupplierID", "SupplierName", "RelationshipAngleInRadians")
.Column(new DEGREES(new Column("RelationshipAngleInRadians")), "RelationshipAngleInDegrees")
.From("AcosResult")
.OrderBy(new OrderBy().SetColumnAscending("RelationshipAngleInRadians"))
.Build();
Query build by SqlQueryBuilder 2
WITH CustomerProductCounts
AS (SELECT c.CustomerID AS CustomerID,
c.CompanyName AS CustomerName,
COUNT(DISTINCT od.ProductID) AS CustomerProductCount,
CAST (COUNT(DISTINCT od.ProductID) AS DECIMAL (10, 8)) / (SELECT MAX(sub.ProductCount) AS NormalizedCustomerProductCount
FROM (SELECT COUNT(DISTINCT od.ProductID) AS ProductCount
FROM Customers AS c
INNER JOIN
Orders AS o
ON c.CustomerID = o.CustomerID
INNER JOIN
[Order Details] AS od
ON o.OrderID = od.OrderID
GROUP BY c.CustomerID) AS sub) AS NormalizedCustomerProductCount
FROM Customers AS c
INNER JOIN
Orders AS o
ON c.CustomerID = o.CustomerID
INNER JOIN
[Order Details] AS od
ON o.OrderID = od.OrderID
GROUP BY c.CustomerID, c.CompanyName),
SupplierProductCounts
AS (SELECT s.SupplierID AS SupplierID,
s.CompanyName AS SupplierName,
COUNT(DISTINCT p.ProductID) AS SupplierProductCount,
CAST (COUNT(DISTINCT p.ProductID) AS DECIMAL (10, 8)) / (SELECT MAX(SupplierProductCount) AS NormalizedSupplierProductCount
FROM (SELECT s.SupplierID AS SupplierID,
COUNT(DISTINCT p.ProductID) AS SupplierProductCount
FROM Suppliers AS s
INNER JOIN
Products AS p
ON s.SupplierID = p.SupplierID
GROUP BY s.SupplierID) AS sub2) AS NormalizedSupplierProductCount
FROM Suppliers AS s
INNER JOIN
Products AS p
ON s.SupplierID = p.SupplierID
GROUP BY s.SupplierID, s.CompanyName),
CustomerSupplierPairs
AS (SELECT cp.CustomerID,
cp.CustomerName,
sp.SupplierID,
sp.SupplierName,
(cp.NormalizedCustomerProductCount * sp.NormalizedSupplierProductCount) AS SimilarityValue
FROM CustomerProductCounts AS cp CROSS JOIN SupplierProductCounts AS sp
WHERE cp.NormalizedCustomerProductCount > @pMAIN_2507192031229683260
AND sp.NormalizedSupplierProductCount > @pMAIN_2507192031229683261),
AcosResult
AS (SELECT CustomerID,
CustomerName,
SupplierID,
SupplierName,
SimilarityValue,
CASE WHEN SimilarityValue > @pMAIN_2507192031229683262 THEN @pMAIN_2507192031229683263 WHEN SimilarityValue < @pMAIN_2507192031229683264 THEN @pMAIN_2507192031229683265 ELSE SimilarityValue END AS AdjustedSimilarityValue,
ACOS(CASE WHEN SimilarityValue > @pMAIN_2507192031229683266 THEN @pMAIN_2507192031229683267 WHEN SimilarityValue < @pMAIN_2507192031229683268 THEN @pMAIN_2507192031229683269 ELSE SimilarityValue END) AS RelationshipAngleInRadians
FROM CustomerSupplierPairs)
SELECT TOP 10 CustomerID,
CustomerName,
SupplierID,
SupplierName,
RelationshipAngleInRadians,
DEGREES(RelationshipAngleInRadians) AS RelationshipAngleInDegrees
FROM AcosResult
ORDER BY RelationshipAngleInRadians ASC;
Parameters (If used)
| Name |
Value |
| @pMAIN_2507192031229683260 |
0 |
| @pMAIN_2507192031229683261 |
0 |
| @pMAIN_2507192031229683262 |
1 |
| @pMAIN_2507192031229683263 |
1 |
| @pMAIN_2507192031229683264 |
-1 |
| @pMAIN_2507192031229683265 |
-1 |
| @pMAIN_2507192031229683266 |
1 |
| @pMAIN_2507192031229683267 |
1 |
| @pMAIN_2507192031229683268 |
-1 |
| @pMAIN_2507192031229683269 |
-1 |
Query Results 2:
| |
CustomerID |
CustomerName |
SupplierId |
RelationshipAngleInRadians |
RelationshipAngleInDegrees |
| 1 |
ERNSH
|
Ernst Handel
|
7
|
0
|
0
|
| 2 |
ERNSH
|
Ernst Handel
|
12
|
0
|
0
|
| 3 |
SAVEA
|
Save-a-lot Markets
|
7
|
0.328806015570302
|
18.8391969706911
|
| 4 |
SAVEA
|
Save-a-lot Markets
|
12
|
0.328806015570302
|
18.8391969706911
|
| 5 |
QUICK
|
QUICK-Stop
|
7
|
0.505360510284157
|
28.9550243718598
|
| 6 |
QUICK
|
QUICK-Stop
|
12
|
0.505360510284157
|
28.9550243718598
|
| 7 |
RATTC
|
Rattlesnake Canyon Grocery
|
7
|
0.637524882148117
|
36.5274850816623
|
| 8 |
RATTC
|
Rattlesnake Canyon Grocery
|
12
|
0.637524882148117
|
36.5274850816623
|
| 9 |
ERNSH
|
Ernst Handel
|
2
|
0.643501108793284
|
36.869897645844
|
| 10 |
ERNSH
|
Ernst Handel
|
8
|
0.643501108793284
|
36.869897645844
|