php - MySQL 中的 NPV 计算

Question

我有一个查询，它根据两个包含购买数据的表提供成本结构的损益 View 。

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)我需要计算成本节约投资的NPV。这又引发了另一个问题。该查询的结构仅提供 1 年的数据，但 NPV 计算需要跨度 5 到 10 年的预测。

最佳查询将平均站点成本并用该平均值预测 future 成本 - 然后它将引入通过新投资降低成本的变量行，最后将执行 NPV 计算以确定投资是否是值得追求的。

我的问题是:

a) MySQL 是执行此操作的正确工具吗？b) 我知道从简单的 select 语句中这是不可能的 - View 语句在这里不起作用，因为我在 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));