Разделить значения из столбца на основе имени столбца

Я работаю с QGIS и PostgreSQL с PostGIS. Мне нужна помощь с динамическими запросами для PostgreSQL.

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

Некоторые столбцы содержат значения, которые необходимо разделить между несколькими сторонами. Например, у нас может быть столбец с именем «PartyA_PartyB» и значением 10, и он должен быть разделен на 5 голосов для PartyA и 5 голосов для PartyB. Кроме того, у нас будут независимые столбцы для PartyA и PartyB (разделенные), поэтому нам нужно вычислить столбец, в котором мы размещаем исходные PartyA + PartyA_PartyB/2.

Так например для данных таблиц «Результаты выборов» и «Партии»:

create table election_results ("Country" text,  "PartyA" text,  "PartyB" text,  "PartyC" text, "PartyA_PartyB" text);
insert into election_results
VALUES
  ('Argentina', 100, 10, 20, 2),
  ('Uruguay', 3, 5, 1, 0),
  ('Chile', 40, 200, 50, 10)
;


create table parties (party text);
insert into parties
VALUES
  ('PartyA'),
  ('PartyB'),
  ('PartyC'),
  ('PartyD'),
  ('PartyE')
;

Мне нужно создать новую таблицу со столбцом, где «новая» PartyA = PartyA + PartyA_PartyB/2 и «новая» PartyB = PartyB + PartyA_PartyB/2

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

Страна Вечеринка а ВечеринкаB ВечеринкаC Аргентина 101 11 20 Уругвай 3 5 1 Чили 45 205 50

Во всех случаях разделяемые имена разделяются специальными символами «_».

У нас может быть n сторон в именах столбцов (например, PartyA_PartyB_PartyD_PartyE). Голоса должны быть разделены между n партиями.

С моим ограниченным пониманием я думаю, что перебор столбцов может быть решением, ищите символ «_» и пересчитывайте.

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
53
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Примечание. Пожалуйста, храните ваши значения не как текст, а как числовой тип.

  1. Создайте новую таблицу:

    CREATE TABLE parties (
         "Country" text,  
         "PartyA" numeric,  
         "PartyB" numeric,  
         "PartyC" numeric
    );
    
  2. Скопируйте значения для «одиночных» столбцов:

    INSERT INTO parties
    
    SELECT "Country", "PartyA", "PartyB", "PartyC"
    FROM election_results;
    
  3. Обновите столбцы с помощью функции

    SELECT * FROM split_and_update_parties();
    

Функция может выглядеть так:

CREATE OR REPLACE FUNCTION split_and_update_parties()
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
    i record;
    j text;
    n integer;
BEGIN 
  FOR i in 
     
      SELECT                                       
           column_name,                                  -- 1
           string_to_array(column_name, '_')             -- 2
       FROM information_schema.columns
       WHERE table_name   = 'election_results'
           AND column_name ~ 'Party'     
     
   LOOP
       n = cardinality(i.string_to_array);               -- 3
       IF n > 1 THEN
           FOREACH j in array i.string_to_array LOOP
            
               EXECUTE format('
         
                   UPDATE parties p                      -- 4
                   SET %I = p.%I + s.val / %s
                   FROM (
                       SELECT %I as val, "Country"
                       FROM election_results
                   ) s
                   WHERE p."Country" = s."Country"
          
               ', j, j, n, i.column_name);

           END LOOP;
       END IF;
   END LOOP;

END
$func$;

Объяснение:

  1. Получить имена столбцов из внутренней информационной схемы
  2. Немедленно разделить имена и преобразовать их в массивы
  3. Подсчитайте элементы массивов, чтобы узнать делитель, необходимый в дальнейшем в расчете
  4. Прокрутите все эти многопартийные массивы/столбцы (с более чем 1 элементом), извлеките исходные значения из таблицы election_results и обновите однопартийные столбцы в новой таблице.

Я ценю вашу помощь. На шаге 2, пожалуйста, помогите мне узнать, как получить список партий из таблицы «стороны», не прописывая их вручную в запросе. Обратите внимание, что в таблице «партии» есть только один столбец («партия»).

Jose H 25.11.2022 04:52

Я даже не уверен, почему у вас есть такой список таблиц. Вы получаете всю информацию о столбцах из заголовков столбцов вашей первой таблицы. Там столбцы с именами от "PartyA" до "PartyE" уже даны, не так ли? Итак, я получаю их из информационной схемы, а не из вашей партийной таблицы.

S-Man 25.11.2022 10:05

Спасибо. Список партий может стать большим и измениться в будущем. Также мне нужно отбросить другие столбцы в таблице «election_results», которые содержат данные другого типа.

Jose H 25.11.2022 19:52

Другие вопросы по теме