Буквенно-цифровая сортировка с помощью PostgreSQL

В базе данных у меня есть различные буквенно-числовые строки в следующем формате:

10_asdaasda
100_inkskabsjd
11_kancaascjas
45_aksndsialcn
22_dsdaskjca
100_skdnascbka

Я хочу, чтобы они по существу были отсортированы по числу перед строкой, а затем по имени самой строки, но, конечно, символы сравниваются по одному, и поэтому результат Order by name производит:

10_asdaasda
100_inkskabsjd
100_skdnascbka
11_kancaascjas
22_dsdaskjca
45_aksndsialcn

вместо порядка, который я бы предпочел:

10_asdaasda
11_kancaascjas
22_dsdaskjca
45_aksndsialcn
100_inkskabsjd
100_skdnascbka

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

Ответ 1

Идеальный способ - нормализовать ваши данные и разделить два компонента столбца на два отдельных столбца. Один из типов integer, один text.

В текущей таблице вы можете сделать что-то вроде продемонстрированного здесь:

WITH x(t) AS (
    VALUES
     ('10_asdaasda')
    ,('100_inkskabsjd')
    ,('11_kancaascjas')
    ,('45_aksndsialcn')
    ,('22_dsdaskjca')
    ,('100_skdnascbka')
    )
SELECT t
FROM   x
ORDER  BY (substring(t, '^[0-9]+'))::int     -- cast to integer
          ,substring(t, '[^0-9_].*$')        -- works as text

Для разделения столбца можно использовать те же substring() выражения.

Регулярные выражения несколько отказоустойчивы:

Первое регулярное выражение выбирает самую длинную числовую строку слева, NULL, если цифры не найдены, поэтому приведение в integer не может пойти не так.
Второе регулярное выражение выбирает остальную часть строки из первого символа, который не является цифрой, или "_".

Если подчеркивание является однозначным как разделитель в любом случае, split_part() выполняется быстрее:

ORDER  BY (split_part(t, '_', 1)::int
          ,split_part(t, '_', 2)

Ответ для вашего примера

SELECT name
FROM   nametable
ORDER  BY (split_part(name, '_', 1)::int
          ,split_part(name, '_', 2)

Ответ 2

Есть способ сделать это с индексом над выражением. Это было бы не мое предпочтительное решение (я бы пошел на Брэда), но вы можете создать индекс в следующем выражении (есть еще несколько способов сделать это):

CREATE INDEX idx_name ON table (CAST(SPLIT_PART(columname, '_', 1) AS integer));

Затем вы можете искать и заказывать CAST(SPLIT_PART(columname, '_', 1) AS integer) каждый раз, когда вам нужен номер перед символом подчеркивания, например:

SELECT * FROM table ORDER BY CAST(SPLIT_PART(columname, '_', 1) AS integer);

Вы можете сделать то же самое со строкой, создав индекс на SPLIT_PART(columname, '_', 2), а затем отсортируйте соответственно.
Однако, как я уже сказал, я считаю это решение очень уродливым. Я бы определенно пошел с двумя другими столбцами (один для числа и один для строки), а затем, возможно, даже удалив упомянутый здесь столбец.

Ответ 3

Вы можете использовать регулярные выражения с подстроками

   order by substring(column, '^[0-9]+')::int, substring(column, '[^0-9]*$')

Ответ 4

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

Затем вы можете создать индекс для правильно напечатанного числового столбца для сортировки.