Discussion :

[bstevy]

Bonjour,


Je bloque je pense sur un problème assez simple, mais je n'arrive pas à réaliser ce que je veux faire :

Voici mes données :

ID_CONTRAT	ID_PERS	TYPE
1	1	1
2	2	1
3	2	3
4	3	2
5	3	2
6	4	1
7	4	1
8	4	2
9	5	2
10	5	2
11	5	2

Il s'agit d'une extraction que je fais comportant :

• le numéro d'identification de mon contrat (unique)
• le numéro d'identifacion du client qui a souscrit le contrat (un client peut souscrire plusieurs contrats)
• le type de véhicule pour lequel il a souscrit son contrat (voiture =1 ou 3, et moto =2)

Je veux rajouter deux colonnes, et je pensais pour cela utiliser une fonction de fenetrage mais j'ai un peu du mal à la concevoir.
Le but étant de créer deux flags,
Pour le premier, je cherche à savoir pour 1 client, s'il possède un autre contrat voiture en plus du contrat de la ligne sur laquelle je suis
Pour le deuxième, je cherche à savoir pour 1 client, s'il possède un autre contrat moto en plus du contrat de la ligne sur laquelle je suis

Ce qui pour mon jeu de donnée devrait me donner le résultat suivant :

ID_CONTRAT	ID_PERS	TYPE	2_car	2_bike
1	1	1	N	N
2	2	1	Y	N
3	2	3	Y	N
4	3	2	N	Y
5	3	2	N	Y
6	4	1	Y	Y
7	4	1	Y	Y
8	4	2	Y	N
9	5	2	N	Y
10	5	2	N	Y
11	5	2	N	Y

Est ce que c'est clair ?
Est ce que vous pouvez m'aider à formuler cela svp ?


Merci d'avance.

Steven

[McM]

Le plus simple à coder est d'utiliser un sous-select

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
WITH CONTRAT AS (SELECT 1 idcontrat, 1 idpers, 1 tp FROM dual
UNION ALL SELECT 2, 2, 1 FROM DUAL
UNION ALL SELECT 3, 2, 3 FROM DUAL
UNION ALL SELECT 4, 3, 2 FROM DUAL
UNION ALL SELECT 5, 3, 2 FROM DUAL
UNION ALL SELECT 6, 4, 1 FROM DUAL
UNION ALL SELECT 7, 4, 1 FROM DUAL
UNION ALL SELECT 8, 4, 2 FROM DUAL
UNION ALL SELECT 9, 5, 2 FROM DUAL
UNION ALL SELECT 10, 5, 2 FROM DUAL
UNION ALL SELECT 11, 5, 2 FROM DUAL)
SELECT a.idcontrat, a.idpers, a.tp,
	NVL((SELECT 'Y' FROM CONTRAT b WHERE b.idcontrat <> a.idcontrat AND b.idpers = a.idpers AND b.tp IN (1,3) AND ROWNUM = 1),'N') "2_car",
  NVL((SELECT 'Y' FROM CONTRAT b WHERE b.idcontrat <> a.idcontrat AND b.idpers = a.idpers AND b.tp = 2 AND ROWNUM = 1),'N') "2_bike"
FROM CONTRAT a

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
IDCONTRAT	IDPERS	TP	2_car	2_bike
1	1	1	N	N
2	2	1	Y	N
3	2	3	Y	N
4	3	2	N	Y
5	3	2	N	Y
6	4	1	Y	Y
7	4	1	Y	Y
8	4	2	Y	N
9	5	2	N	Y
10	5	2	N	Y
11	5	2	N	Y

[aieeeuuuuu]

Bonjour,

Ou avec des fonctions fenêtrées :

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
 
SELECT 
	a.idcontrat, 
	a.idpers, 
	a.tp,
	CASE WHEN 
		COUNT(CASE WHEN Tp IN (1,3) THEN 1 END) OVER(PARTITION BY idpers)
		- CASE WHEN Tp IN (1,3) THEN 1 ELSE 0 END
		> 0 THEN 'Y' ELSE 'N'
	END AS car,
		CASE WHEN 
		COUNT(CASE WHEN Tp = 2 THEN 1 END) OVER(PARTITION BY idpers)
		- CASE WHEN Tp = 2 THEN 1 ELSE 0 END
		> 0 THEN 'Y' ELSE 'N'
	END AS bike
