Problème de calcul

**libremax** · 24/09/2020, 00h25

Bonjour,

Je tourne en rond depuis quelque temps sur une procédure qui me permettrai d'ajouter à un tableau comme ci dessous une adition de la colonne seconde en fonction de la ligne précédente

Index , code machine , seconde, date
1......A001.....150.....10/08/2020
1......A001.....140.....09/08/2020
1......A001.....130.....08/08/2020
1......A001.....110.....07/08/2020
1......A002.....110.....10/08/2020
1......A002.....130.....09/08/2020

Afin d'arriver comme ceci -------->

Index , code machine , seconde, date, add_seconde
1......A001.....150.....10/08/2020 .....150
1......A001.....140.....09/08/2020 .....290
1......A001.....130.....08/08/2020 .....420
1......A001.....110.....07/08/2020 .....530 -> date ou l'on dépasse les 500 sec.
1......A002.....110.....10/08/2020 .....110
1......A002.....130.....09/08/2020 .....240

le but de cela serai de pouvoir récupérer a partir de quelle date on dépasse les 500 secondes .

pour simplifier le tout ma procédure doit fonctionner sur sql 2008r2 ->2019. Pourquoi faire simple vous me direz

A partir de sql 2012, il y a bien des solutions mais elle fonctionne pas sur 2008.
j'ai bien tenté avec un cte mais je suis pas trop alaise alors je sèche ...

Quelqu'un pourrait il me guider vers un tuto ? ou me guider vers un début ?

merci d'avance à toute personne pour son aide.

**libremax** · 24/09/2020, 12h22

J'ai codé ceci mais je n'arrive pas a trouver la valeur maxi quand la Duree_Total <500

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
 
drop table t 
create table t ( ord int primary key, Machine nvarchar(5), secondes int, Ddate datetime, Duree_Total int,Point int)
 
