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!

24
demandé sur Erwin Brandstetter 2012-07-10 20:43:19

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, NULL si pas de chiffres sont trouvés, de sorte que le cast integer vous 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)
35
répondu Erwin Brandstetter 2014-08-27 18:05:03

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.

4
répondu Marcelo Zabani 2012-08-12 17:34:58

Vous pouvez utiliser des expressions régulières avec des substrats

   order by substring(column, '^[0-9]+')::int, substring(column, '[^0-9]*$')
4
répondu D.J. 2013-06-21 18:33:40

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.

1
répondu Brad 2012-07-10 16:53:26