Re-using aggregate level formulas in SQL - any good tactics?
- by Cade Roux
Imagine this case, but with a lot more component buckets and a lot more intermediates and outputs. Many of the intermediates are calculated at the detail level, but a few things are calculated at the aggregate level:
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
) ;
INSERT INTO @Profitability
VALUES ( 1, 'Software', 100, 50 ) ;
INSERT INTO @Profitability
VALUES ( 2, 'Software', 100, 20 ) ;
INSERT INTO @Profitability
VALUES ( 3, 'Software', 100, 60 ) ;
INSERT INTO @Profitability
VALUES ( 4, 'Software', 500, 400 ) ;
INSERT INTO @Profitability
VALUES ( 5, 'Hardware', 1000, 550 ) ;
INSERT INTO @Profitability
VALUES ( 6, 'Hardware', 1000, 250 ) ;
INSERT INTO @Profitability
VALUES ( 7, 'Hardware', 1000, 700 ) ;
INSERT INTO @Profitability
VALUES ( 8, 'Hardware', 5000, 4500 ) ;
SELECT Cust
,Profit = SUM(Income - Expense)
,Margin = SUM(Income - Expense) / SUM(Income)
FROM @Profitability
GROUP BY Cust
SELECT Category
,Profit = SUM(Income - Expense)
,Margin = SUM(Income - Expense) / SUM(Income)
FROM @Profitability
GROUP BY Category
SELECT Profit = SUM(Income - Expense)
,Margin = SUM(Income - Expense) / SUM(Income)
FROM @Profitability
Notice how the same formulae have to be used at the different aggregation levels. This results in code duplication.
I have thought of using UDFs (either scalar or table valued with an OUTER APPLY, since many of the final results may share intermediates which have to be calculated at the aggregate level), but in my experience the scalar and multi-statement table-valued UDFs perform very poorly.
Also thought about using more dynamic SQL and applying the formulas by name, basically.
Any other tricks, techniques or tactics to keeping these kinds of formulae which need to be applied at different levels in sync and/or organized?
