Humanizowane lub naturalne sortowanie liczb mieszanych ciągów słów i liczb
Kontynuując to pytanie przez Sivarama Chintalapudi , interesuje mnie, czy w PostgreSQL jest praktyczne wykonywanie naturalnego - czy "humanizowanego" - sortowania " ciągów zawierających mieszaninę cyfr wielocyfrowych i słów/liter. Nie ma stałego wzorca słów i liczb w łańcuchach, a w łańcuchu może być więcej niż jedna wielocyfrowa liczba.
Jedyne miejsce, gdzie widziałem to robione rutynowo jest w Mac OS Finder, który sortuje nazwy plików zawierające mieszane Liczby i słowa naturalnie, umieszczając "20" po "3", nie przed nim.
Pożądany porządek zestawiania byłby wytwarzany przez algorytm, który dzieli każdy ciąg znaków na bloki na granicy litery-liczby, a następnie porządkuje każdą część, traktując bloki literowe z normalnym zestawianiem i bloki liczbowe jako liczby całkowite do celów zestawiania. Więc:
'AAA2fred'
stałoby się ('AAA',2,'fred')
i 'AAA10bob'
stałoby się ('AAA',10,'bob')
. Można je następnie sortować według potrzeb:
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)
W porównaniu do zwykłej kolejności sortowania ciągów:
regress=# WITH dat AS ( VALUES ('AAA2fred'), ('AAA10bob') )
regress-# SELECT dat FROM dat ORDER BY dat;
dat
------------
(AAA10bob)
(AAA2fred)
(2 rows)
Jednak podejście do porównywania rekordów nie uogólnia, ponieważ Pg nie porównuje wiersza (..) konstruuje lub rejestruje nierówną liczbę wpisów.
Biorąc pod uwagę przykładowe dane w tym SQLFiddle domyślny en_AU.UTF-8 generuje kolejność:
1A, 10A, 2A, AAA10B, AAA11B, AAA1BB, AAA20B, AAA21B, X10C10, X10C2, X1C1, X1C10, X1C3, X1C30, X1C4, X2C1
Ale chcę:
1A, 2A, 10A, AAA1BB, AAA10B, AAA11B, AAA20B, AAA21B, X1C1, X1C3, X1C4, X1C10, X1C30, X2C1, X10C10, X10C2
W tej chwili pracuję z PostgreSQL 9.1, ale 9.2 - tylko sugestie byłyby w porządku. Interesuje mnie porada jak aby uzyskać wydajną metodę dzielenia łańcuchów i jak następnie porównać wynikowe dane podziału w opisanym przemiennym zestawianiu ciąg-następnie-liczba. Lub, oczywiście, na zupełnie innych i lepszych podejściach, które nie wymagają dzielenia ciągów.
PostgreSQL wydaje się nie wspierać funkcji komparatora, w przeciwnym razie można to zrobić dość łatwo za pomocą rekurencyjnego komparatora i czegoś w rodzaju ORDER USING comparator_fn
i comparator(text,text)
funkcji. Niestety ta składnia jest wyimaginowany.
Aktualizacja: wpis na blogu w temacie .
6 answers
Bazując na danych testowych, ale działa to z dowolnymi danymi:
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
Testowane z PostgreSQL 9.1.5.
Sztuczka polega na uformowaniu tablicy
ai
-ai
jest typem złożonym składającym się z kolumnytext
iinteger
. Działa to z różną liczbą elementów.Regexp_matches() z wzorcem
(\D*)(\d*)
I opcjąg
zwraca jeden wiersz dla każdej kombinacji liter i cyfr, plus jeden wiersz na końcu. Z cyfry wiodące, otrzymujemy pusty element na początku dla części literowej.Dodaj
regexp_replace (left(data, 1), '[0-9]', '0')
jako pierwszyORDER BY
Element, aby zająć się wiodącymi cyframi i pustymi łańcuchami.Zamień puste ciągi znaków na
0
dla częściinteger
.
- Jeśli mogą wystąpić znaki specjalne, takie jak {}()"',
, musisz odpowiednio od nich uciec.
@sugestia Craiga, aby użyć wyrażenia
ROW
, opiekuje się to.Jeśli
NULL
może wystąpić, musiałbyś użyć specjalnego przypadku-lub użyć całego shebang w funkcjiSTRICT
, Jak proponuje @Craig.
BTW, to nie uruchomi się w sqlfiddle, ale działa w moim klastrze db. JDBC nie jest do tego zdolny. sqlfiddle narzeka:
Metoda org.postgresql.jdbc3Jdbc3ArraygetArrayImpl (long,int,Map) jest jeszcze nie wdrożone.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-10-19 06:59:35
Dodanie tej odpowiedzi późno, ponieważ wyglądało na to, że wszyscy inni rozpakowują się do tablic lub czegoś takiego. Wydawało się przesadne.
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)
Nie twierdzę, że dwa wyrażenia regularne są najskuteczniejszym sposobem na to, ale rr() jest niezmienna (dla stałej długości), więc możesz ją indeksować. Oh-to jest 9.1
Oczywiście, z plperl można po prostu ocenić zamiennik do pad/przyciąć go za jednym zamachem. Ale z perlem zawsze masz tylko-jedną-więcej-opcji (TM) niż jakiekolwiek inne podejście: -)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-10-19 09:52:29
Następująca funkcja dzieli łańcuch znaków na tablicę par (słowo, Liczba)o dowolnej długości. Jeśli łańcuch zaczyna się od liczby, to pierwszy wpis będzie miał NULL
Słowo.
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;
Umożliwienie sortowania typu composite PostgreSQL:
SELECT data FROM alnum ORDER BY regexp_split_numstring_depth_pairs(data);
I generowanie oczekiwanego wyniku, zgodnie z tym SQLFiddle. Zastosowałem Podstawienie Erwina 0
dla pustego łańcucha we wszystkich łańcuchach zaczynających się od liczby, więc liczby sortują się najpierw; jest czystsze niż za pomocą ORDER BY left(data,1), regexp_split_numstring_depth_pairs(data)
.
Chociaż funkcja jest prawdopodobnie bardzo powolna, może być przynajmniej użyta w indeksie wyrażenia.
To było zabawne!Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-10-19 03:59:17
Stanąłem przed tym samym problemem i chciałem zawinąć rozwiązanie w funkcję, aby móc go łatwo ponownie wykorzystać. Stworzyłem następującą funkcję, aby osiągnąć porządek sortowania w stylu ludzkim w 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;
Testowany do pracy na Postgres 8.3.18 i 9.3.5
- Żadna rekurencja, powinna być szybsza od rozwiązań rekurencyjnych
- może być używany tylko w klauzuli order by, nie musisz mieć do czynienia z kluczem głównym lub ctid
- działa dla każdego select (nie trzeba nawet PK lub ctid)
- prostsze niż niektóre inne rozwiązania, powinny być łatwiejsze do rozszerzenia i utrzymania
- Nadaje się do stosowania w indeksie funkcjonalnym w celu poprawy wydajności
- działa na Postgres w wersji 8.3 lub wyższej
- pozwala na nieograniczoną liczbę przemienników tekstu / liczby na wejściu
- używa tylko jednego wyrażenia regularnego, powinno być szybsze niż wersje z wieloma wyrażeniami regularnymi
- Liczby dłuższe niż 20 cyfr są uporządkowane według pierwszych 20 cyfr
Oto przykład sposób użycia:
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 |
-----------------------------
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-11-12 22:39:34
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}
Pracowałem nad tym przez prawie godzinę i opublikowałem bez patrzenia -- widzę, że Erwin przybył w podobne miejsce. Natknąłem się na ten sam problem "nie można znaleźć typu tablicy dla typu danych tekst[]" jak @Clodoaldo. Miałem wiele problemów z uzyskaniem ćwiczenia czyszczenia, aby nie agg wszystkie wiersze, dopóki nie pomyślałem o grupowaniu przez ctid (co naprawdę wygląda na oszustwo - i nie działa na tabeli psuedo jak w przykładzie op WITH dat AS ( VALUES ('AAA2fred'), ('AAA10bob') )
...
). Byłoby ładniej, gdyby array_agg mógł zaakceptować zbiór wytwarzający subselect.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-10-19 21:15:09
Nie jestem guru RegEx, ale mogę pracować nad tym do pewnego stopnia. Wystarczająco, aby uzyskać tę odpowiedź.
Obsłuży do 2 wartości liczbowych w treści. Nie sądzę, aby OSX poszedł dalej niż to, jeśli nawet obsługuje 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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-10-19 00:14:52