Discussion :

[informer]

Bonjour à tous,

Voici une requête
[CODE=SQL]

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
 SELECT b.pays,b.marque, b.hotel, b.date,b.season,b.day, b.TIME, b.room,
NBresatot  , NBresatotOther 
INTO TauxOccupation
FROM 
(	select  pays, marque,hotel,date,season,day,TIME,room,
		cast((	select count(*)
			from
				reservation
			where 
				TIME>=a.TIME 
				and hotel=a.hotel 
				and date=a.date
		) as  decimal(18,6)) as NBresatot
from reservation a
group by 
pays, marque,hotel,date,season,day,TIME,room) b
left join
(select  pays, marque,hotel,date,season,day,TIME,room,
		cast((	select count(*)
			from
				reservation
			where 
				TIME>=a.TIME 
				and hotel=a.hotel 
				and date=a.date
				and channel_group = 'Other'
		) as decimal(18,6)) as NBresatotOther 
from reservation a
where channel_group = 'Other '
group by 
pays, marque,hotel,date,season,day,TIME,room) c
	on b.hotel = c.hotel
	and b.date = c.date
	and b.time = c.time

que je voudrais transformer en quelque chose de similaire à ça i.e en supprimant la jointure left join. Je pré-sens que c'est possible mais ça bug dans ma tête :


[CODE=SQL]

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
 
SELECT  a.pays, a.marque, a.hotel, a.date, a.season, a.day,a.TIME,a.room,
(select count(*) 
			from
				reservation
			where 
				TIME>=a.TIME 
				and hotel=a.hotel 
				and date=a.date
		)  NbrResaTotal,
(SELECT count(*) as NBresatotOlta , c.pays, c.marque, c.hotel, c.date, c.season, c.day, c.TIME, c.room
	FROM 
		(SELECT b.pays, b.marque, b.hotel, b.date, b.season, b.day, b.TIME, b.room
			from reservation b 
			where channel_group = 'Other'
			group by   b.pays, b.marque, b.hotel, b.date, b.season, b.day, b.TIME, b.room
		) c
		where 
			c.hotel= a.hotel 
			and c.date= a.date
			and c.TIME>= a.TIME 
		group by c.pays, c.marque, c.hotel, c.date, c.season, c.day, c.TIME, c.room			
) as NBresatotOther 
into Ratio
FROM reservation a	
group by 
 a.pays, a.marque, a.hotel, a.date, a.season, a.day,a.TIME, a.room

Merci pour votre aide

[aieeeuuuuu]

Bonjour,

Je pense qu'il est effectivement possible de simplifier la requête avec quelque chose comme ceci par exemple, à adapter (d'où sort la colonne time_lead dans votre requete ?)

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
 
	SELECT  
				r1.pays
			,	r1.marque
			,	r1.hotel
			,	r1.date
			,	r1.season
			,	r1.day
			,	r1.TIME
			,	r1.room
			,	CAST(COUNT(*) AS  decimal(18,6)) AS NBresatot
			,	CAST(
					COUNT(CASE WHEN r2.channel_group = 'Other' THEN 1 END)
					AS  decimal(18,6)
				) AS NBresatot
	INTO TauxOccupation
	FROM reservation r1
	INNER JOIN reservation r2
		ON	r2.hotel=r1.hotel 
		AND r2.date=r1.date
		AND	r2.time > r1.time
 
	GROUP BY 
			r1.pays
			,	r1.marque
			,	r1.hotel
			,	r1.date
			,	r1.season
			,	r1.day
			,	r1.TIME
			,	r1.room

[informer]

Bonjour aieeeuuuuu,

Merci pour ton aide et ta sagacité car en effet il faut remplacer lead_time par time (Voir le script modifié)

Mon problème est de calculer le NbReservationOther sur un échantillon regroupé sur 'OTHER'
et ensuite associé les enregistrements regroupés sur OTHER aux seuls enregistrements de l'échantillon reservation qui ont le même

• hotel
• date
• time

d'où la jointure

Encore merci pour ton aide et vais tester ta solution de suite sauf si tu venais à m dire que le lead_time transformé en time impacte ta solution

[informer]

Re salut aieeeuuuuu

Merci encore pour ta solution que je trouve vraiment élégante 2 problèmes, elle

