[SQLSERVER 2005] Fonction de fenetrage, et numéro de partition

**aziatedu13** · 07/10/2014, 06h45

Bonjour,

je bloque sur un problème sql :

Ma table :

NOM	PRENOM	SOCIETE	STATUT	DATE	Row NUMBER	PARTITION NUMBER
MARC	LOUIS	AdventureWorks	PRESENT	2014-09-01	1	1
MARC	LOUIS	AdventureWorks	PRESENT	2014-09-02	2	1
MARC	LOUIS	AdventureWorks	ABSENT	2014-09-03	1	2
MARC	LOUIS	AdventureWorks	ABSENT	2014-09-04	2	2
MARC	LOUIS	AdventureWorks	ABSENT	2014-09-05	3	2
MARC	LOUIS	AdventureWorks	PRESENT	2014-09-06	1	3
MARC	LOUIS	AdventureWorks	ABSENT	2014-09-07	1	4
MARC	LOUIS	AdventureWorks	ABSENT	2014-09-08	2	4
MARC	LOUIS	AdventureWorks	ABSENT	2014-09-08	3	4

Avec la requête

Code :

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

1
2
select NOM,PRENOM,SOCIETE,STATUS,DATE,row_number() over (NOM,PRENOM,SOCIETE,STATUS,DATE order by DATE asc) 
from matable

j'ai la colonne row_number, mais pour avoir la colonne partition number...aucune idée....

il y a t-il une solution ??
Merci

SQL SERVER 2005 SP4.
WINDOWS SEVEN PRO 64

**SQLpro** · 07/10/2014, 09h55

D'abord votre clause OVER est fausse. Ensuite sur quel critère vous basez vous ? L'informatique c'est pas de la magie. C'est de la logique et des maths !

A ¨+

**aziatedu13** · 07/10/2014, 10h30

Merci pour votre réponse...même si je ne pense pas que l'informatique soit de la magie, mais beaucoup d'apprentissage, passant par des questions et des essais.

J'ai oublié le partition by

Code :

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

SELECT NOM,PRENOM,SOCIETE,STATUS,DATE,row_number() over (partition by NOM,PRENOM,SOCIETE,STATUS,DATE ORDER BY DATE ASC) FROM matable

Mes critères sont
NOM,PRENOM,SOCIETE,STATUS

Merci

**aieeeuuuuu** · 07/10/2014, 11h16

Envoyé par aziatedu13

j'ai la colonne row_number

En êtes vous sûr ? ses résultats sont corrects ?

**aziatedu13** · 07/10/2014, 11h36

Je me suis trompé en retapant trop rapidement ma requête, il n'y a pas la colonne DATE dans mon partition By, sinon j'aurai que des 1 pour la valeur de la colonne Row_number.

Code :

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

SELECT NOM,PRENOM,SOCIETE,STATUS,DATE,row_number() over (partition BY NOM,PRENOM,SOCIETE,STATUS ORDER BY DATE ASC) as row_number FROM matable

J'ai rajouté le

Code :

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

as row_number pour nommer la colonne

Merci.

**aieeeuuuuu** · 07/10/2014, 11h58

oui,

Cependant, il me semble que cela ne doit toujours pas vous renvoyer les résultats que vous présentez : le "compteur" n'est pas remise à zéro à chaque changement de statut...

**aziatedu13** · 07/10/2014, 12h12

C'est bizarre...J'ai bien ça pourtant avec les données du tableau en début de poste....
Que devrait il y avoir pour remettre à 0 le compteur ?

J'ai bien ça comme donnée....

**aieeeuuuuu** · 07/10/2014, 14h32

Non, vous devez avoir ceci :

MARC	LOUIS	AdventureWorks	PRESENT	2014-01-09 00:00:00.000	1
MARC	LOUIS	AdventureWorks	PRESENT	2014-02-09 00:00:00.000	2
MARC	LOUIS	AdventureWorks	ABSENT	2014-03-09 00:00:00.000	1
MARC	LOUIS	AdventureWorks	ABSENT 	2014-04-09 00:00:00.000	2
MARC	LOUIS	AdventureWorks	ABSENT 	2014-05-09 00:00:00.000	3
MARC	LOUIS	AdventureWorks	PRESENT	2014-06-09 00:00:00.000	3
MARC	LOUIS	AdventureWorks	ABSENT 	2014-07-09 00:00:00.000	4
MARC	LOUIS	AdventureWorks	ABSENT 	2014-08-09 00:00:00.000	5
MARC	LOUIS	AdventureWorks	ABSENT 	2014-08-09 00:00:00.000	6

