[SQL-SERVER]Cumuls de valeurs pour un temps donné

**miczl57** · 19/01/2011, 14h04

Bonjour,
Voici mon problème:
j'ai une table du type:

EQ_Name_________________Value______DateHisto
Balise_106_3_A_Alpha_______897_______2011-01-05 12:31:29.2410000
Balise_106_3_A_Alpha_______25________2011-01-05 12:31:32.2410000
Balise_106_3_A_Alpha_______400_______2011-01-05 12:31:35.2410000

et je voudrai ajouter une colonne cumuls des valeurs de 'Value' (A T0 cumuls = 0)

EQ_Name______________Value______DateHisto __________________Cumuls
Balise_106_3_A_Alpha____897_______2011-01-05 12:31:29.2410000__0
Balise_106_3_A_Alpha____25________2011-01-05 12:31:32.2410000__25
Balise_106_3_A_Alpha____400_______2011-01-05 12:31:35.2410000__425

J'ai essayé cette requête trouvé pour oracle mais ca ne fonctionne pas sur sql-server:

Code :

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

1
2
3
4
5
6
SELECT  
      [EQ_NAME]
      ,[Value]
      ,[DateHisto]
      ,sum(Value) over(partition by EQ_NAME order by DateHisto) as cumuls
  FROM [PanoIT].[dbo].[Histo_Flat]

Si quelqu'un a une idée de comment faire...

**aieeeuuuuu** · 19/01/2011, 15h09

Bonjour,

1/ quelle est votre version de SQL Server ?

2/ Avez-vous une contrainte d'unicité sur (EQ_Name, DateHisto) ?

**aieeeuuuuu** · 19/01/2011, 15h29

Re,

Essayez ceci, qui fonctionne si vous avez la contrainte d'unicité, et que vous êtes au moins en SQL Server 2005 (pour les CTE)

Code sql :

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
 
DECLARE @Histo TABLE(
	Lib		VARCHAR(50)
	,Val	INT
	,Dte	DATETIME
	)
 
INSERT INTO @Histo VALUES('Balise_106_3_A_Alpha',897, '2011-01-05 12:31:29.241')
INSERT INTO @Histo VALUES('Balise_106_3_A_Alpha',25,'2011-01-05 12:31:32.241')
INSERT INTO @Histo VALUES('Balise_106_3_A_Alpha',400,'2011-01-05 12:31:35.241')
INSERT INTO @Histo VALUES('Balise_106_3_B_Alpha',897, '2011-01-05 12:31:29.241')
INSERT INTO @Histo VALUES('Balise_106_3_B_Alpha',24,'2011-01-05 12:31:32.241')
INSERT INTO @Histo VALUES('Balise_106_3_B_Alpha',400,'2011-01-05 12:31:35.241')
 
;WITH CTE (Lib, Val, Dte, Somme) AS (
	SELECT Lib, Val, Dte, 0
	FROM(
		SELECT lib, val,Dte, MIN(Dte) OVER(PARTITION BY Lib) AS MinDte
		FROM @Histo H
	)Tmp
	WHERE Dte = MinDte	
	UNION ALL
	SELECT Lib, Val, Dte, Somme
	FROM(
		SELECT H.lib, H.val,H.Dte, MIN(H.Dte) OVER(PARTITION BY H.Lib) AS MinDte, CTE.Somme + H.val AS Somme
		FROM @Histo H
		INNER JOIN CTE ON CTE.Lib = H.Lib
		WHERE H.Dte > CTE.Dte
	)Tmp
	WHERE Dte = MinDte	
 
)
SELECT Lib, Val, Dte, Somme
FROM CTE
ORDER BY Lib, Dte

**miczl57** · 19/01/2011, 15h32

Merci pour ta reponse
Alors ma version de SQL-Server est 2008R2

Je ne sait pas comment on vois s'il y a des contrainte d'unicité (je suis débutant...)

**aieeeuuuuu** · 19/01/2011, 15h41

Envoyé par miczl57

Merci pour ta reponse
Alors ma version de SQL-Server est 2008R2

