Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Nested Set суммирование по полю  [new]
drboboev
Member

Откуда:
Сообщений: 6
Добрый день.

Имеется дерево в БД по модели Nested Sets. Вывожу дерево, в другой таблице записаны значения связь один-ко-многим. Значения вносятся каждый день по нижнему уровню дерева. Нужно сделать запрос, который получит из БД всё дерево и просуммирует на родителя все значения потомков.

Пример:

Родитель 1: 25шт (5 + 15 + 5)
Потомок 1.1: 5шт
Потомок 1.2: 15шт
Потомок 1:3: 5шт
Родитель 2: 45шт (20 + 25)
Потомок 2.1: 20шт (5 + 15)
Потомок 2.1.1: 5шт
Потомок 2.1.2: 15шт
Потомок 2.2: 25шт

Таких уровней может быть до 10 и узлов дерева будет большое множество. Т.е. нужно оптимальное решение, чтобы запрос выполнялся максимально быстро.

Спасибо.
22 май 17, 12:30    [20500998]     Ответить | Цитировать Сообщить модератору
 Re: Nested Set суммирование по полю  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
Кажется, Вы уже задавали этот вопрос. Или задача отличается?

http://www.sql.ru/forum/1250412/ierarhiya-i-summirovanie-poley
23 май 17, 14:44    [20504453]     Ответить | Цитировать Сообщить модератору
 Re: Nested Set суммирование по полю  [new]
drboboev
Member

Откуда:
Сообщений: 6
LeXa NalBat,

Да, задавал, но изначально структура базы была родитель->потомок, а сейчас использую модель Nested Sets. Т.е. изначальная задача та же, но подход другой, а значит и решение уже будет другое.
7 июн 17, 08:37    [20545465]     Ответить | Цитировать Сообщить модератору
 Re: Nested Set суммирование по полю  [new]
qwwq
Member

Откуда:
Сообщений: 2638
drboboev,

приведите ваш вариант.
как признак того, что вы не балду тут пинаете.

+
в лоб, можно написать не самый оптимальный вариант, с суммой, выглядящей как коррелят из агрегата "всех потомков ветки" , подвешенной к каждому узлу "всего дерева"
оба запроса есть тут:
см. http://doc.prototypes.ru/database/trees/nestedsets/theory/use/
агрегат от второго напишете руками
очевидно, что решение не слишком оптимальное. (многократно суммируете одно и то же)

лениво разбираццо, но вот тут, у Целко кажеццо есть что--то вам нужное
http://www.ibase.ru/files/articles/programming/dbmstrees/sqltrees.html
автор
3. Add a GROUP BY and aggregate functions to these basic queries and you have hierarchical reports. For example, the total salaries that each employee controls:
 SELECT P2.emp, SUM(S1.salary)
   FROM Personnel AS P1, Personnel AS P2,
        Salaries AS S1
  WHERE P1.lft BETWEEN P2.lft AND P2.rgt
    AND P1.emp = S1.emp
  GROUP BY P2.emp;

думаю, столь же не оптимальное.

можно пуститься в настоящую рекурсию, (хранимкой, объявив результат стейбл), так и итеративную псевдорекурсию SQL [with recursive ] , стартуя от конечных ветвей==листьев. (все те ветви, у которых right=left+1)
но браться делать что--то за вас пока лениво
7 июн 17, 12:27    [20546449]     Ответить | Цитировать Сообщить модератору
 Re: Nested Set суммирование по полю  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
drboboev
LeXa NalBat,

Да, задавал, но изначально структура базы была родитель->потомок, а сейчас использую модель Nested Sets. Т.е. изначальная задача та же, но подход другой, а значит и решение уже будет другое.
Так для Nested Sets очень просто получается - без рекурсии, джоин по совпадению подстроки.

with t1(path) as (
    values
    ('1'),
    ('2'),
    ('1.3'),
    ('1.4'),
    ('2.5'),
    ('2.6'),
    ('1.3.7'),
    ('1.3.8'),
    ('1.4.9'),
    ('1.4.10'),
    ('2.5.11'),
    ('2.5.12'),
    ('2.6.13'),
    ('2.6.14'),
    ('1.15'),
    ('1.4.16')
), t2(path, val) as (
    values
    ('1.3.7', 1),
    ('1.3.8', 2),
    ('1.4.9', 3),
    ('1.4.10', 4),
    ('2.5.11', 5),
    ('2.5.12', 6),
    ('2.6.13', 7),
    ('2.6.14', 8)
select t1.path, sum(val) as val from t1 left outer join t2 on t2.path||'.' like t1.path||'.%' group by 1 order by 1;
8 июн 17, 11:01    [20549265]     Ответить | Цитировать Сообщить модератору
 Re: Nested Set суммирование по полю  [new]
qwwq
Member

Откуда:
Сообщений: 2638
LeXa NalBat
Так для Nested Sets очень просто получается - без рекурсии, джоин по совпадению подстроки.

with t1(path) as (
    values
    ('1'),
....


Trees in SQL: Nested Sets and Materialized Path
8 июн 17, 11:20    [20549329]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить