Как найти общие поля в таблицах?

Question

[hikamurachi]

Есть 2 таблицы A и B, в каждой из них N-ое кол-во полей.
Необходимо найти поля по которым можно было бы выполнить JOIN двух таблиц.
В запросе надо найти поля в которых есть одинаковые данные

Comments

[Rsa97]

JOIN можно выполнить по любым наборам полей, вопрос лишь в полученном результате.
Как думаете, две таблицы teachers (id, name) и students (id, name) можно объединять по id, если там одинаковые данные?

[hikamurachi]

Rsa97, проблема в том что таблицы сделаны так что у них есть что-то общее, но имена полей отличаются т.е.
teachers (id, name) и students (id, name) выглядят как teachers (us_1, fr_2) и students (rt_1, as_4) соответственно я не знаю какое название поля взять, а их еще может быть очень много

[Rsa97]

hikamurachi, Вопрос в том, что нет никакого смысла объединять преподавателей (teachers) и студентов (students) по их id в базе данных, даже если эти id и полностью совпадают.
Совпадение набора данных, особенно числовых, не означает совпадение смысла этих данных.

[hikamurachi]

Rsa97, тут задача сначала найти что можно объединить, а потом уже смотреть надо ли это делать. У базы тупо 0 связей, и имена полей что можно было бы связать переименовывают и руками искать и перебирать это сложно

[Akina]

Сначала получаете сведения о полях - тип данных, размер. Составляете совместимые пары. Для каждой пары получаете количество уникальных значений по отдельности и совпадающих из них. Формально поле для связывания - это поле, где количество совпадающих как минимум 75% от общего, оптимально для хотя бы одной стороны 100%.

[Rsa97]

Akina, Добавлю пять копеек, преимущественные пары - id с одной стороны и не id с другой. И при расчёте совпадений надо исключать записи с NULL.

[Akina]

Rsa97, ну не то чтобы ID - но с одной стороны по-хорошему поле-кандидат должно быть хотя бы уникальным.

[mletov]

можно было бы выполнить JOIN двух таблиц

Что является критерием возможности выполнить JOIN? Одинаковые типы полей?
Вообще хорошо бы наглядный пример увидеть.

[shurshur]

Сама идея бестолковая и в общем случае никак не решаемая. Пример. Пусть у нас есть таблица students (id, факультет_id, курс, общежитие_id, ...). Тут куча разных id и не только id, но и просто числа, по которым можно наугад делать join с какими попало таблицами. Например, если факультетов не менее 5 и общежитий не менее 5, то номер курса 1...5 легко можно соотнести с id=1...5 в этих таблицах.

Поэтому подобные задачи лучше не автоматизировать, а провести анализ базы и выявить все логические связи в ней. Кстати, если базу проектировали хорошо, там и так должны быть связи foreign key.

Answer 1

[alexalexes]

Если задача на знание справочника SQL, то есть такая конструкция:
таблица1 join таблица2 using (общие_имена_полей)
это эквивалент:

таблица1 join таблица2 on таблица1.общее_имя_поле = таблица2.общее_имя_поле...

Вариант с using будет работать, если выдерживается культура именования, при которой в названии поля есть название таблицы.

Comments

[hikamurachi]

Задача найти общие поля по тем данным которые лежат в таблицах

[Akina]

Как раз USING у него не стрельнёт - судя по описанию, там наименования полей вообще берутся с потолка.

[hikamurachi]

Akina, частично так оно и есть

Answer 2

[Константин Цветков]

Никак.

Есть исключение

Если суррогатный ключ GUID, то по одинаковым значениям можно определить связь. В 1С, например.

Есть программы и сервисы, которые импортируют таблицы и их связи (если нет их нет, то без них) из баз данных в диаграмму. Но оная строится не на основе данных, а по предметной области.

Answer 3

[Olteya]

Фактически задача сводится к поиску количества совпадений значений каждого атрибута таблицы А со значениями каждого атрибута таблицы Б.

Можно попробовать используя метаданные построить список запросов вида:

select
(select count(*) from A where A.column_name1 in (select B.column_name1 from B )) as column_name1_x_column_name1,
(select count(*) from A where A.column_name1 in (select B.column_name2 from B )) as column_name1_x_column_name2 и т.д.

Пример для postgres:

with B as
(
select table_name, column_name, data_type from information_schema.columns c where c.table_name in ('product') --название таблицы А
)
, common as (
select A.table_name A_table_name, A.column_name A_column_name,
	B.table_name B_table_name, B.column_name B_column_name from information_schema.columns as A
inner join  B on  A.data_type = B.data_type
where A.table_name in ('printer') --название таблицы Б
)

select '(select count(*) from ' || common.A_table_name || ' where ' || common.A_column_name || ' in (select '|| B_column_name || ' from ' || B_table_name || ' )) as ' || A_column_name || '_x_' || B_column_name || ',' from common

Вариант соединения для примера сделан по типам данных.
Далее оборачиваете еще в один select и выполняете.
Получается что-то вроде:
где видно количество совпадений.
Результат, конечно, не означает, что по этим полям можно соединять таблицы. Это могут быть ссылки на таблицу С, например. Или совпадающие даты. Дальше нужно анализировать.
Если правильно понимаю, у автора таблицы с большим количеством столбцов без описания. Так можно хотя бы сузить диапазон поиска.