[SQL 2005] Problème pour une requête SELECT

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : [SQL 2005] Problème pour une requête SELECT

Roodie

Bonjour

Voici le problème :
J'ai une table "Chat" avec 2 colonnes, "Pseudo" et "message". Lorsque qu'une personne laisse un message, une ligne est ajoutée à la table. Par exemple, la table est comme ceci :
-------------------------------------------
Pseudo, Message
-------------------------------------------
"Robert", "salut"
"Rémi", "ava ?"
"Robert", "oui"
"Susan", "coucou"

Ce que je voudrais, c'est établir des statistiques en faisant une requête qui me retourne ceci :
-------------------------------------------
Pseudo, Nb_msg, Percentage
-------------------------------------------
"Robert", 2, 100%
"Rémi", 1, 50%
"Susan", 1, 50%

Vous voyez ? J'y suis arrivé, mais en faisant une usine à gaz... alors si qqn a une idée pour une tite requête simple
Mici bien

Message édité par Roodie le 23-06-2007 à 13:58:52

Publicité

Deamon

Salut, fais voir déjà ce que tu as fait.

Déjà pour les 2 premières colonnes tu dois avoir :

Code :

SELECT pseudo,count(msg) FROM table GROUP BY pseudo

Message édité par Deamon le 23-06-2007 à 15:04:57

Roodie

Coucou

Bin voici mon code (qui fonctionne) :

Code :

