Tri particulier dans une requête

**guilou339** · 02/11/2022, 12h12

Bonjour,
Je cherche un faire un tri particulier dans une requête mais je n'arrive pas à la réaliser.
Chaque ligne correspond à un produit que je dois faire. Sur chaque produit j'ai un fil dessus et un fil dessous.
En gros je voudrais optimiser le changement de bobine (fil dessus qui est prioritaire car plus pénible à changer) et le changement de canette (fil dessous plus simple à changer).
Le 1er tableau est trié juste par fil dessus en 1er et fil dessous en second.
Le top serait de se dire je prend le 1er fil dessus qui est le blanc, ensuite je regarde mon fil du dessous et je prend une ligne qui une canette de même couleur, pour ensuite continuer avec son fil du dessus sur la même logique.
Je ne vois pas comment traduire cela en sql.

Nom : Capture2.JPG
Affichages : 675
Taille : 68,2 Ko

Nom : Capture2.JPG
Affichages : 675
Taille : 68,2 Ko

En gros ca pourrait donner :

Nom : Capture.JPG
Affichages : 652
Taille : 82,6 Ko

**vanagreg** · 03/11/2022, 10h01

Bonjour,

Je n'ai pas bien compris le besoin. Tu pourrais préciser?
Merci

**escartefigue** · 03/11/2022, 15h18

Pas mieux, l'explication n'est pas claire

Quoi qu'il en soit, pour modifier l'ordre d'un tri, on utilise le plus souvent un ORDER BY CASE....

**guilou339** · 08/11/2022, 09h43

En fait en effet je vais utiliser un "order by ", mais il me faudrait un colonne de tri sur laquelle je pourrais m'appuyer pour trier les données comme souhaité, pour avoir le moins de changement possible de bobine (fil dessus)/ canette (fil dessous) et je n'arrive pas établi une règle.
Ca n'est pas simplement faire order by fil_dessus, fil_dessous

**JeitEmgie** · 08/11/2022, 16h04

Le problème est que le ORDER BY à utiliser semble devoir changer à chaque inversion de priorité entre bobine et canette, ainsi que l’espace de tri… donc cela va être compliqué sans passer par une procédure.

on commence par priorité à la bobine : donc ici le BLANC apparaît 9x,
maintenant par quelle canette faut-il finir?
celle qui apparaît le plus comme canette en dehors du BLANC comme bobine…
Encore le BLANC, et dans le nouvel espace on doit à nouveau trier par bobine,
et découvrir par laquelle canette il faut terminer : celle qui apparaît le plus comme canette dans ce qui n’a pas encore été utilisé…
etc.

on pourrait imaginer qu’un query récursif pourrait le faire mais cela risque vite d’exploser question # de combinaisons, par contre une procédure récursive avec l’espace de sélection qui diminue à chaque appel…

**JeitEmgie** · 10/11/2022, 08h58

A tester:
la table, avec une colonne en plus qui donnera l'ordre et servira à distinguer ceux qui ont été déjà traités
la (tentative de) solution fonctionne finalement avec une boucle et deux MERGE.

Code :

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

1
2
3
4
5
6
create table t_products_tripart (
    id  NUMBER(10,0) PRIMARY KEY,
    upper   VARCHAR2(24),
    lower   VARCHAR2(24),
    rn      NUMBER(10,0)
);

On a 2 MERGE différents car le query du USING utilise 2 tris différents,
le premier positionne sur le fil du dessus le plus présent en finissant par le fil du dessous
qui aura un fil du dessus le plus présent dans ceux qui resteront et n'est exécuté qu'une seule fois pour initialiser le processus.
Ensuite on boucle sur le 2ème MERGE qui lui trie sur le fil du dessous en commençant par celui qui termine le placement précédent
et termine à nouveau avec le fil du dessous qui aura un fil du dessus le plus présent dans ceux qui restent.

A tester avec d'autres jeux de données pour être certain qu'on ne bouclera jamais à l'infini sur le deuxième MERGE.

