다음과 같이 SQL 쿼리에서 SQL 테이블과 목록 간의 공통 값 찾기

다음과 같이 SQL 쿼리에서 SQL 테이블과 목록 간의 공통 값 찾기

이게 내 샘플 데이터 세트야...

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 Fidle: 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 테이블 사이에서 "유사한 블록 해시"를 찾으려고 합니다.

비율 열 정보:

한다면randomstr1SQL 쿼리에 한 번만 표시되며, 다음으로 최대 1개의 일치 항목을 지정합니다.randomstr1SQL DB에 있습니다.

세 번째 출력 행에 있습니다. ratio0.75가 되는 이유는randomstr1MySQL 테이블에 두 번 나타나더라도 쿼리에 한 번만 나타납니다.세 번째 줄에서 3/4 일치 항목을 찾았습니다. randomstr2는 SQL 쿼리에 2회 이상 표시되므로 3행 모두 일치합니다.

에 대해서pos난 단지 알고 싶을 뿐이야pos의 가치matched blocks.

와 함께ROW_NUMBER()window 함수는 'substr1'이 3회 이상 존재하는지 또는 'bubstr2'가 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

데모를 참조해 주세요.
결과:

> 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

언급URL : https://stackoverflow.com/questions/61259608/find-common-values-between-sql-table-list-in-sql-query-like-this