3 ms·
For the second one, it seems most natural to reach for exists, or something (I have not tried this code..) select node , (case when parent is null th
by arh68 6y ago
For the second one, it seems most natural to reach for exists, or something (I have not tried this code..)
select
node
, (case when parent is null then 'Root'
when exists (
select * from tree c
where c.parent = node
) then 'Inner'
else 'Leaf'
end) "label"
from tree
EDIT: also, in the fourth, it seems like you'd want to partition the window function, who cares about order. Something like
sum(cash_flow) over (partition by date) "cumulative_cf"
- combatentropy 6y agoA subquery in the from-clause is usually lighter than the select-clause. I just so happen to have a table with this structure and hundreds of thousands of rows. This way runs in half the time. It is surprising, in fact, that the difference isn't wider, because Postgres's explain-command says it costs 1/100 as much. select t.node, case when t.parent is null then 'Root' when p.parent is null then 'Leaf' else 'Inner' end as label from tree t left join (select distinct parent from tree) p on t.node = p.parent