Как получить все записи в отдельные столбцы с группировкой по ключу?

Node.js Python SQL

1 ответ

Здравствуйте!
Есть таблица product_parameters с тремя столбцами (product_id, param_key, value), в которой хранятся значения параметров товаров. Т.е. для одного товара может быть несколько записей, но при этом только одна запись для товара по какому-либо параметру.
Например

11 | param1 | крупа 11 | param2 | развес 11 | param3 | 45р/кг 44 | param1 | напиток 44 | param3 | 24р/шт 52 | param1 | быт.химия 52 | param3 | 112р/шт

Так вот, как сделать запрос, чтобы одна строка представляла записи по одному товару и значение каждого параметра было в отдельном столбце, название которого - ключ параметра? Т.е. примерно следующее

product_id | param1 | param2 | param3 11 | крупа | развес | 45р/кг 33 | фрукт | упаковка| 64р/шт

Мне нужно получить только параметры с ключами от param1 до param5.
Пытался через case when и group by, но что-то не то выходит.

Дополнительно:

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

Сергей Паньков, я в курсе правил сервиса и не прошу конкретно составлять мне запрос. Можно ведь просто указать, что можно использовать и в сторону чего мне копать. Я ведь предоставил данные, написал, что пробовал. А запрос я сам составлю, просто прошу дать направление.
Вот, мой последний вариант перед вопросом на тостере:

select     product_id,     case WHEN param_key = 'param1' then value end param1,     case WHEN param_key = 'param2' then value end param2,     case WHEN param_key = 'param3' then value end param3,     case WHEN param_key = 'param4'  then value end param4,     case WHEN param_key = 'param5'  then value end param5 from product_parameters group by product_id

select product_id, case WHEN param_key = 'param1' then value end param1, case WHEN param_key = 'param2' then value end param2, case WHEN param_key = 'param3' then value end param3, case WHEN param_key = 'param4' then value end param4, case WHEN param_key = 'param5' then value end param5 from product_parameters group by product_id

Сергей Паньков, плюс ко всему я могу это сделать с 5 джойнами, но подумал, что вдруг есть запрос поэлегантнее, поэтому и спросил на сервисе

Aljo, 5 left djoin'ов (по одному на каждый параметр) - тоже норм вариант, кстати.

Получилось сделать так. Не знаю, почему не работает без SUM

SELECT     product_id,     SUM(CASE WHEN `param_key ` = 'param1' then `value` END) param1,     SUM(CASE WHEN `param_key ` = 'param2' THEN `value` END) param2,     SUM(CASE WHEN `param_key ` = 'param3' THEN `value` END) param3,     SUM(CASE WHEN `param_key ` = 'param4'  THEN `value` END) param4,     SUM(CASE WHEN `param_key ` = 'param5'  THEN `value` END) param5 FROM product_parameters GROUP BY product_id

SELECT product_id, SUM(CASE WHEN `param_key ` = 'param1' then `value` END) param1, SUM(CASE WHEN `param_key ` = 'param2' THEN `value` END) param2, SUM(CASE WHEN `param_key ` = 'param3' THEN `value` END) param3, SUM(CASE WHEN `param_key ` = 'param4' THEN `value` END) param4, SUM(CASE WHEN `param_key ` = 'param5' THEN `value` END) param5 FROM product_parameters GROUP BY product_id

Нормальный вариант. Но лучше max вместо sum.
Без агрегаторной функии нельзя, потому что у вас группировка.
При группировке столбцы, которые не указаны в перечне group by нужно агрегировать.
Я бы, честно сказать, делал сабселектами.
Как я писал в своем ответе:

SELECT   p.id   ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm1') AS param1       ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm2') AS param2       ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm3') AS param3       ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm4') AS param4     FROM products p

SELECT p.id ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm1') AS param1 ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm2') AS param2 ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm3') AS param3 ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm4') AS param4 FROM products p

Такая схема будет упоминать продукты, к которым нет ни одного параметра, или они все "по умолчанию".
Если у вас есть индекс по паре полей (product_id, param_key), то такие сабселекты будут делаться за O(1) и в целом все будет быстро.
Но ваш вариант тоже норм, если для всех продуктов объявлен хотя бы один параметр.

