代码之家 › 专栏 › 技术社区 › Cade Roux

在SQL中重新使用聚合级别公式-有什么好的策略吗?

dry database-design sql-server sql

Cade Roux · 技术社区 · 14 年前

想象一下这个例子,但是有更多的组件桶和更多的中间产品和输出。许多中间产物是在细节级别计算的,但有一些东西是在聚合级别计算的:

DECLARE @Profitability AS TABLE
    (
     Cust INT NOT NULL
    ,Category VARCHAR(10) NOT NULL
    ,Income DECIMAL(10, 2) NOT NULL
    ,Expense DECIMAL(10, 2) NOT NULL
    ,Liability DECIMAL(10, 2) NOT NULL
    ,AllocatedCapital DECIMAL(10, 2) NOT NULL
    ) ;

INSERT  INTO @Profitability
VALUES  ( 1, 'Software', 100, 50, 0, 0 ) ; 
INSERT  INTO @Profitability
VALUES  ( 2, 'Software', 100, 20, 0, 0 ) ; 
INSERT  INTO @Profitability
VALUES  ( 3, 'Software', 100, 60, 0, 0 ) ; 
INSERT  INTO @Profitability
VALUES  ( 4, 'Software', 500, 400, 0, 0 ) ; 
INSERT  INTO @Profitability
VALUES  (
         5
        ,'Hardware'
        ,1000
        ,550
        ,0
        ,0 
        ) ; 
INSERT  INTO @Profitability
VALUES  (
         6
        ,'Hardware'
        ,1000
        ,250
        ,500
        ,200 
        ) ; 
INSERT  INTO @Profitability
VALUES  (
         7
        ,'Hardware'
        ,1000
        ,700
        ,500
        ,600 
        ) ; 
INSERT  INTO @Profitability
VALUES  (
         8
        ,'Hardware'
        ,5000
        ,4500
        ,2500
        ,800 
        ) ; 

WITH    ProfitView
          AS ( SELECT   Cust
                       ,Category
                       ,Income
                       ,Expense
                       ,Profit = Income - Expense
                       ,NetProfit = Income - Expense
                        - CASE WHEN Liability - AllocatedCapital > 0
                               THEN Liability - AllocatedCapital
                               ELSE 0
                          END
               FROM     @Profitability
             )
    SELECT  Cust
           ,Category
           ,Income
           ,Expense
           ,Profit
           ,NetProfit
           ,Margin = Profit / Income
           ,NetMargin = NetProfit / Income
    FROM    ProfitView ; -- NOTE I've left off the AFTER grouping formulas on this one.

WITH    ProfitView
          AS ( SELECT   Cust
                       ,Category
                       ,Income
                       ,Expense
                       ,Profit = Income - Expense
                       ,NetProfit = Income - Expense
                        - CASE WHEN Liability - AllocatedCapital > 0
                               THEN Liability - AllocatedCapital
                               ELSE 0
                          END
               FROM     @Profitability
             ),
        GROUP1
          AS ( SELECT   Category
                       ,SUM(Profit) AS Profit
                       ,SUM(NetProfit) AS NetProfit
                       ,SUM(Income) AS Income
                       ,SUM(Profit) / SUM(Income) AS Margin
                       ,SUM(NetProfit) / SUM(Income) AS NetMargin
               FROM     ProfitView
               GROUP BY Category
             ),
        GROUP2
          AS ( SELECT   GROUP1.*
                       ,NetProfit - Profit AS Exposure
               FROM     GROUP1
             )
    SELECT  *
           ,Exposure / Income AS ExposureRatio
    FROM    GROUP2 ;

WITH    ProfitView
          AS ( SELECT   Cust
                       ,Category
                       ,Income
                       ,Expense
                       ,Profit = Income - Expense
                       ,NetProfit = Income - Expense
                        - CASE WHEN Liability - AllocatedCapital > 0
                               THEN Liability - AllocatedCapital
                               ELSE 0
                          END
               FROM     @Profitability
             ),
        GROUP1
          AS ( SELECT   SUM(Profit) AS Profit
                       ,SUM(NetProfit) AS NetProfit
                       ,SUM(Income) AS Income
                       ,SUM(Profit) / SUM(Income) AS Margin
                       ,SUM(NetProfit) / SUM(Income) AS NetMargin
               FROM     ProfitView
             ),
        GROUP2
          AS ( SELECT   GROUP1.*
                       ,NetProfit - Profit AS Exposure
               FROM     GROUP1
             )
    SELECT  *
           ,Exposure / Income AS ExposureRatio
    FROM    GROUP2 ;

请注意,相同的公式必须在不同的聚合级别上使用。这会导致代码重复。

我曾考虑过使用UDF(标量或表值与外部应用程序,因为许多最终结果可能共享中间值,这些中间值必须在聚合级别计算),但根据我的经验,标量和多语句表值UDF的性能非常差。

基本上还考虑使用更动态的SQL和按名称应用公式。

是否有其他技巧、技术或策略来保持这些公式的同步和/或有组织地在不同的层次上应用?

3 回复 | 直到 14 年前

Quassnoi 14 年前

请注意,相同的公式必须在不同的聚合级别上使用。这会导致代码重复。

如果您的函数更复杂,您可以从创建自定义 CLR 聚集体。

但是,对于这样一个简单的函数,内置 SUM 是最好的。

不像 PostgreSQL , SQL Server 不允许使用内置语言创建自定义聚合。

Christian Hayter 14 年前

对于您的简化示例,我将通过返回原始数据来重构计算。( SUM(Income) 和 SUM(Expense) )在每个结果集中单独计算 Profit 和 Margin 在业务层中。

如果这在实际情况中是不可能的,你能让你的简单例子稍微复杂一点吗?这样我就能知道你在做什么了。

我最近做过一个项目,需要在查询中进行复杂的业务分析计算。结果证明,在数据查询之外不可能执行这些操作,因此我们最终将所有内容转换为动态SQL。这允许我们构造宏函数来构建每个查询的各个部分。通过这样做,我们牺牲了可读性,但获得了可维护性。我们并没有牺牲可测试性,因为我们编写了单元测试,通过宏函数执行每个可能的代码路径,并在生成时记录每个查询。

Andomar 14 年前

您可以在视图中分离复杂性的一部分:

create view dbo.vw_Profit
as
SELECT  
    Cust
,   Income,
,   Expense
,   Income - Expense as Profit
FROM dbo.Profitability

这使得查询更加简单:

SELECT cust, SUM(profit), SUM(Income) / SUM(Expense)
FROM dbo.vw_Profit
GROUP BY cust

示例查询的复杂性很难保证对视图进行简化。但是对于非常复杂的查询,视图是非常有用的。