Natural Ordenar en MySQL

sorting natural-sort

Question

¿Existe una manera elegante de tener una clasificación natural y efectiva en una base de datos MySQL?

Por ejemplo, si tengo este conjunto de datos:

• Fantasía Final
• Final Fantasy 4
• Final Fantasy 10
• Final Fantasy 12
• Final Fantasy 12: Cadenas de Promathia
• Final Fantasy Adventure
• Final Fantasy Origins
• Final Fantasy Tactics

Cualquier otra solución elegante que dividir los nombres de los juegos en sus componentes

• Título : "Final Fantasy"
• Número : "12"
• Subtítulo : "Cadenas de Promathia"

para asegurarse de que salgan en el orden correcto? (10 después de 4, no antes de 2).

Hacerlo es un dolor en el ** porque de vez en cuando hay otro juego que rompe el mecanismo de analizar el título del juego (por ejemplo, "Warhammer 40,000", "James Bond 007").

Answer 1

1. Agregue una clave de clasificación (rango) en su tabla. ORDER BY rank

2. Utilice la columna "Fecha de lanzamiento". ORDER BY release_date

3. Al extraer los datos de SQL, haga que su objeto haga la clasificación, por ejemplo, si extrae en un Conjunto, conviértalo en un Conjunto de Árboles y haga que su modelo de datos implemente Comparable y aplique el algoritmo de ordenamiento natural aquí (la ordenación por inserción será suficiente si está utilizando un idioma sin colecciones) ya que leerá las filas de SQL una por una al crear su modelo e insertarlo en la colección.

Answer 2

Acabo de encontrar esto:

SELECT names FROM your_table ORDER BY games + 0 ASC

Un tipo natural cuando los números están al frente, podría funcionar también para el medio.

Answer 3

Agregue un campo para "ordenar clave" que tenga todas las cadenas de dígitos sin relleno a una longitud fija y luego ordene en ese campo en su lugar.

Si puede tener cadenas largas de dígitos, otro método es anteponer el número de dígitos (ancho fijo, relleno cero) a cada cadena de dígitos. Por ejemplo, si no va a tener más de 99 dígitos seguidos, entonces, para "Super Blast 10 Ultra", la clave de clasificación sería "Super Blast 0210 Ultra".

Answer 4

Aquí hay una solución rápida:

SELECT alphanumeric, 
       integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric

Answer 5

Con respecto a la mejor respuesta de Richard Toth https://.com/a/12257917/4052357

Tenga cuidado con las cadenas codificadas UTF8 que contienen 2 bytes (o más) caracteres y números, por ejemplo

12 南新宿

Usar LENGTH() de udf_NaturalSortFormat en la función udf_NaturalSortFormat devolverá la longitud del byte de la cadena y será incorrecta, en su lugar use CHAR_LENGTH() que devolverá la longitud correcta del carácter.

En mi caso, el uso de LENGTH() hizo que las consultas nunca se completaran y resultó en un uso de la CPU del 100% para MySQL