1. Est extrêment chronophage (15' contre 36" avec l'originale c'est sûrement dû au CASE chronophage )
2. Ne donne pas le même nombre d'enregistrements avec un NrResaTotalOhter <> 0 qui est de 281614 alors que doit être de 96769

Encore merci pour ton aide mais l'utilisation du case est vraiment à bannir
Bonne journée

[StringBuilder]

J'ai une question à deux balles...

Elle fait quoi au juste la requête ?

Car j'ai l'intime conviction qu'on peut faire BEAUCOUP plus simple.

Code sql :

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

1
2
3
select pays, marque, hotel, date, season, day, time, room, count(*) over (partition by pays, pays, marque, hotel, date, season order by day desc, time desc)
from reservation
order by pays, marque, hotel, date, season, day, time, room;

=> Il me manque la dernière colonne, mais c'est clairement déjà bien plus lisible, même si je ne suis pas du tout certain de mon critère de segmentation...

[informer]

Bonjour StringBuilder

Merci de te pencher aussi sur ce problème

Sachant que la clé primaire de reservation est par hotel, date, TIME

L'objectif est d'avoir pour chaque enregistrement qui ont un channel = Other, le nombre d'enregistrements qui ont le même tuple (hotel, date, channel = Other) ! Mouais, j'ai peur d'être le seul à comprendre

Le truc serait que les enregistrements de la partition correspondent à tous les enregistrements qui ont le même

1. hotel
2. date

ce qui ferait je pense
[CODE]

Code SQL :

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
SELECT  
	r1.pays
	,r1.marque
	,r1.hotel
	,r1.date
	,r1.season
	,r1.stay_day
	,r1.TIME
	,r1.nb_room
	,CAST(COUNT(*) AS  decimal(18,6)) AS NBresatot
	, COUNT (*) OVER (PARTITION BY hotel, date,  channel )	
INTO TauxOccupation 
FROM reservation r1
 GROUP BY 
	r1.pays
	,r1.marque
	,r1.hotel
	,r1.date
	,r1.season
	,r1.stay_day
	,r1.TIME
	,r1.nb_room

Mais là où je pense que ça coince c'est que le COUNT comptabilise les enregistrements de la partition qui ont
channel = Other ==> d'où mon rajout de channel dans la partition !

Possible de faire un CASE WHEN qui comptabilise seulement les enregistrements avec channel = other ( aieeeuuuuu es-tu là ?) genre ben je sais pas si c'est possible

Code :

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

COUNT (CASE WHEN channel = 'OLTA' THEN 1 ELSE 0 END) OVER (PARTITION BY hotel, date,  channel_group) as 	NBresatotOlta

J'ai essayé mais comme channel n'est pas dans le group by pas bon du tout

Je sais pas si mon explication garde de la cohérence surtout qu'il est maintenant tard

Quoiqu'il en soit encore merci

[aieeeuuuuu]

Sachant que la clé primaire de réservation est par hotel, date, TIME

curieux ! des hotels avec une seule chambre ?

Blague a part, du coup, quel est l’intérêt du group by puisqu'il n'y a rien a regrouper (une seule ligne à chaque fois, puisque le group by inclus la clef primaire) ?

Quelque chose m'échappe, un jeu de test + résultat attendu aiderait à bien comprendre ce que vous cherchez à faire...

[Kropernic]

Ce serait éventuellement pas mal d'avoir le DDL de la table [reservation] aussi.

[informer]

Bonjour à vous 3, aieeeuuuuu, StringBuilder, Kropernic et merci pour le temps que vous avez consacré à ma question.

Mais comme le demande Kropernic je dois compléter le contexte de ma question, que je ferai plus tard car j'ai un autre problème prioritaire que j'aimerai soumettre à votre sagacité.

Soit une table avec les champs suivants

1. pays
2. ,marque
3. ,hotel
4. ,saison
5. ,jourSemaine
6. ,IndicTemps
7. ,Taux

Et la clé sur

1. ,hotel
2. ,saison
3. ,jourSemaine
4. ,IndicTemps

Pour chaque enregistrement de cette table qu'on nomme t1, je dois trouver le taux de l'enregistrement ayant une correspondance toujours dans la même table t1 mais qu'on nomme t2 sur:
,hotel
,saison
,jourSemaine

1er champ ==> avec t1.L'IndicTemps = t2.L'IndicTemps-1 ==> 1er champ
2ème champ ==> avec IndicTemp = 0
3ème champ ==> 'IndicTemps = 6



Donc à la fin, je dois avoir 3 colonnes supplémentaires soit

1. ,hotel
2. ,saison
3. ,jourSemaine
4. ,IndicTemps
5. ,IndicTemps-1
6. ,IndicTemps0
7. ,IndicTemps

Je pensais passer par CASE WHEN mais je sais pas comment faire, l'objectif étant de ne pas passer par 3 jointures si c'est possible?


Encore merci pour votre aide

[aieeeuuuuu]

Vous cherchez le nombre d’occurrences dans la partition définie ?

Code :

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

1
2
3
4
 
COUNT(CASE WHEN MIN(IndicTemps) OVER (PARTITION BY hotel,saison,jourSemaine) = 0 THEN 1 END  AS IndicTemps0
,COUNT(CASE WHEN MIN(IndicTemps) OVER (PARTITION BY hotel,saison,jourSemaine) = 6 THEN 1 END  AS IndicTemps6
,COUNT(CASE WHEN MIN(IndicTemps) OVER (PARTITION BY hotel,saison,jourSemaine) = IndicTemps - 1 THEN 1 END  AS IndicTempsMoins1

Mais je suis loin d'être sûr d'avoir compris ce que vous vouliez.
Là encore : un court (pas trop quand même !) jeu d'essai + résultat attendu valent mieux qu'un long discours

[informer]

Bonsoir aieeeuuuuu

Encore merci pour ton aide (allez on se tutoie entre membre du monde sans frontière de l'informatique)

Bon comme l'utilisation de la partition (pagination) est nouvelle pour moi, je voulais savoir si je pouvais récupérer dans une partition la valeur d'un champ de l'occurrence de la partition en fonction de la valeur d'un autre champ

Tu as raison un exemple est toujours plus éloquent

pays marque hotel saison jourSemaine IndicTemps Taux
France marque h1 moyenne lundi 0 0,025
France marque h1 moyenne lundi 1 0,2154
France marque h1 moyenne lundi 2 0,8986
France marque h1 moyenne lundi 3 0,974
France marque h1 moyenne lundi 4 0,15456
France marque h1 moyenne lundi 5 0,6896
France marque h1 moyenne lundi 6 0,5884


pays marque hotel saison jourSemaine IndicTemps Taux taux-1 taux0 taux6
France marque h1 moyenne lundi 0 0,025 Null 0,025 0,5884
France marque h1 moyenne lundi 1 0,2154 0,025 0,025 0,5884
France marque h1 moyenne lundi 2 0,8986 0,2154 0,025 0,5884
France marque h1 moyenne lundi 3 0,974 0,8986 0,025 0,5884
France marque h1 moyenne lundi 4 0,15456 0,974 0,025 0,5884
France marque h1 moyenne lundi 5 0,6896 0,15456 0,025 0,5884
France marque h1 moyenne lundi 6 0,5884 0,6896 0,025 0,5884

Je voyais un truc comme ça

[CODE]

Code SQL :

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

CASE WHEN   IndicTemps= 0 OVER (PARTITION BY ,hotel,saison,jourSemaine ,IndicTemps) = Taux 	THEN Null END  AS Taux0

Encore une fois, ne maniant que depuis peu le partitionnement, je ne sais pas si c'est possible

Merci encore

[informer]

Je fais du reverse SQL coding et ça me fait des nœuds dans la tête. La solution au defi balaise 2 est

[CODE]

Code SQL :

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

1
2
3
4
5
6
7
8
select  * 
,(select Taux  from table b where a.hotel = b.hotel and a.saison = b.saison and a.JourSemaine =b. JourSemaine and a.indicTemps = b.indicTemps -1 ))
 as TauxMinus1
,(select Taux  from table b where a.hotel = b.hotel and a.saison = b.saison and a.JourSemaine =b. JourSemaine and b.indicTemps = 0 ))
 as TauxMinus1
,(select Taux  from table b where a.hotel = b.hotel and a.saison = b.saison and a.JourSemaine =b. JourSemaine and b.indicTemps = 6 ))
 as TauxMinus1
from table a

Mais suis toujours preneur si possible avec CASE WHEN OVER PARTITION juste si c'est possible pour la beauté du truc.

Reste le défi balaise 1 mais j'y reviendrai avec plus de précision plus tard.

Encore merci à tous

[aieeeuuuuu]

C'est donc pas de loin de la solution que j'avais proposé, mais je pensais qu'il s'agissait de compter les occurences, et non de récupérer la valeur.

Pour les cas indicTemps = 0 et indicTemps = 6, ce n'est effectivement pas compliqué.
Pour obtenir le taux précédent, ce serait simple à partir de SQL Server 2012, en utilisant la fonction analytique LAGCelle-ci n'étant pas implémentée en 2008, on peut la simuler comme ceci :

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
 
	SELECT 
			hotel
		,	saison
		,	jourSemaine
		,	indicTemps
		,	Taux
		,	CASE 
				WHEN indicTemps%2 = 1
					THEN MAX(CASE WHEN indicTemps%2 = 0 THEN Taux END) OVER(PARTITION BY hotel, saison, jourSemaine,  indicTemps / 2) 
					ELSE MAX(CASE WHEN indicTemps%2 = 1 THEN Taux END) OVER(PARTITION BY hotel, saison, jourSemaine, (indicTemps + 1) / 2) 
			END as TauxPrecedent
		,	MAX(CASE WHEN indicTemps = 0 THEN Taux END) OVER(PARTITION BY hotel, saison, jourSemaine) AS Taux0
		,	MAX(CASE WHEN indicTemps = 6 THEN Taux END) OVER(PARTITION BY hotel, saison, jourSemaine) AS Taux6
 
	FROM a

[informer]

Salut aieeeuuuuu

Je le répète, je suis bluffé d'autant plus que j'apprends des truc comme la commande LAG dont je regarderai le comportement sur les forum.

Par contre pourrais-tu m'expliquer le coup du modulo (%) ton émulation magique et le transformer pour que t2.IndicTemps = t1.indicTemp+1 c'est à dire que je voudrai le taux suivant au taux de l'enregistrement courant car ton exemple donne le taux antérieur => pour IndicTemps = 0 => Null alors que veux 0,2154

Ton émulation, est-elle possible quelque soit le type de champ, bizarre comme question mais comme ne comprends pas ton émulation magique ça donne ça comme question?

Super, c'est vraiment très fort, encore bravo

[aieeeuuuuu]

Par contre pourrais-tu m'expliquer le coup du modulo (%)

Lorsqu'on divise un INT, par un INT, le résultat est un INT, tronqué.
Ainsi, 2/2 = 1 (jusque là, tout va bien !), mais également : 3/2 = 1 !
D'où le résultat suivant qui défie toutes les lois mathématiques : 3/2 = 2/2

Partant de là, si on divise par 2 les éléments d'une suite arithmétique de raison 1 (ici : 0,1,2,3,4,5,6), le résultat permet de regrouper ces éléments par deux :
-> un nombre pair obtiendra le même résultat que le nombre impair suivant (et forcément, un nombre impair donne le même résultat que le nombre pair précédent)

Si on ajoute 1 à l'élément avant de diviser par, on obtient l'inverse :
-> un nombre pair obtiendra le même résultat que le nombre impair précédent(et forcément, un nombre impair donne le même résultat que le nombre pair suivant)

Code :

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

1
2
3
4
5
6
 
WITH NOMBRE AS (
	SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5)) T(n)
	)
