ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / хранимые агрегаты
13 сообщений из 13, страница 1 из 1
хранимые агрегаты
    #37601275
sergq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergq
Участник
Здравствуйте.

Задумался сделать для нескольких больших отчетов хранимые агрегаты.

Источник для отчета - две таблицы. шапка с некоторыми атрибутами и тело.
Создал таблицы для хранимых агрегатов. хранение агрегатов сделал по неделям. сформировал. вроде все ок.

Но есть одно но.
при расчете отчета используются не только две основных таблицы данных, но в зависимости от значения некоторых полей шапки необходимо джойнить другие таблицы. и соответственно проводить дополнительные вычисления.
Вот как быть с этим не знаю. В таблицах агрегатов нет этих данных.
Если их туда внести - смысл их практически пропадет.

Что посоветуете?

Спасибо
...
Рейтинг: 0 / 0
01.01.2012, 23:26
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601332
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
sergq Если их туда внести - смысл их практически пропадет.Если вы приведете примеры то вероятность внятного ответа повысится. Пока, увы, я не улавливаю смысл вашей проблемы.
...
Рейтинг: 0 / 0
02.01.2012, 04:09
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601370
sergq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergq
Участник
SERG1257,


 
исходные таблицы
Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE HEAD (
    C                        INTEGER NOT NULL,
    DATEE                    DATE,
    ATTR1 INTEGER,
    ATTR2 INTEGER,
    ATTR3 INTEGER
);



Код: sql
1.
2.
3.
4.
5.
6.
CREATE TABLE BODY (
    C                     INTEGER NOT NULL,
    HEAD_ID  INTEGER, --FK
    PEOPLE_ID            INTEGER,
    VALUE               INTEGER, -- может принимать значение от 1 до 9
);






 
таблица агрегатов
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
CREATE TABLE AGG (
    C                     INTEGER,
    DATES_ID  INTEGER, -- ссылка на недельный календарь
    PEOPLE_ID  INTEGER,
    VALUE1              INTEGER,
    VALUE2              INTEGER,
    VALUE3              INTEGER,
    VALUE4              INTEGER,
    VALUE5              INTEGER,
    VALUE6              INTEGER,
    VALUE7              INTEGER,
    VALUE8              INTEGER,
    VALUE9              INTEGER
);




все агрегаты с эту таблицу собрал. все красиво.
Но есть еще две других таблицы


 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE CORR (
    C                       INTEGER NOT NULL,
    FROM_DATE               DATE,
    TO_DATE                 DATE,
    ATTR1_ID   INTEGER, -- ссылка на справочник
    CORR_TYPE            SMALLINT,
    PEOPLE_ID    INTEGER
);

CREATE TABLE CORR_BODY (
    C                       INTEGER NOT NULL,
    CORR_ID             INTEGER,
    ATTR2_ID   INTEGER, -- ссылка на справочник
    ATTR3_ID   INTEGER -- ссылка на справочник
);




т.е. в этих двух таблицах хранятся исключения. в зависимости от CORR_TYPE для вычисления берется либо ATTR1_ID либо ATTR2_ID либо ATTR3_ID.
И в зависимости от наличия этих значений в таблице HEAD корректируются значения агрегатов.
...
Рейтинг: 0 / 0
02.01.2012, 12:50
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601450
ARTURV
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ARTURV
Участник
sergq,
Видимо Вам надо в Вашем SELECT использовать оператор CASE. Без Вашего оператора выборки трудно фантазировать
...
Рейтинг: 0 / 0
03.01.2012, 15:48
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601461
sergq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergq
Участник
ARTURV,

Это понятно что CASE нужен.

Проблема в том, что в таблице агрегатов нет полей ATTR1_ID ,ATTR2_ID, ATTR3_ID.
Если их туда внести, то смысл таблица агрегатов приблизится по объему к исходным таблицам.
...
Рейтинг: 0 / 0
03.01.2012, 16:16
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601477
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
sergqПроблема в том, что в таблице агрегатов нет полей ATTR1_ID ,ATTR2_ID, ATTR3_ID.

Нет, пролема в том, что эти поля есть в других таблицах. Прежде чем базу
денормализовывать, её надо нормализовать.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
03.01.2012, 16:28
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601577
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
Давайте разберемся, чтобы лучше понимать друг-друга
У вас есть таблицы head и body - с этим все понятно.
У вас есть таблица исключений (то что она составная это пока не важно) зависимая от PEOPLE_ID и DATEE. (ее можно считать функцией)
далее у вас есть проблемный запрос к таблицам head и body, CORR и CORR_BODY (его вы не привели, но я попробую воссоздать, если ошибусь поправьте)

Код: sql
1.
2.
3.
4.
5.
6.
7.
select sum(), sum(), count() ... from head h
join body b on h.c=b.HEAD_ID
left join corr c on c.PEOPLE_ID=b.PEOPLE_ID and h.DATEE between FROM_DATE and TO_DATE 
-- left join CORR_BODY я пока опущу для простоты
where DATEE between @dfrom and @dto
-- and ... тут еще могут быть дополнительные условия
group by week -- или DATES_ID



для решения этого запроса вы предложили таблицу AGG. И ваша таблица AGG никак не помогает улучшить проблемный запрос ибо таблица AGG должна содержать уже откорректированные значения
то есть проблемный запрос должен переписываться как
Код: sql
1.
2.
3.
4.
select sum_of_val,sum_of_val,sum_of_val
from agg
where DATES_ID=@DATES_ID
-- and ... тут еще могут быть дополнительные условия



