tutorial pricing postgres create sql postgresql sorting sql-order-by natural-sort

pricing - Clasificación alfanumérica con PostgreSQL



postgresql vs mysql (4)

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.

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!


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


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)


Puedes usar expresiones regulares con subcadenas

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