在T-SQL汉明重量/数量的人口 [英] Hamming weight/population count in T-SQL

问题描述

我在寻找一个快速的方法来计算海明重量/数量的人口/ A BINARY（1024）领域的第1位的数字。 MySQL有一个BIT_COUNT函数，它类似的东西。我找不到在T-SQL类似的功能？

I'm looking for a fast way to calculate the hamming weight/population count/"the number of 1 bits" of a BINARY(1024) field. MySQL has a BIT_COUNT function that does something like that. I couldn't find a similar function in T-SQL?

或者，你会建议存储另一种类型的字段中的二进制数据？

Or would you suggest storing the binary data in a field of another type?

如果你不知道我在说什么，这里有一个维基百科文章关于汉明权

If you don't know what I'm talking about, here's a Wikipedia article about the hamming weight.

推荐答案

您可以使用一个辅助表precalculated汉明权重小的数字，如字节，那么相应的分割的价值，加入到帮助表，并得到部分Hamming重​​量作为价值的汉明权重之和：

You could use a helper table with precalculated Hamming weights for small numbers, like bytes, then split the value accordingly, join to the helper table and get the sum of partial Hamming weights as the value's 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

这篇关于在T-SQL汉明重量/数量的人口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！