查找比较表列的顶部匹配项

Question

我有一个数据库，最多可从不同来源混合400个桌子。我需要按列相似性将这些表分组到Excel文件中（考虑到表具有0、1、2或所有具有相同名称的列）。挑战是示例如下：

fac.table_1 have columns C1, C2, C3, C4 and C5
dim.table_2 has columns C1, C3, and C5
stg.table_3 has columns C1, C6, and C7
stg.table_4 has columns C2, and C99

...

预期的结果应该是：

sch_name | table_name | ncols | nmatches
  dim    |   table_2  |   3   |   3
  stg    |   table_3  |   3   |   1
  stg    |   table_4  |   2   |   1

我认为方法是将类似代码与计数或相交的内容一起使用，将我想与他人进行比较的表名中的位置：

    SELECT
       schemas.name sch_nm,
       tables.name tb_nm,
       columns.name col_nm
    FROM sys.tables
       LEFT JOIN sys.columns ON tables.object_id = columns.object_id
       LEFT JOIN sys.schemas ON tables.schema_id = schemas.schema_id

Answer 1

您想计算另一个表中列名称的列数吗？

select sch_name, tbl_name, 
       ncols      = count(*), 
       nmatches   = sum(case when col_cnt > 1 then 1 else 0 end),
       percentage = sum(case when col_cnt > 1 then 1 else 0 end) * 100 / count(*) 
from
(
    select sch_name = s.name, 
           tbl_name = t.name,
           col_name = c.name,
           col_cnt  = count(c.name) over(partition by c.name)
    from   sys.schemas s
           inner join sys.tables t  on s.schema_id = t.schema_id
           inner join sys.columns c on t.object_id = c.object_id
    where  t.name in ('table1', 'table2', 'table3', 'table4')
) c       
where tbl_name not in ('table1')
group by sch_name, tbl_name
order by c.tbl_name;

结果：

sch_name	tbl_name	ncols	nmatch
face	table_1	5	4
dim	table_2	3	3
stg	table_3	3	1
stg	table_4	2	1

db＆lt;＆gt;小提琴演示