SQL开发与组合损失率

Question

我开始做的事情：

ID  Earned Premium  Losses  Expenses    Coverage
A22 100             15      5           Collision
A22 100             20      0           PIP
A22 100             5       0           Bodily Injury
A22 130             15      5           Collision
A22 130             20      0           PIP
A22 130             5       0           Bodily Injury

因为赢得的保费会随着时间的推移而增加，为了获得最新的信息，我使用了下面的查询：

select t.*
from (select t.*, row_number() over (partition by id, Coverage order by Earned Premium desc) as seq
      from table t 
     ) t 
where seq = 1;

现在我要说的是：

ID  Earned Premium  Losses  Expenses    Coverage
A22 130             15      5           Collision
A22 130             20      0           PIP
A22 130             5       0           Bodily Injury

现在，我必须做的是创建一个单独的列来计算每个承保范围的损失率，并结合整个ID，问题是我不能只计算我的赚取保费总和，因为“130”是总金额，但由于承保范围，它显示了几次。

我想将我的所有损失和费用相加，并根据ID损失率的130赚取保费计算它们，然后逐行计算我的承保损失率。

我在这里被难住了，也不知道…的语法

损失率=损失+费用/赚取保费

我想要的：

ID  Earned Premium  Losses  Expenses    Coverage    Coverage Loss Ratio ID Loss Ratio
A22 130             15      5           Collision    15%                35%
A22 130             20      0           PIP          15%                35%
A22 130             5       0           Bodily Injury 4%                35%

know know…

Answer 1

您可以使用CTE和子选择来获得用于计算ID丢失率的总数。对于覆盖损失率，您可以直接插入您的公式。您可以根据需要更改要格式化的字段(乘以100，更改为varchar以添加%)，但这将显示您需要的计算。

;WITH CTE AS (
select t.*
from (select t.*, row_number() over (partition by id, Coverage order by EarnedPremium desc) as seq
      from table t 
     ) t 
where seq = 1)
SELECT  *, 
        (Losses + Expenses)/EarnedPremium AS CoverageLossRatio, 
        (SELECT SUM(Losses + Expenses) FROM CTE a WHERE a.ID = b.ID) / EarnedPremium AS IDLossRatio
FROM CTE b;