Requete 'sport'

Requete 'sport' - SQL/NoSQL - Programmation

Marsh Posté le 11-03-2005 à 11:54:34    

Pour mon site perso j'utilise une combinaison de script PHP et de requete MySQL pour obtenir les mots (donc séparés par des espaces) présents le plus grand nombre de fois dans un champs d'une de mes tables.
 
Le champs est au format TINY_STRING et je souhaite obtenir que les mots de 3 caractères et plus.
 
J'ai pas mal cherché sur le web des fonction MySQL qui me permettrai d'y arriver en une requete mais sans succes.
 
L'idée c'est donc à la sortie de ma requete d'avoir les mots de plus de 3 caracteres les plus présents ordonnés par nombre d'apparitions decroissantes dans le champs TINY_STRING.


Message édité par prblsouris le 11-03-2005 à 11:55:04

---------------
http://www.hardware404.com L'actualité hardware francophone en continu
Reply

Marsh Posté le 11-03-2005 à 11:54:34   

Reply

Marsh Posté le 11-03-2005 à 12:00:06    

T'en est où dans ta requête ? T'as commencé ou pas ?
 
Déjà, retrouver une liste de mots à partir d'un champ contenant plusieurs mots, sans PS, c'est impossible, donc à moins d'avoir la dernière version de MySQL, t'es mal parti.
 
Faudra passer par le PHP, et bonjour la lenteur ! (déjà qu'en PS tu vas y passer la nuit à chaque requête...)

Reply

Marsh Posté le 11-03-2005 à 12:08:40    

J'ai bidouillé un truc avec des tableaux PHP et le resultat fonctionne et est assez rapide, mais je trouve ça crade.
Sur mon sit c'est en fait les actus qui sont placées en haut, qui en principe representent les news avec les 2 ou 3 mots cle les plus trouvé.
 
Pour info j'utilise MySQL 4.0.22 et même si la requette est un peu longue ce n'est pas trop grave car le resultat est 'stocké' dans une page HTML.
 
Je pige pas ce que tu veux dire par PS ?


---------------
http://www.hardware404.com L'actualité hardware francophone en continu
Reply

Marsh Posté le 11-03-2005 à 12:23:38    

PS = Procédure Stockée
 
C'est une procédure/fonction crée directement dans la base de données, en utilisant un langage spécifique, tel que PL/SQL, T-SQL ou autre.
 
Exemple d'une PS :

Code :
  1. CREATE procedure icsd_GetOrgUsersList
  2. (
  3. @orgid  int,
  4. @onlyInternal int,
  5. @onlyActive int
  6. )
  7. AS
  8. if @onlyInternal = 0
  9. begin
  10.  select UserOrg.UserId
  11.  from Users, UserOrg
  12.  where UserOrg.OrgId = @OrgId
  13.  and Users.UserId = UserOrg.UserId
  14.  and Users.Type = 'U'
  15.  and (Users.Active = 'A' or @onlyActive = 0)
  16. end
  17. else
  18. begin
  19.  select UserOrg.UserId
  20.  from zLoginProfile, Users, UserOrg
  21.  where UserOrg.OrgId = @OrgId
  22.  and Users.UserId = UserOrg.UserId
  23.  and Users.Type = 'U'
  24.  and (Users.Active = 'A' or @onlyActive = 0)
  25.  and zLoginProfile.UserId = Users.UserId
  26.  and zLoginProfile.EventsCommentInt = 1
  27. end
  28. GO


 
Les avantages des PS sont divers :
1/ Plan d'éxécution déjà compilé. Ainsi, le moteur de données ne s'amuse pas à reparser la requête et chercher les index les plus intéressants pour ta requête. Ainsi, sur un bon SGBD, tu peux diminuer le temps d'éxécution des requêtes par 5 facilement.
2/ Pas d'échange de données avec un process externe à la base. Ainsi, l'occupation mémoire est très faible, et surtout, à nouveau on gagne énormément en temps, puisque les données n'ont pas à être baladées d'un programme à l'autre.
3/ Code très clair : lors de l'appel, tu fais, pour ma procédure par exemple, simplement "exec icsd_GetOrgUsersList 1, 0, 1" et ça te retourne tous les utilisateurs de l'organisation "1", internes et externes confondus, et seulement les actifs. Ca peut très rapidement devenir intéressant quand t'as des requêtes extrêment complexes, et différentes selon des conditions (exemple en fin de post)
 
Y'a d'autres avantages, mais ce sont les plus flagrants.
 
Exemple d'une procédure stockée qu'on préfère ne pas avoir à taper dans une page PHP ;)
 

