时间戳

首页 > 解决方案 > SQL开发与组合损失率

mysql - SQL开发与组合损失率

问题描述

我开始的：

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%

久违了……</p>

标签： mysqlsqlmysql-8.0

解决方案

您可以使用 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;

推荐阅读