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

Version imprimable

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:

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

07/10/2014, 09h55
SQLpro

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 ¨+
07/10/2014, 10h30
aziatedu13

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:

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
07/10/2014, 11h16
aieeeuuuuu

Citation:

Envoyé par aziatedu13

j'ai la colonne row_number

En êtes vous sûr ? ses résultats sont corrects ? :koi:
07/10/2014, 11h36
aziatedu13

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:

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:

as row_number pour nommer la colonne

Merci.
07/10/2014, 11h58
aieeeuuuuu

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...
07/10/2014, 12h12
aziatedu13

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....

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

07/10/2014, 23h22
aziatedu13

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.
08/10/2014, 09h13
SQLpro

Essayez un :

Code:

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

A +

Essayez ça :

Code:

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

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

Code:

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

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