*Hi* This is gorgeous but I suspect any level greater than 10 wide will present sorting problems, no?
*no, it should not be inconvenient* Maybe a fixed two-digit, zero filled number per level? *neither* Pushing the problem off by an order of magnitude :) An exercise left to the OP perhaps. El mar., 19 de jun. de 2018 a la(s) 14:52, Rob Sargent ( robjsarg...@gmail.com) escribió: > > > On 06/19/2018 01:14 PM, Hellmuth Vargas wrote: > > > Hi > > with partial sum: > > > > > with recursive pizza (name, step, ingredient, quantity, unit, rel_qty, > path, weight) > as ( > select > name, step, ingredient, quantity, unit > , quantity::numeric(10,2) > , step::text > , case when unit = 'g' then quantity::numeric(10,2) else > null end > from recipe > where name = 'pizza' > union all > select > recipe.name, recipe.step, recipe.ingredient, > recipe.quantity, recipe.unit > , (pizza.rel_qty * recipe.quantity)::numeric(10,2) > , pizza.path || '.' || recipe.step > , case when recipe.unit = 'g' then (pizza.rel_qty * > recipe.quantity)::numeric(10,2) else null end > from pizza > join recipe on (recipe.name = pizza.ingredient) > ) > select path, ingredient, quantity, rel_qty, unit, weight,*sum(weight) > over(partition by split_part(path,'.',1)) as parcial_weight*, *sum(weight) > over() as total_weight* > from pizza > order by path; > > path | ingredient | quantity | rel_qty | unit | weight | > parcial_weight | total_weight > > -------+--------------+----------+---------+-------+--------+----------------+-------------- > 1 | tomato sauce | 1.00 | 1.00 | pcs | | > 113.00 | 313.00 > 1.1 | tomato | 100.00 | 100.00 | g | 100.00 | > 113.00 | 313.00 > 1.2 | basil | 10.00 | 10.00 | g | 10.00 | > 113.00 | 313.00 > 1.3 | salt | 3.00 | 3.00 | g | 3.00 | > 113.00 | 313.00 > 2 | pizza bottom | 1.00 | 1.00 | pcs | | > 200.00 | 313.00 > 2.2 | dough | 1.00 | 1.00 | pcs | | > 200.00 | 313.00 > 2.2.1 | flour | 150.00 | 150.00 | g | 150.00 | > 200.00 | 313.00 > 2.2.2 | water | 50.00 | 50.00 | g | 50.00 | > 200.00 | 313.00 > 2.2.3 | salt | 1.00 | 1.00 | pinch | | > 200.00 | 313.00 > (9 rows) > > > > > > -- Cordialmente, Ing. Hellmuth I. Vargas S. Esp. Telemática y Negocios por Internet Oracle Database 10g Administrator Certified Associate EnterpriseDB Certified PostgreSQL 9.3 Associate
