Выберите все записи, в которых было сопоставлено не менее n из n критериев

Я использую Oracle 12c. Мне интересно, можно ли выбрать все записи, в которых соответствовало n-1 из n критериев?

Пример:

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).

Возможно ли это в Oracle, если мы знаем, что таблица пользователей имеет 5 миллионов записей?

Ответ 1

Общий подход состоит в том, чтобы поставить каждое условие в CASE, возвращающее 1 или 0, и подсчитать количество 1s:

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 в сумму, поэтому вы можете проверить, сколько условий было выполнено.

Ответ 2

Вы можете использовать выражения в предложении 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')

Однако это не очень хорошо.

Ответ 3

Если вы часто запускаете такие запросы (возможно, для разных входов для firstname, lastname и city, которые вам нужно сопоставить), и вам необходимо определить приоритетность выполнения этих запросов по другим запросам (и над выполнением операторов DML), вы можете создать три составных индекса: на (firstname, lastname), на (firstname, city) и на (lastname, city).

Тогда запрос должен быть UNION ALL. Он будет считывать данные три раза вместо одного прохода - но он будет считывать из индексов, что приводит к гораздо более высокой производительности, если только малая часть строк соответствует каждому из трех условий. Тогда только небольшая часть из 5 миллионов строк будет фактически прочитана с диска целиком.

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' (или другие значения!) поставлялись во время выполнения, а не были жестко закодированы в запросе.

Ответ 4

Если у вас есть динамический набор фильтров, который нужно передать в запрос, вы можете 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 |

Если вы хотите получить все столбцы, вы можете присоединиться к нему в исходную таблицу.

Ответ 5

С этим запросом (точно описывая возможные совпадения)

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

Предполагая, что три столбца имеют определенные индексы, вы можете ожидать, что доступ к конкатенированному индексу будет состоять из трех операций INDEX ACCESS.

Вы даже можете определить индекс из двух столбцов, охватывающий ваши варианты соответствия:

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 - только город. Вам придется настроить определение индекса в соответствии с вашими мощностями данных, если это проблема с производительностью.

Вы также можете проверить, если параметр Text Text Index не является тем вариантом, который вам не хватает.