oracle - 标准化表 : finding unique columns over series of rows (Oracle 10. x)

Question

我有一个具有以下结构的表:

WorkerPersons
-------------------------------
ID          (PK)
PersonID    (Indicates which version of Person the record describes)
SomeColumn1 (data specific to Worker)
SomeColumn2 (data specific to Person)
....
SomeColumnN
-------------------------------

如您所见，它是一个非规范化表，在一个表中同时包含 Worker 和 Person(以及一个 Person 的多个版本)数据。我希望对该表进行规范化，但是，由于该表包含大量数据(很多列)，我需要确定哪些列应该转到 Workers 表，哪些列应该转到 Persons 表。结果应该是这样的:

Workers                 Persons
----------------------- ---------------------
ID                      ID
PersonID (now a FK)     PersonColumn1
WorkerColumn1           PersonColumn2
WorkerColumn2           ...
...                     PersonColumnN
WorkerColumnN
----------------------- ---------------------

为此，我需要分析哪些数据在 Person 的范围内不同于所有唯一的 Persons(在 WorkerPersons 中由 PersonID 分隔)。例如:

WorkerPersons
-------------------------------------------------------
ID      PersonID      Column1      Column2      Column3
-------------------------------------------------------
1       PersonA       10.1         John Doe     Single
2       PersonA       10.1         John Doe     Single
3       PersonA       10.1         John Doe     Married
4       PersonB       09.2         Sully        Single
5       PersonB       09.2         Sullivan     Single

在这种情况下，PersonA 有 3 个版本，PersonB 有 2 个版本。 Column1 的值在 Person 的所有版本中始终相同，我们可以将该列移至表 Worker。但是 Column 2 和 Column3 的值会随着 Person 的不同版本而改变，因此这些值应该移到 Person 表中。

没想到，我有大约 10 个这样的表需要规范化，每个表大约有 40 列。每个表包含大约 500k 到 5m 行。

我需要一个脚本来帮助我分析将哪些列移动到哪里。我需要一个脚本来输出整个表中唯一 Person 范围发生变化的所有列。我不知道如何做到这一点。我尝试使用 LAG 分析函数与下一行进行比较，但我不知道如何输出更改的列。

请指教。

祝你好运，安德鲁

Answer 1

由于 10 个表并不多，这里是(某种)伪代码

for each table_name in tables
  for each column_name in columns
    case (exists (select 1
          from table_name
          group by PersonID
          having min(column_name) = max(column_name))
       when true then 'Worker'
       when false then 'Person'
    end case
  end for
end for

利用信息模式和动态查询，您可以制作上述正确的 PL/SQL 或采用核心查询并用您喜欢的语言编写脚本。

编辑:以上假定 column_name 中没有 NULL。

编辑 2:核心查询的其他变体可以是

SELECT 1
FROM 
(SELECT COUNT(DISTINCT column_name) AS distinct_values_by_pid
FROM table_name
GROUP BY PersonID) T
HAVING MIN(distinct_values_by_pid) = MAX(distinct_values_by_pid)

如果每个 PersonID 的所有值都相同，这将返回一行。(这个查询也有 NULL 的问题，但我认为 NULL 是一个单独的问题；为了上述查询的目的，你总是可以将 NULL 转换为一些域外值)

上面的查询也可以写成

SELECT MIN(c1)=MAX(c1), MIN(c2)=MAX(c2), ...
FROM 
(SELECT COUNT(DISTINCT column_name_1) AS c1, COUNT(DISTINCT column_name_2) AS c2, ...
FROM table_name
GROUP BY PersonID) T

这将同时测试多个列，对于属于“Workers”的列返回 true，对于应该进入“Persons”的列返回 false。