Dotaz: SQL řešení hierarchické strukutury

[Karlos]

Příloha:

• Eshop.png (124378 bytů)

Dobrý den, chtěl poprosit o pomoc při vytvoření SQL skriptu.

Mám tento datový model, který charakterizuje e-shop a jeho kategorie pro jednotlivé zboží:

item_category: každá kategorie/subkategorie zboží (list). Pomocí selreferenční vazby parent je definován les stromů kategorií. Kořenové kategorie těchto stromů se nazývají kategorie hlavní.

item_category_transitive_closure - jde o redundantní datovou strukturu obsahující transitivní uzávěr relace nadkategorie-podkategorie, kterou reprezentuje selferefrenční vazba parent nad tabulkou item_category - jedná se o každou reprezentaci vazby mezi dvěma kategoriemi se smyslu: kategorie (#super_cat_id)-s, které leží na cestě ke kořeni stromu kategorií od dané kategorie (#sub_cat_id) (0,M : 0,M) kategorie (#sub_cat_id)-s, které jsou potomky dané kategorie (#super_cat_id) v rámci stromu kategorií. (0,M : 0,M)

Úkolem je vytvořit pro každou hlavní kategorii počet zboží. Přičemž zboží lze vystavovat pouze do listových kategorií.

Moc děkuji za pomoc

Odpovědi

[xkucf03]

Co znamená:

Úkolem je vytvořit pro každou hlavní kategorii počet zboží.

? Nemělo to být spíš zjistit počet nebo vypsat zboží v kategorii (včetně podkategorií)?

[Karlos]

Pouze počet zboží v rámci listu, tedy kategorie, která nemá další podkategorie.

Řešení se našlo:

 INSERT INTO category (id, parent_category_Id)
 values (1,NULL);
  INSERT INTO category (id, parent_category_Id)
 values (2,NULL);
  INSERT INTO category (id, parent_category_Id)
 values (3,1);
  INSERT INTO category (id, parent_category_Id)
 values (4,2);
   INSERT INTO category (id, parent_category_Id)
 values (5,4);
   INSERT INTO category (id, parent_category_Id)
 values (6,4);
   INSERT INTO category (id, parent_category_Id)
 values (8,6);
  INSERT INTO category (id, parent_category_Id)
 values (9,NULL);
 
 
 WITH RECURSIVE cat_cte (id, parent_category_id, depth, top_parent) AS 
 (
 	select id, parent_category_id, 0, id as top_parent  
    FROM category
    where parent_category_id IS NULL
    UNION ALL
    select c.id, c.parent_category_id, cte.depth +1, cte.top_parent FROM category c INNER JOIN cat_cte cte
    on c.parent_category_id = cte.id
   
 ) select * from cat_cte cte;
 
select * from category c left join category c2
on c.id = c2.parent_category_id
where c2.id IS NULL;

Děkuji za vaši analýzu

[xkucf03]

Upravil jsem formátování dotazu.

Ano, to je CTE, o kterém píšu níže :-)

[Karlos]

Děkuji a děkuji za odkaz, skript mi udělal nějaký pán. Klasické CTE umím, ale rekurze a další věci ne, takže ještě učení bude dost. A ano je to do možná nové práce.

[Kit]

Pokud strom kategorií nacpeš do jednoho XML, tak se s tím docela dobře pracuje - včetně prohledávání kategorií a vypisování drobenkové navigace.

Na některé úlohy se prostě NoSQL hodí lépe.

[xkucf03]

Počítám, že dostal úkol ve škole nebo v práci, takže potřebuje vyjít s tím, co má :-)

Co se týče XML, to se dá vložit i do PostgreSQL a pak nad tím pustit XMLTABLE dotaz, který vypíše celou cestu (ID kategorií) nebo cokoli jiného.

Pokud to má být čistě relačním způsobem, tak na to slouží CTE dotazy. Tady je o tom i článek v češtině: CTE a analytické funkce v PostgreSQL.