Discussion :

[rudylar]

Bonjour,

Problème :
Je possède un champ de valeurs.
Dans ce champ, plusieurs valeurs représentent les X et d'autres les Y.
Avec cela je veux calculer la covariance.

Mes connaissances:
Voici un code que j'ai trouvé sur le net

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
DECLARE @avg1  AS float;
DECLARE @avg2  AS float;
DECLARE @cnt AS bigint;
 
-- assuming your columns c1 and c2 are integers
SET @avg1 = (SELECT AVG(CAST([c1]) AS FLOAT) FROM [table]);
SET @avg2=(SELECT AVG(CAST([c2] AS FLOAT))  FROM [table]);
SET @cnt = (SELECT COUNT(c1)  FROM [table]);
 
SELECT (1/(@cnt-1))*SUM(([c1]-@avg1)*([c2]-@avg2)) AS covariance  FROM [table];

Je crois comprendre que c1 et c2 représentent les champs des valeurs X et Y. Mais le problème chez moi est qu'il faut que je me crée deux tables temporaires qui représentent les valeurs X et Y.

Mais je ne connait pas la synthaxe.

Pouvez vous m'aider s'il vous plait ?
Pour tout autre information par rapport à mon pb, n'hésitez pas.
Merci beaucoup.

Màj : je suis sous MS-SQL Serveur 2005

[Yanika_bzh]

Quelle est votre definition de "champ" ?? une colonne ??
Cela veut dire que dans votre colonne vous faites cohabiter 2 valeurs ?

[rudylar]

En fait j'ai une table qui possède 2 champs. Un pour indiquer si c'est X ou Y et un autre champ avec les valeurs numériques.

Lorsque je dis champ, c'est une colonne d'une table oui.

[pacmann]

Salut !

Ce ne sont pas des tables temporaires : tes requêtes calculent des count, sum, ... et les stockent dans des variables.
=> Le problème des variables de dispersion, c'est qu'elles comparent des données "unitaires" à des données de groupes. C'est pour cela qu'il calcule ces grandeurs séparément.

Maintenant, tu dois pouvoir réécrire sauvagement ce truc en :

Code :

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

1
2
3
4
 
SELECT (1/((SELECT count(*) FROM TaTable)-1))
* SUM(([c1]-(SELECT avg(c1) FROM TaTable))*([c2]-(SELECT avg(c2) FROM TaTable))) AS covariance  
FROM [TABLE];

(comme c'est très lourd à écrire, j'ai un peu bâclé, mais je pense que tu comprends le principe...)

[rudylar]

Merci beaucoup mais je ne comprends pas vraiment ce que représente c1 et c2.

Enfin disons que je ne sais pas comment les déclarer.

[pacmann]

Ce sont tes colonnes ou champs qui contiennent les données à analyser ! Donc à priori tes X et Y.
Tu peux nous donner le schéma (la structure) de ta table ?

Tu connais la formule de la covariance ? En regardans la requête, je suis sûr que tu trouveras des similitudes

[rudylar]

Je vous donne un aperçu de ce que cela pourrait être.

Pour que vous compreniez ce que je veux dire je vous montre l'idée en code

Code :

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

1
2
 
SET c1 = SELECT value AS value FROM matable WHERE matable.value=1;

Puis je fais

Code :

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

1
2
3
4
 
SELECT (1/((SELECT count(*) FROM TaTable)-1))
* SUM(([c1]-(SELECT avg(c1) FROM TaTable))*([c2]-(SELECT avg(c2) FROM TaTable))) AS covariance  
FROM [TABLE];

C'est ça ? Et est ce que mon premier code est correct ?

[rudylar]

Et voila je reviens vers vous...

Voici le code de la covariance :

Code :

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

1
2
 
SELECT (1/(@cnt-1))*SUM((c1-@avg1)*(c1-@avg2)) AS covariance  FROM dbo.MainDataTable;

Mais mon problème est bien le meme que le précédent post : comment déclarer le C1.

[pacmann]

La définition de ta table, s'il te plaît !

[rudylar]

Ne me gronde pas mais tu veux dire quoi par définition ? Les types de chaques champs ?

[pacmann]

Non, je ne te gronde pas, je ne suis pas ton papa

Tu veux calculer des résultats sur des données, qui sont dans une table.
Cette table est définie comme un ensemble de colonnes.
Si tu as SQL Server Management Chose, tu dois pouvoir faire clic droit sur cette table, et choisir un truc du genre "générer..." -> "définition".

Le résultat sera de la forme :
CREATE TABLE TaTable
( Colonne1 UnTypeDeDonnees,
Colonne2 UnAutreTypeDeDonnees,
...)

Une fois qu'on connaît ça, et que tu nous expliques à quoi correspondent tes X, Y, ..., on pourra t'aider à écrire la requête en faisant la bonne substitution des C1 et C2.

[rudylar]

Ok voila la definition

Code :

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

1
2
3
4
 
