n개 이상의 기준 중 n-1개 이상이 일치한 모든 레코드

n개 이상의 기준 중 n-1개 이상이 일치한 모든 레코드

저는 오라클 12c를 사용하고 있습니다.n개의 기준 중 n-1개가 일치한 모든 레코드를 선택할 수 있는지 궁금합니다.

예:

CREATE TABLE users
(id number, 
firstname varchar2(100), 
lastname varchar2(100), 
city  varchar2(100));

insert into users(id, firstname, lastname, city)
 values (1, 'John', 'Smith', 'London');
insert into users(id, firstname, lastname, city)
 values (2, 'Tom',  'Smith', 'London');
insert into users(id, firstname, lastname, city)
 values (3, 'John', 'Davis', 'London');
insert into users(id, firstname, lastname, city)
 values (4, 'John', 'Smith', 'Bristol');
insert into users(id, firstname, lastname, city)
 values (5, 'Tom',  'Davis', 'London');
insert into users(id, firstname, lastname, city)
 values (6, 'Tom',  'Davis', 'Bristol');

 select * from users 
  where firstname = 'John' 
    and lastname = 'Smith'
    and city= 'London'

이 선택을 하면 세 가지 기준(id = 1)과 모두 일치하는 레코드가 하나만 반환됩니다.필요한 것은 세 가지 기준(id = 1, 2, 3, 4) 중 적어도 두 가지와 일치하는 모든 레코드를 반환하는 쿼리입니다.

사용자 테이블이 500만건의 기록을 가지고 있는 것을 알면 오라클에서 가능합니까?

일반적인 접근법은 각각의 조건을 A에 넣는 것입니다.CASE1 또는 0을 반환하고 1의 개수를 계산합니다.

select * from users 
where (CASE WHEN firstname = 'John' THEN 1 ELSE 0 END
    + CASE WHEN lastname = 'Smith' THEN 1 ELSE 0 END
    + CASE WHEN city= 'London' THEN 1 ELSE 0 END) >= 2

각 일치 조건이 합에 1을 기여하므로 얼마나 많은 조건을 만족했는지 확인할 수 있습니다.

where 절에서 식을 사용할 수 있습니다.

select *
from users 
where ( (case when firstname = 'John' then 1 else 0 end) +
        (case when lastname = 'Smith' then 1 else 0 end) +
        (case when city = 'London' then 1 else 0 end)
      ) = 2;

이는 쉽게 일반화되지만 3가지 조건과 2가지 일치 항목의 경우 다음 작업을 수행하기에 충분히 쉽습니다.

where (firstname = 'John' and lastname = 'Smith' and city <> 'London') or
      (firstname = 'John' and lastname <> 'Smith' and city = 'London') or
      (firstname <> 'John' and lastname = 'Smith' and city = 'London')

하지만, 이것은 그다지 좋게 일반적이지 않습니다.

이와 같은 쿼리를 자주 실행하는 경우(아마도 다른 입력에 대해)firstname,lastname그리고.city일치시켜야 함)를 선택하고, 이러한 쿼리의 성능을 다른 쿼리(및 DML 문의 성능)보다 우선해야 하며, 다음과 같은 세 가지 복합 인덱스를 만들 수 있습니다.(firstname, lastname), 위에(firstname, city)등에(lastname, city).

그러면 쿼리는 UNION ALL이어야 합니다.단일 패스 대신 데이터를 세 번 읽지만, 인덱스를 통해 읽음으로써 세 조건 각각에 대해 소수의 행만 일치하면 성능이 훨씬 빨라집니다.그러면 500만 행 중 극히 일부만 실제로 전체 디스크에서 읽을 수 있습니다.

select * from users where firstname = 'John' and lastname = 'Smith'
UNION ALL
select * from users where firstname = 'John' and city = 'London'
                          and (lastname  != 'Smith' or lastname  is null)
UNION ALL
select * from users where lastname = 'Smith' and city = 'London'
                          and (firstname != 'John'  or firstname is null)
;

문자열을 바인딩 변수와 일치하도록 변경할 수 있습니다.'John','Smith'그리고.'London'(또는 다른 값!)은 쿼리에 하드 코딩되는 대신 런타임에 제공됩니다.

쿼리에 전달해야 하는 동적 필터 집합이 있는 경우 다음 작업을 수행할 수 있습니다.UNPIVOT데이터와 값을 필터링한 다음GROUP BY id사용.HAVING필터의 개수가 적어도 정확하게 일치하는지 확인합니다.

SQL Fiddle

Oracle 11g R2 스키마 설정:

CREATE TABLE users(id, firstname, lastname, city) AS
  SELECT 1, 'John', 'Smith', 'London'  FROM DUAL UNION ALL
  SELECT 2, 'Tom',  'Smith', 'London'  FROM DUAL UNION ALL
  SELECT 3, 'John', 'Davis', 'London'  FROM DUAL UNION ALL
  SELECT 4, 'John', 'Smith', 'Bristol' FROM DUAL UNION ALL
  SELECT 5, 'Tom',  'Davis', 'London'  FROM DUAL UNION ALL
  SELECT 6, 'Tom',  'Davis', 'Bristol' FROM DUAL;

쿼리 1:

WITH filters ( key, value ) AS (
  SELECT 'FIRSTNAME', 'John'   FROM DUAL UNION ALL
  SELECT 'LASTNAME',  'Smith'  FROM DUAL UNION ALL
  SELECT 'CITY',      'London' FROM DUAL
)
SELECT id
FROM   users
UNPIVOT( value FOR key IN ( firstname, lastname, city ) ) kv
INNER JOIN filters f
ON ( f.key = kv.key AND f.value = kv.value )
GROUP BY id
HAVING COUNT(*) >= 2

결과:

| ID |
|----|
|  1 |
|  2 |
|  4 |
|  3 |

모든 열을 가져오려면 원래 테이블로 다시 연결할 수 있습니다.

이 쿼리를 사용하여(가능한 일치 항목을 정확하게 설명)

select * from users 
where (firstname = 'John' and lastname = 'Smith' ) or
      (firstname = 'John' and  city = 'London') or
      (lastname = 'Smith' and city = 'London')

세 개의 열에 정의된 인덱스가 있다고 가정하면 세 개의 INDEXACCESS 작업으로 구성된 연결된 인덱스 액세스를 기대할 수 있습니다.

매치 옵션을 포함하는 두 열 인덱스를 정의할 수도 있습니다.

create index users_idx1 on users (lastname,firstname);
create index users_idx2 on users (lastname,city);
create index users_idx3 on users (city,firstname );

이를 통해 다음과 같은 실행 계획을 수립할 수 있습니다.

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |    33 |  5577 |     5   (0)| 00:00:01 |
|   1 |  CONCATENATION               |            |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| USERS      |    11 |  1859 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | USERS_IDX3 |     1 |       |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| USERS      |    11 |  1859 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | USERS_IDX3 |     1 |       |     1   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID| USERS      |    11 |  1859 |     2   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | USERS_IDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LASTNAME"='Smith')
   3 - access("CITY"='London')
   4 - filter(LNNVL("LASTNAME"='Smith') OR LNNVL("CITY"='London'))
   5 - access("CITY"='London' AND "FIRSTNAME"='John')
   6 - filter((LNNVL("FIRSTNAME"='John') OR LNNVL("CITY"='London')) AND 
              (LNNVL("LASTNAME"='Smith') OR LNNVL("CITY"='London')))
   7 - access("LASTNAME"='Smith' AND "FIRSTNAME"='John')

7행과 5행의 액세스는 두 열 모두에 술어를 사용하고 3행의 액세스는 도시만 사용합니다. 성능 문제인 경우 데이터 카디널스에 따라 인덱스 정의를 조정해야 합니다.

Oracle Text Index가 누락된 옵션이 아닌 경우 확인할 수도 있습니다.

언급URL : https://stackoverflow.com/questions/48905490/select-all-records-in-which-at-least-n-1-of-n-criteria-has-been-matched