MySQL中的净现值计算

Question

我有一个查询，它提供了基于两个带有购买数据的表的成本结构的P&L视图。

Select Left(A.Location, 5) as Site, A.Account, A.Category, A.Sub_Category,
sum(If(Month(Inv.Invoice_Date)=1, A.Cost, 0)) as Jan,
sum(If(Month(Inv.Invoice_Date)=2, A.Cost, 0)) as Feb,
sum(If(Month(Inv.Invoice_Date)=3, A.Cost, 0)) as Mar,
sum(If(Month(Inv.Invoice_Date)=4, A.Cost, 0)) as Apr,
sum(If(Month(Inv.Invoice_Date)=5, A.Cost, 0)) as May,
sum(If(Month(Inv.Invoice_Date)=6, A.Cost, 0)) as Jun,
sum(If(Month(Inv.Invoice_Date)=7, A.Cost, 0)) as Jul,
sum(If(Month(Inv.Invoice_Date)=8, A.Cost, 0)) as Aug,
sum(If(Month(Inv.Invoice_Date)=9, A.Cost, 0)) as Sep,
sum(If(Month(Inv.Invoice_Date)=10, A.Cost, 0)) as Oct,
sum(If(Month(Inv.Invoice_Date)=11, A.Cost, 0)) as Nov,
sum(If(Month(Inv.Invoice_Date)=12, A.Cost, 0)) as Dece,
sum(Inv.Cost) as Total,
From Table.A as A
Left join (Select Invoice_Number, Invoice_Line_Number, Invoice_Date, group by Invoice_Number, Invoice_Line_Number) as Inv
on Inv.Invoice_Number = A.Invoice_Number and Inv.Invoice_Line_Number = A.Invoice_Line_Number
Where Left(A.Location_Desc, 5) = 'TEX01'
and Year(Inv.Invoice_Date) = '2016'
Group by Site, Account, Category, Sub_Category
Having Total <> 0
;

到目前一切尚好。现在我需要做三件我一直在努力的事情：

1)我需要在每个月列的底部有一个每月总成本行，这是可能的吗? 2)在运行查询之前，我需要添加可以修改的变量行。例如，如果我发现一个投资机会将成本降低X值，我希望能够引入一行，从成本总额中减去该金额，以显示节省的费用。3)需要计算成本节约投资的净现值。这就揭示了另一个问题。查询的结构只为带来1年的数据，但NPV的计算需要5到10年的预测。

最优查询将平均出站点的成本，并用该平均值预测未来的成本--然后引入可变行，通过新的投资降低成本，并最终执行净现值计算，以确定是否值得进行投资。

我的问题是：

( a) MySQL是正确的工具吗？b)我知道，从一个简单的select语句来看，这是不可能的--视图语句在这里不能工作，因为我在Select语句中有一个子查询。我是否应该构建一个将查询的输出作为数据保存的新表，并使用该表来执行所有这一切？( c)我应该研究一种编程语言/MySQL解决方案吗？

谢谢！！

Answer 1

对于第一点，您可以使用下面的查询获得结果

 Select Left(A.Location, 5) as Site, A.Account, A.Category, A.Sub_Category,
sum(If(Month(Inv.Invoice_Date)=1, A.Cost, 0)) as Jan,
sum(If(Month(Inv.Invoice_Date)=2, A.Cost, 0)) as Feb,
sum(If(Month(Inv.Invoice_Date)=3, A.Cost, 0)) as Mar,
sum(If(Month(Inv.Invoice_Date)=4, A.Cost, 0)) as Apr,
sum(If(Month(Inv.Invoice_Date)=5, A.Cost, 0)) as May,
sum(If(Month(Inv.Invoice_Date)=6, A.Cost, 0)) as Jun,
sum(If(Month(Inv.Invoice_Date)=7, A.Cost, 0)) as Jul,
sum(If(Month(Inv.Invoice_Date)=8, A.Cost, 0)) as Aug,
sum(If(Month(Inv.Invoice_Date)=9, A.Cost, 0)) as Sep,
sum(If(Month(Inv.Invoice_Date)=10, A.Cost, 0)) as Oct,
sum(If(Month(Inv.Invoice_Date)=11, A.Cost, 0)) as Nov,
sum(If(Month(Inv.Invoice_Date)=12, A.Cost, 0)) as Dece,
sum(Inv.Cost) as Total,
From Table.A as A
Left join (Select Invoice_Number, Invoice_Line_Number, Invoice_Date, group by Invoice_Number, Invoice_Line_Number) as Inv
on Inv.Invoice_Number = A.Invoice_Number and Inv.Invoice_Line_Number = A.Invoice_Line_Number
Where Left(A.Location_Desc, 5) = 'TEX01'
and Year(Inv.Invoice_Date) = '2016'
Group by A.Site, A.Account, A.Category, A.Sub_Category with ROLLUP Having Total <> 0 AND 
((a.Site is null or A.Account is not null) and (A.Account is null or A.Category is not null) and (A.Category is null or A.Sub_Category is not null));