Как в запросе получить корректную сумму значений поля при ссылающей записи на эту ячейку?

Question

[groulls]

Проблема в следующем: Необходимо найти сумму значений в поле БД всех ячеек принадлежащих к определенным атрибутам(т.е для массива атрибутов создается ячейка или множество ячеек - записи в БД), при это есть связь 1:M таблицы "Ячеек" и таблица "Занятия ячейки" т.е зная id ячейки мы можем несколько раз сделать запись и на основе этого рассчитать сколько ячеек всего, сколько занято и сколько доступно.

Сейчас запрос выглядит следующим образом:

select 
s.id as Id,
sa.id_attribute as IdAtr,
sum(a.limit) as All,
sum(case when c.status_active = false then 1 else 0 end) as Fact,
sum(a.limit) - sum(case when c.status_active = false then 1 else 0 end) as Access
from public."schedule" as s
left join public."schedule_attribute" as sa on sa.id_schedule = s.id
left join public."access_cell" as a on a.id__attribute = sa.id
left join public."closed_cell" as c on c.id_access_cell = a.id
group by
s.id,
sa.id_attribute

Проблема в том, что если в свободную ячейку мы ссылаем один раз в таблицу closed_cell все хорошо, но если несколько раз, то сумма высчитывается не корректно, возрастая в зависимости от значения в поле limit таблицы access_cell.

Comments

[Slava Rozhnev]

Пожалуйста опубликуйте схему таблиц и тестовые данные

[groulls]

Slava Rozhnev, Накинул без подробностей, что бы связь была понятна.


А по поводу тестовых данных, не очень понимаю как показать, опишу:
Создаем Schedule:

{
id:1,
name : TEST,
attribute : [
{
id:1,
name: Ivan,
id_schedule: 1
},
{
id:2,
 name: Semen,
id_schedule: 1
}
]
}

Далее для атрибутов генерируются ячейки в ACCESS для каждого атрибута:

{
 id: 1,
 limit: 1,
id_attr : 1
},
{
 id: 2,
 limit: 3,
id_attr : 1
}
{
id:3,
limit:2,
id_attr : 2
}
{
id:4,
limit:4,
id_attr : 2
}

Далее мы можем эту ячейку занять добавив запись в CLOSE( сколько угодна раз по сути )

{
id:1,
status: false,
id_access: 1
}
{
id:2,
status: false,
id_access: 1
}
{
id:3,
status: false,
id_access: 3
}

Ну вот и нужно вывести 2 записи для каждого аттрибута:
nameS | nameA | LIMIT | PLAN | ACCESS
TEST Ivan 4 2 2
TEST Semen 6 1 5

Сейчас проблема в том, что если одна запись из сущности Close ссылается на Access все норм, а если 2 ссылки на одну ячейку, то SUM(Limit) уже считается не правильно.

[Slava Rozhnev]

Уже лучше :). Осталось понять что такое PLAN & ACCESS.
Пока посмотрие это:

select S.*, Atr.name AS Aname, SUM(`limit`) AS `limit`
from Schedule S
join Attribute Atr ON Atr.id_schedule = S.id
join Access Acc on Acc.id_attribute = Atr.id
group by Atr.name, S.id, S.name
;

MySQL fiddle

[groulls]

Slava Rozhnev, Ну лучше тогда не plan, а fact - факт записи, что ячейка занята, т.е у нас в close будет запись ссылающая на id сущности access и статус (bool) с помощью него будет понимать свободная она или нет.
Access - сколько сейчас доступно т.е разница между limit и fact.

Answer 1

[MaLuTkA_UA]

Извините за плохое оформление пишу с телефона, вот мой пример как это можно сделать (если я правильно понял что вам нужно)

with test as (select id_access, sum(1) as close 
			  from Close where status = 0
			 group by id_access)
select S.*, Atr.name AS Aname, SUM("limit") AS limit, 
sum( t.close), sum("limit") - sum(t.close)
from Schedule S
join Attribute Atr ON Atr.id_schedule = S.id
join Access Acc on Acc.id_attribute = Atr.id
left join test t on Acc.id = t.id_access
group by Atr.name, S.id, S.name