Mais il est clair qu'en cas d'égalité de # dans la sélection du fil du dessus suivant,
on pourrait avoir plusieurs solutions dont on explore ici qu'une branche à chaque étape...

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
DECLARE
    w_todo NUMBER(10,0) ;
BEGIN
    MERGE INTO t_products_tripart d
    USING (
        SELECT s.id, s.upper, s.lower, 
            (SELECT count(id) FROM t_products_tripart WHERE rn IS NOT NULL) +
            ROW_NUMBER() OVER(ORDER BY 
                CASE WHEN s.lower = (SELECT lower FROM t_products_tripart WHERE rn = (SELECT MAX(rn) FROM t_products_tripart)) THEN 0 ELSE 1 END, 
                    -- n_next_lower DESC, 
                    n_next_upper, s.id) AS rn
        FROM t_products_tripart s
        JOIN (
            SELECT s.upper, s.lower, COUNT(dupp.upper) AS n_next_upper
            FROM t_products_tripart s
                LEFT JOIN t_products_tripart dupp ON dupp.upper = s.lower AND dupp.rn IS NULL
            WHERE s.upper = (
                -- next upper to use
                SELECT upper FROM (
                    SELECT upper, n_with_same_lower, n_upper, 
                        ROW_NUMBER() OVER(ORDER BY n_upper DESC, n_with_same_lower DESC) AS rn
                    FROM (
                        SELECT DISTINCT upper, 
                            CASE WHEN upper = (SELECT lower FROM t_products_tripart WHERE rn = (SELECT MAX(rn) FROM t_products_tripart)) THEN 0 ELSE 1 END as p,
                            SUM( 
                                CASE WHEN 
                                    lower = (SELECT lower FROM t_products_tripart WHERE rn = (SELECT MAX(rn) FROM t_products_tripart)) 
                                    THEN 1 ELSE 0 END) OVER(PARTITION BY upper) AS n_with_same_lower,
                            COUNT( id) OVER(PARTITION BY upper) AS n_upper
                        FROM t_products_tripart
                        WHERE rn IS NULL
                    )
                )
                WHERE rn = 1
            )
            GROUP BY s.upper, s.lower
        ) d ON s.lower = d.lower AND s.upper = d.upper
    ) s
    ON (s.id = d.id)
    WHEN MATCHED THEN
        UPDATE SET rn = s.rn
    ;
 
    SELECT COUNT(*) INTO w_todo FROM t_products_tripart WHERE rn IS NULL ;
    WHILE (w_todo > 0)
    LOOP
        MERGE INTO t_products_tripart d
        USING (
            SELECT s.id, s.upper, s.lower, 
                (SELECT count(id) FROM t_products_tripart WHERE rn IS NOT NULL) +
                ROW_NUMBER() OVER(ORDER BY 
                    CASE WHEN s.lower = (SELECT lower FROM t_products_tripart WHERE rn = (SELECT MAX(rn) FROM t_products_tripart)) THEN 0 ELSE 1 END, 
                        n_next_lower, 
                        s.id) AS rn
            FROM t_products_tripart s
            JOIN (
                SELECT s.upper, s.lower
                    , COUNT( CASE WHEN dlow.lower NOT IN (SELECT upper FROM t_products_tripart WHERE rn IS NOT NULL) THEN dlow.upper END ) AS n_next_lower 
                FROM t_products_tripart s
                    LEFT JOIN t_products_tripart dlow ON dlow.lower = s.lower AND dlow.rn IS NULL
                WHERE s.upper = (
                    -- next upper to use
                    SELECT upper FROM (
                        SELECT upper, n_with_same_lower, n_upper, 
                            ROW_NUMBER() OVER(ORDER BY n_with_same_lower DESC, n_upper DESC) AS rn
                        FROM (
                            SELECT DISTINCT upper, 
                                CASE WHEN upper = (SELECT lower FROM t_products_tripart WHERE rn = (SELECT MAX(rn) FROM t_products_tripart)) THEN 0 ELSE 1 END as p,
                                SUM( 
                                    CASE WHEN 
                                        lower = (SELECT lower FROM t_products_tripart WHERE rn = (SELECT MAX(rn) FROM t_products_tripart)) 
                                        THEN 1 ELSE 0 END) OVER(PARTITION BY upper) AS n_with_same_lower,
                                COUNT( id) OVER(PARTITION BY upper) AS n_upper
                            FROM t_products_tripart
                            WHERE rn IS NULL
                        )
                    )
                    WHERE rn = 1
                )
                GROUP BY s.upper, s.lower
            ) d ON s.lower = d.lower AND s.upper = d.upper
        ) s
        ON (s.id = d.id)
        WHEN MATCHED THEN
            UPDATE SET rn = s.rn
        ;
        SELECT COUNT(*) INTO w_todo FROM t_products_tripart WHERE rn IS NULL ;
    END LOOP ;
    COMMIT ;
