sql - ultimo - Clasificación numérica humanizada o natural de cadenas mixtas de palabras y números

separar datos de un campo sql

Question

Siguiendo con esta pregunta de Sivaram Chintalapudi , estoy interesado en si es práctico en PostgreSQL hacer "clasificación " de cadenas " naturalizadas" o "humanizadas" que contengan una combinación de números de varios dígitos y palabras / letras. patrón de palabras y números en las cadenas, y puede haber más de un número de varios dígitos en una cadena.

El único lugar donde he visto esto hecho rutinariamente es en Finder, que ordena nombres de archivos que contienen números mixtos y palabras de forma natural, colocando "20" después de "3", no antes.

El orden de colación deseado sería producido por un algoritmo que divide cada cadena en bloques en los límites de los números de letras, luego ordena cada parte, trata los bloques de letras con intercalación normal y los bloques de números como enteros para propósitos de intercalación. Asi que:

''AAA2fred'' se convertiría en (''AAA'',2,''fred'') y ''AAA10bob'' se convertiría en (''AAA'',10,''bob'') . Estos pueden ser ordenados según lo deseado:

regress=# WITH dat AS ( VALUES (''AAA'',2,''fred''), (''AAA'',10,''bob'') )
regress-# SELECT dat FROM dat ORDER BY dat;
dat
--------------
(AAA,2,fred)
(AAA,10,bob)
(2 rows)

en comparación con el orden habitual de ordenación de cuerdas:

regress=# WITH dat AS ( VALUES (''AAA2fred''), (''AAA10bob'') )
regress-# SELECT dat FROM dat ORDER BY dat;
dat
------------
(AAA10bob)
(AAA2fred)
(2 rows)

Sin embargo, el enfoque de comparación de registros no se generaliza porque Pg no comparará construcciones ROW (...) o registros de números desiguales de entradas.

Teniendo en cuenta los datos de muestra en este SQLFiddle, la intercalación en_AU.UTF-8 predeterminada produce el orden:

1A, 10A, 2A, AAA10B, AAA11B, AAA1BB, AAA20B, AAA21B, X10C10, X10C2, X1C1, X1C10, X1C3, X1C30, X1C4, X2C1

pero yo quiero:

1A, 2A, 10A, AAA1BB, AAA10B, AAA11B, AAA20B, AAA21B, X1C1, X1C3, X1C4, X1C10, X1C30, X2C1, X10C10, X10C2

Estoy trabajando con PostgreSQL 9.1 en este momento, pero las sugerencias de solo 9.2 estarían bien. Me interesan los consejos sobre cómo lograr un método de división de cuerdas eficiente y cómo comparar los datos divididos resultantes en la intercalación alternativa de cadena y número descrita. O, por supuesto, en enfoques completamente diferentes y mejores que no requieren la división de cadenas.

PostgreSQL no parece ser compatible con las funciones de comparación, de lo contrario, esto podría hacerse con bastante facilidad con un comparador recursivo y algo así como ORDER USING comparator_fn y una función de comparator(text,text) . Por desgracia, esa sintaxis es imaginaria.

Actualización: publicación del blog sobre el tema .

Answer 1

Añadiendo esta respuesta tarde porque parecía que los demás estaban desempacando en matrices o algo así. Parecía excesivo.

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)

No pretendo que dos expresiones regulares sean la forma más eficiente de hacerlo, pero rr () es inmutable (para longitud fija) para que pueda indexarlo. Oh, esto es 9.1

Por supuesto, con plperl podría simplemente evaluar el reemplazo para rellenar / recortar de una sola vez. Pero luego con perl siempre tienes solo-una-más-opción (TM) que cualquier otro enfoque :-)

Answer 2

Enfrenté este mismo problema, y ​​quería envolver la solución en una función para que pudiera volver a usarla fácilmente. Creé la siguiente función para lograr un orden de clasificación de ''estilo humano'' en 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;

