Иерархическая зависимость значений

158
03 июля 2019, 10:10

Подскажите, как можно решить проблему: Есть таблица table1:

Id      Group_id    Date        Val1    Val2
1       2           02.03.2018  9       4
2       1           02.03.2018  5       2
3       2           03.03.2018  6       1
4       1           02.03.2018  2       7
5       3           04.03.2018  3       6
6       1           05.03.2018  2       6
7       2           05.03.2018  3       5
8       3           04.03.2018  4       7

на базе которой триггерами заполняется табличка table2, в которой хранится расчетная, сгруппированная по group_id и отсортированная по датам (через параметр Seq, который обозначает порядковый номер в группе) информация:

Id      Group_id    Seq         Date            Summ
2       1           1           02.03.2018      1   
5       3           1           04.03.2018      2
1       2           1           02.03.2018      1
3       2           2           03.03.2018      3
4       1           2           02.03.2018      5
6       1           3           05.03.2018      7
7       2           3           05.03.2018      5
8       3           2           04.03.2018      3

Суть таких действий в том, что Summ в каждой группе имеет иерархическую зависимость, то есть зависит от значения в прошлой строке в этой группе. Сама же проблема заключается в том, что пользователь имеет возможность вводить данные за рандомные даты и изменять у уже существующих записей group_id и Date. Как я представляю действия - если изменилась дата/group_id в table1, то такую запись нужно удалить в table2, пересчитать все послеидущие значения и вставить запись с новыми значениями, с нужным Seq и пересчитать все послеидущие значения. Но как это реализовать не понимаю. Или есть какие-то другие варианты решения?

Answer 1

Наличие поля seq во второй таблице сильно усложняет логику, так как надо его постоянно пересчитывать. При том что в таблице есть другие поля, на основе которых порядок однозначно ясен. (По переписке в комментариях пришли к выводу что однозначный порядок задан полями date, id).

После того как уберем поле seq обновлять таблицу становится гораздо проще. При изменении записи по какой либо группе нам не надо пересчитывать вообще все кумулятивные значения по ней. Реально должны изменится лишь те значения, которые по датам/id идут позже изменяемой. Вычислять их значения с нуля опять же не надо, можно просто к всем записям после текущей добавить (или вычесть) дельту, на сколько они изменяются в результате операции.

Таким образом в триггере для добавляемой записи надо найти в table2 значение из предыдущей записи (select summ from Table2 where group_id=NEW.group_id order by date desc, id desc limit 1), сохранить текущую запись в table2 с значением текущей строки плюс полученная сумма. Добавить значение текущей строки ко всем последующим:

update table2
   set summ=summ+NEW.VAL
 where group_id=NEW.GROUP_ID and date>=NEW.DATE
   and ( (date=NEW.DATE and id>NEW.ID)
      OR date>NEW.DATE )

При удалении записи еще проще, удалить ее из Table2 после чего точно так же вычесть ее значение из всех последующих.

READ ALSO
jdbc over ssh example
MySQL

jdbc over ssh example

Как подключиться к mysql через ssh по jdbc? Желательно с примерами кода

 123
Вставить данные из MySQL в listbox
MySQL

Вставить данные из MySQL в listbox

Как вставить данные из БД в listbox не нажимая ни на какие кнопки, а при открытии окна?При открытии этого окна в форму под текстом "Выбор материала"...

 125
MySQL проверить наличие данных в одной из двух таблиц
MySQL

MySQL проверить наличие данных в одной из двух таблиц

Имеется две таблицы - messages и commentsВ одной из этих таблиц есть строка, у которой content == 'hello'

 160
Переписать запросы из sqlite to my sql
MySQL

Переписать запросы из sqlite to my sql

2update users set data =datetime('now','localtime') where userId=" + chat_id + "

 139