insert t(ord,Machine,secondes,Ddate)  values (1,'A001',150,'10/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (2,'A001',140,'09/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (3,'A001',130,'08/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (4,'A001',110,'07/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (5,'A001',110,'06/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (6,'A002',110,'10/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (7,'A002',130,'09/08/2020')
 
declare @total int,@Mac nvarchar(5), @point int, @Trouve int
 
select @total = 0,  @Mac='',@point=0 , @Trouve=0
 
update t set   
		-- A chaque changement de machine on remet le compteur à 0
		@total=case when @mac <>  Machine then ( secondes) else   @total + secondes end,
		@Mac=Machine,
 
		-- ici on recherche le >500
		@point = case when (@total>=500 and @Trouve=0) then 1 else 0 end,
		@Trouve = case when @point=1 then 1 else 0 end,
		-- je voudrais avoir la valeur maxi de la machine si Duree_Total < 500
		-- dans ce cas ord:7 Machine:A002 Ddate 09/08/2020 
 
		------------------------
		Duree_Total = @total,
		Point = @Trouve
 
SELECT        ord, Machine,  Ddate, Duree_Total
FROM            t
WHERE        (Point = 1)

Quelqu'un peux t'il m'aider ?

Cdt

**vanagreg** · 24/09/2020, 15h43

Bonjour,

Il suffit d'utiliser une somme glissante:

Code :

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

1
2
3
4
5
6
select t.machine,
       t.secondes,
       t.ddate,
       sum(secondes) over (partition by machine order by ddate) as duree_totale,
       case when sum(secondes) over (partition by machine order by ddate) > 500 then 1 end as point
from t;

Invité · 24/09/2020, 16h37

Code :

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

1
2
3
4
sum(secondes) over (partition by machine order by ddate)
 
Msg 102, Level 15, State 1, Line 42
Incorrect syntax near 'order'.

Ça ne marche pas avec la version antédiluvienne 2008R2 (pour information : Support end date: 2014-07-08 - Ext. end date: 2019-07-09 )

**libremax** · 24/09/2020, 16h50

Tout d'abord merci de me répondre.

Comme j'ai expliqué au début, je dois trouver une alternative qui fonctionne de sql 2008 ->2019.
Car j'ai des bases de données qui tournent sur ses différentes plateformes et cela ne peux changer pour l'instant.

du coups je recherche une solution transversale.

partion fonctionne mais pas avec le order ...

**vanagreg** · 24/09/2020, 16h52

lol, pas de fonctions analytiques en 2008 sur SQL Server? Elles existent depuis plus de 20 ans sur Oracle.

**libremax** · 24/09/2020, 19h54

effectivement mais cela ne résous pas mon problème

Quelqu'un à t'il une idée ?

Invité · 24/09/2020, 20h19

Il faut travailler avec ROW_NUMBER
https://docs.microsoft.com/en-us/sql...l-server-ver15

**libremax** · 24/09/2020, 23h10

effectivement j ai essayé mais sans succès.
finalement j ai travaillé en numérotant moi même les lignes.
Pour info:

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
 
drop table t
create table t ( ord int primary key, Machine nvarchar(5), secondes int, Ddate datetime, Duree_Total int,Point int, RowsNo int)
 
 
 
insert t(ord,Machine,secondes,Ddate) values (1,'A001',150,'10/08/2020')
insert t(ord,Machine,secondes,Ddate) values (2,'A001',140,'09/08/2020')
insert t(ord,Machine,secondes,Ddate) values (3,'A001',130,'08/08/2020')
insert t(ord,Machine,secondes,Ddate) values (4,'A001',110,'07/08/2020')
insert t(ord,Machine,secondes,Ddate) values (5,'A001',110,'06/08/2020')
insert t(ord,Machine,secondes,Ddate) values (6,'A002',110,'10/08/2020')
insert t(ord,Machine,secondes,Ddate) values (7,'A002',130,'09/08/2020')
 
declare @total int,@Mac nvarchar(5), @point int, @Trouve int,@NoLigne int,@NbLigne int
 
select @total = 0, @Mac='',@point=0 , @Trouve=0,@NoLigne=1,@NbLigne=0
 
update t set
-- on numerote les lignes
RowsNo = @NoLigne,
@NoLigne=case when @mac <> Machine then 1 else @NoLigne+1 end,
 
-- A chaque changement de machine on remet le compteur à 0
@total=case when @mac <> Machine then ( secondes ) else ( @total + secondes) end,
@Mac=Machine,
 
--on compte combien il y a de ligne par machine
@NbLigne =( select COUNT(*) from t xx where t.Machine = xx.Machine),
 
-- ici on recherche le >500
@point = case when (@total>=500 and @Trouve=0) then 1 else 0 end,
--si trouvé ou si nombre de ligne a explorer terminés mais total de 500 non atteint
@Trouve = case when (@point=1 or ((@NoLigne=@NbLigne) and @total<500)) then 1 else 0 end,
--on injecte le resultat
Duree_Total = @total,
Point = @Trouve
 
SELECT Machine, Ddate,Duree_Total
FROM t
WHERE (Point = 1)

surement que quelqu'un saura simplifier cela... Je serai preneur ...

En attendant j'avance.

Merci à tous pour votre aide.
C est toujours aussi agréable de savoir que l'ont peux discuter autour d'un problème.

**aieeeuuuuu** · 25/09/2020, 11h38

Bonjour,

Dans certaines situations, cette solution pourrait vous donner des résultats faux, car elle s'appuie sur l'ordre des lignes et présuppose de l'ordre de traitement sous le capot, qui n'est pas garanti

Par exemple, en changeant le jeu d'essai par celui-ci (en mixant les lignes des différentes machines)

Code :

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

1
2
3
4
5
6
7
8
 
insert t(ord,Machine,secondes,Ddate)  values (1,'A001',150,'10/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (2,'A001',140,'09/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (3,'A001',130,'08/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (4,'A001',110,'07/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (5,'A002',110,'10/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (6,'A001',110,'06/08/2020')
insert t(ord,Machine,secondes,Ddate)  values (7,'A002',130,'09/08/2020')

Pour une solution fiable sans les la fonction analytique SUM() OVER(...), il faut passer par une auto inéquijointure... sur de gros volumes, vous risquez toutefois des mauvaises performances. il sera indispensable de créer un index couvrant la requête

Code :

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

1
2
3
4
5
6
7
8
 
SELECT T1.ord, T1.Machine, T1.secondes, T1.DDate, SUM(T2.Secondes) AS DureeTotale
FROM t AS T1
INNER JOIN T AS T2
	ON T2.Machine = T1.Machine
	AND T2.DDate <= T1.DDate
GROUP BY T1.ord, T1.Machine, T1.secondes, T1.DDate
ORDER BY  T1.Machine,  T1.DDate

**libremax** · 25/09/2020, 22h43

Et bien là un grand merci.

C'est net et sans bavure.

au moins cette solution fonctionne sur tout les SqlServer !

**SQLpro** · 28/09/2020, 08h45

Envoyé par vanagreg

lol, pas de fonctions analytiques en 2008 sur SQL Server? Elles existent depuis plus de 20 ans sur Oracle.

Si c'est depuis la version 2005, mais pas avec toutes les fonctionnalités !

Comme sous oracle d'ailleurs !

A +

**libremax** · 28/09/2020, 08h58

Juste pour le plaisir du boutisme,
avec le code

Code :

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

1
2
3
4
5
6
7
8
 
SELECT T1.ord, T1.Machine, T1.secondes, T1.DDate, SUM(T2.Secondes) AS DureeTotale
FROM t AS T1
INNER JOIN T AS T2
	ON T2.Machine = T1.Machine
	AND T2.DDate <= T1.DDate
GROUP BY T1.ord, T1.Machine, T1.secondes, T1.DDate
ORDER BY  T1.Machine,  T1.DDate

celui-ci met 48 secondes pour s'exécuter avec 6420 lignes à traiter.

vous risquez toutefois des mauvaises performances. il sera indispensable de créer un index couvrant la requête

comment faire cela ?

**vanagreg** · 28/09/2020, 09h47

Envoyé par SQLpro

Si c'est depuis la version 2005, mais pas avec toutes les fonctionnalités !

Comme sous oracle d'ailleurs !

A +

La version Oracle 8.1.6 qui date de 1999 a déjà la clause ORDER BY pour ses fonctions analytiques:

https://docs.oracle.com/cd/A84870_01...tio2.htm#81489

**SQLpro** · 28/09/2020, 10h05

Envoyé par vanagreg

La version Oracle 8.1.6 qui date de 1999 a déjà la clause ORDER BY pour ses fonctions analytiques:

https://docs.oracle.com/cd/A84870_01...tio2.htm#81489

Oui, à cette époque Oracle était en avance, progressiste et moteur.... C'était la bonne époque, maintenant ils sont largués, rétrogrades, contre performants et courent après l'argent et flinguant leurs clients.... qui les fuient comme L'Oréal, Servier ou encore la DGAC !

A +

**vanagreg** · 28/09/2020, 10h37

Je ne peux pas te donner entièrement tort! Leur politique commerciale depuis quelques années est assez déroutante et la qualité du support n'est plus ce qu'elle était, surtout depuis qu'ils ont tous délocalisés en Roumanie pour l'Europe.

**aieeeuuuuu** · 28/09/2020, 12h02

bonjour,

Vous pouvez créer cet index :

Code :

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

1
2
 
CREATE INDEX IX_T_Machine_DDate ON T(Machine, DDate) INCLUDE (ord, secondes)

Problème de calcul

Développement SQL Server

Discussions similaires

Partager

Partager