如何获取MySQL中满足条件的行的位置 [英] How to get position of rows where a condition meet in MySQL

问题描述

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

This is my sample data set...

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

insert into blockhashtable 
(pos,filehash,blockhash) values 
(1, "randommd51", "randstr1"),
(2, "randommd51", "randstr2"),
(3, "randommd51", "randstr3"),
(1, "randommd52", "randstr2"),
(2, "randommd52", "randstr2"),
(3, "randommd52", "randstr1"),
(4, "randommd52", "randstr7"),
(1, "randommd53", "randstr2"),
(2, "randommd53", "randstr1"),
(3, "randommd53", "randstr2"),
(4, "randommd53", "randstr3"),
(1, "randommd54", "randstr4"),
(2, "randommd54", "randstr55");

...和相同的 http://sqlfiddle.com/#!9/e5b201/14

这是我当前的 SQL 查询和输出:

This is my current SQL query and output:

select pos,filehash,avg( (blockhash in ('randstr1', 'randstr2', 'randstr3') )) as matching_ratio from blockhashtable group by filehash;

pos filehash    matching_ratio
1   randommd51  1
1   randommd52  0.75
1   randommd53  1
1   randommd54  0

我的预期输出是这样的:

My expected output is something like this this:

pos       filehash      matching_ratio
1,2       randommd51    1
1,3       randommd52    0.5
1,2,4     randommd53    0.75
0         randommd54    0

最后一个 row 中的 pos 也可以是 1 ，我可以稍后在 python 中使用自定义条件删除它.

The pos in last row can be 1 also, I can remove it using a custom condition in python later.

基本上，在我的 python 列表中，randstr2 只重复一次，所以我只希望在 SQL 查询中找到最多一个匹配项.这就是为什么 matching_ratio 在我的预期输出中不同的原因.

Basically, in my python list, randstr2 only repeat one time, so I want only maximum one match found in the SQL query. That's why matching_ratio is different in my expected output.

推荐答案

我看不出你的结果集如何对应你的数据集，但你似乎在追求这样的东西......

I don't see how your result set corresponds to your data set, but you seem to be after something like this...

SELECT filehash
     , GROUP_CONCAT(pos ORDER BY pos) pos
     , 1-(COUNT(DISTINCT blockhash IN('randstr1','randstr2','randstr3'))/(COUNT(*))) ratio
  FROM blockhashtable
 GROUP
    BY filehash;
+------------+---------+--------+
| filehash   | pos     | ratio  |
+------------+---------+--------+
| randommd51 | 1,2,3   | 0.6667 |
| randommd52 | 1,2,3,4 | 0.5000 |
| randommd53 | 1,2,3,4 | 0.7500 |
| randommd54 | 1,2     | 0.5000 |
+------------+---------+--------+

这篇关于如何获取MySQL中满足条件的行的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！