Tri alphanumérique avec PostgreSQL
Dans la base de données, j'ai plusieurs alpha-numériques en chaînes de caractères dans le format suivant:
10_asdaasda
100_inkskabsjd
11_kancaascjas
45_aksndsialcn
22_dsdaskjca
100_skdnascbka
je veux qu'ils soient essentiellement triés par le numéro devant la chaîne et ensuite le nom de la chaîne lui-même, mais bien sûr, les caractères sont comparés un par un et donc le résultat de L'ordre par le nom produit:
10_asdaasda
100_inkskabsjd
100_skdnascbka
11_kancaascjas
22_dsdaskjca
45_aksndsialcn
au lieu de l'ordre je préfère:
10_asdaasda
11_kancaascjas
22_dsdaskjca
45_aksndsialcn
100_inkskabsjd
100_skdnascbka
honnêtement, je serais d'accord si les cordes étaient juste triées par le nombre en avant. Je ne suis pas trop familier avec PostgreSQL, donc je n'étais pas sûr de ce que la meilleure façon de faire ce serait. J'apprécierais toute aide!
4 réponses
la solution idéale seraitnormaliser vos données et diviser les deux composantes de la colonne en deux colonnes individuelles. L'une de type integer, un text.
avec la table courante, vous pouvez faire quelque chose comme demonstrated ici:
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
même substring() expressions peut être utilisé pour diviser la colonne.
Les expressions régulières sont un peu à tolérance de panne:
le premier regex choisit la plus longue chaîne numérique à partir de la gauche,
NULLsi pas de chiffres sont trouvés, de sorte que le castintegervous ne pouvez pas aller mal.le second regex sélectionne le reste de la chaîne du premier caractère qui n'est pas un chiffre ou '_'.
si le trait de soulignement est de toute façon un séparateur non ambigu,split_part() est plus rapide:
ORDER BY (split_part(t, '_', 1)::int
,split_part(t, '_', 2)
Réponse pour ton exemple
SELECT name
FROM nametable
ORDER BY (split_part(name, '_', 1)::int
,split_part(name, '_', 2)
il y a une façon de le faire avec un index sur une expression. Ce ne serait pas ma solution préférée (J'irais pour Brad's) mais vous pouvez créer un index sur l'expression suivante (il y a plus de façons de le faire):
CREATE INDEX idx_name ON table (CAST(SPLIT_PART(columname, '_', 1) AS integer));
alors vous pouvez rechercher et commander par CAST(SPLIT_PART(columname, '_', 1) AS integer) chaque fois que vous avez besoin du nombre avant le caractère de soulignement, tel que:
SELECT * FROM table ORDER BY CAST(SPLIT_PART(columname, '_', 1) AS integer);
vous pouvez faire la même chose pour la partie string en créant un index sur SPLIT_PART(columname, '_', 2), et ensuite trier en conséquence comme bien.
Comme je l'ai dit, cependant, je trouve cette solution très laid. Je voudrais certainement aller avec deux autres colonnes (une pour le nombre et une pour la corde), puis peut-être même enlever la colonne que vous mentionnez ici.
Vous pouvez utiliser des expressions régulières avec des substrats
order by substring(column, '^[0-9]+')::int, substring(column, '[^0-9]*$')
vous devriez ajouter une nouvelle colonne à la base de données qui a le type de données numériques et sur persisting un nouvel enregistrement l'a placé à la même valeur que le préfixe sur la valeur de chaîne que vous avez.
alors vous pouvez créer un index sur la colonne numérique correctement tapée pour le tri.