Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Иерархия и суммирование полей / 8 сообщений из 8, страница 1 из 1
17.02.2017, 15:37
    #39406723
drboboev
drboboev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Иерархия и суммирование полей
Доброго времени суток.

Проектирую базу для учёта продаж. Есть иерархия торговых точек по типу родитель-потомок (id, name, pid) и в отдельной таблице хранятся записи о продажах по датам (по одной записи для одной ТТ в день). Структура таблицы продаж - tt_id, amount, date. Записи записываются в БД только на нижнем уровне иерархии, т.е. для каждой ТТ отдельно записываются значения, и потом при выборке из БД суммируются на уровне родителя. Пример: Есть 5 ТТ и по каждой ТТ в БД имеется запись о продажах. Эти 5 ТТ входят в определённую группу, например Группа1. Когда я делаю выборку из БД, я суммирую записи из БД и показываю пользователю общую сумму продаж по Группе1 и так далее до верхнего уровня иерархии. Возможно есть более правильный способ хранения данных, если да, то просьба подсказать.

Использую СУБД PostgreSQL, сразу говорю, что с этой СУБД знакомство начал недавно, до этого работал с MySQL.

Задача стоит следующая:

Сейчас у меня выполняется очень много запросов к БД, что не есть хорошо. Хочу как то оптимизировать выборку, чтобы выполнялось как можно меньше запросов.

Поиск в гугле вывел на WITH RECURSIVE. В итоге получилось выбрать иерархию и суммировать данные одним запросом, но только на нижнем уровне. На уровнях выше суммировать не получается, никак не могу разобраться как именно это можно сделать без циклических обращений к БД из PHP.

Надеюсь на Вашу помощь, укажите в каком направлении копать, реальный пример был бы шикарным подарком.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.02.2017, 09:32
    #39407627
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Иерархия и суммирование полей
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
with recursive t1(id, parent_id) as (
    values
    (1, null),
    (2, null),
    (3, 1),
    (4, 1),
    (5, 2),
    (6, 2),
    (7, 3),
    (8, 3),
    (9, 4),
    (10, 4),
    (11, 5),
    (12, 5),
    (13, 6),
    (14, 6)
), t2(id, val) as (
    values
    (7, 1),
    (8, 2),
    (9, 3),
    (10, 4),
    (11, 5),
    (12, 6),
    (13, 7),
    (14, 8)
), t3(id, val) as (
    select id, val from t2
    union all
    select parent_id, val from t3 natural join t1
)
select id, sum(val) as val from t3 group by 1 order by 1;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.02.2017, 11:27
    #39407696
drboboev
drboboev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Иерархия и суммирование полей
LeXa NalBat,

спасибо. всё работает.

ещё один вопрос: а как добавить условие, чтобы отбирались только те записи, которые подчиняются какому то конкретному родителю. сейчас отбираются все записи из бд, а как например вывести только тех, кто подчиняется id=1 ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.02.2017, 20:38
    #39408132
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Иерархия и суммирование полей
drboboevтех, кто подчиняется id=1 ?
Код: plsql
1.
... from t2 WHERE id=1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2017, 09:21
    #39408284
drboboev
drboboev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Иерархия и суммирование полей
ora601drboboevтех, кто подчиняется id=1 ?
Код: plsql
1.
... from t2 WHERE id=1



Данных не найдено. :-(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2017, 11:26
    #39408382
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Иерархия и суммирование полей
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
with recursive t1(id, parent_id) as (
    values
    (1, null),
    (2, null),
    (3, 1),
    (4, 1),
    (5, 2),
    (6, 2),
    (7, 3),
    (8, 3),
    (9, 4),
    (10, 4),
    (11, 5),
    (12, 5),
    (13, 6),
    (14, 6)
), t2(id, val) as (
    values
    (7, 1),
    (8, 2),
    (9, 3),
    (10, 4),
    (11, 5),
    (12, 6),
    (13, 7),
    (14, 8)
), t3(id, val) as (
    select id, val from t2
    union all
    select parent_id, val from t3 natural join t1
), t4(id) as (
    select 1 as id
    union all
    select t1.id from t4 join t1 on t4.id is not distinct from t1.parent_id
)
select t3.id, sum(val) as val from t3 join t4 on t3.id is not distinct from t4.id group by 1 order by 1;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.02.2017, 11:17
    #39410704
drboboev
drboboev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Иерархия и суммирование полей
LeXa NalBat,

Всё работает отлично, за исключением одного момента - запрос не выбирает тех, у кого нет значений. Попытался сам, но в итоге перестаёт работать условие выборки по конкретному id. Можно ли добавить те id у которых нет значений в бд?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.02.2017, 12:18
    #39411502
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Иерархия и суммирование полей
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
with recursive t1(id, parent_id) as (
    values
    (1, null),
    (2, null),
    (3, 1),
    (4, 1),
    (5, 2),
    (6, 2),
    (7, 3),
    (8, 3),
    (9, 4),
    (10, 4),
    (11, 5),
    (12, 5),
    (13, 6),
    (14, 6),
    (15, 1),
    (16, 4)
), t2(id, val) as (
    values
    (7, 1),
    (8, 2),
    (9, 3),
    (10, 4),
    (11, 5),
    (12, 6),
    (13, 7),
    (14, 8)
), t3(id, val) as (
    select id, val from t2
    union all
    select parent_id, val from t3 natural join t1
), t4(id) as (
    select 1 as id
    union all
    select t1.id from t4 join t1 on t4.id is not distinct from t1.parent_id
)
select t4.id, sum(val) as val from t4 left join t3 on t4.id is not distinct from t3.id group by 1 order by 1;


Всего лишь использовать OUTER JOIN? 7.2.1.1. Joined Tables
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Иерархия и суммирование полей / 8 сообщений из 8, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 1131ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1996690]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 643ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 428ms
get tp. blocked users:
 1ms
others: 15ms
total:  1131ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]