SELECT  n, n/2 as [n/2], (n+1) / 2 as [(n+1) / 2]
FROM  Nombre

n	n/2	(n+1) / 2
0	0	0
1	0	1
2	1	1
3	1	2
4	2	2
5	2	3

Du coup, en partitionnant sur :

• (n/2) si n est impair
• ((n+1)/2 si n est pair

La partition contient deux éléments : n et n - 1

Pour obtenir la valeur de taux pour la ligne précédente, il faut donc filtrer la ligne en cours de la partition. C'est l'objet du MAX, où l'on ne garde la valeur de taux que si la parité en l'inverse de celle de la ligne courante, NULL sinon (qui sera donc éliminée par la fonction d'agrégation).

je voudrai le taux suivant au taux de l'enregistrement courant car ton exemple donne le taux antérieur

Donc, pour obtenir le suivant et non le précédent, il suffit d'inverser les tests sur la parité :

Code :

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

1
2
3
4
5
6
 
		,	CASE 
				WHEN indicTemps%2 = 0
					THEN MAX(CASE WHEN indicTemps%2 = 1 THEN Taux END) OVER(PARTITION BY hotel, saison, jourSemaine,  indicTemps / 2) 
					ELSE MAX(CASE WHEN indicTemps%2 = 0 THEN Taux END) OVER(PARTITION BY hotel, saison, jourSemaine, (indicTemps + 1) / 2) 
			END as TauxSuivant

où d'inverser les conditions de partitionnement :

Code :

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

1
2
3
4
5
6
 
,	CASE 
				WHEN indicTemps%2 = 1
					THEN MAX(CASE WHEN indicTemps%2 = 0 THEN Taux END) OVER(PARTITION BY hotel, saison, jourSemaine,  (indicTemps + 1) / 2) 
					ELSE MAX(CASE WHEN indicTemps%2 = 1 THEN Taux END) OVER(PARTITION BY hotel, saison, jourSemaine, indicTemps / 2) 
			END as TauxSuivant

Ton émulation, est-elle possible quelque soit le type de champ

Directement non, mais indirectement, oui : il faut une étape supplémentaire attribuant à chaque ligne un rang, afin d'obtenir la fameuse suite. typiquement, une CTE avec un ROW_NUMBER()

[informer]

aieeeuuuuu,

Excellente démonstration, super didactique !!

Par contre dans ma table l'IndicTemps est un mumérique (18,0) et j'ai l'impression que ça fout la grouille sur le t2.Indictemps = t1.IndicTemps + 1 parce qu'à part les 2 premières itérations, tout le reste est NULL, qui ne se produit pas pour un jeu de données en varchar, une explication?

pays	marque	hotel	saison	jourSemaine	IndicTemps	Taux	TauxPrecedent	Taux0	Taux6
pays1	marque1	1	0.0	Vendredi	0	0.732828	NULL	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	1	0.695336	0.732828	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	2	0.671077	0.695336	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	3	0.632639	NULL	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	4	0.614051	NULL	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	5	0.590421	NULL	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	6	0.579394	NULL	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	7	0.572463	NULL	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	8	0.540957	NULL	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	9	0.517958	NULL	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	10	0.489287	NULL	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	11	0.474479	NULL	0.732828	0.579394
pays1	marque1	1	0.0	Vendredi	12	0.463452	NULL	0.732828	0.579394

Bon si tu as encore la patience tu pourrais me faire le code avec ROW_NUMBER() ?

Par contre pour les 2 autres taux0 et taux 6 c'est impeccable

Merci encore pour ton aide

[aieeeuuuuu]

aieeeuuuuu,

Excellente démonstration, super didactique !!

Par contre dans ma table l'IndicTemps est un mumérique (18,0)

... ce qui casse tout le postulat de départ :

Lorsqu'on divise un INT, par un INT, le résultat est un INT, tronqué.
Ainsi, 2/2 = 1 (jusque là, tout va bien !), mais également : 3/2 = 1 !
D'où le résultat suivant qui défie toutes les lois mathématiques : 3/2 = 2/2

Partant de là, ...

Car avec un type numérique, 3/2 ça fait... 1.5 !

En transtypant en INT, ça devrait suffire

[informer]

aieeeuuuuu

Merci pour ta réponse

Tu veux dire faire en faisant un
[CODE]

Code SQL :

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

cast(indicTemps as int)

J'ai essayé mais toujours le problème du NULL

[aieeeuuuuu]

Tu veux dire faire en faisant un
[CODE]

Code SQL :

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

cast(indicTemps as int)

Oui, mais il faut le mettre partout.
Et caster avant les opérations. Donc pas CAST(indicTemps /2 AS INT)mais CAST(indicTemps AS INT)/2
vu que indcTemps est utilisé a de nombreuses reprise, il sera surement plus pratique et plus lisible de commencer par une CTE qui va caster indicTemps une bonne fois pour toutes

[informer]

Bonjour aieeeuuuuu

Mon objectif est d'avoir léquivalent de group by avec les partition by:

[CODE]

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
select hote_id
	,dateSejour
	,NbrJours
	,prix
        , CASE WHEN prix >0 and NbrJours =1  
	THEN (select avg(t2.prix) from t2 
        WHERE t2.hotel = t1.hotel
        AND t2.dateSejour = t1.dateSejour
       t2.AND prix >0 
       t2.NbrJours =1) 
ELSE 0 END AS MoyennePrix
from t1

Cad calculer SEULEMENTla moyenne pour les enregistrements qui ont
prix >0 and NbrJours =1
Sur la base des enregistrement ayant le même hôtel et date séjour et dont le
prix >0 and NbrJours =1

Mais mon code n'est pas validé par l'éditeur SQLServer
[CODE]

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
select hote_id
	,dateSejour
	,NbrJours
	,prix
	, CASE WHEN prix >0 and NbrJours =1  
	THEN avg(CASE WHEN prix>0 and NbrJours =1 
			THEN prix END ) over( partition by hotel, dateSejour)
	 END ) as MoyennePrix
into t2
from  t1

Merci pour ton aide car j'ai en face de moi un spécialiste de la partition