Discussion :

[lionheart33806]

Bonjour,

J'ai besoin d'une orientation.
Imaginons que je gère un supermarché. Chaque article est catégorisé avec des catégories, sous-catégories, sous-sous catégories etc ...

Supposons maintenant que je souhaite avoir un résumé d'un ticket de caisse en ayant par catégorie, sous-catégories, etc ... le montant (si possible sans les articles).

Que me conseillez vous au niveau d'un select ?

Merci d'avance !

[Waldar]

Regardez du côté de GROUP BY ROLLUP.

[lionheart33806]

Merci pour cette piste.
Apparemment (sur le peu que j'ai lu), c'est plus "group by cube" qui serait intéressant.

Par contre, les éléments doivent être explicitement déclarés alors que dans mon cas (tordu ...), je suis dans une forme "dynamique" via des liens père/fils entre les catégories à priori infini.

Par ex : la catérogie 1 peut avoir des sous-sous catégories, alors que la catégorie 2 ne peut pas aller plus loin que les sous catégories.

Une autre piste ?

[Waldar]

Ah si tout est dans la même table, il faut jouer avec CONNECT BY !

L'idéal serait d'avoir la structure de votre table (ou un équivalent plus simple si vous désirez préserver la confidentialité de votre modèle) ainsi que quelques lignes (entre cinq et dix), et ce que vous attendez en sortie.

[lionheart33806]

J'avoue ne pas savoir comment représenter correctement une base sur le forum.
Voici une tentative :

T_CATEGORIES
CAT_ID / CAT_LIB / CAT_PERE
1 / Alimentaire / NULL
2 / Viande / 1
3 / Poissons / 1
4 / Veau / 2
5 / Porc / 2
6 / Autres / NULL

T_ARTICLES
ART_ID / CAT_ID / ART_LIB
1 / 4 / Veau de France
2 / 4 / Veau d'Allemagne
3 / 3 / Saumon
4 / 3 / Truite
5 / 1 / Sandwich
6 / 6 / Piles

T_TICKETS
TICK_ID
1

T_TICKETS_LIGNE
TICK_ID / ART_ID / QTE / PRIX_UNIT / MONTANT
1 / 1 / 2 / 10 / 20
1 / 5 / 1 / 5 / 5
1 / 6 / 4 / 1,5 / 6

Pour le ticket 1, je souhaite donc voir
Alimentaire : 25
- Viande : 20
-- Veau : 20
Autres : 6

J'espère que c'est compréhensible

[laurentschneider]

bon, s'il s'agit d'une arborescence, tu auras donc une colonne CAT et une colonne CAT_PARENT.

dans ce cas tu peux tenter un connect by

ex:

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
drop table lsc_order;
drop table lsc_cat;
create table lsc_cat (id number primary key, description varchar2(13), parent_id number references lsc_cat);
insert into lsc_cat values (1, 'Alimentaire',null);
insert into lsc_cat values (2, 'Fruits',1);
insert into lsc_cat values (3, 'Legumes',1);
insert into lsc_cat values (4, 'Choux',3);
insert into lsc_cat values (5, 'Pommes',2);
insert into lsc_cat values (6, 'Sport',null);
create table lsc_order(id number primary key, name varchar2(20), category number references lsc_cat, price number, qty number);
insert into lsc_order values (1, 'Golden Delicious', 5, 2.5, 5);
insert into lsc_order values (2, 'ski head 8i', 6, 1000, 1);
insert into lsc_order values (3, 'citron', 2, 3, .800);
insert into lsc_order values (4, 'mandarine', 2, 3, .800);
commit;

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
select description, name, qty, price from (
select lpad(description,2*level-1+length(description)) description,id
from lsc_cat 
connect by prior id=parent_id
start with parent_id is null ) c
left join lsc_order o on (c.id=o.category);
DESCRIPTION                    NAME                        QTY      PRICE
------------------------------ -------------------- ---------- ----------
 Alimentaire                                                             
   Fruits                      mandarine                    .8          3
   Fruits                      citron                       .8          3
     Pommes                    Golden Delicious              5        2.5
   Legumes                                                               
     Choux                                                               
 Sport                         ski head 8i                   1       1000

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select description, nvl(sum(qty*price),0) total 
from (
select connect_by_root description description,id
from lsc_cat 
connect by prior id=parent_id) c
join lsc_order o on (c.id=o.category)
group by description;
DESCRIPTION                         TOTAL
------------------------------ ----------
Fruits                               17.3
Alimentaire                          17.3
Pommes                               12.5
Sport                                1000

[lionheart33806]

Merci pour ton exemple.
Mais pour l'exemple 1, je n'obtiens pas cet ordre et l'exemple 2 ne s'exécute pas.

[laurentschneider]

pour l'exemple 1, je n'obtiens pas cet ordre

? tu obtiens quoi ?

l'exemple 2 ne s'exécute pas.

CONNECT_BY_ROOT est apparu en 10.1.0.2, peut-être que tu employes une version préhistorique

[lionheart33806]

Voici le copié/collé de Toad

Code :

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

1
2
3
4
5
6
7
8
9
10
 
DESCRIPTION	NAME	QTY	PRICE
 
     Pommes	Golden Delicious	5	2,5
 Sport	ski head 8i	1	1000
   Fruits	citron	0,8	3
   Fruits	mandarine	0,8	3
 Alimentaire			
     Choux			
   Legumes

Je te pris d'excuser mon "environnement" qui ne permet pas autre chose que oracle 9i

[Waldar]

Avec votre exemple, j'arrive à votre solution.
Il faut néanmoins ruser un peu pour conserver l'ordre, en parcourant deux fois la hierarchie :
- une fois pour l'ordre et le libellé (sous requête c1)
- une seconde fois pour multiplier les lignes (sous requête c2)
Il y a peut-être plus simple celà dit.

J'ai laissé quelques lignes inutiles dans les requêtes imbriquées, afin d'aider à la compréhension du mécanisme.
J'ai aussi enlevé la notion de ticket pour l'exemple car il n'était pas utile (au pire il suffit de rajouter une jointure).

Par contre je n'ai pas trouvé de solution sans CONNECT BY ROOT.

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
select
    c1.lib,
    sum(nvl(t.montant, 0)) montant
from
    (select
        cat_id,
        cat_lib,
        lpad(cat_lib, level-1+length(cat_lib), '-') lib,
        rownum as num
    from T_CATEGORIES
    start with cat_pere is null
    connect by prior cat_id = cat_pere) c1
    inner join
    (select
        CONNECT_BY_ROOT cat_id as cat_id_n,
        CONNECT_BY_ROOT cat_lib as cat_lib,
        cat_id
    from T_CATEGORIES
    connect by prior cat_id = cat_pere) c2
      on c2.cat_id_n = c1.cat_id
    inner join T_ARTICLES a
      on a.cat_id = c2.cat_id
    inner join T_TICKETS_LIGNE t
      on t.art_id = a.art_id
group by
    c1.num,
    c1.lib
order by
    c1.num asc

Code :

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

1
2
3
4
5
LIB		MONTANT
Alimentaire	25
-Viande		20
--Veau		20
Autres		6

Les donné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
18
19
20
create table t_categories as
select 1 as cat_id, 'Alimentaire' as cat_lib, null as cat_pere from dual union all
select 2, 'Viande', 1 from dual union all
select 3, 'Poissons', 1 from dual union all
select 4, 'Veau', 2 from dual union all
select 5, 'Porc', 2 from dual union all
select 6, 'Autres', null from dual;
 
create table t_articles as
select 1 as art_id, 4 as cat_id, 'Veau de France' as art_lib from dual union all
select 2, 4, 'Veau d''Allemagne' from dual union all
select 3, 3, 'Saumon' from dual union all
select 4, 3, 'Truite' from dual union all
select 5, 1, 'Sandwich' from dual union all
select 6, 6, 'Piles' from dual;
 
create table t_tickets_ligne as 
select 1 as art_id, 20 as montant from dual union all
select 5, 5 from dual union all
select 6, 6 from dual;

[lionheart33806]

Bon ok ... je l'ai dans l'os ...
Sinon, il n'y aurait pas une solution qui se rapprocherait de la vérité qui demanderait quelques manips en PHP ?

[laurentschneider]

oui, il y existe bien un truc pour 9i...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT description, nvl(sum(qty*price),0) total 
FROM (
SELECT 
substr(
sys_connect_by_path(description,'/'),2,instr(sys_connect_by_path(description,'/')||'/','/',1,2)-2
) description,id
FROM lsc_cat 
connect BY prior id=parent_id
) c
JOIN lsc_order o ON (c.id=o.category)
GROUP BY description;

[lionheart33806]

Haa ba voilà, si je boucle sur la requête de ton 1ier exemple en prenant les montants du 3ième (9i) avec PHP : TADA !
Il n'y a plus qu'à adapter !

Merci beaucoup à vous 2 !

[Waldar]

A ce moment-là en utilisant l'astuce de Laurent ça passe en une seule requête :

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
SELECT
    c1.lib,
    sum(nvl(t.montant, 0)) montant
FROM
    (SELECT
        cat_id,
        cat_lib,
        lpad(cat_lib, level-1+length(cat_lib), '-') lib,
        rownum AS num
    FROM T_CATEGORIES
    start WITH cat_pere IS NULL
    connect BY prior cat_id = cat_pere) c1
    INNER JOIN
    (SELECT
        substr(sys_connect_by_path(cat_id ,'/'),2,instr(sys_connect_by_path(cat_id ,'/')||'/','/',1,2)-2) AS cat_id_n,
        substr(sys_connect_by_path(cat_lib ,'/'),2,instr(sys_connect_by_path(cat_lib ,'/')||'/','/',1,2)-2) AS cat_lib,
        cat_id
    FROM T_CATEGORIES
    connect BY prior cat_id = cat_pere) c2
      ON c2.cat_id_n = c1.cat_id
    INNER JOIN T_ARTICLES a
      ON a.cat_id = c2.cat_id
    INNER JOIN T_TICKETS_LIGNE t
      ON t.art_id = a.art_id
GROUP BY
    c1.num,
    c1.lib
ORDER BY
    c1.num ASC

[lionheart33806]

Ok, à priori, je testerai ça dès lundi ! Merci

[lionheart33806]

Nickel merci !