Значит надо заменить красивую таблицу AGG на полезную для запроса таблицу AGG. Возможно у вас есть другие запросы для которых ваша таблица AGG будет полезна, и если вы их приведете то возможно мы поможем скрестить ужа с ежом и убить двоих зайцев одним выстрелом
...
Рейтинг: 0 / 0
03.01.2012, 18:50
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601626
sergq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergq
Участник
SERG1257,

корректирующие таблицы могут заполняться как после основных, так и до.
т.е. коррекция может быть внесена еще до основных данный.

Получается что в триггерах на основных таблицах надо сразу проверять на исключения,
и в триггерах на таблицах исключений проверять основные таблицы и если это исключение какие либо данные затрагивает,
то сразу корректировать агрегаты?
Правильно мыслю?
...
Рейтинг: 0 / 0
03.01.2012, 20:02
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601648
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
sergq Правильно мыслю?Давайте танцевать от печки (то бишь от исходного запроса). Исходный запрос как и запрос к производной таблице должны давать ОДИНАКОВЫЕ результаты независимо от того до или после основных заполняются корректирующие.

В идеале это должна быть материализуемая (индексируемая) вьюха (с автоматическим пересчетом в случае изменений) и будет совсем хорошо, если СУБД сама догадается использовать вьюху переписав основной запрос. К сожалению, это далеко не всегда возможно, так что хочешь сделать что-либо хорошо сделай это сам.

sergq Получается что в триггерах на основных таблицах надо сразу проверять на исключения,
и в триггерах на таблицах исключений проверять основные таблицы и если это исключение какие либо данные затрагивает,
то сразу корректировать агрегаты?
решение на триггерах имеет право на жизнь. К сожалению, если триггер отключен, то у вас нет абсолютно никаких гарантий , что производная таблица консистентна с базовыми. Крайне рекомендую завести джоб который будет проверять (или пересчитывать) расхождения в период наименьшей нагрузки системы и высылать их (расхождения) по емайлу.
...
Рейтинг: 0 / 0
03.01.2012, 20:36
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601657
sergq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergq
Участник
SERG1257,

собственно две части исходного запроса


соединение корректирующих таблиц
Код: sql
1.
2.
3.
left join corr on corr.people_id = body.people_id_id and header.datee between corr.from_date and corr.to_date
left join corr_body on corr_body.corr_id = corr.c and (corr_body.attr1 = hear.attr1)
left join sp on sp.c= corr_body.sp_id and sp.attr2 = head.attr2 -- ссылка на справочник




и case который считает сумму отклонений.
Слегка корявый но работает )

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
,sum( case when ( case when corr.c is null then case when body.m_id = 6 then 100 else 0 end else

  case when corr.corr_type = 4 then case when head.attr2 = sp.attr2 and body.m_id = 6 then 1 else 100 end else
  case when corr.corr_type = 1 then case when body.m_id = 6 then 1 else 100 end else
  case when corr.corr_type = 2 then case when head.attr3 = corr.attr3 and body.m_id = 6 then 1 else 100 end else
  case when corr.corr_type = 3 then case when head.attr4 = corr_body.attr4 and body.m_id = 6 then 1 else 100 end

end end end end end) = 1 then 1 else 0 end)
...
Рейтинг: 0 / 0
03.01.2012, 20:57
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601699
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
sergq соединение корректирующих таблицНесмотря на неточности (вызваные опечатками, а не желанием запутать) выходит в главном я был прав.
1 Не вносите дополнительные поля в производную таблицу, чтобы рассчитывать конечное значение "на лету" - вместо этого рассчитайте конечное значение
Код: sql
1.
sum( case when ( case when ...

сразу, (кстати хитрое выражение лучше оформить в виде функции)
2 Вносите дополнительные поля, только если есть другие запросы которым требуются похожие данные или другие фильтры для отбора данных.
3 Выбранный период агрегации (неделя) как-то обоснован? Я к тому, что он не бьется с месяцами и годами - гораздо более распространенными.
...
Рейтинг: 0 / 0
03.01.2012, 23:00
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601706
sergq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergq
Участник
SERG1257sergq соединение корректирующих таблицНесмотря на неточности (вызваные опечатками, а не желанием запутать) выходит в главном я был прав.
1 Не вносите дополнительные поля в производную таблицу, чтобы рассчитывать конечное значение "на лету" - вместо этого рассчитайте конечное значение
Код: sql
1.
sum( case when ( case when ...

сразу

вот тут не понял )
Сразу это когда? в самом отчете?
Тогда придется от хранимых отказываться.


А неделя обоснована. это минимальный интервал за который делают этот отчет.
...
Рейтинг: 0 / 0
03.01.2012, 23:11
| Ответить | Цитировать | Написать  
хранимые агрегаты
    #37601718
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
sergq вот тут не понял )
Сразу это когда? в самом отчете?Сразу, это значит рассчитать в производной таблице. А в отчете просто запросить.

sergq А неделя обоснована. это минимальный интервал за который делают этот отчет. Тогда хозяин - барин.
Но если потребуется вывести тоже самое за месяц или год, то придется делать еще одну зависимую таблицу или хитрить с union all запросом из базовой таблицы для первой неполной недели + union all целые недели + запрос из базовой таблицы на последнюю неполную неделю.
...
Рейтинг: 0 / 0
03.01.2012, 23:36
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / хранимые агрегаты
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=32&fpage=54&tid=1541873]:
 0ms
get settings:
 10ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 40ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 28ms
get tp. blocked users:
 1ms
others: 227ms
total:  330ms
созд. / загр.: 330ms
x
x
Закрыть


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