Получить данные о разных строках по ID в одной строке

На чтение 4 мин Опубликовано 27.09.2024

Вопрос или проблема

Мои данные выглядят следующим образом:

ID	Год	Объем1	Объем2
AAA	2023	10	20
AAA	2024	5	10
BBB	2023	20	40
BBB	2024	30	60

Я хотел бы, чтобы объемы для каждого ID были в одной строке, где, в идеале, названия колонок расширены с указанием года, разделенного знаком подчеркивания.

Что я хочу получить:

ID	Объем1_2023	Объем2_2023	Объем1_2024	Объем2_2024
AAA	10	20	5	10
BBB	20	40	30	60

Как я могу сделать это в PostgreSQL?

Неперекрестный запрос сводной базы данных будет выглядеть так:

SELECT
    ID,
    MAX(Объем1) FILTER (WHERE Год = 2023) AS Объем1_2023,
    MAX(Объем2) FILTER (WHERE Год = 2023) AS Объем2_2023,
    MAX(Объем1) FILTER (WHERE Год = 2024) AS Объем1_2024,
    MAX(Объем2) FILTER (WHERE Год = 2024) AS Объем2_2024
FROM yourTable
GROUP BY ID
ORDER BY ID;

Сначала включите расширение tablefunc (если оно еще не включено). Затем выполните этот запрос, заменив “your_table_name” на фактическое название вашей таблицы:

SELECT *
FROM crosstab(
    'SELECT ID, 
            CONCAT(''Объем1_'', Год) AS Объем1_Год, 
            Объем1, 
            CONCAT(''Объем2_'', Год) AS Объем2_Год, 
            Объем2
     FROM your_table_name
     ORDER BY ID, Год'
) AS ct(
    ID text, 
    Объем1_2023 int, 
    Объем2_2023 int, 
    Объем1_2024 int, 
    Объем2_2024 int
);

Мое предложение – агрегировать годы и объемы в столбец JSONB.

select id, 
    jsonb_object_agg('объем1_'||год, объем1) || 
    jsonb_object_agg('объем2_'||год, объем2) as volumes
from the_table
group by id;

DB Fiddle Демо

id	объемы
BBB	{“объем1_2023”: 20, “объем1_2024”: 30, “объем2_2023”: 40, “объем2_2024”: 60}
AAA	{“объем1_2023”: 10, “объем1_2024”: 5, “объем2_2023”: 20, “объем2_2024”: 10}

Ответ или решение

Для решения задачи, связанной с преобразованием данных из таблицы, где информация о объемах (Volume1 и Volume2) распределена по годам, необходимо сделать так, чтобы все строки с одинаковым идентификатором (ID) оказались в одной строке. Мы можем воспользоваться агрегирующими функциями в PostgreSQL и, в частности, использовать оператор FILTER, чтобы извлечь нужные объемы по годам.

Вот как можно выполнить эту задачу с помощью SQL-запроса:

SELECT
    ID,
    MAX(Volume1) FILTER (WHERE Year = 2023) AS Volume1_2023,
    MAX(Volume2) FILTER (WHERE Year = 2023) AS Volume2_2023,
    MAX(Volume1) FILTER (WHERE Year = 2024) AS Volume1_2024,
    MAX(Volume2) FILTER (WHERE Year = 2024) AS Volume2_2024
FROM your_table_name
GROUP BY ID
ORDER BY ID;

В этом запросе мы группируем данные по идентификатору (ID) и используем MAX с условием FILTER для выбора значений объемов конкретного года. Таким образом, мы получаем в одной строке все значения объемов, соответствующие каждому ID.

Альтернативный вариант — использовать функцию crosstab, которая позволяет сделать сводные таблицы. Для этого вам нужно сначала включить расширение tablefunc, если оно еще не включено:

CREATE EXTENSION IF NOT EXISTS tablefunc;

После этого можно выполнить следующий запрос, заменив your_table_name на название вашей таблицы:

SELECT *
FROM crosstab(
    'SELECT ID, 
            CONCAT(''Volume1_'', Year) AS Volume1_Year, 
            Volume1, 
            CONCAT(''Volume2_'', Year) AS Volume2_Year, 
            Volume2
     FROM your_table_name
     ORDER BY ID, Year'
) AS ct(
    ID text, 
    Volume1_2023 int, 
    Volume2_2023 int, 
    Volume1_2024 int, 
    Volume2_2024 int
);

Этот метод создает сводную таблицу, которая будет отображать объемы в формате, желаемом вами.

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

SELECT id, 
    jsonb_object_agg('volume1_' || year, volume1) || 
    jsonb_object_agg('volume2_' || year, volume2) AS volumes
FROM your_table_name
GROUP BY id;

Этот запрос создаст новую колонку с именем volumes, в которой будут храниться объемы в формате JSONB. Он позволяет сохранить данные более компактно и может быть полезен, если вы планируете делать дальнейшую обработку.

Таким образом, представленные методы — как с использованием агрегатной функции с фильтрацией, так и с использованием crosstab и JSONB — позволят вам эффективно преобразовать данные из исходного формата в желаемый.