Code :
  1. CREATE procedure icsd_ARTContractList
  2. (
  3. @orgid   int,
  4. @userid   int,
  5. @leasescheduleid int = 0,
  6. @leasecontractid int = 0,
  7. @duration  int = 0,
  8. @date1   datetime = null,
  9. @date2   datetime = null,
  10. @orderby  varchar(255) = 'org'
  11. )
  12. AS
  13. BEGIN
  14. select name, leasescheduleid, schedule, leasecontractid, contract, leasestopdate, duration, RentalAmount, billfreq, tax2, nbProducts, nbAssets
  15. from (
  16.  select o.name,
  17.   ls.leasescheduleid,
  18.   ls.schedule,
  19.   lc.leasecontractid,
  20.   lc.contract,
  21.   lc.leasestopdate,
  22.   isnull(datediff(m, lc.leasestartdate, lc.leasestopdate), -1) duration,
  23.   sum(zf.prixFacture) rentalAmount,
  24.   lc.billfreq,
  25.   cl.tax2,
  26.   count(distinct a.ProductCode) nbProducts,
  27.   count(distinct a.assetid) nbAssets
  28.  from asset a, zloyer zl, zFactureAsset zf, contractline cl, leasecontract lc, leaseschedule ls, zUserContractPerimeter zu, UserOrg uo, organization o
  29.  where (o.orgid = @orgid or @orgid = 0)
  30.  and o.type = 'C'
  31.  and o.active = 'A'
  32.  and uo.orgid = o.orgid
  33.  and uo.userid = @userid
  34.  and zu.userid = uo.userid
  35.  and zu.orgid = uo.orgid
  36.  and (zu.scheduleid = @leasescheduleid or @leasescheduleid = 0)
  37.  and ls.orgid = o.orgid
  38.  and ls.leasescheduleid = zu.scheduleid
  39.  and ls.active = 'A'
  40.  and ls.userights = 1
  41.  and lc.orgid = ls.orgid
  42.  and lc.leasescheduleid = ls.leasescheduleid
  43.  and (lc.leasecontractid = @leasecontractid or @leasecontractid = 0)
  44.  and (lc.leasecontractid = zu.contractid or zu.contractid = -1)
  45.  and lc.active = 'A'
  46.  and (lc.leasestopdate between @date1 and @date2 or @date1 is null or @date2 is null or @date1 > @date2)
  47.  and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
  48.  and cl.contractlineid = (select max(cl2.contractlineid)
  49.      from contractline cl2
  50.      where cl2.leasecontractid = lc.leasecontractid
  51.      and cl2.active = 'A')
  52.  and zf.leaseScheduleId = ls.leaseScheduleId
  53.  and zf.leaseContractId = lc.leaseContractId
  54.  and zf.ContractLineId = cl.ContractLineID
  55.  and zl.leasecontractid = cl.leasecontractid
  56.  and zl.contractlineid = cl.ContractLineID
  57.  and a.orgid = o.orgid
  58.  and a.linetype in ('B', 'S')
  59.  and a.assetid = zl.assetid
  60.  group by
  61.   o.name,
  62.   ls.leasescheduleid,
  63.   ls.schedule,
  64.   lc.leasecontractid,
  65.   lc.contract,
  66.   lc.leasestopdate,
  67.   isnull(datediff(m, lc.leasestartdate, lc.leasestopdate), -1),
  68.   lc.billfreq,
  69.   cl.tax2
  70.  union all
  71.  select o.name,
  72.   ls.leasescheduleid,
  73.   ls.schedule,
  74.   lc.leasecontractid,
  75.   lc.contract,
  76.   lc.leasestopdate,
  77.   isnull(datediff(m, lc.leasestartdate, lc.leasestopdate), -1) duration,
  78.   sum(zf.prixFacture) rentalAmount,
  79.   lc.billfreq,
  80.   cl.tax2,
  81.   count(distinct a.ProductCode) nbProducts,
  82.   count(distinct a.assetid) nbAssets
  83.  from asset a, zloyer zl, zFactureAsset zf, contractline cl, leasecontract lc, leaseschedule ls, UserOrg uo, organization o
  84.  where (o.orgid = @orgid or @orgid = 0)
  85.  and o.type = 'C'
  86.  and o.active = 'A'
  87.  and uo.orgid = o.orgid
  88.  and uo.userid = @userid
  89.  and ls.orgid = o.orgid
  90.  and (ls.leasescheduleid = @leasescheduleid or @leasescheduleid = 0)
  91.  and ls.active = 'A'
  92.  and ls.userights = 0
  93.  and lc.orgid = ls.orgid
  94.  and lc.leasescheduleid = ls.leasescheduleid
  95.  and lc.active = 'A'
  96.  and (lc.leasecontractid = @leasecontractid or @leasecontractid = 0)
  97.  and (lc.leasestopdate between @date1 and @date2 or @date1 is null or @date2 is null or @date1 > @date2)
  98.  and (datediff(m, lc.leasestartdate, lc.leasestopdate) = @duration or @duration = 0)
  99.  and cl.contractlineid = (select max(cl2.contractlineid)
  100.      from contractline cl2
  101.      where cl2.leasecontractid = lc.leasecontractid
  102.      and cl2.active = 'A')
  103.  and zf.leaseScheduleId = ls.leaseScheduleId
  104.  and zf.leaseContractId = lc.leaseContractId
  105.  and zf.ContractLineId = cl.ContractLineID
  106.  and zl.leasecontractid = cl.leasecontractid
  107.  and zl.contractlineid = cl.ContractLineID
  108.  and a.orgid = o.orgid
  109.  and a.linetype in ('B', 'S')
  110.  and a.assetid = zl.assetid
  111.  group by
  112.   o.name,
  113.   ls.leasescheduleid,
  114.   ls.schedule,
  115.   lc.leasecontractid,
  116.   lc.contract,
  117.   lc.leasestopdate,
  118.   isnull(datediff(m, lc.leasestartdate, lc.leasestopdate), -1),
  119.   lc.billfreq,
  120.   cl.tax2
  121. ) tmp
  122. order by case @orderby
  123.    when 'org'      then name
  124.    when 'contract'     then contract
  125.    when 'stopdate'     then convert(char(8), leasestopdate, 112)
  126.    when 'duration'     then right('0000000000' + duration, 10)
  127.    when 'rentalamount' then right('0000000000' + rentalamount, 10)
  128.    when 'billfreq'     then billfreq
  129.    when 'tax2'      then right('0000000000' + tax2, 10)
  130.    when 'nbproducts'   then right('0000000000' + nbproducts, 10)
  131.    when 'nbassets'     then right('0000000000' + nbassets, 10)
  132.   end,
  133.   case @orderby
  134.    when 'org'      then schedule
  135.    when 'contract'     then contract
  136.    when 'stopdate'     then convert(char(8), leasestopdate, 112)
  137.    when 'duration'     then right('0000000000' + duration, 10)
  138.    when 'rentalamount' then right('0000000000' + rentalamount, 10)
  139.    when 'billfreq'     then billfreq
  140.    when 'tax2'      then right('0000000000' + tax2, 10)
  141.    when 'nbproducts'   then right('0000000000' + nbproducts, 10)
  142.    when 'nbassets'     then right('0000000000' + nbassets, 10)
  143.   end
  144. END
  145. GO


 
