我正在寻找一种快速计算BINARY(1024)字段的汉明重量/总体数/“1位数”的方法. MysqL有一个BIT_COUNT函数可以做类似的事情.我在T-sql中找不到类似的功能？
或者您是否建议将二进制数据存储在另一种类型的字段中？
如果你不知道我在说什么,这里是Wikipedia article about the hamming weight.
解决方法
您可以使用具有预先计算的汉明权重的辅助表来获取小数字(如字节),然后相应地拆分值,连接到辅助表并获得部分汉明权重的总和作为值的汉明权重：
- -- define Hamming weight helper table
- DECLARE @hwtally TABLE (byte tinyint,hw int);
- INSERT INTO @hwtally (byte,hw) VALUES (0,0);
- INSERT INTO @hwtally (byte,hw) SELECT 1 - byte,1 - hw FROM @hwtally;
- INSERT INTO @hwtally (byte,hw) SELECT 3 - byte,2 - hw FROM @hwtally;
- INSERT INTO @hwtally (byte,hw) SELECT 7 - byte,3 - hw FROM @hwtally;
- INSERT INTO @hwtally (byte,hw) SELECT 15 - byte,4 - hw FROM @hwtally;
- INSERT INTO @hwtally (byte,hw) SELECT 31 - byte,5 - hw FROM @hwtally;
- INSERT INTO @hwtally (byte,hw) SELECT 63 - byte,6 - hw FROM @hwtally;
- INSERT INTO @hwtally (byte,hw) SELECT 127 - byte,7 - hw FROM @hwtally;
- INSERT INTO @hwtally (byte,hw) SELECT 255 - byte,8 - hw FROM @hwtally;
- -- calculate
- WITH split AS (
- SELECT SUBSTRING(@value,number,1) AS byte
- FROM master.dbo.spt_values
- WHERE type = 'P' AND number BETWEEN 1 AND LEN(@value)
- )
- SELECT
- Value = @value,HammingWeight = SUM(t.hw)
- FROM split s
- INNER JOIN @hwtally t ON s.byte = t.byte
