Гуманизированное или натуральное число, сортировка смешанных строк слова и номера

Ответ 1

Основываясь на ваших тестовых данных, но это работает с произвольными данными:

CREATE TYPE ai AS (a text, i int); -- Could also be a table or even a temp table

SELECT data
FROM (
    SELECT ctid, data, regexp_matches(data, '(\D*)(\d*)', 'g') AS x
    FROM   alnum
    ) x
GROUP  BY ctid, data   -- ctid as stand-in for a missing pk
ORDER  BY regexp_replace (left(data, 1), '[0-9]', '0')
        , array_agg(ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai)
        , data         -- for special case of trailing 0

Протестировано с помощью PostgreSQL 9.1.5.

• Трюк состоит в том, чтобы сформировать массив ai - ai, являющийся составным типом, состоящим из столбца text и integer. Это работает с различным количеством элементов.

• regexp_matches() с шаблоном (\D*)(\d*), а параметр g возвращает одну строку для каждой комбинации букв и цифр, плюс одна строка в конце. С ведущими цифрами мы получаем пустой элемент в начале для части письма.

• Добавьте regexp_replace (left(data, 1), '[0-9]', '0') в качестве первого элемента ORDER BY, чтобы позаботиться о ведущих цифрах и пустых строках.

• Замените пустые строки на 0 для части integer.

- Если могут возникнуть специальные символы, такие как {}()"',, вам нужно будет их избежать.

• Предложение @Craig использовать выражение ROW позаботится об этом.

• Если NULL может произойти, вам придется использовать специальный случай - используйте весь shebang в функции STRICT, как предлагает @Craig.

Кстати, это не будет выполняться в sqlfiddle, но это происходит в моем кластере db. JDBC не справляется с этим. sqlfiddle жалуется:

Метод org.postgresql.jdbc3.Jdbc3Array.getArrayImpl(long, int, Map) является еще не реализованы.

Ответ 2

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

CREATE FUNCTION rr(text,int) RETURNS text AS $$
SELECT regexp_replace(
    regexp_replace($1, '[0-9]+', repeat('0',$2) || '\&', 'g'), 
    '[0-9]*([0-9]{' || $2 || '})', 
    '\1', 
    'g'
)
$$ LANGUAGE sql;

SELECT t,rr(t,9) FROM mixed ORDER BY t;
      t       |             rr              
--------------+-----------------------------
 AAA02free    | AAA000000002free
 AAA10bob     | AAA000000010bob
 AAA2bbb03boo | AAA000000002bbb000000003boo
 AAA2bbb3baa  | AAA000000002bbb000000003baa
 AAA2fred     | AAA000000002fred
(5 rows)

