Obtenir le Top X des liens visités

[darKStein]

Bonjour, c'est encore moi !

Dans ma table de statistiques, il m'est demandé de conserver pour chaque client les 4 liens les plus visités dans mes e-mails. J'ai toujours ma structure statistiques e-mails :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE TABLE [STAT_EMAIL] (
	[email] [varchar] (255),
	[type] [varchar] (10),
	[theme] [varchar] (50),
	[time] [smalldatetime]
) ON [PRIMARY]
GO
CREATE TABLE [SEGMENTATION] (
	[EMAIL] [varchar] (255),
	[ESEG0] [char] (2),
	[ESEG1] [smallint],
	[ESEG2] [smallint],
	[ESEG3] [smallint], -- TRANCHE HORAIRE D'OUVERTURE DES E-MAILS
	[ESEG4] [smallint], -- JOUR D'OUVERTURE DES E-MAILS
	[THEME1] [varchar] (255),
	[THEME2] [varchar] (255),
	[THEME3] [varchar] (255),
	[THEME4] [varchar] (255)
) ON [PRIMARY]
GO

Lorsque le [type] = 'Click', j'ai dans ma table le [theme] associé
Exemple :
EMAIL TYPE THEME TIME
----- ---- ----- -------------------
toto@ Click accueil 2010-12-11 19:30:00
toto@ Click promo 2010-12-12 12:01:00
toto@ Click promo 2010-12-12 12:01:00
toto@ Click faq 2010-12-12 12:02:00
titi@ Click accueil 2010-12-04 09:47:00
titi@ Click accueil 2010-12-19 10:19:00
titi@ Click promo 2010-11-27 15:29:00
titi@ Click faq 2010-11-27 17:25:00
titi@ Click promo 2010-12-12 18:12:00
titi@ Click contact 2010-11-28 15:25:00

Pour le moment à part mettre à jour les thèmes un à un (pour le premier, il me suffit de prendre le MAX, le suivant sera le MAX qui ne correspond pas au thème N°1, le troisième sera le MAX sans les thèmes 1 et 2, etc.), je ne vois pas trop comment faire la mise à jour de mes champs...

Alors bien sûr, on me dira qu'il n'est pas convenable de stockeur dans la table de statistiques 4 champs THEME1..4, mais le cahier des charges étant précis sur ce point - et au vue des statistiques actuelles, il y a rarement plus de 3 ou 4 thèmes différents dans nos e-mailings ; en outre je pense au requêtage futur sur cette table SEGMENTATION (un outil qui permettra de sélectionner les clients en fonction de leur préférence, etc.)

[elsuket]

Bonjour, c'est encore moi !

Bonjour ! décidément vous êtes abonnés aux "top"

Alors bien sûr, on me dira qu'il n'est pas convenable de stockeur dans la table de statistiques 4 champs THEME1..4, mais le cahier des charges étant précis sur ce point - et au vue des statistiques actuelles, il y a rarement plus de 3 ou 4 thèmes différents dans nos e-mailings ; en outre je pense au requêtage futur sur cette table SEGMENTATION (un outil qui permettra de sélectionner les clients en fonction de leur préférence, etc.)

Le cahier des charges est peut-être précis mais il est faux.
En terme d'évolutivité c'est nul, et d'interrogation de la table n'en parlons pas.
Mais bon comme visiblement certains de vos collègues font de l'abus d'autorité, on va passer ...

Voici une piste :

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
DECLARE	@STAT_EMAIL TABLE
(
	email varchar (255)
	, type varchar (10)
	, theme varchar (50)
	, time smalldatetime
)
 
INSERT @STAT_EMAIL VALUES ('toto@', 'Click', 'accueil', '2010-12-11 19:30:00')
, ('toto@', 'Click', 'promo', '2010-12-12 12:01:00')
, ('toto@', 'Click', 'promo', '2010-12-12 12:01:00')
, ('toto@', 'Click', 'faq', '2010-12-12 12:02:00')
, ('titi@', 'Click', 'accueil', '2010-12-04 09:47:00')
, ('titi@', 'Click', 'accueil', '2010-12-19 10:19:00')
, ('titi@', 'Click', 'promo', '2010-11-27 15:29:00')
, ('titi@', 'Click', 'faq', '2010-11-27 17:25:00')
, ('titi@', 'Click', 'promo', '2010-12-12 18:12:00')
, ('titi@', 'Click', 'contact', '2010-11-28 15:25:00')
 
;WITH
	CTE AS
	(
		SELECT	email
			, theme
			, RANK() OVER(PARTITION BY email ORDER BY occurences DESC) AS rang
		FROM	(
				SELECT	email
					, theme
					, COUNT(*) AS occurences
				FROM	@STAT_EMAIL
				GROUP	BY email, theme
			) AS S
	)
