mysql - 像这样在 SQL 查询中查找 SQL 表和列表之间的公共(public)值

Question

这是我的示例数据集...

CREATE TABLE blockhashtable (id int PRIMARY KEY AUTO_INCREMENT,pos int,filehash varchar(35), blockhash varchar(130) );

insert into blockhashtable 
(pos,filehash,blockhash) values 
(1, "random_md51", "randstr1"),
(2, "random_md51", "randstr2"),
(3, "random_md51", "randstr3"),
(1, "random_md52", "randstr2"),
(2, "random_md52", "randstr2"),
(3, "random_md52", "randstr2"),
(4, "random_md52", "randstr1"),
(5, "random_md52", "randstr7"),
(1, "random_md53", "randstr2"),
(2, "random_md53", "randstr1"),
(3, "random_md53", "randstr2"),
(4, "random_md53", "randstr1"),
(1, "random_md54", "randstr1"),
(2, "random_md54", "randstr55");

当前的 SQL 查询(需要修复):

SELECT filehash
     , GROUP_CONCAT(pos ORDER BY pos) pos
     , (avg(blockhash IN('randstr1','randstr2','randstr3','randstr2','randstr2'))) as ratio
  FROM blockhashtable
 GROUP
    BY filehash

电流输出(需要修复)

filehash    pos        ratio
random_md51 1,2,3      1
random_md52 1,2,3,4,5  0.8
random_md53 1,2,3,4    1
random_md54 1,2        0.5

SQL fiddle :http://sqlfiddle.com/#!9/6b5220/10

预期输出:

filehash    pos        ratio
random_md51 1,2,3      1
random_md52 1,2,3,4    0.8
random_md53 1,2,3      0.75
random_md54 1          0.5

我基本上是在尝试在查询列表和 sql 表之间找到“类似的 blockhash”。

关于比率列:

如果 randomstr1 在 SQL 查询中只出现一次，那么我希望 SQL 数据库中的 randomstr1 最多匹配 1 次。

在第三个输出行。 ratio 是 0.75 因为 randomstr1 在查询中只出现了一次，即使它在 MySQL 表中出现了两次。所以在第三行，我们找到了 3/4 匹配。 randomstr2 在第三行中匹配了两次，因为它在 SQL 查询中出现了 2 次或更多次。

关于 pos。我只想知道 匹配 block 的 pos 值。

Answer 1

使用 ROW_NUMBER() 窗口函数，您可以检查“randomstr1”是否存在超过 3 次或“randomstr2”是否存在超过 3 次，因此您可以忽略它们:

with 
  row_numbers as (
    select *, 
      row_number() over (partition by filehash, blockhash order by pos) rn
    from blockhashtable 
  ),
  cte as (
    select *, 
    (blockhash = 'randstr1' and rn = 1)
    or 
    (blockhash = 'randstr2' and rn <= 3)
    or 
    (blockhash = 'randstr3') valid
    from row_numbers
  )
select filehash,
  group_concat(case when valid then pos end order by pos) pos,
  avg(valid) as ratio
from cte
group by filehash

参见 demo .
结果:

> filehash    | pos     |  ratio
> :---------- | :------ | -----:
> random_md51 | 1,2,3   | 1.00
> random_md52 | 1,2,3,4 | 0.80
> random_md53 | 1,2,3   | 0.75
> random_md54 | 1       | 0.50