:)

Reply

Marsh Posté le 11-03-2005 à 12:27:29    

wow  :ouch:  
Je savais que ça existais mais j'ai jamais eu l'occasion (jusqu'a present ;) ) d'essayer.
merci je vais tenter ça de ce pas !


Message édité par prblsouris le 11-03-2005 à 12:28:04

---------------
http://www.hardware404.com L'actualité hardware francophone en continu
Reply

Marsh Posté le 11-03-2005 à 12:29:25    

Déjà, va falloir passer à MySQL 4.1.jesaispluscombien sinon il ne supporte pas ;)
 
Et le support, actuellement, m'a pas l'air terrible sous MySQL, il paraît que c'est plus lent que passer par le PHP.
Cela dit, à plus ou moins court terme, les développeurs de MySQL devraient arranger ça, donc je pense que c'est un plus effectivement de s'y mettre dès maintenant (d'autant plus que tous les SGBD sérieux les supportent très bien ;))

Reply

Marsh Posté le 11-03-2005 à 12:35:27    

Bon bah là je vais être bloqué pour le site, il est en mutualisé avec MySQL 4.0.22 mais en effet je vais y jeter un oeil en local ;)
 
sinon personne n'a d'idées pour y arriver en une requete ? :D


---------------
http://www.hardware404.com L'actualité hardware francophone en continu
Reply

Marsh Posté le 11-03-2005 à 13:03:05    

prblsouris a écrit :

Pour mon site perso j'utilise une combinaison de script PHP et de requete MySQL pour obtenir les mots (donc séparés par des espaces) présents le plus grand nombre de fois dans un champs d'une de mes tables.
 
Le champs est au format TINY_STRING et je souhaite obtenir que les mots de 3 caractères et plus.
 
J'ai pas mal cherché sur le web des fonction MySQL qui me permettrai d'y arriver en une requete mais sans succes.
 
L'idée c'est donc à la sortie de ma requete d'avoir les mots de plus de 3 caracteres les plus présents ordonnés par nombre d'apparitions decroissantes dans le champs TINY_STRING.

Une autre approche serait d'avoir une table qui stocke le nombre d'apparition de chaque mot de 3 lettres et plus (un champ mot et un champ nb par exemple).
 
Et cette table serait mise à jour lors de la modification de ta table où est stocké le message.
 
Tout dépend ensuite si tu as plus de modifications que de lecture.
 
Si tu as plus de lectures que de modifications, je pense que c'est la meilleure méthode.

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed