Эффективный способ запроса из таблицы пар ключ-значение с использованием SQL
У меня есть база данных sqlite с двумя таблицами, содержащими информацию о клиентах. Одна таблица (таблица1) хранит фиксированную информацию о клиенте (например, customer_id, имя, возраст и т. д.). Другая таблица (таблица2) хранит свойства в виде словаря (пары ключ-значение) с тремя столбцами — customer_id, property_key, property_value.
Теперь я пытаюсь запросить клиента, сопоставив все его свойства.
SELECT customer_id FROM table1 WHERE name=<SOME-NAMES>
INTERSECT
SELECT customer_id FROM table2 WHERE property_key=<KEY-1> AND property_value=<VALUE-1>
INTERSECT
SELECT customer_id FROM table2 WHERE property_key=<KEY-2> AND property_value=<VALUE-2>...
Я добиваюсь этого, используя INTERSECT, который работает правильно, пока я не встречаю редкую запись, содержащую более тысячи свойств (мне понадобится тысяча INTERSECT), что приводит к ошибке из-за слишком большого количества членов в составном SELECT.
Я не хочу помещать новые фиксированные столбцы в таблицу table1, поскольку свойства property_keys у каждого клиента разные. Я думаю только о получении всех идентификаторов клиентов с 50 свойствами за раз и последующей обработке полученных идентификаторов клиентов в другой программе.
Есть ли какое-нибудь решение, чтобы сделать это более эффективно?
Ответы 1
Найти клиента со всеми совпадающими свойствами можно, подсчитав общее количество свойств этого клиента и сравнив это число с количеством совпадающих/отфильтрованных свойств.
SELECT table1.customer_id
FROM table1
JOIN (
SELECT customer_id, COUNT(*) AS 'count'
FROM table2
GROUP BY customer_id
) properties ON properties.customer_id = table1.customer_id
JOIN (
SELECT customer_id, COUNT(*) AS 'count'
FROM table2
WHERE ( -- your filters here
property_key = 'key1' AND property_value = 'val1' OR
property_key = 'key2' AND property_value = 'val2' OR
property_key = 'key3' AND property_value = 'val3'
)
GROUP BY customer_id
) matches ON matches.customer_id = table1.customer_id
WHERE properties.count = matches.count
Обновлено: этот запрос в его нынешнем виде нельзя использовать для поиска клиентов без свойств.
Возможно,
table2мог бы быть столбцом JSON вtable1. В настоящее время большинство СУБД имеют очень хорошую поддержку JSON. Но я не могу быть уверен в эффективности.