代码之家 › 专栏 › 技术社区 › Daniel V

将总价转换为交易

view join sql mysql

Daniel V · 技术社区 · 6 年前

我有一个数据库可以跟踪索赔的规模。

每个索赔都有存储在 claim (如 claim_id 和 date_reported_to_insurer )

每个月,我都会收到一份添加到表中的报告 claim_month . 这包括以下字段: 克拉米德 , month_id [101是2018年1月31日,102是2018年2月28日,等等] paid_to_date .

由于大多数索赔不会随月份而变化,所以我只添加了一个索赔月份的记录,因为这个数字自上个月以来已经发生了变化。因此,索赔可能有6月报告和8月报告,但不是7月报告。这是因为迄今为止支付的金额在6月和8月有所增加,而不是7月。

我现在的问题是,我希望能够核对每月支付的金额。

考虑以下示例数据:

+----------------+----------+----------------+--------------+
| claim_month_id | claim_id | month_id       | paid_to_date |
+----------------+----------+----------------+--------------+
| 1              | 1        | 6              | 1000         |
+----------------+----------+----------------+--------------+
| 5              | 1        | 7              | 1200         |
+----------------+----------+----------------+--------------+
| 7              | 2        | 6              | 500          |
+----------------+----------+----------------+--------------+
| 12             | 1        | 9              | 1400         |
+----------------+----------+----------------+--------------+
| 18             | 2        | 8              | 600          |
+----------------+----------+----------------+--------------+

如果我们假设这是关于权利要求1和2的所有信息,那么这就意味着它们都是2018年6月发生的权利要求。他们的交易应该如下所示:

+----------------+----------+----------------+------------+
| claim_month_id | claim_id | month_id       | paid_month |
+----------------+----------+----------------+------------+
| 1              | 1        | 6              | 1000       |
+----------------+----------+----------------+------------+
| 5              | 1        | 7              | 200        |
+----------------+----------+----------------+------------+
| 7              | 2        | 6              | 500        |
+----------------+----------+----------------+------------+
| 12             | 1        | 9              | 200        |
+----------------+----------+----------------+------------+
| 18             | 2        | 8              | 100        |
+----------------+----------+----------------+------------+

我用的算法是

SELECT claim_month_id, 
       month_id, 
       claim_id, 
       new.paid_to_date - old.paid_to_date AS paid_to_date_change, 
FROM   claim_month AS new 
       LEFT JOIN claim_month AS old 
              ON new.claim_id = old.claim_id 
                 AND ( new.month_id > old.month_id 
                        OR old.month_id IS NULL ) 
GROUP  BY new.claim_month_id 
HAVING old.month_id = Max(old.month_id)

然而,这有两个问题:

在处理多重索赔方面似乎效率很低记录。我没有进行任何基准测试,但很明显。
它没有显示新的声明。在上面的示例中,它只显示第2行、第3行和第5行。

我的算法哪里出错了,有没有更好的逻辑来做这个?

1 回复 | 直到 6 年前

Larry Li 6 年前

使用lag函数获取下一个 paid_to_date 每一个 claim_id ,并使用电流 排卵期 减去下一个 排卵期 .

SELECT 
claim_month_id, 
claim_id, 
month_id,
paid_to_date - LAG(paid_to_date, 1, 0) OVER (PARTITION BY claim_id ORDER BY month_id) AS paid_month
FROM claim

输出表为:

+----------------+----------+----------+------------+
| claim_month_id | claim_id | month_id | paid_month |
+----------------+----------+----------+------------+
|              1 |        1 |        6 |       1000 |
|              5 |        1 |        7 |        200 |
|             12 |        1 |        9 |        200 |
|              7 |        2 |        6 |        500 |
|             18 |        2 |        8 |        100 |
+----------------+----------+----------+------------+