END ;
/

Pour le problème donné, on arrive à

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
 
5	BLANC		GRIS FONCE	1
8	BLANC		VERT FONCE	2
9	BLANC		VERT FONCE	3
3	BLANC		BLEU CLAIR	4
4	BLANC		BLEU CLAIR	5
6	BLANC		NOIR		6
7	BLANC		NOIR		7
1	BLANC		BLANC		8
2	BLANC		BLANC		9
15	NOIR		BLANC		10
16	NOIR		BLANC		11
17	NOIR		BLANC		12
18	NOIR		ORANGE		13
19	NOIR		ROSE FONCE	14
26	ROUGE		ROSE FONCE	15
25	ROUGE		GRIS CLAIR	16
10	BLEU CLAIR	BLANC		17
11	BLEU CLAIR	BLANC		18
12	BLEU CLAIR	BLANC		19
20	ORANGE		BLANC		20
21	ORANGE		BLEU CLAIR	21
22	ORANGE		VERT CLAIR	22
23	ROSE CLAIR	BLEU FONCE	23
24	ROSE CLAIR	JAUNE		24
13	GRIS CLAIR	BLANC		25
14	GRIS FONCE	BLANC		26
27	VERT FONCE	BLANC		27

**guilou339** · 10/11/2022, 15h48

Bonjour,
Le tri que vous obtenez me parait très bien.
J'ai du mal à l'appliquer à mon cas.