CREATE TABLE [dbo].[MainDataTable](	
	[série] [int] NOT NULL,
	[valeur] [float] NULL,

Pour les X et Y j'ai mis une image en pièce jointe dans un des post précédent. En fait les X représentent les 1 et les Y les 2.

[pacmann]

Ok, donc valeur peut être un X ou un Y.
Et c'est la colonne série qui indique si c'est X ou Y ?

Mais pour avoir la covariance de X et Y, il faudrait qu'on ait des couples (X, Y), non ?

[rudylar]

Exact et en fait dans la colonne série il y a autant de valeur de x que de y, et la première valeur de x correspond à la première valeur de y.

[pacmann]

Le problème, c'est que l'ordre d'insertion n'est pas un ordre.
Ta table correspond à un ensemble (au sens math).
Donc tu as deux solutions :
- ajouter une colonne "NumObservation" afin de pouvoir faire le lien entre les instances de X et de Y.
- mettre X et Y sur la même ligne.
(ta table serait alors :

CREATE TABLE [dbo].[MainDataTable](
[valeurX] [float] NULL,
[valeurY] [float] NULL)

)

[rudylar]

Je prends la première solution car en fait j'ai une troisieme colonne qui représente une date. Et ce qu'il se passe c'est que les couples (X,Y) sont pour une date donnée.

Màj : voilà une mise à jour de ma table.

[pacmann]

Ah, ben ça va être plus facile maintenant !
Donc, l'idée, c'est qu'il te faut d'abord récupérer les données sous forme de couple (X,Y).

Je suppose que tu connais le principe des jointures :

Code :

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

1
2
3
4
5
6
 
SELECT a.valeur as X, b.valeur as Y
FROM MainData a JOIN MainData b
  ON a.date = b.date
WHERE a.serie = 1
  AND b.serie = 2

Du coup, tu peux considérer que X est C1 et Y est C2.
Par exemple :

Code :

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

1
2
3
4
5
6
 
SELECT avg(a.valeur) as AvgX, avg(b.valeur) as avgY
FROM MainData a JOIN MainData b
  ON a.date = b.date
WHERE a.serie = 1
  AND b.serie = 2

Il ne reste plus qu'à adapter l'ensemble de la formule...

[rudylar]

"Pacmann" je te remercie beaucoup pour ta patiente.
Je me mets au boulot dessuite. Je pense que maintenant cela va aller.

Je te tiens au courant vite.

[rudylar]

Alors voilà mon code actuel :

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
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		moi
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE coviariance 
	-- Add the parameters for the stored procedure here
	@serie1 int, 
	@serie2 int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
 
 
	SELECT (1/((SELECT count(a.valeur) AS countX
				FROM dbo.MainDataTable a JOIN dbo.MainDataTable b
				  ON a.date = b.date
				WHERE a.serie = @serie1
				  AND b.serie = @serie2)-1))*SUM(((SELECT a.valeur AS X	FROM dbo.MainDataTable a JOIN dbo.MainDataTable b
													  ON a.date = b.date
													WHERE a.serie = @serie1
													  AND b.serie = @serie2)-(SELECT avg(a.valeur) AS avgX
																				FROM dbo.MainDataTable a JOIN dbo.MainDataTable b
																				  ON a.date = b.date
																				WHERE a.serie = @serie1
																				  AND b.serie = @serie2))*((SELECT a.valeur AS X, b.valeur AS Y
																											FROM dbo.MainDataTable a JOIN dbo.MainDataTable b
																											  ON a.date = b.date
																											WHERE a.serie = @serie1
																											  AND b.serie = @serie2)-(SELECT avg(b.valeur) AS avgY
																																		FROM dbo.MainDataTable a JOIN dbo.MainDataTable b
																																		  ON a.date = b.date
																																		WHERE a.serie = @serie1
																																		  AND b.serie = @serie2))) AS covariance  FROM dbo.MainDataTable;
END
GO

Je me rends compte que la procédure stockée n'accepte pas les sous requêtes. Et je ne vois pas d'autres solutions car je ne peux pas faire plusieurs SELECT non plus.

[pacmann]

En fait, tu n'as pas besoin de faire une procédure : tu peux écrire ça en une seule requête :

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
 
WITH tCounts AS
(
SELECT count(*) - 1 as cnt, avg(a.value) as AvgX, avg(b.value) as AvgY
FROM MainData a JOIN MainData b
  ON a.date = b.date
WHERE a.serie = 1
  AND b.serie = 2
)
SELECT 1 / cnt * sum((a.value - AvgX) * (b.value -AvgY)) as covariance
FROM Maindata a JOIN MainDate b
  ON a.date = b.date
                CROSS JOIN tCounts
WHERE a.serie = 1
  AND b.serie = 2

- Dans la clause WITH, tu définis l'expression qui comporte tes moyennes et nombres de lignes (ce sont des grandeurs d'agrégat)
- Dans le select juste en dessous, tu appliques la formule de la covariance, en utilisant ce qui est calculé dans WITH.