SELECT	email
	, theme
	, rang
FROM	CTE
WHERE	rang <= 4
ORDER	BY email, rang

Vous remarquerez que les ex-æquo ont un rang identique, mais que le rang qui suit le rang d'ex-æquo est sauté.

Donc pour le code qui vous permet de faire cela en une seule requête, ça va pas être de la tarte, étant donné que vous ne pouvez pas déterminer à l'avance la liste des themes qui seront retournés par cette requête.
Cela aurait pu nous mener à faire un UNPIVOT (que je déteste).

Si la table était au départ correctement implémentée, alors la requête que je vous ai donné fait le travail tranquillement.

@++

[darKStein]

Apparemment RANK() n'est pas reconnue sous SQL Server 2000 (j'ai oublié de le préciser) ?

[elsuket]

Héhé oui

Sous cette version, ni l'expression de table commune, ni les fonctions de fenêtrage, ni les opérateurs PIVOT et UNPIVOT n'existent.

Laissez-moi revenir vers vous avec une requête qui fonctionnera pour SQL Server 2000.

@++

[darKStein]

J'adore ce forum, on est servis comme des princes !

[darKStein]

Sain-by-noo,

Je reviens vers vous pour m'aider dans la conversion de ce "bouh qu'il est vilain" curseur en belle requête assertionnelle toute propre :

Je résume : je parcours ma table qui m'indique par client les liens visités et le nombre de fois où ils ont été visités, puis je veux obtenir le top 4 de mes liens :

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
51
52
53
54
55
56
57
58
59
60
 
DECLARE	cTheme CURSOR FOR
	SELECT	LV_EMAIL, THEMENAME
	FROM	V_ESEG_THEME (NOLOCK)
	ORDER	BY LV_EMAIL, NB DESC, THEMENAME
 
SET	@lv_email_anc = ''
 
OPEN	cTheme
WHILE	(1 = 1)
BEGIN
	FETCH	NEXT
	FROM	cTheme
	INTO	@lv_email, @lv_theme
 
	IF	(@@fetch_status <> 0)	BREAK
---
-- NOUVEAU CLIENT : ON MET LE PRECEDENT A JOUR
---
	IF	(@lv_email <> @lv_email_anc)
	BEGIN
		IF	(@lv_email <> '')
			UPDATE	SEGMENTATION
			SET	THEME1 = @lv_theme1,
				THEME2 = @lv_theme2,
				THEME3 = @lv_theme3,
				THEME4 = @lv_theme4
			WHERE	LV_EMAIL = @lv_email_anc
 
		SET	@lv_theme1 = ''
		SET	@lv_theme2 = ''
		SET	@lv_theme3 = ''
		SET	@lv_theme4 = ''	
	END
 
	IF	(@lv_theme1 = '')
		SET	@lv_theme1 = @lv_theme
	ELSE
	BEGIN
		IF	(@lv_theme2 = '')
			SET	@lv_theme2 = @lv_theme
		ELSE
		BEGIN
			IF	(@lv_theme3 = '')
				SET	@lv_theme3 = @lv_theme
			ELSE
			BEGIN
				IF	(@lv_theme4 = '')
					SET	@lv_theme4 = @lv_theme
			END
		END
	END
 
	SET	@lv_email_anc = @lv_email
END
CLOSE	cTheme
DEALLOCATE	cTheme
---
-- MISE A JOUR DU DERNIER ENREGISTREMENT
---

