Блог

Главная - Вопросы по программированию - Как объединить две таблицы — Когда в столбце первичного ключа имеется несколько значений, которые вы будете использовать для ОБЪЕДИНЕНИЯ

Как объединить две таблицы — Когда в столбце первичного ключа имеется несколько значений, которые вы будете использовать для ОБЪЕДИНЕНИЯ

#postgresql

Вопрос:

Я хотел бы присоединиться к следующим двум таблицам

Пользователи:

идентификатор пользователя имя пользователя
12 Джон
13 Брайан
14 Стив
15 Барри

посещения:

идентификатор пользователя посещение_страницы
пользователь_12 gasvhs
пользователь_12,пользователь_14 dfvjk
пользователь_15 djvnjv
пользователь_14,пользователь_15 sdhjcb

ОЖИДАЕМЫЕ РЕЗУЛЬТАТЫ:

посещение_страницы имя пользователя
gasvhs Джон
dfvjk Джон;Стив
djvnjv Барри
sdhjcb Стив;Барри

Используя следующий запрос, я получаю результат, показанный ниже

Запрос, который я написал:

 SELECT visited_page, user_name
FROM VISITS
JOIN 
USERS on substring(user_id::VARCHAR FROM '[0-9] ')::INT = users.user_id

РЕЗУЛЬТАТЫ, КОТОРЫЕ Я ПОЛУЧИЛ:

посещение_страницы имя пользователя
gasvhs Джон
dfvjk Джон
djvnjv Барри
sdhjcb Стив

Есть какие-нибудь предложения о том, как получить ожидаемые результаты ?

Комментарии:

1. Пожалуйста, не размещайте здесь ссылки на фотографии. И, пожалуйста, также не включайте фотографии напрямую. Здесь, на SO, рекомендуется публиковать все ваши данные в виде текста (обычно в блоках кода с использованием тройных обратных ссылок). Пожалуйста, переработайте свой вопрос, спасибо.

2. Что с этим substring(user_id::VARCHAR FROM '[0-9] ')::INT такое ? Почему это visits.user_id не целочисленный столбец?

3. На самом деле не совсем ясно, как вы приходите к этому ожидаемому результату. У вас есть четыре пользователя, и ровно по одному посещению каждым из них в ваших данных примера.

4. Привет @Berhi — Это визит.идентификатор пользователя-это ТЕКСТ.

5. Извините, ребята. В приведенных мной образцах данных были некоторые проблемы, и я просто исправил их

Ответ №1:

Это отличный пример того, почему хранение списка, разделенного запятыми, — такая очень, очень плохая идея.Это уже достаточно плохо, зачем вам когда-либо добавлять к идентификатору пользователя текстовую строку? :(. Первое, что нужно сделать, это развернуть visits.user_id в отдельные строки, а затем удалить префикс. В результате получается то, какой visits должна была быть таблица в первую очередь (за вычетом обязательного FK, конечно).

 select (replace(user_id,'user_',''))::integer user_id,visited_page
  from ( select unnest(string_to_array(user_id, ',')) user_id, visited_page  
           from visits
       ) s;

Имея это на месте, мы можем ПРИСОЕДИНИТЬ его к пользователям и агрегировать имя пользователя по странице; Давая окончательный запрос: (см. демонстрацию)

 select v.visited_page
     , string_agg( u.user_name, ',' order by u.user_name) users_visiting
  from users u
  join( select (replace(user_id,'user_',''))::integer user_id,visited_page
          from ( select unnest(string_to_array(user_id, ',')) user_id, visited_page  
                   from visits
                ) s
           ) v
      on (v.user_id = u.user_id) 
group by v.visited_page
order by v.visited_page;

Предложение: Нормализуйте таблицы и не сохраняйте списки. Это приведет к упрощению запросов, повышению производительности и обеспечению целостности данных. Рассмотрим, что произойдет с текущей схемой, если бы идентификатор пользователя был просто перенесен (потенциально чрезвычайно распространенная ошибка) как 41 вместо 14, а пользователь 41 не существовал. И не создавайте ненужных префиксов к значениям данных, чтобы сообщить вам, что это такое, имя уже делает это — по крайней мере, если вы выбираете хорошие имена и непротиворечивы. Но это темы для другого дня.

Метки: ,