Oracle PL SQL查询[逻辑]中的分配成本计算

Question

我的应用程序中遇到了一个逻辑问题。我一直在尝试使用SQL根据他们的客户ID来查找分配的成本。我的问题是这样的：在第一个表（Table1）中，我有BusinessUnit、ProductFamily、Cost

在另一个（表2）中，我有业务部门、客户ID、产品系列、收入

数据如下`

 BU      Product_Family   Cost
 ------------------------------ 
  4      Fiat             145 
  5      TI               200

`

BU  CID Product_Family  Revenue
-----------------------------------
 4  c1    Fiat            50
 4  c2    Fiat            80
 4  c3    Fiat            40
 5   c3         TI               40
 5   c2       TI               80
 Sum_of_Revenue for BU,Product_Family wise [4,Fiat]:               (50+80+40) = 170

现在我需要找到每个CID（Customer_ID）的分配成本：计算如下

BU的C1分配成本，产品_家庭[4，菲亚特]=[表1.成本（此处值为145）*表2.C1客户的成本（此处为50）]/收入BU的总和，产品_家族[4，Fiat]（此处值170）。

对于C3[BU，Product_Family-wise（5，TI）]，值应为（200*40）/（40+80）

你能建议我如何设计我的代码来完成这项任务吗。

Answer 1

尝试以下选择

select 
BusinessUnit, CustomerID, ProductFamily , Revenue
, (table1.Cost * table2.Revenue ) 
  / (SUM(Revenue) over(partition by table1.BusinessUnit, table1.ProductFamily )) 
                 AS  Allocated_Cost
from 
    table1 
    INNER JOIN table2 ON ( table1.BusinessUnit =  table2.BusinessUnit
                          AND table1.ProductFamily = table2.ProductFamily )
;

Answer 2

尝试

    SELECT c.cid
         , p.bu
         , p.product_family 
         , p.cost * c.revenue / sor.s   AS allocated_cost
      FROM table1 p
INNER JOIN (
              SELECT bu
                   , product_family
                   , sum(revenue)    AS s
                FROM table2 t2
            GROUP BY bu
                   , product_family
           ) sor
        ON (     sor.bu             = p.bu
             AND sor.product_family = p.product_family )
INNER JOIN table2 c
        ON (     c.bu               = p.bu
             AND c.product_family   = p.product_family )
     WHERE BU = ...
         ;

Answer 3

下面的查询是否适合您？

WITH cust_total_rev AS (
SELECT CID
, SUM(Revenue) AS total_rev
FROM Table2
GROUP BY CID
)
SELECT CID
, (t1.Cost * t2.Revenue)/ctr.total_rev AS Allocated_Cost
FROM Table1 t1
JOIN Table2 t2 ON t1.BU = t2.BU
JOIN cust_total_rev ctr ON ctr.CID = t2.CID
GROUP BY t2.CID