Discussion :

[BaronSamdi]

Bonjour à tous,

Je voudrais faire une requête en évitant de passer par des curseurs mais la je sèche un peu. J'aurais besoins d'un peu d'aide. Je travail sur une base tournant sous Sqlserver 2005 et 2008.

J'ai une table avec un numéro d'utilisateur et une date et heure. Je voudrais numéroter les lignes par utilisateur par date et surtout par bloc. La numérotation pour un utilisateur recommence à 1 après chaque bloc.

Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
N°user    dateHeure                     ResultatVoulu
9999	     20091116 020340           1
9999	     20091116 032926           2
1777	     20091116 055644           1
1777	     20091116 080406           2
1777	     20091116 093814           3
1777	     20091116 095940           4
1777	     20091116 102600           5
9999	     20091116 234758           1
9999	     20091116 235116           2
9999	     20091116 235444           3

En utilisant un RANK() la numérotation ne recommence pas à 1 après chaque bloc.

Est ce que quelqu'un aurait une astuce pour ce genre de problème.


Merci d'avance

[elsuket]

Bonjour,

Utilisez pour cela la clause PARTITION BY dans la clause OVER() de la fonction RANK()

@++

[BaronSamdi]

J'avais bien mis la clause partition mais la numérotation ne recommence pas à 1.

Voici ma requête :

Code :

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

1
2
3
4
5
	select 
	noutilisateur,dateheure,	
	RANK() over (partition by noutilisateur order by  dateheure)	
	from test
	order by dateheure

Voici le script de création de la table :

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
CREATE TABLE [dbo].[test](
	[noUtilisateur] [int] NULL,
	[dateHeure] [char](15) COLLATE French_CI_AS NULL
)
Go
 
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (9999, N'20091116 020340')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (9999, N'20091116 032926')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 055644')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 080406')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 093814')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 095940')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 102600')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 110828')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 130034')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 150840')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 155452')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 160814')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 161728')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 172826')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (1777, N'20091116 173524')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (9999, N'20091116 234758')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (9999, N'20091116 235116')
INSERT [dbo].[test] ([noUtilisateur], [dateHeure]) VALUES (9999, N'20091116 235444')

[SQLpro]

1) L'exemple que vous nous avez donné est faux, car vous ne nous précisez pas la notion de BLOC !
2) votre requête est fausse car elle ne prend pas en compte la colonne noutilisateur dans les clause ORDER BY.

Code :

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

1
2
3
4
SELECT noutilisateur,dateheure,	
	   ROW_NUMBER() over (partition BY noutilisateur ORDER BY  noutilisateur, dateheure) AS NUM	
FROM test
ORDER BY noutilisateur, dateheure

A +

[BaronSamdi]

L'exemple que j'ai transmis correspond au résultat attendu.
Je voudrais pouvoir recommencer la numérotation à chaque changement
d'utilisateur tout en conservant l'ordre chronologique.

Dans l'exemple j'ai 3 blocs :

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
bloc 1:
 
9999	     20091116 020340           
9999	     20091116 032926           
 
bloc 2:
 
1777	     20091116 055644           
1777	     20091116 080406           
1777	     20091116 093814           
1777	     20091116 095940           
1777	     20091116 102600           
 
bloc 3:
9999	     20091116 234758           
9999	     20091116 235116           
9999	     20091116 235444

C'est le 3ème bloc qui me pose problème. La numérotation se poursuit alors
que je voudrais qu'elle recommence à 1.

[SQLpro]

Vous venez donc de découvrir qu'il vous manque une information dans votre table, celle de bloc.
Un SGBDR ne peut pas inventer une information qu'il n'a pas.
De plus dans un SGBDR il n'existe pas d'ordre des lignes, car la théorie sur laquelle repose les SGBDR est la théorie des ensembles...
Lisez ceci pour vous en convaincre : http://blog.developpez.com/sqlpro/p5...sont-des-ense/

A +

[Waldar]

Vous manquez d'imagination SQLPro, il y a tout ce qu'il faut :

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 TEST2 (noUtilisateur, dateHeure, bloc) AS
(
select noUtilisateur, dateHeure,
       row_number() over(                           order by dateHeure asc) - 
       row_number() over(partition by noUtilisateur order by dateHeure asc)
  from TEST
)
  select noUtilisateur, dateHeure,
         row_number() over(partition by bloc order by dateHeure asc) as rn
    from TEST2
order by dateHeure asc;
 
noUtilisateur dateHeure       rn
------------- --------------- --------------------
9999          20091116 020340 1
9999          20091116 032926 2
1777          20091116 055644 1
1777          20091116 080406 2
1777          20091116 093814 3
1777          20091116 095940 4
1777          20091116 102600 5
1777          20091116 110828 6
1777          20091116 130034 7
1777          20091116 150840 8
1777          20091116 155452 9
1777          20091116 160814 10
1777          20091116 161728 11
1777          20091116 172826 12
1777          20091116 173524 13
9999          20091116 234758 1
9999          20091116 235116 2
9999          20091116 235444 3