Выберите несколько значений строки в одну строку с многотабличными предложениями

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

Я нашел эту тему ... что очень похоже на то, что мне нужно, за исключением того, что это для MySQL, и я продолжал сталкиваться с ошибками, пытаясь перевести его в синтаксис psql. Надеясь, что кто-то может помочь мне собрать все вместе. Вот сценарий:

Атрибут

attrib_id | attrib_name

Атрибут пользователя

user_id | attrib_id | value

Вот небольшой пример того, как выглядят данные:

Атрибут

attrib_id | attrib_name
-----------------------
1         | attrib1
2         | attrib2
3         | attrib3
4         | attrib4
5         | attrib5

UserAttribute – для каждого user_id может быть до 15 attrib_id/value.

user_id | attrib_id | value
----------------------------
101     | 1         | valueA
101     | 2         | valueB
102     | 1         | valueC
102     | 2         | valueD
103     | 1         | valueA
103     | 2         | valueB
104     | 1         | valueC
104     | 2         | valueD
105     | 1         | valueA
105     | 2         | valueB

Вот что я ищу

Результат

user_id    | attrib1_value | attrib2_value
--------------------------------------------------------
101        | valueA        | valueB
102        | valueC        | valueD
103        | valueA        | valueB
104        | valueC        | valueD
105        | valueA        | valueB

Как показано, я ищу отдельные строки, содержащие: - user_id из таблицы UserAttribute - значения атрибутов из таблицы UserAttribute

Примечание. Мне нужны только значения атрибутов из таблицы UserAttribute для двух конкретных имен атрибутов в таблице атрибутов.

Опять же, будем очень признательны за любую помощь или ссылку на существующее решение.

ОБНОВЛЕНИЕ:

@ronin предоставил запрос, который дает желаемые результаты:

SELECT ua.user_id
      ,MAX(CASE WHEN a.attrib_name = 'attrib1' THEN ua.value ELSE NULL END) AS attrib_1_val
      ,MAX(CASE WHEN a.attrib_name = 'attrib2' THEN ua.value ELSE NULL END) AS attrib_2_val
  FROM UserAttribute ua
  JOIN Attribute a ON (a.attrib_id = ua.attrib_id)
  WHERE a.attrib_name IN ('attrib1', 'attrib2')
  GROUP BY ua.user_id;

Чтобы опираться на это, я попытался добавить соответствие шаблону «НРАВИТСЯ» в условии «КОГДА» (против ua.value), но все заканчивается как значение «ЛОЖЬ». Начну новый вопрос, чтобы посмотреть, можно ли это включить, если я не могу понять это. Спасибо всем за помощь!!


postgresql select aggregate-functions aggregate-filter
person vmoralito    schedule 10.02.2015    source источник
comment
Какие ошибки вы получили? Какой запрос вы пробовали? Пожалуйста, отредактируйте свой вопрос, чтобы показать эти вещи   -  person Bohemian♦    schedule 11.02.2015
comment
Похоже, вам нужна какая-то сводная таблица.   -  person Andreas    schedule 11.02.2015
comment
Я не уверен, что понимаю.. если у клиента есть 15 атрибутов, вам нужно 15 столбцов?   -  person Joe Love    schedule 11.02.2015
comment
@Bohemian Bohemian - я получал ошибки группировки и недопустимые ссылки. Я безуспешно шел по маршруту вложенных подзапросов/соединений.   -  person vmoralito    schedule 11.02.2015
comment
@JoeLove - мне не нужны были все 15, только 2 атрибута.   -  person vmoralito    schedule 11.02.2015
comment
Извините, ребята, я думал, что буду получать электронные письма, когда кто-то публикует комментарии / ответы. Ответ @ronin дал мне довольно хорошую отправную точку. Я хотел бы иметь возможность фильтровать результаты в запросе sql, но я мог бы просто сделать это в остальной части кода.   -  person vmoralito    schedule 11.02.2015
comment
Ну, я думаю, что присоединение к таблице дважды было бы намного проще.   -  person Joe Love    schedule 11.02.2015
comment
Поскольку на ваш первоначальный вопрос был дан правильный ответ, возможно, будет лучше создать новый вопрос. В противном случае вы можете свести на нет честные усилия отвечающих и запутать всех. усилие Вы всегда можете сослаться на это для контекста.   -  person Erwin Brandstetter    schedule 12.02.2015
comment
@vmoralito Да, наверное, лучше опубликовать еще один вопрос. Вы можете использовать LIKE в операторе CASE: CASE WHEN a.attrib_name LIKE 'attrib%1' THEN ua.value ELSE NULL END, но в условии, а не в значении.   -  person Glenn    schedule 12.02.2015

Ответы (3)


arrow_upward
3
arrow_downward

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

SELECT user_id
      ,CASE WHEN attrib_id = 1 THEN value ELSE NULL END AS attrib_1_val
      ,CASE WHEN attrib_id = 2 THEN value ELSE NULL END AS attrib
SELECT user_id
      ,MAX(CASE WHEN attrib_id = 1 THEN value ELSE NULL END) AS attrib_1_val
      ,MAX(CASE WHEN attrib_id = 2 THEN value ELSE NULL END) AS attrib_2_val
  FROM UserAttribute
  GROUP BY user_id;

val
  FROM UserAttribute;