(...Et en plus c'est long !)
Je suis toujours sous SQL SERVER 2000

[elsuket]

Désolé de vous avoir oublié.
En fait j'avais écrit la requête suivante, qui fonctionne :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT	S1.email
	, S1.theme
	, S1.occurences
	, (
		SELECT	COUNT(DISTINCT occurences)
		FROM	(
				SELECT	email
					, theme
					, COUNT(*) AS occurences
				FROM	@STAT_EMAIL
				GROUP	BY email, theme
			) AS S2
		WHERE	S1.occurences <= S2.occurences	
	) AS rang
FROM	(
		SELECT	email
			, theme
			, COUNT(*) AS occurences
		FROM	@STAT_EMAIL
		GROUP	BY email, theme
	) AS S1
ORDER	BY email, rang

Mais j'avais trouvé ça un peu gore / lourd et j'avais pensé qu'on peut trouver plus simple.
Si j'y parviens je vous fais signe.

@++

[darKStein]

Il ne manquerait pas dans le S2 une jointure sur l'e-mail ?

En tous cas merci du décorticage de cerveau, tout ça pour une tâche hebdomadaire (en même temps qu'elle tourne 1 h ou 4 h, l'important c'est que les résultats soient juste, hein, un dimanche, ça n'embête personne ! )

La solution par curseur telle qu'appliquée met 02:50 à tourner. Je vais voir comment appliquer votre solution et les performances obtenues.

[elsuket]

(en même temps qu'elle tourne 1 h ou 4 h, l'important c'est que les résultats soient juste, hein, un dimanche, ça n'embête personne !

Cela ne sera peut-être pas le cas tout au long de votre carrière...
Ce n'est par exemple pas acceptable pour un hôpital ou une banque, ...

Si j'ai une requête de ce type qui prend 1h, je vais regarder comment réduire son temps d'exécution, parce que pendant qu'elle lit les données, elle verrouille des lignes, pages, ... et consomme des ressources qui pourraient être allouées à d'autres traitements.

Désolé mais je n'ai jamais aimé le "bwo on s'en fout ça se voit pas"

Il ne manquerait pas dans le S2 une jointure sur l'e-mail ?

A priori je ne pense pas, j'ai testé la requête avec la variable de type TABLE que j'ai donné dans une réponse plus haut.

La solution la plus rapide pour votre problème, et je crois que cela vous a déjà été proposé, c'est de créer une vue indexée dont la définition est la sous-requête qui définit S1 dans la dernière requête que je vous ai donné.
Cela simplifierait la requête tout en la rendant extrêmement performante

En revanche le coût de maintenance d'une vue indexée n'est pas à négliger si votre table STATS_EMAIL subit de nombreuses modifications ...

@++

[darKStein]

...Par contre je ne vois pas trop comment incorporer cette requête dans un ordre de SELECT qui me permettrait au final d'obtenir par rotation les colonnes EMAIL, THEME1, THEME2, THEME3, THEME4...

[darKStein]

Envoyé par elsuket

Cela ne sera peut-être pas le cas tout au long de votre carrière...
Ce n'est par exemple pas acceptable pour un hôpital ou une banque, ...

Si j'ai une requête de ce type qui prend 1h, je vais regarder comment réduire son temps d'exécution, parce que pendant qu'elle lit les données, elle verrouille des lignes, pages, ... et consomme des ressources qui pourraient être allouées à d'autres traitements.

Désolé mais je n'ai jamais aimé le "bwo on s'en fout ça se voit pas"


A priori je ne pense pas, j'ai testé la requête avec la variable de type TABLE que j'ai donné dans une réponse plus haut.

La solution la plus rapide pour votre problème, et je crois que cela vous a déjà été proposé, c'est de créer une vue indexée dont la définition est la sous-requête qui définit S1 dans la dernière requête que je vous ai donné.
Cela simplifierait la requête tout en la rendant extrêmement performante

En revanche le coût de maintenance d'une vue indexée n'est pas à négliger si votre table STATS_EMAIL subit de nombreuses modifications ...

@++

Moi non plus ça ne me plaît pas de laisser les choses en l'état, mais c'est toujours pareil, les priorités ne sont jamais à l'optimisation de ce genre de traitement...
Quant à la vue indexée, apparemment elle nécessite un paramétrage particulier au niveau du serveur ? J'en avais touché un mot à mon DBA, je verrai ce qu'il est possible d'obtenir...
J'ai effectivement créé une vue (ne serait-ce que pour la lisibilité)
Quant à la table de données STATS_EMAIL, elle devrait être alimentée au mieux une fois par jour, au pire une fois par semaine.

[elsuket]

les priorités ne sont jamais à l'optimisation de ce genre de traitement...

Malheureusement ... jusqu'au jour où c'est la catastrophe, alors là on prend des mesures drastiques, tout est sur-contrôlé, ... bref !
En revanche ce que je comprend moins, c'est que votre DBA laisse faire cela, parce que c'est sa responsabilité.

Quant à la vue indexée, apparemment elle nécessite un paramétrage particulier au niveau du serveur ?

Non, c'est juste que sa maintenance a un coût. Mais comme :

elle devrait être alimentée au mieux une fois par jour, au pire une fois par semaine.

Cela ne devrait poser aucun problème

...Par contre je ne vois pas trop comment incorporer cette requête dans un ordre de SELECT qui me permettrait au final d'obtenir par rotation les colonnes EMAIL, THEME1, THEME2, THEME3, THEME4...

Qu'est-ce que je vous avais dit plus haut ?

Envoyé par darKStein

Alors bien sûr, on me dira qu'il n'est pas convenable de stockeur dans la table de statistiques 4 champs THEME1..4, mais le cahier des charges étant précis sur ce point - et au vue des statistiques actuelles, il y a rarement plus de 3 ou 4 thèmes différents dans nos e-mailings ; en outre je pense au requêtage futur sur cette table SEGMENTATION (un outil qui permettra de sélectionner les clients en fonction de leur préférence, etc.)

.

Faire cela c'est de la présentation, ce pour quoi ne sont conçus ni le langage SQL, ni par voie de conséquence T-SQL et SQL Server.
SQL est conçu pour manipuler et interroger des données, tout en garantissant leur intégrité et la plus grande vitesse d'accès possible à celles-ci.
Rien de plus, rien de moins ... et c'est déjà énorme !

La seule façon que je vois, c'est de faire une jointure sur l'e-mail ... et faire une jointure sur des chaînes de caractère, c'est méga-moche !

[darKStein]

Par chez nous on en a usé quelques-uns des DBA, avec chacun ses habitudes et surtout un historique lourd sur la DB, du coup il y a je pense une certaine "lassitude" qui s'installe à se dire que le travail de restructuration serait énorme, voire hénaurme, et surtout qu'au-dessus de tout cela il y a les applicatifs qui eux sont sous ma responsabilité, mais ont aussi du vécu...

Et puis moi je ne demande que ça un DBA qui nous tape sur les doigts et nous impose les rêgles de l'art que, pauvre de moi, je ne connais pas ; mais ô combien j'ai soif d'apprendre !

Merci de votre approche !

[darKStein]

Envoyé par elsuket

La seule façon que je vois, c'est de faire une jointure sur l'e-mail ... et faire une jointure sur des chaînes de caractère, c'est méga-moche !

En quoi une jointure sur chaine de caractères est "moche" ? si cette chaîne est structurée et pas une zone type commentaire ? Car, au final, un e-mail peut être considéré comme un identifiant, non ?

[iberserk]

C'est surtout moche d'un point de vue performance (cout en requêtage, volume important car propagé dans les table en FK. etC.) comparé a un identifiant purement technique comme un autoIncrement...

[darKStein]

Malheureusement dans mon cas présent, le choix est mince car les données de STAT_EMAIL proviennent d'un prestataire (fichier texte importé périodiquement) qui nous fournit des statistiques sur le comportement de notre clientèle e-mailing (donc l'e-mail est une clef). Ces données doivent ensuite être intégrées à un simili-datawarehouse dont, ici, la clef est un identifiant varchar, certes, mais de type barcode "123456123456". Pourquoi varchar ? Car les zéros non significatifs doivent apparaître ("012345012345").

Voici le pourquoi du comment. Et c'est pas facile tous les jours...
Mais bon pour le coup j'ai demandé à investir dans de la documentation de fonds et j'ai fait acheter le livre de référence de M.Brouard

[elsuket]

Car, au final, un e-mail peut être considéré comme un identifiant, non ?

Et si demain je change de mail, on fait comment ?
On met toutes les tables qui référencent celle-ci, ou on met simplement à jour la valeur parce que la clé primaire de la table est un entier ?

Malheureusement dans mon cas présent, le choix est mince car les données de STAT_EMAIL proviennent d'un prestataire (fichier texte importé périodiquement) qui nous fournit des statistiques sur le comportement de notre clientèle e-mailing (donc l'e-mail est une clef)

