Discussion :

[freestyler]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
select categorie, article, avg(ventes) as average_ventes, 
ROW_NUMBER() OVER(partition by categorie order by categorie, avg(ventes) DESC) AS 'RowNumber'
from ARTILCES P
inner join VENTES V on V.article = A.article
group by categorie, V.article

Ce code là me génére les ventes moyennes de chaque article par catégorie.
Le RowNumber est une colonne que j'ai du ajouter pour numéroter les lignes de chaque catégorie (se réinitalize à 1 à chaque changement dans le colonne catégorie)

donc, des enregistrements de la forme (categorie, article, ventes, rownumber)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
cat1	1204640	71.4060		1
cat1	 396233	41.9904		2
cat1    1249238	18.9575		3
cat1     396226	 5.5059		4
cat2    2827032	908.122		1
cat2    1294508	446.263		2
cat2 	1284515	 95.345		3
cat3 	2755212	815.432		1
cat3 	2269163	721.295		2
cat3 	3560884	349.409		3
cat3 	3560921	 78.424		4
cat3 	3560922	 68.424		5

ce n'est qu'un petit aperçu car le rownumber peut aller à 2000 ou plus par exemple...

Ce que je veux en fait, c'est d'extraire l'information :
"Quel article (par catégorie bien entendu) occupe la position médiane?"
je m'explique:
pour catégorie 1 : nous avons 4 articles
50% des articles de categorie1 ici ont un volume de ventes supérieur à 41.9904 (l'ordre fourni par le code sql le montre bien)

En plus simple, la position médiane correspond à l'article dont le rownumber est égal au max de ses rownumbers (ici cat1-->4, cat2-->3 et cat3-->5) divisé par 2

Des idées ? merci

[SQLpro]

Lisez l'article que j'ai écrit à ce sujet : http://www.sqlspot.com/Calcul-de-la-mediane-en-SQL.html

Le plus simple étant de réaliser la chose de la manière suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
CREATE TABLE T_ARTICLE_ART
(ART_ID         INT NOT NULL PRIMARY KEY,
 ART_LIBELLE    VARCHAR(16),
 ART_CATEGORIE  VARCHAR(16))
 
INSERT INTO T_ARTICLE_ART VALUES (1, 'TV', 'Electroménager')
INSERT INTO T_ARTICLE_ART VALUES (2, 'FRIGO', 'Electroménager')
INSERT INTO T_ARTICLE_ART VALUES (5, 'LAVELINGE', 'Electroménager')
INSERT INTO T_ARTICLE_ART VALUES (3, 'Saumon', 'Poisson')
INSERT INTO T_ARTICLE_ART VALUES (4, 'Truite', 'Poisson')
 
CREATE TABLE T_VENTE_VTE
(VTE_ID         INT NOT NULL PRIMARY KEY,
 ART_ID         INT NOT NULL FOREIGN KEY REFERENCES T_ARTICLE_ART (ART_ID ),
 VTE_QUANTITE   FLOAT)
 
INSERT INTO T_VENTE_VTE VALUES (1, 1, 43)
INSERT INTO T_VENTE_VTE VALUES (2, 3, 21)
INSERT INTO T_VENTE_VTE VALUES (3, 2, 43)
INSERT INTO T_VENTE_VTE VALUES (4, 4, 21)
INSERT INTO T_VENTE_VTE VALUES (5, 1, 22)
INSERT INTO T_VENTE_VTE VALUES (6, 3, 33)
INSERT INTO T_VENTE_VTE VALUES (7, 2, 5)
INSERT INTO T_VENTE_VTE VALUES (8, 4, 2)
INSERT INTO T_VENTE_VTE VALUES (9, 5, 12)
 
SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY ART_CATEGORIE ORDER BY TOTAL_VENTE) AS NUM,
       COUNT(*) OVER(PARTITION BY ART_CATEGORIE) AS NOMBRE
FROM
(
SELECT A.ART_ID, ART_LIBELLE, ART_CATEGORIE, 
       SUM(VTE_QUANTITE) AS TOTAL_VENTE
FROM   T_ARTICLE_ART A
       INNER JOIN T_VENTE_VTE V
             ON A.ART_ID = V.ART_ID
GROUP  BY A.ART_ID, ART_LIBELLE, ART_CATEGORIE 
) AS T
 
) AS TT
WHERE  NUM >= CASE 
                 WHEN NUM % 2 = 1 THEN (NOMBRE / 2) + 1
                 ELSE (NOMBRE / 2)
              END
  AND  NUM <= CASE 
                 WHEN NUM % 2 = 1 THEN (NOMBRE / 2) + 1
                 ELSE (NOMBRE / 2) + 1
              END

J'ai pas eu le temps de finaliser la chose. Le dernier filtre whre doit ^pêtre modifié...

A +

[freestyler]

Bon je n'ai pas suivi ton code, car je me me suis concentré sur le mien (surtout que j'ai besoin de plus que la médiane (le quart, le triple quart, ..))

Tout est probléme du count de la série.

Alors j'ai pensé à un moyen pour avoir le count de la série dans la méme table.

Si tu ordonnes chaque série (row_num over partition...) dans un sens, puis dans l'autre, et que tu fais la somme des Rangs tu obtiens toujours le count + 1 de chaque série

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
select H.categorie, H.average_sales as median
from (
	select R.* , 
	ROW_NUMBER() OVER(partition by R.categorie order by R.categorie, average_sales desc) AS 'RowNumberAsc'
	from (select categorie, 
				article, 
				avg(sales_volume) as average_sales, 
				ROW_NUMBER() OVER(partition by categorie order by categorie, avg(sales_volume)) AS 'RowNumber'
				from ARTICLES P
				inner join VENTES Hs on Hs.sku = P.sku_id
				group by categorie, Hs.article
		  ) R
	) H
where RowNumber = (RowNumber + RowNumberAsc - 1) / 2
order by H.categorie

avec ce code, tu peux adapter le dernier where pour obtenir n'importe quelle position dans ta série

Je vais jeter un oeil à ton code une fois tout ceci terminé. Pt etre bien que l'on pourrait poster un code générique en combinant nos 2 codes.

Merci en tout cas de prendre la peine d'y réfléchir.

[SQLpro]

Tu peut utiliser dans ce cas NTILE....

A +

[freestyler]

tu peux détailler STP..
J'ai jeté un coup d'oeil à cette fonction sur la doc Miscrosoft

Il semble bien que ça correspond à ce que je cherche, mais je n'ai pas compris comment l'utiliser

Merci bp

[freestyler]

Ok je vois mieux maintenant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select categorie, average_sales,
ntile(4) over (partition by categorie order by categorie)
from VENTES

va diviser chaque groupe de categories en 4


Comment donc avoir la ligne qui correspond à la premiére valeur de 2, 3 et 4 pour chaque categorie?

Merci