Блог

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

У меня есть таблица, которая содержит USER_FULL_NAME первичный ключ и около 15 столбцов различных показателей справа. У меня есть несколько случаев, когда несколько USER_FULL_NAME букв пишутся неправильно, но на самом деле их следует суммировать в одну строку. Т. е. «Дэвид Хэндс» можно было бы написать как «Давиде Хэндс», а «Бобби Орр» можно было бы написать как «Боби Ор». Всего в таблице примерно 150 строк, но уникальных около 136 USER_FULL_NAME .

Как я могу написать SQL-запрос для объединения 150 строк в уникальные 136 строк, где те USER_FULL_NAME , которые должны быть объединены, фактически объединены??

Я добавил еще один столбец в матричную таблицу с именем TPS_User_names , один для USER_FULL_NAME и один для DUPLICATE_USER_FULL_NAME . Это запрос, который я придумал, но, похоже, не работает:

 select

tun.user_full_name,

sum(ad.processed_mss)

from all_data ad left join tps_user_names tun on ad.user_full_name = tun.user_full_name and ad.user_full_name = tun.duplicate_user_full_name

where ad.user_full_name = 'A' and ad.user_full_name = 'A1'

group by tun.user_full_name
 

Вряд ли это тривиальная проблема, которую можно решить на практике, даже если на первый взгляд может быть легко уловить «похожие» и, следовательно, предполагаемые идентичные имена.

Подумайте об этом — если бы вы делали это алгоритмически, у вас было бы множество пограничных случаев, когда вам — или компьютеру — нужно было бы сделать вызов: является ли Джон Смит тем же, что и Джон Смит, или Джон П. Смит? Может быть. Может быть, и нет.

Если у вас нет других идентификаторов, узнать это невозможно, а это значит, что вам будет трудно получить из него значимые агрегированные данные. Один из способов выполнить нечеткое сопоставление текста — это вычислить расстояния Левенштейна, способ количественно определить, насколько близки две строки друг к другу, но по указанным причинам вы все равно можете получить ложные срабатывания.

Если ваша проблема является повторяющейся (например, нуждается в частых обновлениях), вы можете переосмыслить структуру своей базы данных и использовать первичный ключ, который не является фактическим именем, и посмотреть, как новые записи добавляются в таблицу. Это идеальное, если возможно, время для оценки, имеете ли вы дело с новым именем пользователя.

Для одноразового использования я бы запросил 16 строк, которые имеют дубликаты, и оценил бы их по отдельности.

Не совсем решение для кодирования, но вы могли бы создать матричную таблицу, содержащую два столбца: дублированные USER_FULL_NAMEs и фактическое USER_FULL_NAME, ЛЕВОЕ СОЕДИНЕНИЕ с этой таблицей и использовать фактическое USER_FULL_NAME всякий раз, когда вы выполняете свои агрегации. Если не пересматривать вашу схему или исправлять ошибки в самой таблице, это лучший способ, imo. В случае, если это продолжает происходить, вы добавляете строку в свою матричную таблицу вместо изменения кода. (Следует также отметить, что вы можете использовать промежуточный шаг, включающий матричную таблицу, для повторной нормализации исходной таблицы. Помимо ключевых проблем с отношениями, вы можете создать матрицу, извлечь агрегированный запрос, а затем использовать это для повторной вставки / обновления исходной таблицы.)

РЕДАКТИРОВАТЬ: поскольку вы предоставили свой код, вот изменения, которые вы можете внести для интеграции вашей новой таблицы в микс:

 SELECT    Nz(tun.user_full_name, ad.user_full_name) As user_full_name_agg,
          SUM(ad.processed_mss)
FROM      all_data ad LEFT JOIN tps_user_names tun
          ON ad.user_full_name = tun.duplicate_user_full_name
GROUP BY  Nz(tun.user_full_name, ad.user_full_name);
 

Nz выберет первое ненулевое значение из tun.user_full_name или ad.user_full_name, которое он найдет. Таким образом, для обманщиков он будет использовать консолидированное user_full_name, которое он находит в tun, для других — в ad.