Rien ne vous empêche de générer une clé technique, et de mettre une contrainte d'unicité sur l'adresse e-mail.
Dans ce cas la clé technique est dite subrogée.

Ces données doivent ensuite être intégrées à un simili-datawarehouse dont, ici, la clef est un identifiant varchar, certes, mais de type barcode "123456123456". Pourquoi varchar ? Car les zéros non significatifs doivent apparaître ("012345012345").

Même réponse : une clé primaire technique de type entier, et une contrainte d'unicité sur le code barre.

Voici le pourquoi du comment. Et c'est pas facile tous les jours...

Héhé oui !

et j'ai fait acheter le livre de référence de M.Brouard

C'est un bon choix !

@++

[darKStein]

Envoyé par elsuket

Et si demain je change de mail, on fait comment ?
On met toutes les tables qui référencent celle-ci, ou on met simplement à jour la valeur parce que la clé primaire de la table est un entier ?

Eh bien l'enregistrement avec l'ancien e-mail sera flaggé "annulé" et le nouveau sera activé. Ainsi on conserve l'historique d'appartenance de l'e-mail à une personne physique ainsi que les contacts effectués sur cet e-mail. Mais je prends bonne note de vos remarques !

[iberserk]

Rien ne vous empêche de générer une clé technique, et de mettre une contrainte d'unicité sur l'adresse e-mail.
Dans ce cas la clé technique est dite subrogée.

C'est dailleurs le principe de la modélisation des customers/membership des sites ASP.NET prévu par MS...

[elsuket]

Eh bien l'enregistrement avec l'ancien e-mail sera flaggé "annulé" et le nouveau sera activé. Ainsi on conserve l'historique d'appartenance de l'e-mail à une personne physique ainsi que les contacts effectués sur cet e-mail. Mais je prends bonne note de vos remarques !

Dans ce cas faites plutôt une table séparée avec l'historique

@++