多行计算 SQL Server [英] Calculations over Multiple Rows SQL Server

问题描述

如果我有格式的数据；

Account  | Period     | Values
Revenue  | 2013-01-01 | 5432
Revenue  | 2013-02-01 | 6471
Revenue  | 2013-03-01 | 7231
Costs    | 2013-01-01 | 4321
Costs    | 2013-02-01 | 5672
Costs    | 2013-03-01 | 4562

我想得到这样的结果；

Account  | Period     | Values
Margin   | 2013-01-01 | 1111
Margin   | 2013-02-01 |  799
Margin   | 2013-03-01 | 2669
M%       | 2013-01-01 |  .20
M%       | 2013-02-01 |  .13
M%       | 2013-03-01 |  .37

其中保证金 = 收入 - 成本，M% 是每个时期的(收入 - 成本)/收入.

Where Margin = Revenue - Costs and M% is (Revenue - Costs)/Revenue for each period.

我可以看到实现这一点的各种方法，但都非常丑陋，我想知道是否有针对此类多行计算的优雅通用方法.

I can see various ways of achieving this but all are quite ugly and I wanted to know if there was elegant general approach for these sorts of multi-row calculations.

谢谢

编辑

其中一些计算可能会变得非常复杂，例如

Some of these calculations can get really complicated like

自由现金流 = 保证金 - 运营支出 - 资本支出 + 营运资金变化 + 已付利息

Free Cash Flow = Margin - Opex - Capex + Change in Working Capital + Interest Paid

所以我希望有一种不需要大量连接的通用方法.

So I am hoping for a general method that doesn't require lots of joins back to itself.

谢谢

推荐答案

好的，那么只要 Max 覆盖一个 Case 语句，就像这样:

Ok, then just Max over a Case statement, like such:

with RevAndCost as (revenue,costs,period)
as
(

    select "Revenue" = Max(Case when account="Revenue" then Values else null end),
           "Costs" = MAX(Case when account="Costs" then values else null end),
           period
            from data
    group by period

)

select Margin = revenue-costs,
       "M%" = (revenue-costs)/nullif(revenue,0)
from RevAndCost

这篇关于多行计算 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！