@CricketIvan
Junior Programmer

Как посчитать ФИО в колонках с неправильным порядком?

Дано:
id  |  col1       |  col2         |  col3
--------------------------------------
1   |  Иванов | Иван          | Иванович
2   |  Иван     | Иванович  | Иванов
3   |  Петров | Сергей        | Алексеевич
4   |  Петров | Сергей        | Алексеевич
5   |  Сергей  | Алексеевич| Петров

Иванов Иван Иванович и Иван Иванович Иванов - одно и тоже лицо, но id разный просто перепутан порядок Ф.И.О. Как привести в "правильный порядок" и посчитать (сгруппировать) по ФИО?
  • Вопрос задан
  • 129 просмотров
Решения вопроса 1
@Akina
Сетевой и системный админ, SQL-программист.
Пример реализации для MySQL 8+:
WITH 
cte1 AS ( SELECT id, col1 val FROM test UNION ALL 
          SELECT id, col2 FROM test UNION ALL
          SELECT id, col3 FROM test ),
cte2 AS ( SELECT id, GROUP_CONCAT(val ORDER BY val) FIO
          FROM cte1 
          GROUP BY id )

SELECT test.id, test.col1, test.col2, test.col3, GROUP_CONCAT(cte2_2.id) ids
FROM test
JOIN cte2 cte2_1 USING (id)
JOIN cte2 cte2_2 USING (FIO)
GROUP BY test.id, test.col1, test.col2, test.col3

Вывод:

id	col1		col2		col3		ids
1	Иванов		Иван		Иванович	1,2
2	Иван		Иванович	Иванов		1,2
3	Петров		Сергей		Алексеевич	3,4,5
4	Петров		Сергей		Алексеевич	3,4,5
5	Сергей		Алексеевич	Петров		3,4,5
DEMO

Если нужна ещё и нормализация (т.е. строго в col1 сунуть фамилию, в col2 имя...) - наилучшим вариантом считаю загрузку справочников имён, отчеств и фамилий (вполне доступные справочники) и их использование для категорирования. 99% записей будут полностью обработаны по ним, а оставшийся десяток записей (где два или все три значения отсутствуют в справочниках) можно и вручную обработать, вернее, пополнить справочник, чтобы при втором запуске все 100% были распознаны и категорированы корректно.

выполняться будет на MS SQL Server, версию не могу сказать.

Для MS SQL вместо GROUP_CONCAT будет STRING_AGG(column) [WITHIN GROUP ( ORDER BY column)]. https://docs.microsoft.com/ru-ru/sql/t-sql/functio...
Ответ написан
Пригласить эксперта
Ответы на вопрос 2
@ComodoHacker
Можно только вряд ли запросом. Скорее кодом.

Анализировать окончания слов, составить списки окончаний, характерных для фамилий, имен, отчеств. А также списки исключений. И все равно результат нужно будет проверять глазами и кое-где исправлять.

Очистка данных дело такое, дорогое и хлопотное. И проводить ее желательно до попадания в базу.
Ответ написан
@rPman
так как цель найти одних и тех же людей с перепутанным порядком
найди записи в которых col1 совпадает с col2 потом с col3, потом col2 с col3, получишь список пар, в которых это перепутано, осторожно, вырожденные случаи (например в ФИО записаны инициалы) или к примеру восточные имена (там встречаются очень сложные составные фио, которые записывают кто во что горазд), эти моменты лучше обработать отдельной логикой

select a.*,b.*
from table a inner join table b on
a.col1=b.col2
-- a.col1=b.col3
-- a.col2=b.col3
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы