时间戳

首页 > 解决方案 > Excel 公式转 SQL

sql-server - Excel 公式转 SQL

问题描述

我想在我的 MSSQL 数据库中添加一个包含计算值的列，但我不知道如何创建 SQL 代码

我的数据包含列PricePerUnit和Instance_Type

我希望新的计算值列显示每个 Instance_Type 与同一 Instance_Type 中最昂贵的百分比相比便宜多少。例如，最昂贵的 c5.12xlarge 位于第一行（伦敦），因此便宜 0%，但相同的 c5.12xlarge 是爱尔兰便宜 4.95%，而在俄勒冈州，相同的 c5.12xlarge Instance_Type 为 15.84%更便宜。我希望 SQL 中的计算值列显示 0% 和 4.95%。15.84% 等等。

在 Excel 中，我将使用以下内容

formula =(MAXIFS(A:A,B:B,B2)-A2)/MAXIFS(A:A,B:B,B2)

数据库表称为 AmazonEC2

这是它在 Excel 中工作的图像。第一张蓝色的表和SQL数据库中的数据一模一样，黑色的表代表我想用SQL实现的。

在此处输入图像描述

标签： sql-serverexcelcalculated-columns

解决方案

我认为将其作为计算列来执行并不好（甚至可能不可能）。

从评论看来，PricePerUnit 的数据类型是 nvarchar()。如果是这种情况，我必须指出这是糟糕的数据库设计。我了解更改可能超出您的控制范围。

无论如何，我创建了一个 AmazonEC2 表，因为我认为您可能拥有它。使用您的数据电子表格，我创建了插入语句，使用以下公式用您的数据填充该表。

=CONCATENATE("insert into AmazonEC2 (PricePerUnit, Instance_Type, Instance_Family, Location) values ('", A2, "', ", "'", B2, "', '", C2, "', '", D2, "')")

我将所有这些都构建到了一个dbfiddle中，这样您就可以看到它的运行情况，这样这里的其他人就可以操纵数据并尝试不同的方法。

这是检索数据并在检索时计算 PercentCheaper 的最终 SQL 语句。您还可以基于此 SQL 语句创建视图。

SELECT
     x.PricePerUnit
   , x.Instance_Type
   , x.Instance_Family
   , x.Location
   , FORMAT((x.MaxPricePerUnit - convert(decimal(10, 2), PricePerUnit)) / x.MaxPricePerUnit, 'P') AS PercentCheaper
FROM (
         SELECT
              PricePerUnit
            , Instance_Type
            , Instance_Family
            , Location
            , MAX(convert(decimal(10, 2), PricePerUnit)) OVER (PARTITION BY Instance_Type) AS MaxPricePerUnit
         FROM AmazonEC2
     ) x;

我们在这里所做的是获取子查询中每个 Instance_Type 的最大 PricePerUnit，我将其别名为“x”。然后我从中选择并执行计算以找到每行的 PercentCheaper。

由于 PricePerUnit 似乎是一个 nvarchar() 列，因此您需要将其转换为数字才能进行计算。请注意，您不需要转换 MaxPricePerUnit，因为转换发生在它被用作 MAX() 函数的输入之前，导致输出具有 decimal(10,2) 数据类型。

推荐阅读