Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Новый топик    Ответить
 Запрос с множественными комбинациями в условии  [new]
Semen Popov
Member

Откуда: Сыктывкар
Сообщений: 793
Здравствуйте всем!
IBM DB2 WSE 9.7

В базе есть таблицы:

1. REGISTER (IDREG as bigint, IDNSI1 as bigint, IDNSI2 as bigint, IDNSI3 as bigint, IDNSI4 as bigint, IDNSI5 as bigint, IDNSI6 as bigint, IDNSI7 as bigint и другие)
2. NSI1 (IDNSI1 as bigint, NAM1 as varchar(100))
3. NSI2 (IDNSI2 as bigint, NAM2 as varchar(100))
4. NSI3 (IDNSI3 as bigint, NAM3 as varchar(100))
5. NSI4 (IDNSI4 as bigint, NAM4 as varchar(100))
6. NSI5 (IDNSI5 as bigint, NAM5 as varchar(100))
7. NSI6 (IDNSI6 as bigint, NAM6 as varchar(100))
8. NSI7 (IDNSI7 as bigint, NAM7 as varchar(100))

Необходимо подсчитать количество записей из REGISTER, например в 3 колонки, где для каждой из колонок заданы определенные условия NAM из разных NSI. Например, вычислить количество P1, P2, P3, где
в P1 должны подсчитываться записи с комбинациями
(NAM1, NAM2, NAM4)=('1VAL1', '1VAL2',' 1VAL4') и
(NAM1, NAM5, NAM6, NAM7)=('2VAL1', '2VAL5',' '2VAL6','2VAL7') и
(NAM3, NAM4, NAM5)=('3VAL3', '3VAL4',' 3VAL5') и другие,
в P2 должны подсчитываться записи с комбинациями
(NAM1, NAM2, NAM4, NAM7)=('1VAL1', '1VAL2',' 1VAL4','1VAL7') и
(NAM1, NAM5, NAM6)=('2VAL1', '2VAL5', '2VAL6') и
(NAM3, NAM4, NAM5, NAM6)=('3VAL3', '3VAL4',' 3VAL5', '3VAL6') и другие,
в P3 - еще один набор комбинации
и т.д.

Мне, кроме как применять конструкции case when, больше не видится как реализовать это запрос. Но в этом случае запрос становится громадным и наверно не оптимальным.
select SUM(case when () then 1 else 0) as P1,
           SUM(case when () then 1 else 0) as P2,
           SUM(case when () then 1 else 0) as P3,
from REGISTER as r
left join NSI1 as n1 on r.IDNSI1=n1.IDNSI1
left join NSI2 as n2 on r.IDNSI2=n2.IDNSI2
left join NSI3 as n3 on r.IDNSI3=n3.IDNSI3
left join NSI4 as n4 on r.IDNSI4=n4.IDNSI4
left join NSI5 as n5 on r.IDNSI5=n5.IDNSI5
left join NSI6 as n6 on r.IDNSI6=n6.IDNSI6
left join NSI7 as n7 on r.IDNSI7=n7.IDNSI7


Подскажите, может есть более красивый и оптимальный способ?


С уважением, Семен Попов
30 сен 16, 16:27    [19729688]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с множественными комбинациями в условии  [new]
Semen Popov
Member

Откуда: Сыктывкар
Сообщений: 793
Есть идея. Все возможные комбинации условий запихнуть во временную таблицу CONDITIONS (IDCON, IDNSI1, IDNSI2, IDNSI3, IDNSI4, IDNSI5, IDNSI6, IDNSI7). В тех полях, что допускают любые значения, внести значение 0 или null.
А затем как-то ее наложить на REGISTER. Возможно, написать пользовательскую функцию ISCOND (CONNUM), которая бы сверяла вхождения CONDITIONS в записи REGISTER и возвращала значение 0 или 1. Временная таблица с ID еще хороша тем, что при подсчете сверяются числовые значения. Работа с числами всегда быстрее, чем со строками.

Тогда запрос превратился бы в нечто иное
select count (ISCOND(1)), count (ISCOND(2)), count (ISCOND(3))
from REGISTER


Скажите, правильно ли я мыслю?
3 окт 16, 12:10    [19736792]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с множественными комбинациями в условии  [new]
Semen Popov
Member

Откуда: Сыктывкар
Сообщений: 793
Semen Popov
...
Тогда запрос превратился бы в нечто иное
select count (ISCOND(1)), count (ISCOND(2)), count (ISCOND(3))
from REGISTER



Нет. Запрос будет выглядеть так
select sum (ISCOND(1)), sum (ISCOND(2)), sum (ISCOND(3))
from REGISTER
3 окт 16, 16:10    [19738143]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с множественными комбинациями в условии  [new]
CawaSPb
Member

Откуда: Питер/Москва/Wroclaw
Сообщений: 1010
Semen Popov,

Оптимальность не бывает "подвешена в воздухе".
- Каковы вообще предполагаемые объёмы данных? Тысячи строк, миллионы, сотни миллионов, больше? Может вообще овчинка выделки не стоит и как ни делай, всё равно.

- Каковы относительные объёмы? Можно предположить, что таблицы NISx - "справочники" с относительно малым объёмом (единицы строк, тысячи, десятки, миллионы), а вот "REGISTER" - таблица фактов несравнимо большего объёма.

- Являются ли bigint значения в таблицах NISx первичными ключами ключами? Уникальны ли NAMx в рамках своих таблиц?

- Являются приведённые запросы - "профильной" нагрузкой? Т.е. надо ли строить всё преимущественно под них.

- Есть ли наиболее часто используемые группы в наборах (NAMx, NAMy, NAMz, ...) или это под какую-нибудь аналитику/дата-майнинг, прости господи, чтобы данные со всех сторон вертеть? (Они в имеющихся решениях чаще вертятся уже полностью выгруженные на клиент)

- Как обновляются таблицы?


Далее в предположении, что данных много.

Приведённые Вами способы выразить запрос приведут к полному построению ("развёртыванию") имеющейся конструкции (этакая денормализация в одну таблицу). Второй способ не до конца ясен, пока Вы не привели код ISCOND(x) ф-и, но ситуфция, полагаю, будет схожей.
СУБД не настолько "умна", чтобы развернуть логику внутри SUM, чтобы исключить из построения часть строк ("понимая", что 0 не повлияет на сумму, да и вообще как-то соображая, как тот 0 может получиться).
Ситуация подобна выборке по предикату "where F(column_X) = ?". Сколько ни строй индексов по column_X, потребуется просканировать всю таблицу, построить все результаты и произвести сравнение. Ровно потому, что в общем случае (если Вы сталкивались с теорией алгоритмов) машинным образом построить обратный алгоритм и преобразовать предикат в "where column_X = F1(?)" невозможно (и не всегда такая обратная ф-я вообще есть).
/* Может быть не совсем так с INLINED SQL ф-ей, и есть костыль в виде функционального индекса */

Давайте теперь посмотрим, что будет происходить.
Это будет full scan по самой большой таблице (REGISTER?) с допустим миллионом записей с последующим объединением с NISx с пусть хотя бы по 1000 записей.
Так или иначе объединяя записи (просто проверяя условие объединения) потребуется перелопатить:
1000000*1000*1000*1000*1000*1000*1000*1000 строк, оставить тот же 1000000 и физически сформировать гораздо более широкую со строками вместо bigint, потом пройтись этому 1000000 строк для вычисления окончательного результата.
Это будет долго. Очень долго.


Вообще говоря, приведённая структура таблиц более менее стандартна для хранилищ (если верно предположение о характере и относительных объёмах таблиц). СУБД хорошо с ними работают. Только пишите более понятно для оптимизатора (предполагая, что NAMx уникальны в NSIx):
select 
  'P1' as NAME
 ,count(*)
from REGISTER
where IDNSI1 = (select IDNSI1 from NSI1 where NAM1='1VAL1')
  AND IDNSI2 = (select IDNSI2 from NSI2 where NAM2='1VAL2')
  AND IDNSI4 = (select IDNSI4 from NSI4 where NAM4='1VAL4')
union all
select 
  'P2' as NAME
 ,count(*)
from REGISTER
where IDNSI1 = (select IDNSI1 from NSI1 where NAM1='2VAL1')
  AND IDNSI5 = (select IDNSI5 from NSI5 where NAM5='2VAL5')
  AND IDNSI6 = (select IDNSI6 from NSI6 where NAM6='2VAL6')
  AND IDNSI7 = (select IDNSI7 from NSI7 where NAM7='2VAL7')
select 
  'P3' as NAME
...

Или вообще выполняйте эти запросы по отдельности.
Все запросы к NISx выродятся в несколько коротких look-up'ов, далее зависит от индексов на REGISTER. В худшем случае просто full-scan таблицы. В лучшем - INDEX scan(s) и динамическое построение bitmap index'а или просто scan по региону индекса.

Можете оставить:
select 'P1' as NAME, count(*)
from REGISTER as r
  join NSI1 as n1 on r.IDNSI1=n1.IDNSI1
  join NSI2 as n2 on r.IDNSI2=n2.IDNSI2
  join NSI4 as n4 on r.IDNSI4=n4.IDNSI4
where (NAM1, NAM2, NAM4)=('1VAL1', '1VAL2',' 1VAL4')

Оптимизатор развернёт эту констркцию как надо (но всё-таки всегда проверяйте получившийся план).
Только пожалуйста, используйте в данном случае просто join, чтобы как раз отфильтровать записи из REGISTER и выбросите лишние, чтобы отбросить ненужную фильтрацию по наличию записей в неиспользуемых в предикате "таблиц-справочников".

В качестве упражнения - оформить несколько запросов в виде Common Table Expression (CTE) и собрать результат в одну строку ;)