(reverse-i-search)`OD': SELECT crypt('richpass','$2$08$aJ9ko0uKa^C1krIbdValZ.dUH8D0R0dj8mqte0Xw2FjImP5B86ugC');
richardh=> 
richardh=> SELECT t,rr(t,9) FROM mixed ORDER BY rr(t,9);
      t       |             rr              
--------------+-----------------------------
 AAA2bbb3baa  | AAA000000002bbb000000003baa
 AAA2bbb03boo | AAA000000002bbb000000003boo
 AAA2fred     | AAA000000002fred
 AAA02free    | AAA000000002free
 AAA10bob     | AAA000000010bob
(5 rows)

Я не утверждаю, что два регулярных выражения - самый эффективный способ сделать это, но rr() неизменен (для фиксированной длины), поэтому вы можете его индексировать. Ох - это 9.1

Конечно, с помощью plperl вы могли бы просто оценить замену на пэд/обрезать его за один раз. Но тогда с perl у вас всегда есть один-единственный вариант (TM), чем любой другой подход: -)

Ответ 3

Следующая функция разбивает строку на массив пар слов (число, число) произвольной длины. Если строка начинается с числа, тогда первая запись будет иметь слово NULL.

CREATE TYPE alnumpair AS (wordpart text,numpart integer);

CREATE OR REPLACE FUNCTION regexp_split_numstring_depth_pairs(instr text)
RETURNS alnumpair[] AS $$
WITH x(match) AS (SELECT regexp_matches($1, '(\D*)(\d+)(.*)'))
SELECT
  ARRAY[(CASE WHEN match[1] = '' THEN '0' ELSE match[1] END, match[2])::alnumpair] || (CASE 
  WHEN match[3] = '' THEN
    ARRAY[]::alnumpair[]
  ELSE 
    regexp_split_numstring_depth_pairs(match[3]) 
  END)
FROM x;$$ LANGUAGE 'sql' IMMUTABLE;

позволяет сортировать композитный тип PostgreSQL:

SELECT data FROM alnum ORDER BY regexp_split_numstring_depth_pairs(data);

и получения ожидаемого результата, согласно этот SQLFiddle. Я принял замену Erwin 0 для пустой строки во всех строках, начиная с числа, чтобы сначала сортировать числа; он более чистый, чем при использовании ORDER BY left(data,1), regexp_split_numstring_depth_pairs(data).

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

Это было весело!

Ответ 4

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

CREATE OR REPLACE FUNCTION human_sort(text)
  RETURNS text[] AS
$BODY$   
  /* Split the input text into contiguous chunks where no numbers appear,
     and contiguous chunks of only numbers. For the numbers, add leading 
     zeros to 20 digits, so we can use one text array, but sort the 
     numbers as if they were big integers.

       For example, human_sort('Run 12 Miles') gives
            {'Run ', '00000000000000000012', ' Miles'}
  */
  select array_agg(
    case
      when a.match_array[1]::text is not null 
        then a.match_array[1]::text         
      else lpad(a.match_array[2]::text, 20::int, '0'::text)::text                                      
    end::text)
    from (
      select regexp_matches(
        case when $1 = '' then null else $1 end, E'(\\D+)|(\\d+)', 'g'
      ) AS match_array      
    ) AS a  
$BODY$
  LANGUAGE sql IMMUTABLE;

протестирован для работы над Postgres 8.3.18 и 9.3.5

• Нет рекурсии, должно быть быстрее рекурсивных решений
• Может использоваться только в предложении order by, не нужно иметь дело с первичным ключом или ctid
• Работает для любого выбора (даже не нужен PK или ctid)
• Упрощение, чем некоторые другие решения, должно быть проще расширить и поддерживать
• Подходит для использования в функциональном индексе для повышения производительности.
• Работает на Postgres v8.3 или выше
• Позволяет неограниченное количество чередований текста/номера на входе
• Использует только одно регулярное выражение, должно быть быстрее, чем версии с несколькими регулярными выражениями
• Числа длиной более 20 цифр упорядочены по их первым 20 цифрам

Здесь пример использования:

select * from (values 
  ('Books 1', 9),
  ('Book 20 Chapter 1', 8),
  ('Book 3 Suffix 1', 7),
  ('Book 3 Chapter 20', 6),
  ('Book 3 Chapter 2', 5),
  ('Book 3 Chapter 1', 4),
  ('Book 1 Chapter 20', 3),
  ('Book 1 Chapter 3', 2),
  ('Book 1 Chapter 1', 1),
  ('', 0),
  (null::text, 0)
) as a(name, sort)
order by human_sort(a.name)
-----------------------------
|name               |  sort |
-----------------------------
|                   |   0   |
|                   |   0   |
|Book 1 Chapter 1   |   1   |
|Book 1 Chapter 3   |   2   |
|Book 1 Chapter 20  |   3   |
|Book 3 Chapter 1   |   4   |
|Book 3 Chapter 2   |   5   |
|Book 3 Chapter 20  |   6   |
|Book 3 Suffix 1    |   7   |
|Book 20 Chapter 1  |   8   |
|Books 1            |   9   |
-----------------------------

Ответ 5

create table dat(val text)
insert into dat ( VALUES ('BBB0adam'), ('AAA10fred'), ('AAA2fred'), ('AAA2bob') );

select 
  array_agg( case when z.x[1] ~ E'\\d' then lpad(z.x[1],10,'0') else z.x[1] end ) alnum_key
from (
  SELECT ctid, regexp_matches(dat.val, E'(\\D+|\\d+)','g') as x
  from dat
) z
group by z.ctid
order by alnum_key;

       alnum_key       
-----------------------
 {AAA,0000000002,bob}
 {AAA,0000000002,fred}
 {AAA,0000000010,fred}
 {BBB,0000000000,adam}

Работал над этим почти час и отправился без взгляда - я вижу, что Эрвин прибыл в аналогичное место. Ran в том же "не удалось найти тип массива для текста типа данных []" проблема как @Clodoaldo. Было много проблем с тем, чтобы выполнить упражнение по очистке, чтобы не перегружать все строки до тех пор, пока я не подумал о группировке с помощью ctid (что действительно похоже на обман) и не работает в psuedo-таблице, как в примере OP WITH dat AS ( VALUES ('AAA2fred'), ('AAA10bob') ) ...). Было бы лучше, если array_agg мог принять подзапрос, производящий набор.

Ответ 6

Я не RegEx гуру, но я могу работать в некоторой степени. Достаточно для получения этого ответа.

В нем будет обрабатываться до 2 числовых значений. Я не думаю, что OSX идет дальше, если он даже обрабатывает 2.

WITH parted AS (
  select data,
         substring(data from '([A-Za-z]+).*') part1,
         substring('a'||data from '[A-Za-z]+([0-9]+).*') part2,
         substring('a'||data from '[A-Za-z]+[0-9]+([A-Za-z]+).*') part3,
         substring('a'||data from '[A-Za-z]+[0-9]+[A-Za-z]+([0-9]+).*') part4
    from alnum
)
  select data
    from parted
order by part1,
         cast(part2 as int),
         part3,
         cast(part4 as int),
         data;

SQLFiddle