C'est déjà une bonne nouvelle

Je ne sait pas comment on vois s'il y a des contrainte d'unicité (je suis débutant...)

Dans management studio, clique droit sur la table > script table as > create to > new query window...

**miczl57** · 19/01/2011, 15h45

Alors voici ce que j'obtient:

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
USE [PanoIT]
GO
 
/****** Object:  View [dbo].[Histo_Flat]    Script Date: 01/19/2011 15:44:15 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE VIEW [dbo].[Histo_Flat]
AS
SELECT     [Private].Raw_Histo.Id, [Private].Raw_Histo.RefData AS RefData,
                      [Private].Raw_Desc_Data.Name AS IT_NAME, 
                      [Private].Raw_Desc_Data.Info AS IT_INFO, [Private].Raw_Desc_Equip.Name AS EQ_NAME, [Private].Raw_Desc_EquipTypes.Name AS EQ_TYPE, 
                      [Private].Raw_Desc_EquipModels.Name AS EQ_MODEL, [Private].Raw_Desc_Equip.Info AS EQ_INFO, [Private].Raw_Desc_DataGroups.Name AS IT_GROUP, 
                      [Private].Raw_Desc_Data.Priority, [Private].Raw_Desc_Categories.Name AS IT_CAT, [Private].Raw_Desc_Sites.Name AS IT_SITE_NAME, 
                      [Private].Raw_Desc_Sites.Info AS IT_SITE_INFO, [Private].Raw_Histo.Value,
                      [Private].Raw_Histo.DateHisto AS DateHisto, [Private].Raw_Histo.DateHistoUTC AS DateHistoUTC
FROM         [Private].Raw_Histo INNER JOIN
                      [Private].Raw_Desc_Data ON [Private].Raw_Desc_Data.Id = [Private].Raw_Histo.RefData INNER JOIN
                      [Private].Raw_Desc_Equip ON [Private].Raw_Desc_Equip.Id = [Private].Raw_Desc_Data.RefEquip INNER JOIN
                      [Private].Raw_Desc_DataGroups ON [Private].Raw_Desc_DataGroups.Id = [Private].Raw_Desc_Data.RefDataGroup INNER JOIN
                      [Private].Raw_Desc_EquipModels ON [Private].Raw_Desc_EquipModels.Id = [Private].Raw_Desc_Equip.RefEquipModel INNER JOIN
                      [Private].Raw_Desc_EquipTypes ON [Private].Raw_Desc_EquipTypes.Id = [Private].Raw_Desc_EquipModels.RefEquipType INNER JOIN
                      [Private].Raw_Desc_Categories ON [Private].Raw_Desc_Categories.Id = [Private].Raw_Desc_Data.RefCategory INNER JOIN
                      [Private].Raw_Desc_Sites ON [Private].Raw_Desc_Sites.Id = [Private].Raw_Desc_Equip.RefSite
 
GO

**aieeeuuuuu** · 19/01/2011, 16h18

c'est une vue...

pourrais tu faire de même sur la table Raw_Histo dans ce cas ?

**Waldar** · 19/01/2011, 17h23

Vous pouvez aussi essayer ainsi :

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
DECLARE @Histo TABLE
(
    Lib VARCHAR(20),
    Val SMALLINT   ,
    Dte DATETIME
)
 
INSERT INTO @Histo (Lib, Val, Dte) 
select 'Balise_106_3_A_Alpha', 897, {ts '2011-01-05 12:31:29.241'} union all
select 'Balise_106_3_A_Alpha',  25, {ts '2011-01-05 12:31:32.241'} union all
select 'Balise_106_3_A_Alpha', 400, {ts '2011-01-05 12:31:35.241'} union all
select 'Balise_106_3_B_Alpha', 897, {ts '2011-01-05 12:31:29.241'} union all
select 'Balise_106_3_B_Alpha',  24, {ts '2011-01-05 12:31:32.241'} union all
select 'Balise_106_3_B_Alpha', 400, {ts '2011-01-05 12:31:35.241'}
 
;With Histo (Lib, Val, Dte, rn) as
(
select Lib, val, Dte,
       row_number() over(PARTITION BY Lib order by Dte asc)
  from @Histo
)
  select T1.Lib, T1.val, T1.Dte,
         sum(case T2.rn when 1 then 0 else T2.val end) as Cumul
    from Histo as T1
         inner join Histo as T2
           on T2.Lib  = T1.Lib
          and T2.Dte <= T1.Dte
group by T1.Lib, T1.val, T1.Dte
order by T1.Lib asc, T1.Dte asc
 
Lib                  val    Dte                     Cumul
-------------------- ------ ----------------------- -----------
Balise_106_3_A_Alpha 897    2011-01-05 12:31:29.240 0
Balise_106_3_A_Alpha 25     2011-01-05 12:31:32.240 25
Balise_106_3_A_Alpha 400    2011-01-05 12:31:35.240 425
Balise_106_3_B_Alpha 897    2011-01-05 12:31:29.240 0
Balise_106_3_B_Alpha 24     2011-01-05 12:31:32.240 24
Balise_106_3_B_Alpha 400    2011-01-05 12:31:35.240 424

**miczl57** · 20/01/2011, 09h14

Pour la table j'obtient ceci:

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
USE [PanoIT]
GO
 
/****** Object:  Table [Private].[Raw_Histo]    Script Date: 01/20/2011 09:07:41 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [Private].[Raw_Histo](
	[DateHistoUTC] [datetime2](7) NOT NULL,
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[DateHisto] [datetime2](7) NOT NULL,
	[RefData] [bigint] NOT NULL,
	[Value] [float] NULL,
 CONSTRAINT [PK_Raw_Histo] PRIMARY KEY CLUSTERED 
(
	[DateHistoUTC] ASC,
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [Private].[Raw_Histo]  WITH CHECK ADD  CONSTRAINT [FK_Raw_Histo_Raw_Desc_Data] FOREIGN KEY([RefData])
REFERENCES [Private].[Raw_Desc_Data] ([Id])
GO
 
ALTER TABLE [Private].[Raw_Histo] CHECK CONSTRAINT [FK_Raw_Histo_Raw_Desc_Data]
GO

**miczl57** · 20/01/2011, 09h28

Bon j'ai modifié la requête en utilisant mes nom de champ:

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
 
;WITH CTE (EQ_NAME, Value, DateHisto, Somme) AS (
	SELECT EQ_NAME, Value, DateHisto, 0
	FROM(
		SELECT EQ_NAME, Value,DateHisto, MIN(DateHisto) OVER(PARTITION BY EQ_NAME) AS MinDte
		FROM Histo_Flat
	)Tmp
	WHERE DateHisto = MinDte	
	UNION ALL
	SELECT EQ_NAME, Value, DateHisto, Somme
	FROM(
		SELECT H.EQ_NAME, H.Value,H.DateHisto, MIN(H.DateHisto) OVER(PARTITION BY H.EQ_NAME) AS MinDte, CTE.Somme + H.Value AS Somme
		FROM Histo_Flat H
		INNER JOIN CTE ON CTE.EQ_NAME = H.EQ_NAME
		WHERE H.DateHisto > CTE.DateHisto
	)Tmp
	WHERE DateHisto = MinDte	
 
)
SELECT EQ_NAME, Value, DateHisto, Somme
FROM CTE
ORDER BY EQ_NAME, DateHisto

Mais j'obtiens le message d'erreur :
Msg*240, Niveau*16, État*1, Ligne*1
Les types ne correspondent pas entre la partie d'ancrage et la partie récursive dans la colonne "Somme" de la requête récursive "CTE".

**aieeeuuuuu** · 20/01/2011, 11h19

visiblement, ta colonne value est de type float, il faut donc caster dans l'ancrage de la CTE :

Code sql :

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
 
;WITH CTE (EQ_NAME, Value, DateHisto, Somme) AS (
	SELECT EQ_NAME, Value, DateHisto, CAST(0 AS FLOAT)
	FROM(
		SELECT EQ_NAME, Value,DateHisto, MIN(DateHisto) OVER(PARTITION BY EQ_NAME) AS MinDte
		FROM Histo_Flat
	)Tmp
	WHERE DateHisto = MinDte	
	UNION ALL
	SELECT EQ_NAME, Value, DateHisto, Somme
	FROM(
		SELECT H.EQ_NAME, H.Value,H.DateHisto, MIN(H.DateHisto) OVER(PARTITION BY H.EQ_NAME) AS MinDte, CTE.Somme + H.Value AS Somme
		FROM Histo_Flat H
		INNER JOIN CTE ON CTE.EQ_NAME = H.EQ_NAME
		WHERE H.DateHisto > CTE.DateHisto
	)Tmp
	WHERE DateHisto = MinDte	
 
)
SELECT EQ_NAME, Value, DateHisto, Somme
FROM CTE
ORDER BY EQ_NAME, DateHisto

**miczl57** · 21/01/2011, 08h55

Bon après test de cette 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
16
17
18
19
20
21
;WITH CTE (EQ_NAME, Value, DateHisto, Somme) AS (
	SELECT EQ_NAME, Value, DateHisto, CAST(0 AS FLOAT)
	FROM(
		SELECT EQ_NAME, Value,DateHisto, MIN(DateHisto) OVER(PARTITION BY EQ_NAME) AS MinDte
		FROM Histo_Flat
	)Tmp
	WHERE DateHisto = MinDte	
	UNION ALL
	SELECT EQ_NAME, Value, DateHisto, Somme
	FROM(
		SELECT H.EQ_NAME, H.Value,H.DateHisto, MIN(H.DateHisto) OVER(PARTITION BY H.EQ_NAME) AS MinDte, CTE.Somme + H.Value AS Somme
		FROM Histo_Flat H
		INNER JOIN CTE ON CTE.EQ_NAME = H.EQ_NAME
		WHERE H.DateHisto > CTE.DateHisto
	)Tmp
	WHERE DateHisto = MinDte	
 
)
SELECT EQ_NAME, Value, DateHisto, Somme
FROM CTE
ORDER BY EQ_NAME, DateHisto

J'obtient le code d'erreur suivant après plusieurs minute de moulinage :
Msg*530, Niveau*16, État*1, Ligne*2
L'instruction a été terminée. La récursivité maximale 100 a été épuisée avant la fin de l'instruction.

**aieeeuuuuu** · 21/01/2011, 10h11

par défaut le nombre de récursions est limité à 100

Ajoute cette ligne à la fin de ta requete pour spécifier un nombre illimité de récursions :

Code sql :

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

1
2
 
OPTION MAXRECURSION 0

**vmolines** · 21/01/2011, 10h28

Votre cumul peut se faire par simple autojointure si je ne m'abuse :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
	A.EQ_Name, 
	A.DateHisto, 
	A.Value, 
	SUM(COALESCE(B.Value, 0)) as Cumuls
FROM Histo_Flat A
	LEFT JOIN Histo_Flat B
		ON A.EQ_Name = B.EQ_Name 
		AND A.DateHisto > B.DateHisto
GROUP BY 
	A.EQ_Name, 
	A.DateHisto, 
	A.Value

**miczl57** · 21/01/2011, 11h14

Merci a tous pour vos réponses.
vmolines ta solution fonctionne,par contre mes balises me font une mesure toutes les 3 secondes donc c'est très lent.
je vais essayer de faire un cumul toutes les heures.

**aieeeuuuuu** · 21/01/2011, 11h21

En effet vmonlines !

Sauf que comme il ne veut pas prendre en compte la "première valeur", le code serait plutot celui-ci :

Code sql :

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
 
SELECT 
	A.EQ_Name, 
	A.DateHisto, 
	A.Value, 
	SUM(COALESCE(B.Value, 0)) - Premier.PremiereValeur AS Cumuls 
FROM Histo_Flat  A
	LEFT JOIN Histo_Flat B
		ON A.EQ_Name = B.EQ_Name 
		AND A.DateHisto >= B.DateHisto
	LEFT JOIN (
		SELECT EQ_NAME, Value
		FROM(
			SELECT 
				EQ_NAME, 
				Value,
				DateHisto, 
				MIN(DateHisto) OVER(PARTITION BY EQ_NAME) AS MinDte
			FROM Histo_Flat 
		)Tmp
		WHERE DateHisto = MinDte
	) AS Premier(EQ_Name, PremiereValeur) ON Premier.EQ_Name = A.EQ_Name
GROUP BY 
	A.EQ_Name, 
	A.DateHisto, 
	A.Value,
	Premier.PremiereValeur
ORDER BY A.EQ_NAME, A.DateHisto

**Waldar** · 21/01/2011, 11h26

Jetez un œil ma solution qui a été oubliée je pense, non pas que je cherche à me faire mousser, mais je pense que côté performance elle doit tenir la route.

**vmolines** · 21/01/2011, 11h34

C'est possible et pas de mal à rappeler une solution oubliée.

Côté performance de ma requête, elle mériterait :

- de travailler avec une clé qui ne soit pas du texte (EQ_NAME)
- de bénéficier d'un index couvrant la date
- de s'affranchir de la vue initiale qui doit bien alourdir le tout. Je n'ai pas pris le temps de l'analyser.

**aieeeuuuuu** · 21/01/2011, 11h38

Envoyé par Waldar

Jetez un œil ma solution qui a été oubliée je pense, non pas que je cherche à me faire mousser, mais je pense que côté performance elle doit tenir la route.

En effet !

**miczl57** · 21/01/2011, 11h42

En fait a part la première solution de vmolines les autres solutions ne m'affiche toujours rien au bout de 5 minutes...
Par contre j'ai 27 EQ_NAME différend,si je met une condition Where avec le nom de la balise le resultat est faux...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
	A.EQ_Name, 
	A.DateHisto, 
	A.Value, 
	SUM(COALESCE(B.Value, 0)) AS Cumuls
FROM Histo_Flat A
	LEFT JOIN Histo_Flat B
		ON A.EQ_Name = B.EQ_Name 
		AND A.DateHisto > B.DateHisto
where a.eq_name='Balise_106_3_A_Alpha'
GROUP BY 
	A.EQ_Name, 
	A.DateHisto, 
	A.Value

Resultat:

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
 
EQ_Name	                DateHisto                    Value     Cumuls
Balise_106_3_A_Alpha	2011-01-05 12:31:37.2510000	-3	15576
Balise_106_3_A_Alpha	2011-01-05 12:31:48.2510000	-3	32043
Balise_106_3_A_Alpha	2011-01-05 12:31:59.2510000	-3	48510
Balise_106_3_A_Alpha	2011-01-05 12:32:10.2670000	-3	64977
Balise_106_3_A_Alpha	2011-01-05 12:32:21.2680000	-3	81444
Balise_106_3_A_Alpha	2011-01-05 12:32:32.2680000	-3	97911
Balise_106_3_A_Alpha	2011-01-05 12:32:43.2680000	-3	114378
Balise_106_3_A_Alpha	2011-01-05 12:32:54.2700000	-3	130845
Balise_106_3_A_Alpha	2011-01-05 12:33:05.2730000	-3	147312
Balise_106_3_A_Alpha	2011-01-05 12:33:16.2730000	-3	163779
Balise_106_3_A_Alpha	2011-01-05 12:33:27.2750000	-3	180246
Balise_106_3_A_Alpha	2011-01-05 12:33:38.2740000	-3	196713
Balise_106_3_A_Alpha	2011-01-05 12:33:49.2750000	-3	213180
Balise_106_3_A_Alpha	2011-01-05 12:34:00.2750000	-3	229647
Balise_106_3_A_Alpha	2011-01-05 12:34:11.2830000	-3	246114

[SQL-SERVER]Cumuls de valeurs pour un temps donné

MS SQL Server

Discussions similaires

Partager

Partager