SQL: переименуйте повторяющиеся имена файлов, добавив 1,2,3 перед расширением

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

ID       | FILENAME
----------------------
1        | file1.ext
2        | file2.ext
3        | file1.ext
4        | file1.ext
5        | file3.ext
6        | file3.ext
7        | file4.ext

Итак, в приведенном выше примере я хочу, чтобы следующее было уникальным:

ID       | FILENAME
----------------------
1        | file1.ext
3        | file1.ext
4        | file1.ext
5        | file3.ext
6        | file3.ext

Изменив имена файлов на:

ID       | FILENAME
----------------------
1        | file1-1.ext
3        | file1-2.ext
4        | file1-3.ext
5        | file3-1.ext
6        | file3-2.ext

Я знаю, как найти дубликаты, но я не уверен, как добавить приращения непосредственно перед расширением или как увеличить счетчик в первую очередь.

Любая помощь будет принята с благодарностью.


sql duplicates rename increment
person Brian    schedule 06.11.2011    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Используйте оконную функцию row_number() для получения числа и replace для манипулирование строками.
Вы не раскрыли свою СУБД. Следующий запрос протестирован на PostgreSQL 9.0. MySQL не поддерживает оконные функции, в отличие от большинства других крупных СУБД.

Переименуйте все имена файлов:

SELECT id
      ,replace(filename, '.',  
               '-'
               || row_number() OVER (PARTITION BY filename ORDER BY id)
               || '.')
FROM   mytbl

Переименовывать только повторяющиеся имена файлов:

SELECT id
      ,CASE WHEN (count(*) OVER (PARTITION BY filename)) > 1 THEN
          replace(filename, '.',  
                  '-'
                  || row_number() OVER (PARTITION BY filename ORDER BY id)
                  || '.')
       ELSE filename END AS filename
FROM   mytbl;

Изменить с дополнительными запрошенными функциями

Эта версия работает с несколькими точками или без них в имени. Протестировано в PostgreSQL 9.0.

SELECT id
      ,CASE WHEN (count(*) OVER (PARTITION BY filename)) > 1 THEN
          regexp_replace(filename
              -- pick the longest string from the start not 
             ,'^([^.]*)'containing a '.'
              -- and replace it with itself + row_number
             ,E'\\1-' || row_number() OVER (PARTITION BY filename ORDER BY id))
       ELSE filename END AS filename
FROM   mytbl
person Erwin Brandstetter    schedule 06.11.2011
comment
Спасибо, Эрвин, я использую IBM DB2, и все вышеперечисленное работает отлично. Просто чтобы добавить немного больше сложности :) 1. Некоторые файлы могут иметь '.' в их имени, а также в расширении, например. 'some.file.ext' 2. Некоторые файлы могут не иметь расширения. Нужно ли мне просто накапливать дела? - person Brian; 06.11.2011
comment
@Brian Оптимальное манипулирование строками во многом зависит от фактического распределения имен файлов и функций, доступных в DB2. Я добавил еще одну версию, которая делает все, что вы упомянули в PostgreSQL 9.0. - person Erwin Brandstetter; 06.11.2011