pricing - Clasificación alfanumérica con PostgreSQL

postgresql vs mysql

Question

En la base de datos, tengo varias cadenas alfanuméricas en el siguiente formato:

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

Básicamente, quiero que estén ordenados por el número delante de la cadena y luego el nombre de la cadena, pero, por supuesto, los caracteres se comparan uno por uno y, por lo tanto, el resultado de Ordenar por nombre produce:

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

En lugar del orden que prefiero:

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

Honestamente, estaría bien si las cuerdas fueran ordenadas por el número al frente. No estoy muy familiarizado con PostgreSQL, así que no estaba seguro de cuál sería la mejor manera de hacerlo. ¡Apreciaría cualquier ayuda!

Answer 1

Debe agregar una nueva columna a la base de datos, que tiene un tipo de datos numérico y al mantener un nuevo registro, establezca el mismo valor que el prefijo en el valor de cadena que tiene.

Luego, puede crear un índice en la columna numérica correctamente escrita para clasificar.

Answer 2

Hay una manera de hacerlo con un índice sobre una expresión. No sería mi solución preferida (apostaría por Brad), pero puedes crear un índice en la siguiente expresión (hay más formas de hacerlo):

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

Luego puede buscar y ordenar por CAST(SPLIT_PART(columname, ''_'', 1) AS integer) cada vez que necesite el número antes del carácter de subrayado, como:

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

Puede hacer lo mismo con la parte de la cadena creando un índice en SPLIT_PART(columname, ''_'', 2) y, a continuación, también ordenar en consecuencia.
Como dije, sin embargo, encuentro esta solución muy fea. Definitivamente iría con otras dos columnas (una para el número y otra para la cadena), y tal vez incluso eliminando la columna que mencionas aquí.

Answer 3

La forma ideal sería normalizar sus datos y dividir los dos componentes de la columna en dos columnas individuales. Uno de tipo integer , un text .

Con la tabla actual, puedes hacer algo como lo que se muestra aquí:

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

Las mismas expresiones de substring() se pueden usar para dividir la columna.

Las expresiones regulares son algo tolerantes a fallas:

• El primer regex elige la cadena numérica más larga de la izquierda, NULL si no se encuentran los dígitos, por lo que la conversión a integer no puede ir mal.

• El segundo regex selecciona el resto de la cadena del primer carácter que no es un dígito o ''_''.

Si el subrayado no es ambiguo como separador, split_part() es más rápido:

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

Responde a tu ejemplo

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

Answer 4

Puedes usar expresiones regulares con subcadenas

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