在 SQL 中求解代数方程

这是一个略有不同的版本：

DECLARE @target NUMERIC(9,2) = 0.35
,   @A INT
,   @B INT
,   @TARGET_COEFF NUMERIC(19, 9)

SELECT  @A = MAX(CASE WHEN Product = 'Product1' THEN Quantity END)
,   @B = SUM(CASE WHEN Product <> 'Product1' THEN Quantity END)
FROM
(
    VALUES  (N'Product1', 300)
    ,   (N'Product2', 200)
    ,   (N'Product3', 200)
    ,   (N'Product4', 300)
    ,   (N'Total', 1000)
) t (Product,Quantity)
WHERE   Product <> 'Total'

/*
Ax / (Ax + B) = 0.35

Ax = 0.35 * Ax + B * 0.35

Ax - 0.35Ax = B * 0.35

0.65Ax = B * 0.35

x = B * @target / (( 1 - @target) * A)

*/
SELECT  @TARGET_COEFF = @B * @target / ((1 - @target) * @A)

SELECT  @TARGET_COEFF AS coeff, @A * @TARGET_COEFF AS new_quantity, @A * @TARGET_COEFF - @A AS needed
,   @A * @TARGET_COEFF / (@B + @A * @TARGET_COEFF) AS verification

我包含在方程的变换中，这样你就可以得到 x 本身以及一些验证。最重要的是你要正确地转换你的方程。

输出：

为了计算给定产品数量 a 满足指定目标阈值 T（给定其他产品数量 b 之和）所需的最小数量 x，您可以使用以下公式：

x = (Ta - a + Tb) / (1 - T)

最简单的情况是 2 种产品。例如，如果产品 A 和产品 B 的数量均为 5（各 50%），并且您想查看还需要订购多少产品 A 才能达到 60%，则可以按如下方式计算：

x = ((0.6 * 5) - 5 + (0.6 * 5)) / (1 - 0.6) = 2.5

因此，要使任何一种产品达到 60%，您都需要订购（仅）2.5（或实际上，3）。

当您添加其他产品时，等式保持不变：您的 b 只是成为其他产品数量的总和。例如，如果您有 3 个产品，每个产品的数量为 5，则使用 10 表示 b 的值。

但是我们如何将其转化为 SQL？我们可以使用窗口总和来比较每个产品的总数量。然后，我们可以在查询中进行计算（甚至使用横向连接，或在 MS SQL 语法中）以简化等式。例如：CROSS APPLY

DECLARE @target DECIMAL(18,2) = 0.35;

WITH windowed AS 
(
  SELECT *, SUM(quantity) OVER () * 1.0 AS total 
  FROM products
)
SELECT 
  product
  quantity,
  total,
  quantity / total AS current_percent,
  @target AS target_percent,
  CASE 
    WHEN (quantity / total) < @target 
    THEN ((T * a) - a + (T * b)) / (1 - T)
  END AS needed
FROM windowed
  CROSS APPLY
  (
    SELECT 
      quantity AS a,
      total - quantity AS b,
      @target AS T
  ) eq

在这里工作小提琴。