DROP FUNCTION IF EXISTS `udf_NaturalSortFormat`;
DELIMITER ;;
CREATE FUNCTION `udf_NaturalSortFormat` (`instring` varchar(4000), `numberLength` int, `sameOrderChars` char(50)) 
RETURNS varchar(4000)
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
    DECLARE sortString varchar(4000);
    DECLARE numStartIndex int;
    DECLARE numEndIndex int;
    DECLARE padLength int;
    DECLARE totalPadLength int;
    DECLARE i int;
    DECLARE sameOrderCharsLen int;

    SET totalPadLength = 0;
    SET instring = TRIM(instring);
    SET sortString = instring;
    SET numStartIndex = udf_FirstNumberPos(instring);
    SET numEndIndex = 0;
    SET i = 1;
    SET sameOrderCharsLen = CHAR_LENGTH(sameOrderChars);

    WHILE (i <= sameOrderCharsLen) DO
        SET sortString = REPLACE(sortString, SUBSTRING(sameOrderChars, i, 1), '' '');
        SET i = i + 1;
    END WHILE;

    WHILE (numStartIndex <> 0) DO
        SET numStartIndex = numStartIndex + numEndIndex;
        SET numEndIndex = numStartIndex;

        WHILE (udf_FirstNumberPos(SUBSTRING(instring, numEndIndex, 1)) = 1) DO
            SET numEndIndex = numEndIndex + 1;
        END WHILE;

        SET numEndIndex = numEndIndex - 1;

        SET padLength = numberLength - (numEndIndex + 1 - numStartIndex);

        IF padLength < 0 THEN
            SET padLength = 0;
        END IF;

        SET sortString = INSERT(sortString, numStartIndex + totalPadLength, 0, REPEAT(''0'', padLength));

        SET totalPadLength = totalPadLength + padLength;
        SET numStartIndex = udf_FirstNumberPos(RIGHT(instring, CHAR_LENGTH(instring) - numEndIndex));
    END WHILE;

    RETURN sortString;
END
;;

ps Hubiera agregado esto como un comentario al original, pero aún no tengo suficiente reputación (todavía)

Answer 6

Creo que esta es la razón por la cual muchas cosas están ordenadas por fecha de lanzamiento.

Una solución podría ser crear otra columna en su tabla para la "SortKey". Esta podría ser una versión desinfectada del título que se ajusta a un patrón que crea para una clasificación sencilla o un contador.

Answer 7

Entonces, aunque sé que ha encontrado una respuesta satisfactoria, estuve luchando con este problema por un tiempo, y habíamos determinado previamente que no se podía hacer razonablemente bien en SQL y que íbamos a tener que usar javascript en un JSON formación.

Así es como lo resolví solo con SQL. Espero que esto sea útil para otros:

Tenía datos como:

Scene 1 Scene 1A Scene 1B Scene 2A Scene 3 ... Scene 101 Scene XXA1 Scene XXA2

De hecho, no "lancé" las cosas, aunque supongo que también pueden haber funcionado.

Primero reemplacé las partes que no cambiaban en los datos, en este caso "Escena", y luego hice una LPAD para alinear las cosas. Esto parece permitir bastante bien que las cadenas alfa se clasifiquen correctamente, así como las numeradas.

Mi cláusula ORDER BY ve así:

ORDER BY LPAD(REPLACE(`table`.`column`,''Scene '',''''),10,''0'')

Obviamente, esto no ayuda con el problema original, que no era tan uniforme, pero imagino que esto probablemente funcionaría para muchos otros problemas relacionados, así que ponlo ahí.

Answer 8

Escribí esta función para MSSQL 2000 hace un tiempo:

/**
 * Returns a string formatted for natural sorting. This function is very useful when having to sort alpha-numeric strings.
 *
 * @author Alexandre Potvin Latreille (plalx)
 * @param {nvarchar(4000)} string The formatted string.
 * @param {int} numberLength The length each number should have (including padding). This should be the length of the longest number. Defaults to 10.
 * @param {char(50)} sameOrderChars A list of characters that should have the same order. Ex: ''.-/''. Defaults to empty string.
 *
 * @return {nvarchar(4000)} A string for natural sorting.
 * Example of use: 
 * 
 *      SELECT Name FROM TableA ORDER BY Name
 *  TableA (unordered)              TableA (ordered)
 *  ------------                    ------------
 *  ID  Name                    ID  Name
 *  1.  A1.                 1.  A1-1.       
 *  2.  A1-1.                   2.  A1.
 *  3.  R1      -->         3.  R1
 *  4.  R11                 4.  R11
 *  5.  R2                  5.  R2
 *
 *  
 *  As we can see, humans would expect A1., A1-1., R1, R2, R11 but that''s not how SQL is sorting it.
 *  We can use this function to fix this.
 *
 *      SELECT Name FROM TableA ORDER BY dbo.udf_NaturalSortFormat(Name, default, ''.-'')
 *  TableA (unordered)              TableA (ordered)
 *  ------------                    ------------
 *  ID  Name                    ID  Name
 *  1.  A1.                 1.  A1.     
 *  2.  A1-1.                   2.  A1-1.
 *  3.  R1      -->         3.  R1
 *  4.  R11                 4.  R2
 *  5.  R2                  5.  R11
 */
CREATE FUNCTION dbo.udf_NaturalSortFormat(
    @string nvarchar(4000),
    @numberLength int = 10,
    @sameOrderChars char(50) = ''''
)
RETURNS varchar(4000)
AS
BEGIN
    DECLARE @sortString varchar(4000),
        @numStartIndex int,
        @numEndIndex int,
        @padLength int,
        @totalPadLength int,
        @i int,
        @sameOrderCharsLen int;

    SELECT 
        @totalPadLength = 0,
        @string = RTRIM(LTRIM(@string)),
        @sortString = @string,
        @numStartIndex = PATINDEX(''%[0-9]%'', @string),
        @numEndIndex = 0,
        @i = 1,
        @sameOrderCharsLen = LEN(@sameOrderChars);

    -- Replace all char that has to have the same order by a space.
    WHILE (@i <= @sameOrderCharsLen)
    BEGIN
        SET @sortString = REPLACE(@sortString, SUBSTRING(@sameOrderChars, @i, 1), '' '');
        SET @i = @i + 1;
    END

    -- Pad numbers with zeros.
    WHILE (@numStartIndex <> 0)
    BEGIN
        SET @numStartIndex = @numStartIndex + @numEndIndex;
        SET @numEndIndex = @numStartIndex;

        WHILE(PATINDEX(''[0-9]'', SUBSTRING(@string, @numEndIndex, 1)) = 1)
        BEGIN
            SET @numEndIndex = @numEndIndex + 1;
        END

        SET @numEndIndex = @numEndIndex - 1;

        SET @padLength = @numberLength - (@numEndIndex + 1 - @numStartIndex);

        IF @padLength < 0
        BEGIN
            SET @padLength = 0;
        END

        SET @sortString = STUFF(
            @sortString,
            @numStartIndex + @totalPadLength,
            0,
            REPLICATE(''0'', @padLength)
        );

        SET @totalPadLength = @totalPadLength + @padLength;
        SET @numStartIndex = PATINDEX(''%[0-9]%'', RIGHT(@string, LEN(@string) - @numEndIndex));
    END

    RETURN @sortString;
END

GO

Answer 9

He intentado varias soluciones, pero en realidad es muy simple:

SELECT test_column FROM test_table ORDER BY LENGTH(test_column) DESC, test_column DESC

/* 
Result 
--------
value_1
value_2
value_3
value_4
value_5
value_6
value_7
value_8
value_9
value_10
value_11
value_12
value_13
value_14
value_15
...
*/

Answer 10

La misma función que la publicada por @plalx, ​​pero reescrita en MySQL:

DROP FUNCTION IF EXISTS `udf_FirstNumberPos`;
DELIMITER ;;
CREATE FUNCTION `udf_FirstNumberPos` (`instring` varchar(4000)) 
RETURNS int
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
    DECLARE position int;
    DECLARE tmp_position int;
    SET position = 5000;
    SET tmp_position = LOCATE(''0'', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF; 
    SET tmp_position = LOCATE(''1'', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE(''2'', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE(''3'', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE(''4'', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE(''5'', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE(''6'', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE(''7'', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE(''8'', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE(''9'', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;

    IF (position = 5000) THEN RETURN 0; END IF;
    RETURN position;
END
;;

DROP FUNCTION IF EXISTS `udf_NaturalSortFormat`;
DELIMITER ;;
CREATE FUNCTION `udf_NaturalSortFormat` (`instring` varchar(4000), `numberLength` int, `sameOrderChars` char(50)) 
RETURNS varchar(4000)
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
    DECLARE sortString varchar(4000);
    DECLARE numStartIndex int;
    DECLARE numEndIndex int;
    DECLARE padLength int;
    DECLARE totalPadLength int;
    DECLARE i int;
    DECLARE sameOrderCharsLen int;

    SET totalPadLength = 0;
    SET instring = TRIM(instring);
    SET sortString = instring;
    SET numStartIndex = udf_FirstNumberPos(instring);
    SET numEndIndex = 0;
    SET i = 1;
    SET sameOrderCharsLen = CHAR_LENGTH(sameOrderChars);

    WHILE (i <= sameOrderCharsLen) DO
        SET sortString = REPLACE(sortString, SUBSTRING(sameOrderChars, i, 1), '' '');
        SET i = i + 1;
    END WHILE;

    WHILE (numStartIndex <> 0) DO
        SET numStartIndex = numStartIndex + numEndIndex;
        SET numEndIndex = numStartIndex;

        WHILE (udf_FirstNumberPos(SUBSTRING(instring, numEndIndex, 1)) = 1) DO
            SET numEndIndex = numEndIndex + 1;
        END WHILE;

        SET numEndIndex = numEndIndex - 1;

        SET padLength = numberLength - (numEndIndex + 1 - numStartIndex);

        IF padLength < 0 THEN
            SET padLength = 0;
        END IF;

        SET sortString = INSERT(sortString, numStartIndex + totalPadLength, 0, REPEAT(''0'', padLength));

        SET totalPadLength = totalPadLength + padLength;
        SET numStartIndex = udf_FirstNumberPos(RIGHT(instring, CHAR_LENGTH(instring) - numEndIndex));
    END WHILE;

    RETURN sortString;
END
;;

Uso:

SELECT name FROM products ORDER BY udf_NaturalSortFormat(name, 10, ".")

Answer 11

MySQL no permite este tipo de "clasificación natural", por lo que parece que la mejor manera de obtener lo que busca es dividir su configuración de datos como lo describió anteriormente (campo de Id. Separado, etc.) o fallar. eso, realiza un tipo basado en un elemento no titulado, elemento indexado en tu db (fecha, id insertado en el db, etc.).

Hacer que el DB haga la clasificación por usted casi siempre será más rápido que leer grandes conjuntos de datos en el lenguaje de programación de su elección y ordenarlos allí, así que si tiene algún control sobre el esquema de DB aquí, entonces mire cómo agregar campos ordenados fácilmente como se describió anteriormente, le ahorrará mucha molestia y mantenimiento a largo plazo.

Las solicitudes para agregar un "tipo natural" surgen de vez en cuando en los errores y foros de discusión de MySQL , y muchas soluciones giran en torno a eliminar partes específicas de sus datos y convertirlas en parte de la consulta ORDER BY , por ejemplo

SELECT * FROM table ORDER BY CAST(mid(name, 6, LENGTH(c) -5) AS unsigned) 

Este tipo de solución podría funcionar para su ejemplo de Final Fantasy anterior, pero no es particularmente flexible y es poco probable que se extienda limpiamente a un conjunto de datos que incluya, por ejemplo, "Warhammer 40,000" y "James Bond 007", me temo .

Answer 12

Otra opción es hacer la ordenación en la memoria después de extraer los datos de mysql. Si bien no será la mejor opción desde el punto de vista del rendimiento, si no clasifica grandes listas, debería estar bien.

Si echas un vistazo a la publicación de Jeff, puedes encontrar muchos algoritmos para cualquier idioma con el que estés trabajando. http://www.codinghorror.com/blog/archives/001018.html

Answer 13

Sé que este tema es antiguo, pero creo que he encontrado una manera de hacerlo:

SELECT * FROM `table` ORDER BY 
CONCAT(
  GREATEST(
    LOCATE(''1'', name),
    LOCATE(''2'', name),
    LOCATE(''3'', name),
    LOCATE(''4'', name),
    LOCATE(''5'', name),
    LOCATE(''6'', name),
    LOCATE(''7'', name),
    LOCATE(''8'', name),
    LOCATE(''9'', name)
   ),
   name
) ASC

Chatarra que, ordenó el siguiente conjunto incorrectamente (Es inútil lol):

Final Fantasy 1 Final Fantasy 2 Final Fantasy 5 Final Fantasy 7 Final Fantasy 7: Advent Children Final Fantasy 12 Final Fantasy 112 FF1 FF2

Answer 14

Si está utilizando PHP puede hacer el tipo natural en php.

$keys = array();
$values = array();
foreach ($results as $index => $row) {
   $key = $row[''name''].''__''.$index; // Add the index to create an unique key.
   $keys[] = $key;
   $values[$key] = $row; 
}
natsort($keys);
$sortedValues = array(); 
foreach($keys as $index) {
  $sortedValues[] = $values[$index]; 
}

Espero que MySQL implemente la ordenación natural en una versión futura, pero la solicitud de funciones (# 1588) está abierta desde 2003, así que no aguantaría la respiración.

Answer 15

Si no quieres reinventar la rueda o tienes un dolor de cabeza con mucho código que no funciona, simplemente usa Drupal Natural Sort ... Simplemente ejecuta el SQL que viene comprimido (MySQL o Postgre), y eso es todo. Al hacer una consulta, simplemente solicite usando:

... ORDER BY natsort_canon(column_name, ''natural'')

Answer 16

También hay natsort . Está destinado a ser parte de un plugin drupal , pero funciona bien de manera autónoma.

Answer 17

También puede crear de forma dinámica la "columna de clasificación":

SELECT name, (name = ''-'') boolDash, (name = ''0'') boolZero, (name+0 > 0) boolNum 
FROM table 
ORDER BY boolDash DESC, boolZero DESC, boolNum DESC, (name+0), name

De esta forma, puedes crear grupos para ordenar.

En mi consulta, quería el ''-'' delante de todo, luego los números, luego el texto. Lo que podría resultar en algo como:

-
0    
1
2
3
4
5
10
13
19
99
102
Chair
Dog
Table
Windows

De esta forma, no tiene que mantener la columna de clasificación en el orden correcto al agregar datos. También puede cambiar su orden de clasificación dependiendo de lo que necesite.

Answer 18

Una versión simplificada no udf de la mejor respuesta de @ plaix / Richard Toth / Luke Hoggett, que funciona solo para el primer entero en el campo, es

SELECT name,
LEAST(
    IFNULL(NULLIF(LOCATE(''0'', name), 0), ~0),
    IFNULL(NULLIF(LOCATE(''1'', name), 0), ~0),
    IFNULL(NULLIF(LOCATE(''2'', name), 0), ~0),
    IFNULL(NULLIF(LOCATE(''3'', name), 0), ~0),
    IFNULL(NULLIF(LOCATE(''4'', name), 0), ~0),
    IFNULL(NULLIF(LOCATE(''5'', name), 0), ~0),
    IFNULL(NULLIF(LOCATE(''6'', name), 0), ~0),
    IFNULL(NULLIF(LOCATE(''7'', name), 0), ~0),
    IFNULL(NULLIF(LOCATE(''8'', name), 0), ~0),
    IFNULL(NULLIF(LOCATE(''9'', name), 0), ~0)
) AS first_int
FROM table
ORDER BY IF(first_int = ~0, name, CONCAT(
    SUBSTR(name, 1, first_int - 1),
    LPAD(CAST(SUBSTR(name, first_int) AS UNSIGNED), LENGTH(~0), ''0''),
    SUBSTR(name, first_int + LENGTH(CAST(SUBSTR(name, first_int) AS UNSIGNED)))
)) ASC

Answer 19

Ordenar:
0
1
2
10
23
101
205
1000
un
aac
segundo
Casdsadsa
css

Use esta consulta:

SELECT column_name FROM table_name ORDER BY column_name REGEXP ''^/d*[^/da-z&/./' /-/"/!/@/#/$/%/^/*/(/)/;/://,/?///~/`/|/_/-]'' DESC, column_name + 0, column_name;