FROM CONTRAT a
order by idcontrat

[SQLpro]

Ton résultat est faux pour ID_PERS = 4

Ce serait mieux si tu nous avait donné le DDL et un jeu d'essais.

En SQL normatif (Oracle commence à s'y mettre !) :

Code :

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

1
2
3
SELECT *, COALESCE(SIGN(SUM(CASE WHEN "TYPE" = 2 THEN 1 ELSE NULL END)  OVER(PARTITION BY ID_PERS) -1), 0) AS MOTO2,
          COALESCE(SIGN(SUM(CASE WHEN "TYPE" <> 2 THEN 1 ELSE NULL END) OVER(PARTITION BY ID_PERS) -1), 0) AS VOIT2
FROM   CONTRAT

Mon jeu d'essai et DDL :

Code :

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

1
2
3
4
CREATE TABLE CONTRAT 
(ID_CONTRAT INT,	
 ID_PERS 	INT,
 "TYPE"       INT);

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO CONTRAT VALUES
(1,		1, 	1),
(2, 	2, 	1),
(3, 	2,	3),
(4, 	3,	2),
(5, 	3, 	2),
(6, 	4, 	1),
(7, 	4, 	1),
(8, 	4, 	2),
(9, 	5, 	2),
(10, 	5, 	2),
(11, 	5, 	2);

A +

[bstevy]

@SQLpro, merci pour cette solution, mais ca ne fonctionne absolument pas chez moi. XD
Je pense que ca doit peut-etre être du à la version d'Oracle. Je suis en 11g, la limite vient peut etre de là, non ?
Désolé de ne pas l'avoir précisé, je ne pensais pas que tu allais me pondre un truc aussi avancé. En tout cas, je garde en réservé, car j'ai jamais vu ces notations encore, et je suis curieux d'apprendre comment ca fonctionne

A qui t'adresses tu quand tu dis que ID_PERS = 4 est faux ? A moi ? Parce que je viens de vérifier, et revérifier mon résultat, et je ne vois pas où c'est faux. Pourrais tu etre plus précis stp ?

En tout cas, désolé de pas pouvoir sortir les DDL, j'ai groupé mon résultat et mes cas de test dans un tableau simplifié car le vrai model joint 10 tables avant d'arriver à ce résultat.




@aieeeuuuuu & McM : merci pour vos requêtes, c'est ce que je cherchais à faire sans vraiment y arriver. ^^ Je vais voir laquelle des deux est la plus performante, et ca devrait le faire.

[mnitu]

Ca marche pour moi

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
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
 
SQL> 
SQL> WITH CONTRAT AS (SELECT 1 idcontrat, 1 idpers, 1 tp FROM dual
  2  UNION ALL SELECT 2, 2, 1 FROM DUAL
  3  UNION ALL SELECT 3, 2, 3 FROM DUAL
  4  UNION ALL SELECT 4, 3, 2 FROM DUAL
  5  UNION ALL SELECT 5, 3, 2 FROM DUAL
  6  UNION ALL SELECT 6, 4, 1 FROM DUAL
  7  UNION ALL SELECT 7, 4, 1 FROM DUAL
  8  UNION ALL SELECT 8, 4, 2 FROM DUAL
  9  UNION ALL SELECT 9, 5, 2 FROM DUAL
 10  UNION ALL SELECT 10, 5, 2 FROM DUAL
 11  UNION ALL SELECT 11, 5, 2 FROM DUAL
 12  )
 13  SELECT t.*,
 14         COALESCE(SIGN(SUM(CASE WHEN tp = 2 THEN 1 ELSE NULL END)  OVER(PARTITION BY IDPERS) -1), 0) AS MOTO2,
 15         COALESCE(SIGN(SUM(CASE WHEN tp <> 2 THEN 1 ELSE NULL END) OVER(PARTITION BY IDPERS) -1), 0) AS VOIT2
 16    FROM CONTRAT t
 17  /
 IDCONTRAT     IDPERS         TP      MOTO2      VOIT2
---------- ---------- ---------- ---------- ----------
         1          1          1          0          0
         2          2          1          0          1
         3          2          3          0          1
         4          3          2          1          0
         5          3          2          1          0
         6          4          1          0          1
         8          4          2          0          1
         7          4          1          0          1
        10          5          2          1          0
         9          5          2          1          0
        11          5          2          1          0
11 rows selected
 
SQL>