**aziatedu13** · 07/10/2014, 23h22

Vous avez raison, j'ai fait beaucoup d'erreur, en voulant exprimer mon problème.

En fait le tableau initiale est ce que j'aimerai avoir.
Un compteur réinitialisé à chaque changement de status , et la colonne partition_number.
Et je ne sais pas comment l'avoir.

Merci.

**SQLpro** · 08/10/2014, 09h13

Essayez un :

Code :

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

DENSE_RANK() OVER(PARTITION BY NOM, PRENOM, SOCIETE ORDER BY STATUS DESC)

A +

**aieeeuuuuu** · 10/10/2014, 14h58

Essayez ça :

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
 
;WITH Tmp AS (
	SELECT 
		NOM,
		PRENOM,
		SOCIETE,
		STATUS,
		DATE,
		row_number() over (PARTITION BY NOM,PRENOM,SOCIETE ORDER BY DATE ASC) AS rn 
	FROM matable
),
ch AS (
	SELECT 
			NOM,
			PRENOM,
			SOCIETE,
			STATUS,
			DATE,
			CASE WHEN RN%2 = 1 
				THEN MAX(CASE WHEN RN%2 = 0 THEN Status END) OVER(PARTITION BY NOM,PRENOM,SOCIETE, rn/2)
				ELSE
				MAX(CASE WHEN RN%2 = 1 THEN Status END) OVER(PARTITION BY NOM,PRENOM,SOCIETE, (rn-1)/2)
			END AS StatutPrecedent
	FROM tmp
)
SELECT 
			A.NOM
			,A.PRENOM
			,A.SOCIETE
			,A.STATUS
			,A.DATE
			,ROW_NUMBER() OVER(
				PARTITION BY A.NOM,A.PRENOM,A.SOCIETE, COUNT(B.StatutPrecedent ) 
				ORDER BY A.DATE
			) AS RowNumber
			,COUNT(B.StatutPrecedent ) + 1 AS PartitionNumber
FROM ch A
LEFT JOIN ch B
	ON B.DATE <= A.Date
	AND B.StatutPrecedent <> B.Status
	AND B.Nom = A.Nom
	AND B.PRENOM = A.PRENOM
	AND B.SOCIETE = A.SOCIETE
GROUP BY 
			A.NOM,
			A.PRENOM,
			A.SOCIETE,
			A.STATUS,
			A.DATE
ORDER BY DATE

**Waldar** · 13/10/2014, 11h42

Le besoin n'étant pas trivial, je vous donne ma solution :

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
with sr1 (nom, prenom, societe, status, date, grp) as
(
select nom, prenom, societe, status, date
     , rank() over(partition by nom, prenom, societe         order by date asc)
     - rank() over(partition by nom, prenom, societe, status order by date asc)
  from matable
)
  ,  sr2 (nom, prenom, societe, status, date, grp, dt_min) as
(
select nom, prenom, societe, status, date
     , grp
     , min(date) over(partition by grp, status)
  from sr1
)
  select nom, prenom, societe, status, date
       , row_number() over(partition by grp, dt_min order by date asc) as rnum
       , dense_rank() over(order by grp asc, dt_min asc) as pnum
    from sr2
order by date asc, status;
 
 
NOM  PRENOM SOCIETE        STATUS  DT               RNUM       PNUM
---- ------ -------------- ------- ---------- ---------- ----------
MARC LOUIS  AdventureWorks PRESENT 2014-09-01          1          1 
MARC LOUIS  AdventureWorks PRESENT 2014-09-02          2          1 
MARC LOUIS  AdventureWorks ABSENT  2014-09-03          1          2 
MARC LOUIS  AdventureWorks ABSENT  2014-09-04          2          2 
MARC LOUIS  AdventureWorks ABSENT  2014-09-05          3          2 
MARC LOUIS  AdventureWorks PRESENT 2014-09-06          1          3 
MARC LOUIS  AdventureWorks ABSENT  2014-09-07          1          4 
MARC LOUIS  AdventureWorks ABSENT  2014-09-08          2          4 
MARC LOUIS  AdventureWorks ABSENT  2014-09-08          3          4

[SQLSERVER 2005] Fonction de fenetrage, et numéro de partition

Langage SQL

Discussions similaires

Partager

Partager