Затем сожмите матрицу с помощью агрегатной функции:

SELECT user_id
      ,MAX(CASE WHEN attrib_id = 1 THEN value ELSE NULL END) AS attrib_1_val
      ,MAX(CASE WHEN attrib_id = 2 THEN value ELSE NULL END) AS attrib_2_val
  FROM UserAttribute
  GROUP BY user_id;

В ответ на комментарий поиск по имени атрибута, а не по идентификатору:

SELECT ua.user_id
      ,MAX(CASE WHEN a.attrib_name = 'attrib1' THEN ua.value ELSE NULL END) AS attrib_1_val
      ,MAX(CASE WHEN a.attrib_name = 'attrib2' THEN ua.value ELSE NULL END) AS attrib_2_val
  FROM UserAttribute ua
  JOIN Attribute a ON (a.attrib_id = ua.attrib_id)
  WHERE a.attrib_name IN ('attrib1', 'attrib2')
  GROUP BY ua.user_id;
person Glenn    schedule 11.02.2015
comment
Это отличная отправная точка! Не уверен, что я бы добрался сюда сам. Насколько сложнее было бы фильтровать на основе attrib_name в таблице атрибутов (поскольку идентификатор может различаться между экземплярами базы данных, которые я запрашиваю)? Кроме того, чтобы добавить, если значение содержит «строку», вместо этого вернуть это значение? - person vmoralito; 11.02.2015
comment
Я попытался добавить соответствие шаблону «НРАВИТСЯ» в условии «КОГДА» (против ua.value), но все заканчивается как значение «ЛОЖЬ». Возможно ли это сделать даже в этом случае? Если нет, я разберусь с регулярным выражением в PHP-коде. - person vmoralito; 11.02.2015
comment
@vmoralito: Почти все возможно. Но, пожалуйста, не задавайте вопросы в комментариях. Обновите свой вопрос или просто начните новый. - person Erwin Brandstetter; 11.02.2015

arrow_upward
1
arrow_downward

Начиная с Postgres 9.4, вы можете использовать более простой агрегированное предложение FILTER:

SELECT user_id
      ,MAX(value) FILTER (WHERE attrib_id = 1) AS attrib_1_val
      ,MAX(value) FILTER (WHERE attrib_id = 2) AS attrib_2_val
FROM   UserAttribute
WHERE  attrib_id IN (1,2)
GROUP  BY 1;

Для большего количества атрибутов или максимальной производительности обратитесь к crosstab() из дополнительного модуля tablefunc (Postgres 8.3+). Подробности здесь:

person Erwin Brandstetter    schedule 11.02.2015
comment
На данный момент я заблокирован на 9.1.3 (поддерживается поставщиком), но спасибо за комментарий. Я сохраню ссылки отмеченными. - person vmoralito; 11.02.2015

arrow_upward
0
arrow_downward

Как насчет такого:

select ua.user_id, a.attrib_name attrib_value1, a2.attrib_name attrib_value2
from user_attribute ua
left join attribute a on a.atribute_id=ua.attribute_id and a.attribute_id in (1,2)
left join user_attribute ua2 on ua2.user_id=ua.user_id and ua2.attribute_id > ua.attribute_id
left join attribute a2 on a2.attribute_id=ua2.attribute_id and a2.attribute_id in (1,2)
person Joe Love    schedule 11.02.2015
comment
Сделал это после настройки, чтобы соответствовать фактическим именам таблиц, но я получаю эту ошибку: ОШИБКА: столбец a.value не существует LINE 1: выберите ua.user_id, a.value attrib_value1, a2.attrib_value_2 - person vmoralito; 11.02.2015
comment
Пришлось добавить несколько запятых в SELECT и снова переименовать таблицы, но теперь я получаю это: ОШИБКА: столбец a.atribute_id не существует СТРОКА 3: левое соединение ccs_attribute a на a.atribute_id=ua.attribute_id a... - person vmoralito; 11.02.2015
comment
Мне пришлось внести еще несколько правок, чтобы заставить это работать. Этот запрос выполняется, но я не получаю ожидаемых результатов: select ua.user_id, a.name, ua.value, a2.name, ua2.value from ccs_user_attribute ua left join ccs_attribute a on a.attribute_id=ua.attribute_id and a.attribute_id in (1,2) left join ccs_user_attribute ua2 on ua2.user_id=ua.user_id and ua2.attribute_id > ua.attribute_id left join ccs_attribute a2 on a2.attribute_id=ua2.attribute_id and a2.attribute_id in (1,2); Это также заставляет меня знать attrib_id, который зависит от экземпляра. - person vmoralito; 11.02.2015
comment
Пытаюсь понять, что вы здесь делаете select ua.user_id, a.attrib_name attrib_value1, a2.attrib_name a2.attrib_value_2 , но получаю синтаксическую ошибку. Обратите внимание, что attrib_value1 нигде не существует как столбец, это именно то, что я хотел, чтобы отображалась таблица результатов. - person vmoralito; 11.02.2015
comment
У меня была опечатка - исправил. Возможно, если бы у нас был SQLfiddle, с которым можно было бы поиграть, было бы проще. Надеюсь, это сработает, я просто делаю пару соединений с одной и той же таблицей, чтобы получить каждый атрибут. Извините за долгую задержку с ответом. - person Joe Love; 12.02.2015