Ответы:

Транспонирование таблиц в SQL не предусмотрено в большинстве БД.
Если набор параметров динамический, то так не получится, потому что не ясно сколько получится столбцов в итоге, а динамически их делать нельзя.
Можно собрать JSON с атрибутами, но это тоже такое себе.
Обычно такое транспонирование делают в коде на бэке, а не в SQL.
Между тем вы не написали даже какая у вас БД и на чем делаете бэкенд.

На постгресе оптимально было бы сделать джойн, группировку, а при агрегации собирать массив key-value, или JSON, тогда у вас будет два столбца: id, params. Второй столбец будет содержать json'ы.

Ещё один способ можно применить при более-менее статичном наборе возможных параметров. Отдельным запросом получаете все возможные уникальные имена параметров в алфавитном порядке и в том же порядке джойните слева (left join) соответствующие значения. При агрегации вы просто собираете csv-строку с строковыми значениями параметров, а для тех параметров, которых нет, будет пустая строка или null.
Можно собирать не строку с разделителями, а массив строк, или json-список.

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

Вам стоило прочитать мой вопрос, я ведь написал, что мне нужны конкретные параметры с ключами param1 - param5.
Aljo, смотрите pivot для вашей субд
Vitsliputsli, пробовал, вообще не получилось даже близко
Aljo, для транспонирования матрицы используется pivot, он может по-разному называться в СУБД, или вообще отсутствовать. При отсутствии, можно его делать через join.
Но, если не получилось, ("что не получилось?" - "ничего не получилось!"), значит для вас эта задача не решаема.

Нужно решить такую задачу?

Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.

Заказать помощь

Лучший ответ

Ирина WP Ответ

Для того чтобы получить все записи в отдельные столбцы с группировкой по ключу, вам нужно использовать операцию PIVOT в SQL. PIVOT позволяет вам транспонировать строки в столбцы, что делает данные более удобными для анализа.

Прежде всего, вам необходимо определить, какие столбцы вы хотите видеть в итоговой таблице. Для этого вам нужно использовать операторы агрегации (например, SUM(), AVG(), MAX(), MIN()) для каждого столбца, который вы хотите видеть в результате.

Пример использования оператора PIVOT в SQL для группировки записей по ключу:

SELECT *
FROM (
    SELECT KEY, VALUE, attribute
    FROM your_table
) AS SourceTable
PIVOT (
    MAX(VALUE)
    FOR attribute IN ([attribute1], [attribute2], [attribute3])
) AS PivotTable;

SELECT * FROM ( SELECT key, value, attribute FROM your_table ) AS SourceTable PIVOT ( MAX(value) FOR attribute IN ([attribute1], [attribute2], [attribute3]) ) AS PivotTable;

В данном примере мы выбираем ключ (key), значение (value) и атрибут (attribute) из вашей таблицы. Затем мы используем оператор PIVOT, чтобы преобразовать атрибуты в столбцы ([attribute1], [attribute2], [attribute3]) и применяем агрегатную функцию MAX() к значениям. Вы можете заменить MAX() на любую другую агрегатную функцию в зависимости от ваших потребностей.

Не забудьте заменить your_table на имя вашей таблицы, а attribute1, attribute2, attribute3 на фактические названия атрибутов, которые вы хотите увидеть в результирующей таблице.

Таким образом, используя оператор PIVOT в SQL, вы сможете получить все записи в отдельные столбцы с группировкой по ключу в удобном для анализа формате.

Другие ответы (0)

Пока нет других ответов. Будьте первым, кто поможет автору.

Ответить на вопрос

комментарий Отменить ответ

Вам также может быть интересно

VPN Как правильно настроить vless для Android TV? 1 ответ Pyrogram Как правильно зарегистрировать юзер бота в Telegram? 1 ответ печатные-платы Как заставить запускаться программу M3.exe от компании Hanxing AOI в инспекционной машине на Windows 7 Pro? 1 ответ code-review Нужен ли код-ревью React/TypeScript проекта? 1 ответ

Как получить все записи в отдельные столбцы с группировкой по ключу?

Дополнительно:

Ответы:

комментарий Отменить ответ

Вам также может быть интересно

Ежедневно, круглосуточно