问寻找相关属性

EN

with pairs as (
 select l.*, r.name as name2, r.value as value2
 from Attribute l join Attribute r on l.ItemId = r.ItemId and l.name < r.name),
counts as (
 select name,name2,count(distinct value2)
 from pairs l join pairs r using (name,value,name2,value2)
 where l.itemid <= r.itemid group by name,value,name2)
select name,name2 from counts group by name, name2 having max(count)=1;

with associations as (
  -- associations of 
  select
    a1."ItemId" as Id1,
    a2."ItemId" as Id2,
    a1."Name" as Name,
    a1."Value" as Value1,
    a2."Value" as Value2  
  from Attribute a1
  join Attribute a2
    on  a1."ItemId" < a2."ItemId"
    and a1."Name"   = a2."Name"
),
names as (
  select distinct "Name"
  from Attribute
)
select *
from names n1
join names n2
  on n1."Name" < n2."Name"
  and not exists (
    -- try to find a miscorrelation
    select *
    from associations s1
    join associations s2
      on  s1.Id1 = s2.Id1
      and s1.Id2 = s2.Id2
      and s1.name in (n1."Name", n2."Name")
      and s2.name in (n1."Name", n2."Name")
      and s1.value1 = s1.value2
      and s2.value1 != s2.value2
  )
;