Как мне представляется - неверный посыл с Вашей стороны - попытка оформить всё в таком виде, чтобы при выполнении осущесвлялся только один проход по таблице.


PS Если данных _действительно_ много, то есть ещё такая вещь, как BLU Acceleration (но не в 9.7).
В общем, вам действительно надо или абстрактный интерес?
4 окт 16, 19:29    [19743580]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с множественными комбинациями в условии  [new]
Semen Popov
Member

Откуда: Сыктывкар
Сообщений: 793
CawaSPb, спасибо за развернутые пояснения. Попробую ответить на Ваши вопросы.

1. количество записей в REGISTER - ~ 1,6 млн. Но в таблице присутствует поле DATREG (дата регистрации), и в отчет будут задаваться дополнительные условия ограничения по этой дате (период с и по). Поэтому в итоге подсчет будет выполняться по меньшему количеству записей - ~500-600 тыс.

2. количество справочников (NSI) не 7, а 8. Но это наверно сути не меняет. Число записей в них разное, от 100 до 500. NAM внутри одного справочника может повторяться. Ограничений на уникальность NAM не накладывается. Наложена уникальность только по IDNSI (первичный ключ).

3. разрабатываемый запрос не является "профильной загрузкой". Обращение к этим цифрам будет непостоянное. Раз в неделю или вообще раз в месяц.

