Ordinamento numerico umanizzato o naturale di stringhe miste di parole e numeri

Seguendo questa domanda di Sivaram Chintalapudi , mi interessa sapere se è pratico in PostgreSQL fare in modo naturale – o “umanizzato” – l’ordinamento “di stringhe che contengono una combinazione di numeri a più cifre e parole / lettere. schema di parole e numeri nelle stringhe e potrebbe esserci più di un numero a più cifre in una stringa.

L’unico posto che ho visto di routine è il Finder di Mac OS, che ordina in modo naturale nomi di file contenenti numeri e parole misti, posizionando “20” dopo “3”, non prima di esso.

L’ordine di confronto desiderato sarebbe prodotto da un algoritmo che suddivideva ogni stringa in blocchi ai limiti del numero di lettera, quindi ordinava ogni parte, trattando i blocchi di lettere con le normali regole di confronto ei blocchi numerici come numeri interi per scopi di confronto. Così:

'AAA2fred' diventerebbe ('AAA',2,'fred') e 'AAA10bob' diventerebbero ('AAA',10,'bob') . Questi possono quindi essere ordinati come desiderato:

 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) 

rispetto al consueto ordinamento di fascicolazione delle stringhe:

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

Tuttavia, l’approccio di confronto dei record non è generalizzato in quanto Pg non confronta i costrutti o i record ROW (..) di numeri non uguali di voci.

Dati i dati di esempio in questo SQLFiddle, la collazione predefinita en_AU.UTF-8 produce l’ordine:

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

ma io voglio:

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

Al momento sto lavorando con PostgreSQL 9.1, ma i suggerimenti solo 9.2 potrebbero andare bene. Sono interessato a consigli su come ottenere un efficiente metodo di divisione delle stringhe e su come confrontare i dati di suddivisione risultanti nella serie alternata di stringhe e numeri descritta. O, naturalmente, su approcci completamente diversi e migliori che non richiedono la divisione di stringhe.

PostgreSQL non sembra supportare le funzioni di confronto, altrimenti questo potrebbe essere fatto abbastanza facilmente con un comparatore ricorsivo e qualcosa come ORDER USING comparator_fn e una funzione di comparator(text,text) . Ahimè, quella syntax è immaginaria.

Aggiornamento: post sul blog sull’argomento .

Basandosi sui dati di test, ma funziona con dati arbitrari:

 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 

Testato con PostgreSQL 9.1.5.

  • Il trucco consiste nel formare una matrice di aiai essendo un tipo composito costituito da un text e da una colonna integer . Funziona con un numero variabile di elementi.

  • regexp_matches () con il modello (\D*)(\d*) e l’opzione g restituisce una riga per ogni combinazione di lettere e numeri, più una riga alla fine. Con le cifre iniziali, otteniamo un elemento vuoto all’inizio per la parte della lettera.

  • Aggiungi regexp_replace (left(data, 1), '[0-9]', '0') come primo articolo ORDER BY per occuparti delle cifre iniziali e delle stringhe vuote.

  • Sostituisci stringhe vuote con 0 per la parte integer .

– Se possono verificarsi caratteri speciali come {}()"', , dovrai sfuggire a quelli di conseguenza.

  • @ Il suggerimento di Craig di usare un’espressione ROW prende cura di questo.

  • Se NULL può accadere, dovresti farlo in casi particolari – o usare l’intero shebang in una funzione STRICT come propone @Craig.

BTW, questo non verrà eseguito in sqlfiddle, ma lo fa nel mio cluster db. JDBC non è all’altezza. sqlfiddle si lamenta:

Metodo org.postgresql.jdbc3.Jdbc3Array.getArrayImpl (long, int, Map) non è ancora stato implementato.

Aggiungendo questa risposta in ritardo perché sembrava che tutti gli altri stessero scartando in array o altri. Sembrava eccessivo.

 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) 

Non sto sostenendo che due espressioni regolari siano il modo più efficace per farlo, ma rr () è immutabile (per la lunghezza fissa), quindi puoi indicizzarlo. Oh – questo è 9.1

Ovviamente, con plperl, è sufficiente valutare la sostituzione del pad / tagliarlo in un colpo solo. Ma poi con perl hai sempre solo una opzione in più (TM) rispetto a qualsiasi altro approccio 🙂

La seguente funzione divide una stringa in una matrice di coppie (parola, numero) di lunghezza arbitraria. Se la stringa inizia con un numero, la prima voce avrà una parola 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; 

permettendo all’ordinamento composito di PostgreSQL di entrare in gioco:

 SELECT data FROM alnum ORDER BY regexp_split_numstring_depth_pairs(data); 

e producendo il risultato atteso, come da questo SQLFiddle . Ho adottato la sostituzione di Erwin di 0 per la stringa vuota in tutte le stringhe che inizia con un numero in modo che i numeri vengano ordinati per primi; è più pulito dell’uso di ORDER BY left(data,1), regexp_split_numstring_depth_pairs(data) .

Mentre la funzione è probabilmente orribilmente lenta, può essere utilizzata almeno in un indice di espressione.

È stato divertente!

Ho affrontato lo stesso problema e volevo avvolgere la soluzione in una funzione in modo da poterla riutilizzare facilmente. Ho creato la seguente funzione per ottenere un ordinamento “stile umano” in 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; 

testato per funzionare su Postgres 8.3.18 e 9.3.5

  • Nessuna ricorsione, dovrebbe essere più veloce delle soluzioni ricorsive
  • Può essere usato solo nella clausola order by, non ha a che fare con la chiave primaria o ctid
  • Funziona per qualsiasi selezione (non serve nemmeno un PK o ctid)
  • Più semplice di alcune altre soluzioni, dovrebbe essere più facile da estendere e mantenere
  • Adatto per l’uso in un indice funzionale per migliorare le prestazioni
  • Funziona su Postgres v8.3 o versioni successive
  • Permette un numero illimitato di alternanze di testo / numero nell’input
  • Utilizza solo una regex, dovrebbe essere più veloce delle versioni con regex multipli
  • I numeri più lunghi di 20 cifre sono ordinati in base alle loro prime 20 cifre

Ecco un esempio di utilizzo:

 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 | ----------------------------- 
 create table dat(val text) insert into dat ( VALUES ('BBB0adam'), ('AAA10fred'), ('AAA2fred'), ('AAA2bob') ); select array_agg( case when zx[1] ~ E'\\d' then lpad(zx[1],10,'0') else zx[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} 

Ho lavorato su questo per quasi un’ora e ho postato senza guardare – Vedo che Erwin è arrivato in un posto simile. Ran nella stessa “imansible trovare il tipo di array per il tipo di dati text []” problemi come @Clodoaldo. Ho avuto un sacco di problemi nell’ottenere l’esercizio di pulizia per non aggirare tutte le righe fino a quando ho pensato di raggruppare il ctid (che sembra veramente imbroglio – e non funziona su una tabella psuedo come nell’esempio OP WITH dat AS ( VALUES ('AAA2fred'), ('AAA10bob') ) ... ). Sarebbe più bello se array_agg potesse accettare una sottoselezione che produce set.

Non sono un guru RegEx, ma posso lavorarlo in una certa misura. Abbastanza per produrre questa risposta.

Gestirà fino a 2 valori numerici all’interno del contenuto. Non credo che OSX vada oltre, se ne gestisce addirittura 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