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

Marsh Posté le 23-06-2007 à 13:57:17

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

Reply

Marsh Posté le 23-06-2007 à 13:57:17

Reply

Marsh Posté le 23-06-2007 à 14:52:16

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

Reply

Marsh Posté le 23-06-2007 à 17:13:16

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

Reply

Marsh Posté le 25-06-2007 à 09:52:02

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.

Reply

Marsh Posté le 28-06-2007 à 23:42:15

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

Reply

Marsh Posté le 29-06-2007 à 09:14:03

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

Reply

Marsh Posté le 30-06-2007 à 11:22:38

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

Reply

Marsh Posté le 30-06-2007 à 14:27:19

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

Reply

Marsh Posté le 30-06-2007 à 14:32:21

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)

Reply

Marsh Posté le 30-06-2007 à 15:59:10

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

Reply

Marsh Posté le 30-06-2007 à 15:59:10

Reply

Marsh Posté le 14-07-2007 à 00:06:59

soit dit en passant ta table temporaire ne sert à rien

Reply

Marsh Posté le 14-07-2007 à 01:04:55

à 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

Reply

Marsh Posté le 14-07-2007 à 01:14:19

le type float c'est beurk

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

Reply

Marsh Posté le 14-07-2007 à 01:23:02

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

Reply

Marsh Posté le 14-07-2007 à 01:24:39

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

Reply

Marsh Posté le 14-07-2007 à 01:38:32

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é

Reply

Marsh Posté le 14-07-2007 à 01:40:11

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

Reply

Marsh Posté le 14-07-2007 à 01:45:55

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)

Reply

Marsh Posté le 14-07-2007 à 01:50:31

-------------------------------------------
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

Reply

Marsh Posté le 14-07-2007 à 10:26:06

non mais fallait pas lui dire

Reply

Marsh Posté le 14-07-2007 à 11:00:35

grmpf ah oui effectivement

Reply

Marsh Posté le 14-07-2007 à 11:00:59

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

Reply

Marsh Posté le

Reply

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

Sujets relatifs:

Leave a Replay