Postgres находит похожие строки между двумя таблицами
Я пытаюсь найти сходство между строками из разных таблиц. Вот DDL.
CREATE TABLE a (id int, fname text, lname text, email text, phone text);
INSERT INTO a VALUES
(1, 'john', 'doe', '[email protected]', null),
(2, 'peter', 'green', '[email protected]', null);
CREATE TABLE b (id int, fname text, lname text, email text, phone text);
INSERT INTO b VALUES
(null, 'peter', 'glover', '[email protected]', '777'),
(null, null, 'green', '[email protected]', '666');
скажем, у нас есть следующая конфигурация подобия
fname = 0.1
lname = 0.3
email = 0.5
phone = 0.5
так что мы можем сказать, что подобное
(2, 'peter', 'green', '[email protected]', null) and
(null, null, 'green', '[email protected]', '666') is 0.8 (lname + email)
(2, 'peter', 'green', '[email protected]', null) and
(null, 'peter', 'glover', '[email protected]', '777') is 0.1 (fname)
в результате я ожидаю получить данные из таблицы b, которая имеет сходство с таблицей более чем на некоторый порог (скажем, 0,7). Итак, согласно примеру, мне нужно получить smt вот так
id, fname, lname, email, phone, similarity
2, null,'green', '[email protected]', '666', 0.8
где id - это id из аналогичной строки из таблицы a
Я уже пробовал NATURAL FULL OUTER JOIN и EXCEPT, но он не работает для моей цели, или я просто ошибся с smt. также какой индекс подходит для запроса? потому что таблица может иметь миллиард строк
Обновлять: Цель - сопоставить строки. Так что, наверное, лучше хранить всю информацию в одной таблице и выполнять оконную функцию? Логика будет та же, рассчитываем на подобие конфигурации
id | fname | lname | email | phone
---+-------+--------+-----------------+-------
1 | john | doe | [email protected] |
2 | peter | green | [email protected] |
| peter | glover | [email protected] | 777
| | green | [email protected] | 666
после некоторой операции строки с идентификатором null должны быть заполнены идентификатором строки, имеющим наибольшее сходство и больше 0,7, в противном случае сгенерируйте новый
Ответы 1
-- get similarity betweena and b tables
with with_similarity as (
select
a.id, b.id as tmp_id, b.fname, b.lname, b.email, b.phone,
( coalesce((a.fname = b.fname)::int, 0) * 0.1 +
coalesce((a.lname = b.lname)::int, 0) * 0.3 +
coalesce((a.email = b.email)::int, 0) * 0.5 +
coalesce((a.phone = b.phone)::int, 0) * 0.5
) as similarity
from b
cross join a
),
-- as we have matched weight for all rows, we can pickup rank them
matched as (
select *,
ROW_NUMBER() OVER(PARTITION BY tmp_id ORDER BY similarity DESC) AS rk
from with_similarity
)
-- pick up best match and insert matched + not matched rows
select id, fname, lname, email, phone from matched where rk=1 and similarity >= 0.7
union all
select tmp_id, fname, lname, email, phone from matched where similarity < 0.7 and rk = 1;