SQL : Triage par nom de domaine d'email

Question

Quelle est l'instruction SQL la plus courte et/ou la plus efficace pour trier une table avec une colonne d'adresse électronique par son fragment de nom de DOMAINE ?

Il s'agit essentiellement d'ignorer ce qui se trouve avant "@" dans les adresses électroniques et de ne pas tenir compte de la casse. Ignorons les noms de domaine internationalisés pour cette fois.

Cible : mySQL, MSSQL, Oracle

Les données de l'échantillon de TABLE1

id   name           email 
------------------------------------------
 1   John Doe       johndoe@domain.com
 2   Jane Doe       janedoe@helloworld.com
 3   Ali Baba       ali@babaland.com
 4   Foo Bar        foo@worldof.bar.net
 5   Tarrack Ocama  me@am-no-president.org

Commander par e-mail
SELECT * FROM TABLE1 ORDER BY EMAIL ASC

id   name           email 
------------------------------------------
 3   Ali Baba       ali@babaland.com
 4   Foo Bar        foo@worldof.bar.net
 2   Jane Doe       janedoe@helloworld.com
 1   John Doe       johndoe@domain.com
 5   Tarrack Ocama  me@am-no-president.org

Ordre par domaine
SELECT * FROM TABLE1 ORDER BY ?????? ASC

id   name           email 
------------------------------------------
 5   Tarrack Ocama  me@am-no-president.org
 3   Ali Baba       ali@babaland.com
 1   John Doe       johndoe@domain.com
 2   Jane Doe       janedoe@helloworld.com
 4   Foo Bar        foo@worldof.bar.net

EDITAR:
Je ne demande pas une seule instruction SQL qui fonctionnera sur les 3 moteurs SQL ou plus. Toute contribution est la bienvenue :)

Answer 1

Essayez ceci

Requête (pour Sql Server) :

select * from mytbl
order by SUBSTRING(email,(CHARINDEX('@',email)+1),1)

Requête (pour Oracle) :

select * from mytbl
order by substr(email,INSTR(email,'@',1) + 1,1)

Requête (pour MySQL)

pygorex1 already answered

Sortie :

id nom email

5   Tarrack Ocama   me@am-no-president.org
3   Ali Baba    ali@babaland.com
1   John Doe    johndoe@domain.com
2   Jane Doe    janedoe@helloworld.com
4   Foo Bar foo@worldof.bar.net

Answer 2

Pour MySQL :

select email, SUBSTRING_INDEX(email,'@',-1) AS domain from user order by domain desc;

Pour les cas non sensibles à la casse :

select user_id, username, email, LOWER(SUBSTRING_INDEX(email,'@',-1)) AS domain from user order by domain desc;

Answer 3

Si vous voulez que cette solution puisse évoluer, vous devez pas essayer d'extraire des sous-colonnes. Les fonctions par ligne sont notoirement lentes à mesure que la table s'agrandit.

Le site droite La chose à faire dans ce cas est de déplacer le coût de l'extraction de select (où cela arrive souvent) à insert/update où cela arrive moins (dans la plupart des bases de données normales). En supportant le coût uniquement sur insert y update vous augmentez considérablement l'efficacité globale de la base de données, puisque c'est le moyen le plus efficace d'obtenir des résultats. uniquement moment où vous devez le faire (c'est-à-dire le seul moment où les données changent).

Pour ce faire, divisez l'adresse électronique en deux colonnes distinctes dans le tableau, email_user y email_domain ). Ensuite, vous pouvez soit le diviser dans votre application avant l'insertion/mise à jour, soit utiliser un déclencheur (ou des colonnes précalculées si votre SGBD le prend en charge) dans la base de données pour le faire automatiquement.

Ensuite, vous faites un tri sur email_domain et, lorsque vous voulez l'adresse électronique complète, vous utilisez email_name|'@'|email_domain .

Alternativement, vous pouvez garder l'intégralité email et utiliser un déclencheur pour dupliquer seulement la partie domaine dans la colonne email_domain alors vous n'aurez jamais à vous soucier de concaténer les colonnes pour obtenir l'adresse électronique complète.

Il est parfaitement acceptable de revenir à 3NF pour des raisons de performance, à condition de savoir ce que l'on fait. Dans ce cas, les données des deux colonnes ne peuvent pas être désynchronisées, simplement parce que les déclencheurs ne le permettent pas. C'est une bonne façon de troquer l'espace disque (relativement bon marché) contre les performances (nous ne sommes pas obligés de le faire). toujours veulent plus de cela).

Et si vous êtes de ceux qui n'aiment pas du tout revenir de la 3NF, la fonction email_name/email_domain va régler ce problème.

Ceci suppose également que vous voulez juste gérer les adresses e-mail du formulaire a@b - il existe d'autres adresses électroniques valables, mais je ne me souviens pas en avoir vu dans la nature depuis des années.

Answer 4

Pour SQL Server, vous pouvez ajouter un colonne calculée à votre table en extrayant le domaine dans un champ séparé. Si vous faites persister cette colonne dans la table, vous pouvez l'utiliser comme n'importe quel autre champ et même y placer un index, pour accélérer les choses, si vous effectuez beaucoup de requêtes par nom de domaine :

ALTER TABLE Table1
  ADD DomainName AS 
     SUBSTRING(email, CHARINDEX('@', email)+1, 500) PERSISTED

Votre tableau comporterait donc une colonne supplémentaire "DomainName", qui contiendrait tout ce qui suit le signe "@" de votre adresse électronique.

Answer 5

En supposant que vous devez vraiment prendre en charge MySQL, Oracle et MSSQL le moyen le plus efficace serait de stocker le nom du compte et le nom de domaine dans deux champs distincts. Vous pourrez ensuite passer vos commandes :

select id,name,email from table order by name

select id,name,email,account,domain from table order by email

select id,name,email,account,domain from table order by domain,account

comme le souligne donnie, les fonctions de manipulation des chaînes de caractères ne sont pas standard c'est pourquoi vous devrez garder les données redondantes !

J'ai ajouté le compte et le domaine à la troisième requête, car je crois me souvenir que tous les SGBD ne trient pas une requête sur un champ qui ne fait pas partie des champs sélectionnés.