如何在SQL查询中根据此条件找到类似的行百分比匹配？

Question

我有这样一个SQL表：

MD5                         BlockHash
randommd51                  randstr1
randommd51                  randstr2
randommd51                  randstr3
randommd52                  randstr2
randommd52                  randstr2
randommd52                  randstr1
randommd52                  randstr7
randommd53                  randstr2
randommd53                  randstr1
randommd53                  randstr2
randommd53                  randstr3
randommd54                  randstr4
randommd54                  randstr55

Python中还有一个名为"query“(dictonary)的变量。字典键是MD5值，值是"BlockHash“。

如下所示：

{ "randommd55": ["randomstr1", "randomstr2", "randomstr1"] }

我的主要目标是：

我想搜索类似的文件。MD5值表示完整文件的MD5值。即使文件中有1位的差异，它也会改变.

对于每个文件，我随机获取1KB块并对它们进行散列。

现在，我有了一个由多个块哈希组成的查询字典。我想将查询块与SQL表中的文件进行比较。

我想看看查询文件和sql文件之间有多少块哈希匹配(MD5是每个文件的unqiue )。

例如：

md5=="randommd51“所在的文件有两个相同的块(randstr1，randstr2)。所以它是2/3 = 66.666%的匹配。

md5=="randommd52“所在的文件也有两个相同的块(randomstr1，randomstr2)。所以这是一个2/4，50%的匹配。请注意，randomstr2在此文件中重复两次。但它只在queryhash中重复了一次。因此，只有2/4匹配(50%)。

我想以一种快速的方式计算SQL中每个唯一MD5的百分比匹配。我将有数百万排

Answer 1

如果我正确理解，您希望获得与每个MD5匹配的块散列的比例：

select md5,
       avg( (blockhash in ('randomstr1', 'randomstr2', 'randomstr3') ) as matching_ratio
from t
group by md5

Answer 2

如果我理解您的问题，对于您的md5字典中的每个query键/块列表值，您希望为该md5值计算数据库中块值匹配的百分比。

def flatten(items):
    for x in items:
        if isinstance(x, (list, tuple)):
            yield from flatten(x)
        else:
            yield x

query =  { "randommd55": ["randomstr1", "randomstr2", "randomstr1"] } # plus other key, value entries

for md5, blockhash_list in query.items():
    set1 = set(blockhash_list) # set of blockhash values in query
    # conn is database connection using perhaps pymysql
    cursor = conn.cursor()
    cursor.execute('select BLOCKHASH from the_table where MD5 = %s', (md5,))
    set2 = set(flatten(cursor.fetchall())) # set of blockhash values on database for this md5
    # do we need to test for an empty set2; we don't want to divide by zero?
    l = len(set2)
    if l == 0:
        print('md5 {} does not exist on the database.'.format(md5))
    else:
        fraction = len(set1 & set2) / l
        print('md5 = {}, percentage = {:.3}'.format(md5, fraction))