SQL方法填充唯一标识符的空行

Question

第一次发布帖子，希望能得到一些帮助。

我的编码经验非常有限，因此专业术语可能会让我感到困惑。

我正在尝试在Zoho中使用SQL清理数据。

数据包括： A）每项政策的交易数据（保费，费用，净收益） B）索赔数据（发生金额）

问题出在唯一标识符上——同一客户可能有多个与 A) 或 B) 不存储在一起的政策编号。 我一直在使用系统自己的客户代码（与政策编号不同），其将所有政策编号存储在同一个客户下。第二个问题是索赔数据没有映射到这个“客户代码”上去。Excel 的索引/匹配 /vlookup 是我暂时的解决方案，并且已经可以很好地运行了，但我们正在转向通过 SQL 运行的 Zoho。

例如：

| Client Code | Policy Number  | Premium  | Claims |
| --------    | -------------- | -------- | ------ |
| C1          | 123            | 500      | 300    |
| C2          | 456            | 100      |        |
| C1          | 767            | 0        |        | <---
|             | 767            |          | 800    | <--- want these columns put all under C1

问题：如何使用SQL将左下方的空白填写为C1，然后为每个客户（C1和C2）分组并计算他们的总保费和索赔金额？

目标：

| Client Code |  Premium  | Claims |
| --------    | --------- | ------ | 
| C1          |  500      | 1100   |
| C2          |  100      |        |

我考虑使用自连接 -

SELECT 
t1."Client Code", 
t1."Policy Number", 
t1."Premium", 
t1."Claims", 
t2."Client Code"
FROM table1 as t1
FULL OUTER JOIN 
   (SELECT 
   "Policy Number", 
   "Client Code" 
   FROM table1) t2 
ON t1."Policy Number" = t2."Policy Number"

这明显不起作用，更不用说当我尝试按保费求和时，我开始收到"分组"的错误消息。

任何帮助都将受到赞赏。

结果：

t1.客户代码	t1.保单号码	t1.保费	t1.索赔	t2.客户代码
C1	123	500	300	C1
C2	456	100		C2
C1	767	0		C1
C1	767	0
	767		800	C1
	767		800

其他需要考虑的因素我已经排除：保单年度，由于月度/年度付款而产生的更多交易数据行等。

- insurancenoob

---

你的文本表格和 SQL 语句难以阅读，请使用单一格式对 SQL 进行排版。并且修复表格，可能是 markdown 错误。 - Michael Currin

300去哪了？也就是说，为什么索赔额是800而不是1100？ - Gordon Linoff

嗨，Gordon，抱歉你是正确的，总共应该是1100。@Strawberry，我会很快尝试这个问题——最近才开始研究SQL，因为Zoho不允许创建表格功能（只能选择）。 - insurancenoob

Answer 1

如果您有一个唯一的标识符，那么说明您想要进行内连接。查看内连接和外连接的维恩图。内连接只会显示交集，因此不会出现空白。左连接或右连接将显示一个表中的值，另一个表中可能为空。您可能不需要完全的其他连接，我很少遇到这种情况。

关于聚合或分组。您需要使用GROUP BY和SUM。通过按客户代码分组，该字段将变得唯一。

在这种情况下，您只有一个包含所有数据的表格，因此不需要对表格1进行自身连接。哦，我发现一个问题，同一保单存在多次索赔将使保单保费成本出现多次，然后会被错误地累加。

我将不考虑保单号，因为您的理想表格中使用它，并希望跨保单进行总计，而不是按保单进行总计。

将所有内容整合在一起。

首先，不考虑保费。不要一次做太多事情。逐步构建。

SELECT 
  "Client Code", 
  SUM("Claims") AS `Total Claims`
FROM table1
GROUP BY "Client Code"

Client code    Total claims
C1                   800
C2                   0

现在只需解决保费问题。我假设保费将针对保单号固定（也许不是这样？），但同一客户的多个保单可能会有相同的保费。我不知道如何随着时间推移累加保费……？您将不得不根据业务逻辑来解决这个问题。

SELECT DISTINCT
  "Client Code", 
  "Policy Number",
  "Premium"
FROM table1

结果将是唯一的行。如果保持保费不变，则客户ID将重复，但保单号应该是唯一的。

然后，您可以添加聚合以获取客户跨保单的总保费。但我们将在下面留到最后。

然后，将两个表连接在一起处理索赔和保费。

SELECT 
    table1."Client Code", 
    SUM(table1.Claims) AS `Total Claims`,
    SUM(Policy.Premium) AS `Total Premiums`
FROM table1
INNER JOIN (
    SELECT DISTINCT
        "Client Code", 
        "Policy Number",
        Premium
     FROM table1
) AS Policy ON Policy."Client Code" = table1."Client Code"
GROUP BY "Client Code"

如果您想查看底层数据以确定连接是否有意义，则可以移除SUM和SUM并删除GROUP BY行。

---

您的表格和字段命名不够准确。table1、t1和t2都比较模糊。所有数据都来自于table1，这对于建模来说不是很好。更好的方式是创建一个客户及其代码的表格，其他表格通过行ID引用客户，例如“1”或“789”。

此外，您需要在许多地方使用字段引号。

更好的结构应该是这样的，也许有一个针对索赔（基于事件）和保单（基于合同）的表格。

client.code
policy.client_id

policy.policy_number
policy.premium_value

claim.value
claim.policy_id

也许 Zoho 不允许您进行此类重建。希望您能做到这一点。

- Michael Currin

---

谢谢你，Mike。我唯一的担心是，如果我先求和，我会得到按保单号分类的保费和索赔总额，但是某些客户可能会有不同的保单号，包括其保费和索赔数据（索赔数据条目没有客户代码的原因）。我正在寻找一种方法来将这些不同的保单号与一个客户代码联系起来。（请注意，只有索赔数据随机缺少客户代码）。希望这样说得清楚。 - insurancenoob

好的。如果坏数据是您的问题，那么我建议您浏览您的数据并更新缺失的客户代码。您可以使用已知的客户代码来替换策略号，并在客户代码缺失的同一策略号下填写它。此外，将表格建模为多个表格的好处是每个索赔都有一个策略号，该策略号具有客户和客户代码。因此，要重命名客户代码，只需更改一个记录即可。而且，如果您制定了没有客户的政策，则可以使用参照完整性来提供清晰的错误。 - Michael Currin

现在已经添加了一个缺失的位。AS Policy ON Policy."Client Code" = table1."Client Code"。 - Michael Currin

我建议不要试图让你的查询适用于缺失的客户端代码，因为这会使查询变得更加复杂和容易出错，并且每次进行查询时都必须进行调整，相比之下修复缺失的数据更为明智。 - Michael Currin