时间戳

首页 > 解决方案 > 需要帮助将 Excel if 和 sumif 公式转换为我的 SQL Server 代码

sql-server - 需要帮助将 Excel if 和 sumif 公式转换为我的 SQL Server 代码

问题描述

我选择了 SQL 查询，它返回我指定的列（客户、产品、Mnth、上一年度、当前年度、方差）中的数据，并将其导出到 Excel 表中。

在 Excel 中，我使用 If 和 Sumifs 公式向表 (Move) 添加了一列，该公式根据其他列中的条件返回是或否。

我相信 case when 相当于 SQL Server 中的 if，但我不知道如何按行处理 case。

基本上，IF 声明只是由客户说，对于去年和今年之间的每个相应期间，他们使用较少的产品以及他们目前使用更多的产品意味着他们从一种产品转移到另一种产品。

请参阅下面的 Excel 和 SQL 代码以及 excel 中的结果屏幕截图。

Excel公式的缩进版本：

=IF(
    [@Product]="Product 1",
    IF(
        AND(
            [@Variance]<0,
            OR(
                SUMIFS([Variance],[Customer],[@Customer],[mnth],[@mnth],[Product],"Product 2")>0,
                SUMIFS([Variance],[Customer],[@Customer],[mnth],[@mnth],[Product],"Product 3")>0
            )
        ),
        "Yes",
        "No"
    ),
    IF(
        [@Product]="Product 2",
        IF(
            AND(
                [@Variance]<0,
                SUMIFS([Variance],[Customer],[@Customer],[mnth],[@mnth],[Product],"Product 3")>0
            ),
            "Yes",
            "No"
        ),
        IF(
            AND(
                [@Variance]>0,
                OR(
                   SUMIFS([Variance],[Customer],[@Customer],[mnth],[@mnth],[Product],"Product 2")<0,
                   SUMIFS([Variance],[Customer],[@Customer],[mnth],[@mnth],[Product],"Product 1")<0
                )
            ),
            "Yes",
            "No"
        )
    )
)

生成所附图片前 6 列的 SQL：

SELECT
    subqry.Customer, subqry.Product, subqry.Mnth, 
    SUM(subqry.PriorYr) AS Previous Yr, 
    SUM(subqry.CurrentYr) AS Current Yr, 
    SUM(subqry.CurrentYr) - SUM(subqry.PriorYr) AS Variance
FROM
    (SELECT
         Customer, Product, 
         YEAR(TransactionDate) AS Yr, MONTH(TransactionDate) AS Mnth, 
         0 AS PriorYr, 
         SUM(TransactionCharges + OtherCharges) AS CurrentYr
     FROM
         Storedfunction1 ('01-01-19','03-31-19')
     WHERE
         Customer <> 'internal'   --EXCLUDE INTERNAL CUSTOMERS
         AND Product IN ('Product 1', 'Product 2', 'Product 3')
     GROUP BY
         Customer, Product, YEAR(TransactionDate), MONTH(TransactionDate)

     UNION ALL

     SELECT
         Customer, Product, 
         YEAR(TransactionDate) AS Yr, MONTH(TransactionDate) AS Mnth, 
         SUM(TransationCharges + OtherCharges) AS PriorYr, 
         0 AS CurrentYr
     FROM
         Storedfunction1 ('01-01-18','03-31-18')
     WHERE
         Customer <> 'internal'   --EXCLUDE INTERNAL CUSTOMERS
         AND Product IN ('Product 1', 'Product 2', 'Product 3')
     GROUP BY
         Customer, Product, YEAR(TransactionDate), MONTH(TransactionDate)
    ) Subqry
GROUP BY
    subqry.Customer, subqry.Product, subqry.Mnth
ORDER BY 
    1, 2, 3

我想创建第 7 列（移动）并在 SQL 中而不是在 Excel 中进行计算，因为我想限制在 Excel 中发生的计算。因此，在 SQL 中创建我在 Excel 中创建的列，并在 SQL 中执行 Excel ifs。

我希望我足够清楚地解释我的要求和预期结果，但如果我需要澄清，请告诉我。谢谢

Excel_Result_Table

标签： sql-server

解决方案

所以，我想我看到了你的一般逻辑。不过稍微澄清一下：一个人“从一种产品转移到另一种产品”并没有那么多，这对我来说意味着他们现在有了全新的偏好。相反，它更像是一个人已经“远离当前产品”，转而支持另一个产品。虽然要注意，真正捕捉到这一点最好通过花费金钱比例的方差来完成，而不是绝对方差。除此之外，这就是我的处理方式：

如果可以的话，我更喜欢使用公用表表达式 (CTE)，而不是子查询。在这种情况下，我将逻辑分为三层。

  • 在我称之为“baseData”的第一个 CTE 中，我将数据保持在未聚合的状态并在那里进行联合。实际年份无关紧要，因此我将其转换为描述性字符串而不是数字。请记住，我这样做是为了可读性和风格，而不是出于性能原因。所以你可能不得不使用它。主要是，我建议您绕过您的“storedFunction1”并直接点击基础表。但那是你的特权。

  • 在称为“getVariances”的第二个 CTE 中，我计算了前一年和当前年份之间的差异。请注意，当没有条件匹配 case 语句时，将返回 null。因此，该区域的聚合仅分别将“当前”和“上一个”年份相加。

  • 在最后的 sql 语句中，我计算“移动”。根据您的需要查看窗口函数。基本上，它在不压缩数据集的情况下进行聚合，这是您在这里需要的，我认为这与您尝试对 sumif 进行的操作等效。

这是代码：

with

    baseData as (

        select 'prev' as yr, Customer, Product, TransactionDate, TransactionCharges, OtherCharges
        from Storedfunction1 ('01-01-19','03-31-19')

        union all
        select 'cur' as yr, Customer, Product, TransactionDate, TransactionCharges, OtherCharges
        from Storedfunction1 ('01-01-18','03-31-18')

    ),

    getVariances (

        select      Customer, Product, mnth,

                    variance = 
                          sum(case when yr = 'cur' then transactionCharges + otherCharges end)
                        - sum(case when yr = 'prev' then transactionCharges + otherCharges end)

        from        baseData
        cross apply (select mnth = month(transactionDate)) ap
        where       Customer <> 'internal'   
        and         Product in ('Product 1', 'Product 2', 'Product 3')
        group by    Customer, Product, mnth

    )

    select      *,

                move = 
                    case 
                    when variance >= 0 then 'no' 
                    when max(variance) over(partition by customer, mnth) > 0 then 'yes'
                    else 'no'
                    end

    from        getVariances

推荐阅读