Блог

Главная - Вопросы по программированию - РАЗВОРОТ с помощью PostgreSQL

РАЗВОРОТ с помощью PostgreSQL

#postgresql

Вопрос:

У меня возникли проблемы с выполнением разворота в PostgreSQL 12. Мне приходится работать со столом, который сконструирован неловко. Это аналогичная таблица, которую я создал (только для простого представления)-

 CREATE TABLE test(Product_num INT, SN INT, Attribute VARCHAR(100), Value DECIMAL, Note VARCHAR(50) );

Следующим шагом был импорт CSV-файла с помощью функции КОПИРОВАНИЯ-

 COPY public.test from 'C:File Locationtest.csv' DELIMITER ',' csv HEADER;

Я получаю таблицу с более чем 30 тыс. строк, которая выглядит так-

 Product_num |    SN    |   Attribute   |   Value   |  Note  |
    100         9225       Unit sold       50          USA
    100         9225       Unit price      4.99
    100         9225       Num_boxes       2.5
    101         9226       Unit sold       1           GER
    101         9226       Unit price      920
    101         9226       Num_boxes       2

Я хочу получить таблицу, похожую на следующую таблицу-

 Product_num |    SN    |   Unit Sold   |   Unit price   |  Num_boxes |   Note
    100     |    9225  |        50     |       4.99     |    2.5     |   USA           
    101     |    9226  |        1      |       920      |     2      |   GER

Я попробовал несколько методов, включая crosstab (), а также попытался объединить несколько столбцов, но столкнулся с некоторыми проблемами.
В таблице есть некоторые вопросы, которые необходимо принять во внимание-

  1. Столбец атрибутов не идентичен для каждого SN — это означает, что каждый SN имеет разные атрибуты, некоторые идентичные, а некоторые нет.
  2. Существует множество нулевых значений.
  3. Существует множество значений 0.

Надеюсь, мне удалось объясниться как можно лучше, спасибо

Ответ №1:

Я бы объединил все атрибуты в структуру JSON, а затем извлек их в окончательном запросе:

 select product_num, sn, 
       (attributes ->> 'Num_boxes')::decimal as num_boxes,
       (attributes ->> 'Unit sold')::decimal as unit_sold,
       (attributes ->> 'Unit price')::decimal as unit_price,
       note
from  (
  select product_num, sn, max(note) as note,
         jsonb_object_agg(attribute, value) as attributes
  from test
  group by product_num, sn
) t
order by product_num, sn;

Если будут добавлены новые атрибуты, вам потребуется расширить внешний список выбора, чтобы отразить это. Невозможно создать динамический список столбцов без изменения запроса. В SQL число и типы столбцов запроса должны быть известны до запуска инструкции.

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

1. Это хорошее решение, спасибо! У меня около 40 атрибутов, так что добавить больше вручную не проблема

Ответ №2:

Я предложу таким образом:

Сначала выполните приведенный ниже запрос:

 select distinct (attribute) from test;

Вы получите все атрибуты из приведенного выше запроса:

Теперь добавьте все атрибуты в приведенный ниже запрос, чтобы получить свой результат:

 select 
product_num,
sn,
coalesce(max(value) filter (where attribute ='Unit sold'),0) as "Unit Sold",
coalesce(max(value) filter (where attribute ='Unit price'),0) as "Unit Price",
coalesce(max(value) filter (where attribute ='Num_boxes'),0) as "Num_boxes",
.
.

coalesce(max(note),'')

from test

group by 1,2

вы ilike также можете использовать условие фильтра, если хотите, чтобы значения совпадали, игнорируя регистр.

Метки: