From: | Hellmuth Vargas <hivs77(at)gmail(dot)com> |
---|---|
To: | robjsargent(at)gmail(dot)com |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Is postorder tree traversal possible with recursive CTE's? |
Date: | 2018-06-19 19:51:58 |
Message-ID: | CAN3Qy4pAZJFnbALEUFLVCc5ZXqLRcnpd-UZmvnw1UP_ZFV__uw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
*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 (
robjsargent(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2018-06-19 19:54:15 | Re: Run Stored procedure - function from VBA |
Previous Message | Rob Sargent | 2018-06-19 19:51:29 | Re: Is postorder tree traversal possible with recursive CTE's? |