4. Обновление NSI происходит редко. В таблицу REGISTER в основном происходит вставка записей (ежедневно до 1000). Изменение записей редко.

5. В условиях можно выделить пересечение в комбинациях, но только в пределах подсчета одного показателя. Например, для P1
должны подсчитываться записи с комбинациями
(NAM3, NAM4, NAM5)=('3VAL1', '4VAL1',' 5VAL1') и
(NAM3, NAM4, NAM5, NAM6)=('3VAL1', '4VAL2',' '5VAL2','6VAL1') и
(NAM3, NAM4, NAM5)=('3VAL1', '4VAL3',' 5VAL3')
В P2 и P3 тоже можно найти пересечения только внутри показателя.

В целом предполагаю, что тут в любом случае будет полный скан таблицы. И реализация будет сводится к тому, чтобы сократить предварительное количество записей, например для P1 условие where (D1<=DATREG and DATREG<=D2) and NAM3='3VAL1', а подсчет уже проводить по полученному массиву. Но в этом случае придется для каждого показателя свои условия where. То есть вообще разбить на несколько под запросов по одной и той же таблице. Ну и подумать над алгоритмом поиска записи по подходящим параметрам, чтобы тот был максимально производительным.

Про функцию я говорил и вижу примерный результат:

+ Как-то так
-- отбрасываем таблицу условий подсчета
drop table CONDITIONS@

-- создаем таблицу для сохранения условий подсчета
create table CONDITIONS(
 COND integer not null,
 IDNSI1 bigint not null default 0,
 IDNSI2 bigint not null default 0,
 IDNSI3 bigint not null default 0,
 IDNSI4 bigint not null default 0,
 IDNSI5 bigint not null default 0,
 IDNSI6 bigint not null default 0,
 IDNSI7 bigint not null default 0,
 IDNSI8 bigint not null default 0
)@

-- создаем индекс по полю COND
create index CONDS_IDX on CONDITIONS (COND)@

-- заполняем таблицу условий
begin

-- Условие 1 подсчета для графы 3
insert into CONDITIONS
(COND,
 IDNSI1,
 IDNSI2,
 IDNSI3,
 IDNSI4,
 IDNSI5,
 IDNSI6,
 IDNSI7,
 IDNSI8)
select 
 1,
 c1.ID,c2.ID,c3.ID,c4.ID,c5.ID,c6.ID,c7.ID,c8.ID
from 
(select ID from table (values (0)) as e(ID)) as c1 
cross join
(select ID from table (values (0)) as e(ID)) as c2
cross join
(select ID from NSI3 where NAM='3VAL1') as c3
cross join
(select ID from NSI3 where NAM='4VAL1') as c4
cross join
(select ID from NSI3 where NAM='5VAL1') as c5
cross join
(select ID from NSI3 where NAM='6VAL1') as c6
cross join
(select ID from NSI3 where NAM='7VAL1') as c7
cross join
(select ID from table (values (0)) as e(ID)) as c8;

-- Другие условия подсчета
...
end@

create or replace function F_ISCOND (F_COND integer,
 F_IDNSI1 bigint,
 F_IDNSI2 bigint,
 F_IDNSI3 bigint,
 F_IDNSI4 bigint,
 F_IDNSI5 bigint,
 F_IDNSI6 bigint,
 F_IDNSI7 bigint,
 F_IDNSI8 bigint)
returns integer
reads sql data
no external action
begin 
 declare f_retval integer default 0;
 for cond_ as (select * from CONDITIONS where COND=F_COND)
 do
    case 
    when cond_.IDNSI8>0 then
	    if (cond_.IDNSI8<>F_IDNSI8) then
                set f_retval=0;
		goto next;
            end if;   
    when cond_.IDNSI7>0 then
	    if (cond_.IDNSI7<>F_IDNSI7) then
                set f_retval=0;
		goto next;
            end if;   
    when cond_.IDNSI6>0 then
	    if (cond_.IDNSI6<>F_IDNSI6) then
                set f_retval=0;
		goto next;
            end if;   
    when cond_.IDNSI5>0 then
	    if (cond_.IDNSI5<>F_IDNSI5) then
                set f_retval=0;
		goto next;
            end if;   
    when cond_.IDNSI4>0 then

    when cond_.IDNSI3>0 then

    when cond_.IDNSI2>0 then

    when cond_.IDNSI1>0 then

    end case;
    set f_retval=1;
    goto exit;
 next:
 end for;
 exit:

 return f_retval;
end@

-- Расчет показателя P1
select count(*) as TOTAL, 
           sum(F_ISCOND(1,IDNSI1,IDNSI2,IDNSI3,IDNSI4,IDNSI5,IDNSI6,IDNSI7,IDNSI8)) as P1
from REGISTER
where (D1<=DATREG and DATREG<=D2)@

connect reset@
5 окт 16, 11:37    [19745342]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с множественными комбинациями в условии  [new]
Semen Popov
Member

Откуда: Сыктывкар
Сообщений: 793
Как оказалось, скрипт ниже работает быстрее, чем тот, что я предложил выше (через функцию)
with pr as (
 select r.IDREG as RID,
        r.DATREG as DATREG,
        n1.NAME as N1NAM,
        n2.NAME as N1NAM,
        n3.NAME as N1NAM,
        n4.NAME as N1NAM,
        n5.NAME as N1NAM,
        n6.NAME as N1NAM,
        n7.NAME as N1NAM,
        n8.NAME as N1NAM
from REGISTER as r
left join NSI1 as n1 on r.IDNSI1=n1.IDNSI1
left join NSI2 as n2 on r.IDNSI2=n2.IDNSI2
left join NSI3 as n3 on r.IDNSI3=n3.IDNSI3
left join NSI4 as n4 on r.IDNSI4=n4.IDNSI4
left join NSI5 as n5 on r.IDNSI5=n5.IDNSI5
left join NSI6 as n6 on r.IDNSI6=n6.IDNSI6
left join NSI7 as n7 on r.IDNSI7=n7.IDNSI7
left join NSI8 as n8 on r.IDNSI8=n8.IDNSI8
where r.DATREG between ? and ?
     and n5.NAME='VAL1' )
select 
  'P1' as NAME
 ,count(*)
from pr
where N1NAM='1VAL1'
          AND N2NAM='1VAL2')
          AND N4NAM='1VAL4')
union all
select 
  'P2' as NAME
 ,count(*)
from pr
where N1NAM='2VAL1')
  AND N6NAM6='2VAL6')
  AND N7NAM='2VAL7')
select 
  'P3' as NAME
...
18 окт 16, 11:38    [19793796]     Ответить | Цитировать Сообщить модератору
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Ответить