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

ProgramBox

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

Post author:admin
Запись опубликована:26 марта, 2023
Post category:Вопросы по программированию

#sql #postgresql #sequence #greatest-n-per-group

#sql #postgresql #последовательность #наибольшее число на группу

Вопрос:

У меня есть таблица sample_1 в базе данных Postgres 10.7 с некоторыми данными продольных исследований и возрастающим sequence числом для key . Мне нужны INSERT данные из промежуточной таблицы ( sample_2 ), поддерживающие sequence столбец соответствующим образом.

sequence числа основаны на 0. Я предполагаю, что мне нужен запрос для поиска наибольшего sequence числа на key в sample_1 и добавления его к последующему порядковому номеру каждой новой строки. На этом этапе я в основном борюсь с арифметикой порядкового номера. Пробовал это:

 INSERT INTO sample_1 (KEY, SEQUENCE, DATA)
SELECT KEY, sample_2.SEQUENCE   max(sample_1.SEQUENCE), DATA
FROM sample_2;

Однако я получаю ошибки, в которых говорится, что я не могу использовать ‘sample_1.SEQUENCE’ в строке 2, потому что это таблица, вставляемая в to. Я не могу понять, как выполнить арифметику с моей последовательностью вставки!

Пример данных:

 sample_1

| KEY         | SEQUENCE | DATA |
 ------------- ---------- ------ 
| YMH_0001_XX |     0    |  a   |
| YMH_0001_XX |     1    |  b   |
| YMH_0002_YY |     0    |  c   |

 sample_2

| KEY         | SEQUENCE | DATA |
 ------------- ---------- ------ 
| YMH_0001_XX |     1    |  d   |
| YMH_0002_YY |     1    |  e   |
| YMH_0002_YY |     2    |  f   |

Я хочу продолжить возрастание sequence чисел на key для вставленных строк.

Чтобы было понятно, результирующая таблица в этом примере будет состоять из 3 столбцов и 6 строк как таковых:

 sample_1

| KEY         | SEQUENCE | DATA |
 ------------- ---------- ------ 
| YMH_0001_XX |     0    |  a   |
| YMH_0001_XX |     1    |  b   |
| YMH_0001_XX |     2    |  d   |
| YMH_0002_YY |     0    |  c   |
| YMH_0002_YY |     1    |  e   |
| YMH_0002_YY |     2    |  f   |

1. Если вы добавляете max(seq) in sample_1 ко всем строкам, не должно ли sequence последних 2 строк в вашей результирующей таблице быть 2 и 3?

2. @Crazy2crack — Я не уверен, что понимаю, почему? Единственной строкой в ‘1’ для YMH_0002 является sequence == 0, поэтому max (1.sequence) 2.sequence равно 1 и 2 соответственно? Базовая последовательность для каждого отдельного кода равна 0, а все последующие временные точки равны 1, 2 и т.д.

3. В идеале вы предоставляете свою версию Postgres и точные определения таблиц ( CREATE TABLE инструкции, разъясняющие типы данных, ограничения, значения по умолчанию, …) А также раскрывает любые триггеры, последовательности и т.д., Которые могут быть задействованы.

4. @ErwinBrandstetter — Спасибо. Я отредактирую свой пост. Поскольку это мой первый пост, он дал мне экскурсию по его созданию и особо упомянул, что номера версий следует избегать, если это специально не требуется.

5. Я взял на себя смелость уточнить описание. Пожалуйста, отредактируйте, если я что-то не так понял.

Ответ №1:

Это должно делать то, что вам нужно:

 INSERT INTO sample_1 (key, sequence, data)
SELECT s2.key
     , COALESCE(s1.seq_base, -1)
       row_number() OVER (PARTITION BY s2.key ORDER BY s2.sequence)
     , s2.data
FROM   sample_2 s2
LEFT   JOIN (
   SELECT key, max(sequence) AS seq_base
   FROM   sample_1
   GROUP  BY 1
   ) s1 USING (key);

Примечания

Вам нужно использовать существующий максимум sequence на key в sample_1 . (Я назвал его seq_base .) Вычислите это в подзапросе и присоединитесь к нему.
Добавьте row_number() к нему, как показано. Это сохраняет порядок входных строк, отбрасывая абсолютные числа.
Нам нужно LEFT JOIN , чтобы избежать потери строк с новыми ключами из sample_2 .
Аналогично, нам нужно COALESCE начать новую последовательность для новых ключей. По умолчанию используется значение -1, чтобы эффективно начинать последовательности с 0 после добавления номера строки на основе 1.
Это небезопасно для одновременного выполнения, но я не думаю, что это ваш вариант использования.