-- determine nb msg max among members
CREATE TABLE #MSG_PER_MEMBER
(
pseudo varchar(64),
nb_msg int
)
INSERT INTO #MSG_PER_MEMBER
SELECT pseudo, count(id_chat) as nb_msg FROM chat GROUP BY pseudo
DECLARE @nb_msg_member_max float
SET @nb_msg_member_max = cast((SELECT max(nb_msg) FROM #MSG_PER_MEMBER) as float)
-- perform select
SELECT
pseudo,
count(*) as nb_messages,
round((count(*) / @nb_msg_member_max), 2) as [percent]
FROM
chat
GROUP BY
pseudo
ORDER BY
nb_messages desc
DROP TABLE #MSG_PER_MEMBER

Je le trouve hyper lourd
Donc si qqn a une idée

Message édité par Roodie le 23-06-2007 à 17:59:54

MagicBuzz

Essaie de comprendre ce que fait cet exemple :

Code :

SELECT u1.pays_id, count(DISTINCT u1.id), ((count(DISTINCT u1.id) * 100) / count(DISTINCT u2.id)) pct
FROM uti u1
CROSS JOIN uti u2
GROUP BY u1.pays_id

Et tu auras ta réponse.

Roodie

Coucou Magic

J'ai beau me creuser la tête, j'ai du mal à comprendre où tu veux en venir avec ton CROSS JOIN.
Ta requête me retourne en 2ème colonne un nombre astronomique (puisque c'est un CROSS JOIN), et la 3ème retourne 100 tout le tps (logique...).
Je ne comprends pas comment je pourrais utiliser le CROSS JOIN dans mon cas
Merci bien si tu peux m'éclairer

MagicBuzz

pourtant moi ça marche... t'es sûr que tu fais bien des count(distinct pk) ?

Roodie

Coucou Magic

Bin j'ai recopié texto ta requête. La 1ère colonne est OK, mais la 2ème me renvoit toujours 100. C'est logique d'ailleurs :
u1 = u2 => (count(DISTINCT u1.id)) = (count(DISTINCT u2.id))

Qu'as-tu essayé de faire avec ton CROSS JOIN ?
Merci bien en tout cas

Message édité par Roodie le 30-06-2007 à 11:22:59

MagicBuzz

oui, et chez moi ça marche.

count(distinct u1.id) => retourne le nombre de u1.id différents par pays_id.
count(distinct u2.id) => retourne le nombre de u2.id différents dans la table uti, quelle que soit la valeur de pays_id

en gros :

uti

id pays_id
1 1
2 1
3 2
4 3

la requête doit retourner

pays_id count1 count2
1 2 50
2 1 25
3 1 25

MagicBuzz

Sur ma base de test :

Code :

CREATE TABLE uti
(
  id numeric,
  pays_id numeric
);
go
 
INSERT INTO uti (id, pays_id) VALUES (1, 1);
INSERT INTO uti (id, pays_id) VALUES (2, 1);
INSERT INTO uti (id, pays_id) VALUES (3, 2);
INSERT INTO uti (id, pays_id) VALUES (4, 3);
 
SELECT * FROM uti;
 
SELECT u1.pays_id, count(DISTINCT u1.id), ((count(DISTINCT u1.id) * 100) / count(DISTINCT u2.id)) pct
FROM uti u1
CROSS JOIN uti u2
GROUP BY u1.pays_id;

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
id pays_id
--------------------------------------- ---------------------------------------
1 1
2 1
3 2
4 3

(4 row(s) affected)

pays_id pct
--------------------------------------- ----------- -----------
1 2 50
2 1 25
3 1 25

(3 row(s) affected)

Roodie

Ah oui effectivement ça marche ton cocode
Bin je vais l'étudier, et voir la relation avec mon problème, cas réel. Merci bcp à toi en tout cas !

Edit : je m'étais trompé en recopiant ta requête et en l'applicant à mon cas... en fait ça marche
Par contre, côté perf... bof. Ta requête mets plus d'une minute alors que mon énorme code mets qq centièmes de secondes.
Bin... le CROSS JOIN ça fait mal sur ma table de 2000 lignes.

En tout cas, merci beaucoup ! J'ai tjs pas compris comment ton CROSS JOIN produit le bon résultat, mais ça peut toujours servir

Message édité par Roodie le 30-06-2007 à 16:19:51

Publicité

HappyHarry

soit dit en passant ta table temporaire ne sert à rien

MagicBuzz

à mon avis, c'est parceque tu produis un mauvais résultat que ton cross join est lent.

parceque le cross join c'est un bête produit cartésien, c'est le type de jointure le plus rapide à produire normalement.

bon, ensuite moi je fais des distinct sur la PK, donc je n'accède pas aux données à proprement parler. si tu commences à taper dans les champs de ta table qui ne sont pas indexés, effectivement ça risque d'être très moyen niveau perfs...

Message édité par MagicBuzz le 14-07-2007 à 01:06:23

HappyHarry

le type float c'est beurk

Message édité par HappyHarry le 14-07-2007 à 01:16:35

MagicBuzz

damned, c'est vrai qu'il est pourri SQL Server 2005 avec un cross join :heink:

Bon, autre solution vraiment toute bête et ultra rapide :
Jeu de test :

Code :

DROP TABLE uti;
go
 
-- table de test
CREATE TABLE uti
(
  id numeric PRIMARY KEY,
  pays_id numeric
);
go
 
-- jeu de test
declare @i int;
SET @i = 1;
while @i < 1000000
begin
  INSERT INTO uti (id, pays_id) VALUES (@i, cast(sqrt(rand() * 1000) AS int) + 1);
  SET @i = @i + 1;
end;
go
 
CREATE INDEX ix_pays ON uti(pays_id)
go

Le code :

Code :

-- tout rapide :)
begin transaction;
declare @total numeric;
SELECT @total = count(*) FROM uti WITH(xlock);
SELECT pays_id, count(id) nb, cast(count(id) * 100 / @total AS decimal(3,2)) pct
FROM uti
GROUP BY pays_id;
commit;
go

Sortie :

pays_id nb pct
--------------------------------------- ----------- ---------------------------------------
1 967 0.10
2 2888 0.29
3 5091 0.51
4 6894 0.69
5 8804 0.88
6 11001 1.10
7 13059 1.31
8 15187 1.52
9 17050 1.71
10 19140 1.91
11 20980 2.10
12 23056 2.31
13 25212 2.52
14 26860 2.69
15 29302 2.93
16 30896 3.09
17 33015 3.30
18 34501 3.45
19 37058 3.71
20 39021 3.90
21 40857 4.09
22 43222 4.32
23 44595 4.46
24 47054 4.71
25 49301 4.93
26 51170 5.12
27 53569 5.36
28 54786 5.48
29 56613 5.66
30 58915 5.89
31 60968 6.10
32 38967 3.90

(32 row(s) affected)

PS : J'utilise une transaction afin de pouvoir utiliser "with(xlock)" qui permet de coller un verroux exclusif sur la table durant ma transaction. Ainsi, je suis sûr que ma seconde requête va porter sur le même nombre de lignes que celles comptées dans la requête qui a posé le verroux. Le verrou est abandonné lorsque la transaction se termine.

Pour 1 million de lignes, le résultat met 3 secondes à s'afficher.

Message édité par MagicBuzz le 14-07-2007 à 01:37:14

HappyHarry

c'est pas tout à fait ce qu'il cherche à faire

MagicBuzz

il cherche à faire quoi alors ?

j'affiche bêtement pour chaque valeur de "pays_id" le nombre d'occurences dans ma table ainsi que le pourcentage que cela représente :spamafote:

ps : changé mon test avec 1 000 000 lignes pour avoir un résultat moins instantané

HappyHarry

pourcentage par rapport aux max(nb_mesg) et non pas par rapport au total

edit : je suis pénible, je sais. Déformation professionnelle

Message édité par HappyHarry le 14-07-2007 à 01:44:09

MagicBuzz

je te le refais avec la même structure vu que tu vois pas le raport, ce sera peut-être plus clair

En fait, quand on regarde bien, je fais rigoureusement la même chose sauf que je ne passe pas par une table temporaire inutile et j'évite les valeurs anormales via mon lock

Code :

DROP TABLE uti;
go
 
-- table de test
CREATE TABLE uti
(
  id numeric PRIMARY KEY,
  pseudo varchar(30),
  msg varchar(4000)
);
go
 
-- jeu de test
declare @i int;
SET @i = 0;
while @i < 1000000
begin
  INSERT INTO uti (id, pseudo, msg) VALUES (@i, 'User ' + cast(cast(sqrt(rand() * 1000) AS int) + 1 AS varchar(30)), 'blablablabla');
  SET @i = @i + 1;
end;
go
 
CREATE INDEX ix_pseudo ON uti(pseudo)
go

2 minutes 40 secondes pour générer le jeu de test :sleep:

Code :

-- tout rapide :)
begin transaction;
declare @total numeric;
SELECT @total = count(*) FROM uti WITH(xlock);
SELECT pseudo, count(*) nb, cast(count(*) * 100 / @total AS decimal(3,2)) pct
FROM uti
GROUP BY pseudo;
commit;
go

1 seconde (c'est encore mieux qu'avec des int :pt1cable: )

pseudo nb pct
------------------------------ ----------- ---------------------------------------
User 1 1025 0.10
User 10 18993 1.90
User 11 21167 2.12
User 12 22822 2.28
User 13 25124 2.51
User 14 26999 2.70
User 15 29026 2.90
User 16 30889 3.09
User 17 33202 3.32
User 18 35174 3.52
User 19 36618 3.66
User 2 3045 0.30
User 20 39067 3.91
User 21 40834 4.08
User 22 43104 4.31
User 23 44963 4.50
User 24 47045 4.70
User 25 49326 4.93
User 26 50922 5.09
User 27 53118 5.31
User 28 54991 5.50
User 29 56797 5.68
User 3 5002 0.50
User 30 59141 5.91
User 31 60873 6.09
User 32 38818 3.88
User 4 7022 0.70
User 5 8911 0.89
User 6 11046 1.10
User 7 13038 1.30
User 8 15009 1.50
User 9 16889 1.69

(32 row(s) affected)

HappyHarry

-------------------------------------------
Pseudo, Nb_msg, Percentage
-------------------------------------------
"Robert", 2, 100%
"Rémi", 1, 50%
"Susan", 1, 50%

cherche l'erreur :ange:

Message édité par HappyHarry le 14-07-2007 à 01:50:57

jpcheck

Pioupiou

non mais fallait pas lui dire

MagicBuzz

grmpf ah oui effectivement

MagicBuzz

ça change juste la première requête, c'est pas la mort

Publicité

FORUM HardWare.fr

Programmation

SQL/NoSQL

[SQL 2005] Problème pour une requête SELECT

Sujets relatifs
Problème de SMTP avec la fonction mail() et Yahoo	besoin d'aide pour requete mysql
Problème avec la fonction mail()	[MySQl] erreur bizarre dans une requete
Problème de copie via macro d'une feuille vers un classeur différent	[javascript] problème getElementById sur un objet <A>
problème de formulaire sous firefox alors qu'il marche sur IE et Opéra	fonction SQL dans macro excel
[SQL] Fonctionnement des sous-requêtes	[SQL] aide avec la fonction MAX()
Plus de sujets relatifs à : [SQL 2005] Problème pour une requête SELECT

Page générée en 0.122 secondes