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

Question

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

极少的编码经验，所以行话可能会造成混淆。

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

数据包括A) 每份保单的交易数据(保费、费用、净收入)B) 理赔数据(发生金额)

问题与唯一标识符有关 - 同一客户可能有多个保单编号，其中 A) 或 B) 未存储在一起。我一直在使用的是系统自己的客户端代码(不同于保单编号)，它将所有保单编号存储在同一客户端下。第二个问题是 claim 数据未映射到此“客户端代码”。 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      |        |

我想过使用 Self Join -

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"

这显然行不通，更不用说当我尝试按保费计算总和时，我开始收到按子句分组的错误消息。

如有任何帮助，我们将不胜感激。

结果:

<表类="s-表"><头>t1.客户端代码t1.保单编号t1.Premiumt1. claim t2.客户端代码<正文>C1123500300C1C2456100C2C17670C1C17670767800C1767800

我排除的其他要考虑的因素:保单年份、由于每月/每年付款等导致的更多交易数据行。

Answer 1

如果您有一个唯一的标识符，那就告诉我您想要一个 INNER 连接。查看内部连接与外部连接的维恩图。内部连接只会让你有交集，所以没有空白。左外连接或右外连接将在一个中显示值，在另一个中显示可能的空白。您可能不需要完整的其他加入。我很少遇到这种情况。

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

在这种情况下，您只有一个包含所有数据的表，因此不需要看到对表 1 本身进行联接的必要性。哦，我看到一个问题，即针对同一保单的多项 claim 将使保费费用出现多次，然后将其加起来不正确。

我将把保单编号从这里省略，因为您的理想表格确实使用它并且希望跨保单而不是保单的总计。

把它们放在一起。

首先，忽略溢价。不要一次做太多。建立起来。

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 将重复，但如果保持保费值(value)不变，保单编号应该是唯一的。

然后您添加聚合以获得客户跨保单的总保费。但我们会把它留到下面。

然后将这两个表连接在一起以处理 claim 和保费。

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”)引用客户。

而且您到处都需要现场引用。

更好的结构应该是这样的。也许是 claim 表(基于事件)和政策(基于契约(Contract))。

client.code
policy.client_id

policy.policy_number
policy.premium_value

claim.value
claim.policy_id

也许 Zoho 不允许您这样改造。希望你能做到。