J'ai une table tri_piqure
avec les colonne :
- lancement (c'est tous les produits qui sont mis en production le même jour par exemple 1 pour le 1er groupement de produit mis en production),
- n_commande (numéro de commande),
- fils_dessus,
- fils_dessous,
- num_produit (il s'agit du numéro d'un produit dans un lancement. Par exemple si 10 produits dans un lancement, chaque produit sera numéroté de 1 à 10)

Dans OBIEE et dans ma requête je met un paramètre :lancement pour n'appeler que le lancement que je souhaite sélectionner.

Au final je ne veux pas créer une nouvelle table avec le tri, mais juste une vu avec un select pour extraire les données dans OBIEE
En cas d'égalité de #, ca n'est pas un problème qu'il prenne un fil ou l'autre, tant qu'il les prends tous au final.

En tout cas votre requête me redonne espoir sur ce qui est pour moi un casse tête.

**JeitEmgie** · 10/11/2022, 16h15

Pour appliquer la solution vous aurez besoin de la colonne qui stocke la position, mais vous pourriez peut-être copier le problème,
c'est-à-dire le subset des données nécessaires dans une table temporaire... et appliquer la procédure dessus.

**guilou339** · 11/11/2022, 08h39

Pour moi la position c'était num_produit car je sélectionne toujours un seul lancement.
Vous entendez quoi par la position ? Le numéro de ligne ? Ou une position après un tri fil dessus, fil dessous?
Faire des requêtes avec des select, je gère mais après mes connaissances sont limitées.

**JeitEmgie** · 11/11/2022, 09h26

La colonne “rn” pour row number… celle qui donne l’ordre final, pour moi num_produit c’était une clé invariable. A vous de voir… mais l’algorithme ne fonctionne que si l’on peut distinguer ce qui reste à placer du reste…

En faisant de t_products_tripart une

Code :

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

GLOBAL TEMPORARY TABLE ... ON COMMIT DELETE ROWS ;

Au debut de la procédure :

Code :

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

1
2
3
4
5
6
 
    INSERT INTO t_products_tripart(id, upper, lower)
    SELECT ROW_NUMBER() OVER(PARTITION BY lancement ORDER BY upper, lower), fil_dessus, fil_dessous
    FROM tri_piqure 
    WHERE lancement = i_lancement 
    ;

Et à la fin après le END LOOP et avant le COMMIT :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
    MERGE INTO tri_piqure d
    USING(
        SELECT * FROM t_products_tripart
    ) s
    ON ( d.fil_dessus = s.upper AND d.fil_dessous = s.lower )
    WHEN MATCHED THEN 
        UPDATE SET d.num_produit = s.rn
    WHERE
        d.lancement = i_lancement ;

Avec i_lancement le paramètre de la procédure.

**guilou339** · 11/11/2022, 16h19

Ok merci. Je vais essayer ça demain.
Par contre tout ça va peut s'intégrer dans une requête d'un tableau de bord de obiee ?
Parce que c'est dans obiee que je sélectionne le lancement

**JeitEmgie** · 11/11/2022, 16h40

OBIEE: pour "Oracle Business Intelligence Enterprise Edition" ?
Difficile d'imaginer que l'on ne puisse pas appeler une procédure ORACLE à partir d'un outil ORACLE....

**guilou339** · 14/11/2022, 10h50

J'ai le message :
ORA-38106: Opération MERGE non prise en charge sur la vue de jointure ou la vue comportant le déclencheur INSTEAD OF.
ORA-06512: à ligne 89 (C'est la ligne MERGE INTO)
Ca c'est quand j'ajoute la partie MERGE INTO entre END LOOP ; et COMMIT ;

**JeitEmgie** · 14/11/2022, 14h06

tri_piqure est une VIEW ? évidemment çà marche moins bien... faudrait faire le MERGE sur la table originale...
ou donner le DDL complet de cet objet... et s'il y a des triggers dessus, leur DDL aussi...

Quoique vous n'avez pas besoin du MERGE si vous sortez le résultat d'une manière ou une autre :
le MERGE n'est là que pour rendre le résultat permanent.

**guilou339** · 14/11/2022, 15h20

Oui en effet c'est une vue car elle fait appel à plusieurs tables.
Je ne pensais pas que ca fonctionnait différemment avec une vue, mais en réfléchissant oui c'est normal que ca ne fonctionne pas.

Si on part du principe qu'il n'y a pas besoin de faire le merge, la requête s'exécute jusqu'au bout, par contre ca vide la table t_products_tripart donc je ne sais pas ou récupérer le résultat.
Quand je fais le insert au début, j'ai bien la table t_products_tripart qui se rempli, mais à la fin de la boucle elle est vide.

**JeitEmgie** · 14/11/2022, 16h13

Ben oui avec DELETE ON COMMIT.... il faut sortir le résultat quelque part avant le COMMIT ou enlever le DELETE ON COMMIT et le remplacer par un TRUNCATE au début de la procédure...
alors le résultat sera dans la table jusqu'à la prochaine exécution...

**guilou339** · 14/11/2022, 17h50

Ça fonctionne plutôt très bien, il reste juste pour les dernières lignes ou ça n'est pas optimal. Par exemple dans un autre lancement :

Noir-blanc
Noir-bleu
Noir-rose
Vert-beige
Gris-blanc
Bleu-blanc

Ça c'est l'ordre qu'il me donne.
J'aurais bien aimé que le noir-blanc soit en ligne 3 et pas en ligne 1 ici pour qu'il enchaîne sur le gris-blanc
Les 20 autres lignes au dessus sont bien triées comme il faut.

En tout cas c'est top !

**JeitEmgie** · 14/11/2022, 18h15

Il faudrait l’ensemble des données pour trouver pourquoi, probablement un critère de tri à rajouter pour mieux distinguer les situations où il y a le même nombre de “upper”,

en tous cas sur ces données j'obtiens

Code :

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

1
2
3
4
5
6
1	NOIR	BLANC	1
3	NOIR	ROSE	       2
2	NOIR	BLEU	       3
6	BLEU	       BLANC	4
5	GRIS	       BLANC	5
4	VERT        BEIGE	6

ce qui correspond bien à l'exigence de la première phase : terminer par le fil du dessous qui est le plus de fois au-dessus à la phase suivante, càd ici le BLEU
donc pour améliorer il faut travailler sur les critères de tri du premier MERGE.

**JeitEmgie** · 14/11/2022, 19h06

Essayez cette version du premier MERGE :

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
38
39
40
41
42
 
    MERGE INTO t_products_tripart d
    USING (
        SELECT s.id, s.upper, s.lower, 
            (SELECT count(id) FROM t_products_tripart WHERE rn IS NOT NULL) +
            ROW_NUMBER() OVER(ORDER BY 
                n_next_lower ASC,
                CASE WHEN s.lower = (SELECT lower FROM t_products_tripart WHERE rn = (SELECT MAX(rn) FROM t_products_tripart)) THEN 0 ELSE 1 END, 
                    n_next_upper, s.id) AS rn
        FROM t_products_tripart s
        JOIN (
            SELECT s.upper, s.lower, COUNT(dupp.upper) AS n_next_upper
                ,COUNT(dlow.id) AS n_next_lower
            FROM t_products_tripart s
                LEFT JOIN t_products_tripart dupp ON dupp.upper = s.lower AND dupp.rn IS NULL
                LEFT JOIN t_products_tripart dlow ON dlow.lower = s.lower AND dupp.rn IS NULL AND dlow.id <> s.id
            WHERE s.upper = (
                -- next upper to use
                SELECT upper FROM (
                    SELECT upper, n_with_same_lower, n_upper, 
                        ROW_NUMBER() OVER(ORDER BY n_upper DESC, n_with_same_lower DESC) AS rn
                    FROM (
                        SELECT DISTINCT upper, 
                            CASE WHEN upper = (SELECT lower FROM t_products_tripart WHERE rn = (SELECT MAX(rn) FROM t_products_tripart)) THEN 0 ELSE 1 END as p,
                            SUM( 
                                CASE WHEN 
                                    lower = (SELECT lower FROM t_products_tripart WHERE rn = (SELECT MAX(rn) FROM t_products_tripart)) 
                                    THEN 1 ELSE 0 END) OVER(PARTITION BY upper) AS n_with_same_lower,
                            COUNT( id) OVER(PARTITION BY upper) AS n_upper
                        FROM t_products_tripart
                        WHERE rn IS NULL
                    )
                )
                WHERE rn = 1
            )
            GROUP BY s.upper, s.lower
        ) d ON s.lower = d.lower AND s.upper = d.upper
    ) s
    ON (s.id = d.id)
    WHEN MATCHED THEN
        UPDATE SET rn = s.rn
    ;

**guilou339** · 14/11/2022, 19h21

Voilà la liste :
J'aurais souhaité que la ligne 33 et 35 soient inversées, pour ensuite mettre la ligne 37 en position 36,38 en position 37 et 36 en 38.
Quand il n'y a plus de correspondance en fil dessus, on regroupe les fils dessous.
Pareil pour les violet, il ne trouve pas de correspondance, mais il aurait terminer par violet orange pour enchaîner par bleu foncé orange
Nom : Capture.JPG
Affichages : 544
Taille : 89,5 Ko

Nom : Capture.JPG
Affichages : 544
Taille : 89,5 Ko

Tri particulier dans une requête

PL/SQL Oracle

Discussions similaires

Partager

Partager