代码之家 › 专栏 › 技术社区 › user315648

规范化表:在一系列行上查找唯一的列(Oracle 10.x)

database-normalization denormalization normalization oracle

user315648 · 技术社区 · 15 年前

我有一张结构如下的桌子:

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(以及一个人的多个版本)数据。我的愿望是规范化该表,但是,由于该表包含大量数据(许多列),我需要确定哪些列应该转到Workers表,哪些列应该转到Persons表。结果应该是这样的:

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

要做到这一点,我需要分析哪些数据在Person的范围上不同于所有唯一的Person(在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

不,想象一下,我有大约10个这样的表需要规范化,每个表中大约有40列。每张桌子可容纳大约500k到5m行。

我需要一个脚本,帮助我分析哪些列移动到哪里。我需要一个脚本,输出在整个表的unique Person范围内更改的所有列。我不知道怎么做。我尝试使用滞后分析函数来与下一行进行比较,但如何输出已更改的列却是我无法理解的。

请告知。

最美好的祝福, 安得烈

2 回复 | 直到 15 年前

Unreason 15 年前

由于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,或者获取核心查询并用您喜欢的语言编写脚本。

编辑: 以上假设没有 NULL s在 column_name .

核心查询的其他变体可以是

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的所有值都相同,则返回一行。 (这个查询也有空值的问题,但我认为空值是一个单独的问题;为了上面的查询,您可以始终将空值强制转换为某个域外值)

上面的查询也可以写成

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。

Hari 12 年前

谢谢,但我通过让Excel在表模式信息上创建一系列选择来解决这个问题。它生成的最后一个查询是一个很长的selects列表,但它可以工作(尽管它运行了一个小时)。“核心查询”(实际上是Excel中用于创建核心查询的公式):

=IF(AND(C17<>"CLOB";C17<>"NCLOB");"SELECT '"&A17&".'||initcap('"&B17&"') description,
decode(count(*),0,'SAME OVE VERSIONS','DIFFERENT OVER VERSIONS') values FROM (SELECT 
objektid, count(DISTINCT nvl("&B17&","&IF(C17="DATE";"'01.02.0004'";IF(C17="VARCHAR2"
;"'!#Â¤Â¤%Â¤(%#Â¤%AS'";"-1234561"))&")) OVER (PARTITION BY objectid) arv FROM "&A17&") 
WHERE number > 1 union all";"SELECT '"&A17&".'||initcap('"&B17&"') description, 'CLOB
field' values from dual union all")