probado para trabajar en Postgres 8.3.18 y 9.3.5

• Ninguna recursión, debería ser más rápida que las soluciones recursivas
• Puede usarse solo en la cláusula order by, no tiene que tratar con la clave principal o ctid
• Funciona para cualquier selección (ni siquiera necesita PK o ctid)
• Más simple que algunas otras soluciones, debería ser más fácil de extender y mantener
• Adecuado para usar en un índice funcional para mejorar el rendimiento
• Funciona en Postgres v8.3 o superior
• Permite un número ilimitado de alternancias de texto / número en la entrada
• Utiliza solo una expresión regular, debería ser más rápido que las versiones con expresiones múltiples
• Los números de más de 20 dígitos se ordenan por sus primeros 20 dígitos

Aquí hay un ejemplo de uso:

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 |
-----------------------------

Answer 3

La siguiente función divide una cadena en una matriz de pares (palabra, número) de longitud arbitraria. Si la cadena comienza con un número, la primera entrada tendrá una palabra 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;

permitiendo que la clasificación de tipo compuesto de PostgreSQL entre en juego:

SELECT data FROM alnum ORDER BY regexp_split_numstring_depth_pairs(data);

y produciendo el resultado esperado, según este SQLFiddle . Adopté la sustitución de Erwin de 0 para la cadena vacía en todas las cadenas, comenzando con un número para que los números primero se clasifiquen; es más limpio que usar ORDER BY left(data,1), regexp_split_numstring_depth_pairs(data) .

Si bien la función es probablemente muy lenta, al menos se puede utilizar en un índice de expresión.

¡Eso fue divertido!

Answer 4

No soy un gurú RegEx, pero puedo trabajarlo hasta cierto punto. Suficiente para producir esta respuesta.

Manejará hasta 2 valores numéricos dentro del contenido. No creo que OSX vaya más allá, incluso si maneja 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

Answer 5

Sobre la base de los datos de prueba, pero esto funciona con datos arbitrarios:

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

Probado con PostgreSQL 9.1.5.

• El truco consiste en formar una matriz de ai - ai es un tipo compuesto que consiste en un text y una columna integer . Esto funciona con un número variable de elementos.

• regexp_matches () con el patrón (/D*)(/d*) y la opción g devuelve una fila para cada combinación de letras y números, más una fila al final. Con los dígitos iniciales, obtenemos un elemento vacío al principio para la parte de la letra.

• Agregue regexp_replace (left(data, 1), ''[0-9]'', ''0'') como primer elemento ORDER BY para cuidar los dígitos iniciales y las cadenas vacías.

• Reemplace cadenas vacías con 0 para la parte integer .

- Si pueden aparecer caracteres especiales como {}()"'', tendrías que escapar de ellos en consecuencia.

• La sugerencia de @ Craig de usar una expresión ROW se ocupa de eso.

• Si NULL puede ocurrir, tendrías que tener un caso especial: usa todo el shebang en una función STRICT como @Craig propone.

Por cierto, esto no se ejecutará en sqlfiddle, pero sí en mi clúster db. JDBC no está a la altura. sqlfiddle se queja:

El método org.postgresql.jdbc3.Jdbc3Array.getArrayImpl (long, int, Map) aún no está implementado.

Answer 6

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}

Trabajé en esto durante casi una hora y lo publiqué sin mirar. Veo que Erwin llegó a un lugar similar. Se encontró con el mismo problema "no se pudo encontrar el tipo de matriz para texto de tipo de datos []" como @Clodoaldo. Tuve muchos problemas para hacer que el ejercicio de limpieza no afectara todas las filas hasta que pensé en agruparlo por la ctid (lo cual se siente como hacer trampa realmente; y no funciona en una tabla de psuedo como en el ejemplo OP WITH dat AS ( VALUES (''AAA2fred''), (''AAA10bob'') ) ... ). Sería más agradable si array_agg pudiera aceptar una subselección de producción de conjuntos.