sql - 如何在 SQL Server 中散列表的列？

Question

我从外部来源接收原始数据文件，需要对其进行分析。我将文件加载到一个表中并将字段设置为 varchars，然后运行一个复杂的 SQL 脚本来进行一些自动分析。我一直试图解决的一个问题是:如何判断一列数据是否与同一表中的 1 个或多个其他列重复？

我的目标是为每一列创建一个散列、校验和或类似的东西，以按照它们出现的顺序查看每行中的列值。我有动态 SQL，它根据 INFORMATION_SCHEMA.COLUMNS 中列出的字段循环遍历每个字段(不同的表将具有可变数量的列)，因此无需担心如何完成该部分。

我一整天都在研究这个问题，但似乎找不到任何合理的方法来散列字段的每一行。 Google 和 StackOverflow 搜索返回如何对数据行执行各种操作，但我找不到太多关于如何在字段上垂直执行相同操作的信息。

因此，我考虑了 2 种可能性并遇到了 2 个障碍:

1. HASHBYTES - 使用“FOR XML PATH”(或类似的)获取每一行并在每行之间使用分隔符，然后使用 HASHBYTES 对长字符串进行哈希处理。不幸的是，这对我不起作用，因为我运行的是 SQL Server 2014，并且 HASHBYTES 限制为 8000 个字符的输入。 (我也可以想象在具有数百万行、循环 200 多列的表上性能会很糟糕)。
2. CHECKSUM + CHECKSUM_AGG - 获取每个值的 CHECKSUM，将其转换为整数，然后对结果使用 CHECKSUM_AGG(因为 CHECKSUM_AGG 需要整数)。这看起来很有希望，但是没有考虑数据的顺序，在不同的行上返回相同的值。此外，碰撞的风险更高。

第二个看起来很有希望，但没有像我希望的那样工作......

declare @t1 table
    (col_1 varchar(5)
    , col_2 varchar(5)
    , col_3 varchar(5));

insert into @t1
values ('ABC', 'ABC', 'ABC')
    , ('ABC', 'ABC', 'BCD')
    , ('BCD', 'BCD', NULL)
    , (NULL, NULL, 'ABC');

select * from @t1; 

select cs_1 = CHECKSUM(col_1)
    , cs_2 = CHECKSUM(col_2)
    , cs_3 = CHECKSUM(col_3)
from @t1;

select csa_1 = CHECKSUM_AGG(CHECKSUM([col_1]))
    , csa_2 = CHECKSUM_AGG(CHECKSUM([col_2]))
    , csa_3 = CHECKSUM_AGG(CHECKSUM([col_3]))
from @t1;

在最后一个结果集中，所有 3 列都返回相同的值:2147449198。

期望的结果:我的目标是让一些代码在其中 csa_1 和 csa_2 带回相同的值，而 csa_3 带回不同的值，表明它是自己独特的集合。

Answer 1

您可以用这种方式比较每个列的组合，而不是使用散列:

select case when count(case when column1 = column2 then 1 else null end) = count(1) then 1 else 0 end Column1EqualsColumn2
, case when count(case when column1 = column3 then 1 else null end) = count(1) then 1 else 0 end Column1EqualsColumn3
, case when count(case when column1 = column4 then 1 else null end) = count(1) then 1 else 0 end Column1EqualsColumn4
, case when count(case when column1 = column5 then 1 else null end) = count(1) then 1 else 0 end Column1EqualsColumn5
, case when count(case when column2 = column3 then 1 else null end) = count(1) then 1 else 0 end Column2EqualsColumn3
, case when count(case when column2 = column4 then 1 else null end) = count(1) then 1 else 0 end Column2EqualsColumn4
, case when count(case when column2 = column5 then 1 else null end) = count(1) then 1 else 0 end Column2EqualsColumn5
, case when count(case when column3 = column4 then 1 else null end) = count(1) then 1 else 0 end Column3EqualsColumn4
, case when count(case when column3 = column5 then 1 else null end) = count(1) then 1 else 0 end Column3EqualsColumn5
, case when count(case when column4 = column5 then 1 else null end) = count(1) then 1 else 0 end Column4EqualsColumn5
from myData a 

这是设置代码:

create table myData
(
  id integer not null identity(1,1)
  , column1 nvarchar (32)
  , column2 nvarchar (32)
  , column3 nvarchar (32)
  , column4 nvarchar (32)
  , column5 nvarchar (32)
)

insert myData (column1, column2, column3, column4, column5) 
values ('hello', 'hello', 'no', 'match', 'match')
,('world', 'world', 'world', 'world', 'world')
,('repeat', 'repeat', 'repeat', 'repeat', 'repeat')
,('me', 'me', 'me', 'me', 'me')

这是强制性的 SQL Fiddle .

此外，为了避免您必须编写此代码，这里有一些代码可以生成上述内容。此版本还将包含处理两列的值为空的情况的逻辑:

declare @tableName sysname = 'myData'
, @sql nvarchar(max) 
;with cte as (
    select name, row_number() over (order by column_id) r
    from sys.columns 
    where object_id = object_id(@tableName, 'U') --filter on our table
    and name not in ('id') --only process for the columns we're interested in
)
select @sql = coalesce(@sql + char(10) + ', ', 'select') + ' case when count(case when ' + quotename(a.name) + ' = ' + quotename(b.name) + ' or (' + quotename(a.name) + ' is null and ' + quotename(b.name) + ' is null) then 1 else null end) = count(1) then 1 else 0 end ' + quotename(a.name + '_' + b.name)
from cte a
inner join cte b
on b.r > a.r
order by a.r, b.r

set @sql = @sql  + char(10) + 'from ' + quotename(@tableName)
print @sql

注意:这并不是说您应该将其作为动态 SQL 运行；相反，您可以使用它来生成代码(除非您需要支持列的数量或名称在运行时可能会发生变化的情况，在这种情况下您显然需要动态选项)。