Firebird 2.5. Группировка и вложенный запрос.

[Антон Ю.Б.]

Коллеги, не сталкивался ли кто с подобным? Посоветует ли кто, что можно сделать?

Firebird 2.5

Мне надо в некоторой таблице сгруппировать записи по набору полей и для каждой группы получить список ключей записей этой группы (ну и для технического упрощения - любой ключ из этой группы отдельно). Для этого составил такой запрос:

select MIN(ID_) ID_, list(ID_) GROUP_ID_ from
(select M1.ID_, M1.bcch_, m1.mcc_, m1.mnc_, m1.lac_, m1.cid_
from MAINTABLE M1 left join maintable M2
on M1.bcch_=M2.bcch_ and M1.lac_=M2.lac_ AND M1.mnc_=M2.mnc_
and M1.mcc_=M2.mcc_ and M1.cid_=M2.cid_ AND M1.GSM_TYPE_=M2.GSM_TYPE_
where M2.ID_ in (:ID_))
group BY BCCH_, MCC_, MNC_, LAC_, CID_

Выполняю этот запрос с параметрами (: ID_ меняю на 198,186,194,191,182,184,190,187,185 ,200,189,199,188,193,183,196,192,19 5,197) и получаю такой результат:

ID_  GROUP_ID_
188  188,189,199,202,205,188,189,199,202,205,188,189,199,202,205                                                      
183  183,193,204,206,183,193,204,206                                                                                  
196  196,207                                                                                                          
192  192,195,208,192,195,208                                                                                          
197  197,209                                                                                                          
186  186,191,194,198,201,203,210,186,191,194,198,201,203,210,186,191,194,198,201,203,210,186,191,194,198,201,203,210  
182  182,184,187,190,211,182,184,187,190,211,182,184,187,190,211,182,184,187,190,211                                  
185  185,212                                                                                                          
200  200,213,214

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

Результаты вложенного запроса - они, конечно, избыточны для поставленной задачи, убираем (distinct) эту избыточность и смотрим результаты вложенного запроса:

ID_  BCCH_   MCC_  MNC_  LAC_    CID_
186  10*788   250    99  34*801   200  
191  10*788   250    99  34*801   200  
194  10*788   250    99  34*801   200  
198  10*788   250    99  34*801   200  
201  10*788   250    99  34*801   200  
203  10*788   250    99  34*801   200  
210  10*788   250    99  34*801   200  
182  10*788   250    99  34*801   201  
184  10*788   250    99  34*801   201  
187  10*788   250    99  34*801   201  
190  10*788   250    99  34*801   201  
211  10*788   250    99  34*801   201  
185  10*788   250    99  34*801   207  
212  10*788   250    99  34*801   207  
200  10*788   250    99  34*801   213  
213  10*788   250    99  34*801   213  
214  10*788   250    99  34*801   213  
188  10*762   250     1  53*000   404  
189  10*762   250     1  53*000   404  
199  10*762   250     1  53*000   404  
202  10*762   250     1  53*000   404  
205  10*762   250     1  53*000   404  
183  10*762   250     1  53*000   702  
193  10*762   250     1  53*000   702  
204  10*762   250     1  53*000   702  
206  10*762   250     1  53*000   702  
196  10*762   250     1  53*000   707  
207  10*762   250     1  53*000   707  
192  10*762   250     1  53*000   708  
195  10*762   250     1  53*000   708  
208  10*762   250     1  53*000   708  
197  10*762   250     1  53*000   806  
209  10*762   250     1  53*000   806

Несложно видеть, что группы вполне соответствуют уникальным частям списков из результатов первого запроса. Так что правим вложенный запрос в исходном (добавляем distinct) и смотрим:

ID_  GROUP_ID_
182  182        
183  183        
184  184        
185  185        
186  186        
187  187        
188  188,189    
190  190        
191  191        
192  192        
193  193        
194  194        
195  195        
196  196        
197  197        
198  198        
199  199        
200  200        
201  201        
202  202        
203  203        
204  204        
205  205        
206  206        
207  207        
208  208        
209  209        
210  210        
211  211        
212  212        
213  213,214

Результат, мягко говоря, не соответствует ожиданиям. Вопрос - почему? И как добиться ожидаемого? Базу прилагаю.

[Антон Ю.Б.]

Прошу админов не рассматривать как даблпост (новые аспекты и т.д.). Итак, задача для практического применения решается (не освежил в памяти описание ф-ии list):

select MIN(ID_) ID_, list(distinct ID_) GROUP_ID_ from
(select M1.ID_, M1.bcch_, m1.mcc_, m1.mnc_, m1.lac_, m1.cid_
from MAINTABLE M1 left join maintable M2
on M1.bcch_=M2.bcch_ and M1.lac_=M2.lac_ AND M1.mnc_=M2.mnc_
and M1.mcc_=M2.mcc_ and M1.cid_=M2.cid_ AND M1.GSM_TYPE_=M2.GSM_TYPE_
where M2.ID_ in (198,186,194,191,182,184,190,187,185,200,189,199,188,193,183,196,192,195,197)
)
group BY BCCH_, MCC_, MNC_, LAC_, CID_

Но нам то важна не только практика, но и понимание теории, а вот с этим оказалось еще более проблем - внесем во вложенный запрос всего лишь сортировку:

select MIN(ID_) ID_, list(distinct ID_) GROUP_ID_ from
(select M1.ID_, M1.bcch_, m1.mcc_, m1.mnc_, m1.lac_, m1.cid_
from MAINTABLE M1 left join maintable M2
on M1.bcch_=M2.bcch_ and M1.lac_=M2.lac_ AND M1.mnc_=M2.mnc_
and M1.mcc_=M2.mcc_ and M1.cid_=M2.cid_ AND M1.GSM_TYPE_=M2.GSM_TYPE_
where M2.ID_ in (198,186,194,191,182,184,190,187,185,200,189,199,188,193,183,196,192,195,197)
order by ID_
)
group BY BCCH_, MCC_, MNC_, LAC_, CID_

ID_  GROUP_ID_
182  182        
183  183        
184  184        
185  185        
186  186        
187  187        
188  188,189    
190  190        
191  191        
192  192        
193  193        
194  194        
195  195        
196  196        
197  197        
198  198        
199  199        
200  200        
201  201        
202  202        
203  203        
204  204        
205  205        
206  206        
207  207        
208  208        
209  209        
210  210        
211  211        
212  212        
213  213,214

Я так понимаю, что с этим вопросом надо стучаться к Firebird Foundation

[soleil@mmc]

чтобы ответить на твой вопрос - нужно знать хотя бы как выглядят данные без вот этой твоей операции

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

в твоем случае дистинкт имеет смысл применять только после внутреннего запроса, иначе ты убиваешь дубли вне зависимости от твоей группировки - что ты и показал в результате запроса во 2м посте

надо покумекать чо тут можно придумать...

наверное придумал
если есть возможность построить еще один селект фром, то стОит прикрутить промежуточный селект с расширенной группировкой, которая уберет дубли по ID_ или просто группировку внести внутрь первого запроса и добавить второму
и никаких дистинктов

select MIN(ID_) ID_, list(ID_) GROUP_ID_ 
from
     (select M1.ID_, M1.bcch_, m1.mcc_, m1.mnc_, m1.lac_, m1.cid_
      from MAINTABLE M1 left join maintable M2
         on M1.bcch_=M2.bcch_ and M1.lac_=M2.lac_ AND M1.mnc_=M2.mnc_
         and M1.mcc_=M2.mcc_ and M1.cid_=M2.cid_ AND M1.GSM_TYPE_=M2.GSM_TYPE_
      where M2.ID_ in (:ID_)
      group by m1.ID_, m1.BCCH_, m1.MCC_, m1.MNC_, m1.LAC_, m1.CID_)
group by BCCH_, MCC_, MNC_, LAC_, CID_

[Антон Ю.Б.]

soleil@mmc, это типа глубокомысленно, но не оч осмысленно, так что можно поподробней?

Я вообще-то показал, что внутренний distinct для внутреннего запроса дает ровно то, группировка по чему дала бы ожидаемый и правильный результат. Результат запроса во втором посте совпадает с одним из результатов первого поста и показывает ровно одно - модификация внутреннего поста distinct-ом или сортировкой (а это уж явный признак внутренней ошибки, а не неверности запроса) приводит к неадекватности результатов внешнего (общего) запроса.

Конкретный совет - да, ок. Он дает то же, что и вариант с директивой distinct внутри list (о чем я и написал). Вариант - не лучше и не хуже, кроме того - точный ответ на один из моих вопросов.

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

[Антон Ю.Б.]

Правильный результат получается и вот таким запросом (во вложенном запросе перемножаем таблицы без Left Join):

select MIN(ID_) ID_, list(ID_) GROUP_ID_ from
(select distinct M1.ID_, M1.bcch_, m1.mcc_, m1.mnc_, m1.lac_, m1.cid_
from MAINTABLE M1, maintable M2
where M1.bcch_=M2.bcch_ and M1.lac_=M2.lac_ AND M1.mnc_=M2.mnc_
and M1.mcc_=M2.mcc_ and M1.cid_=M2.cid_ AND M1.GSM_TYPE_=M2.GSM_TYPE_
and M2.ID_ in (198,186,194,191,182,184,190,187,185,200,189,199,188,193,183,196,192,195,197)
)
group BY BCCH_, MCC_, MNC_, LAC_, CID_

Этот запрос не портит сортировка вложенного запроса (но и не влияет на результат). Ясно, что вложенный запрос дает ровно тот же результат, что и вложенный запрос с left join и distinct. Так что речь наверное идет о внутренней ошибке.

[soleil@mmc]

чтобы говорить о соображениях приведите ддл таблиц и набор тестовых данных (приаттаченную БД увидел тока чичас, но все равно - прицепить ее не к чему)

в первом посте видно что ваша функа Лист() сворачивает еще и дубли, и, скорее всего, если показать данные внутреннего запроса без дистинкта, то их там и будет в том самом кол-ве
а дальше вы уже начинаете прикручивать дистинкт не в те места и задаете вопросы не понятно о чем - поиграйтесь дальше - может что-то и станет ясно

накидал тестовый примерчик для оракла
wm_concat() делает по смыслу то же самое что и ваш list(), я надеюсь
внутренний запрос с набором данных в with()

with
  t as (
  select 500 id, 1 group_id from dual union all
  select 501, 1 from dual union all
  select 511, 1 from dual union all
  select 531, 1 from dual union all
  select 505, 1 from dual union all
  select 501, 1 from dual union all
  select 201, 2 from dual union all
  select 201, 2 from dual union all
  select 511, 2 from dual union all
  select 511, 3 from dual union all
  select 511, 3 from dual union all
  select 401, 3 from dual union all
  select 521, 3 from dual union all
  select 601, 6 from dual union all
  select 611, 6 from dual union all
  select 701, 7 from dual union all
  select 701, 7 from dual
  )
  
select
  min(id),
  wm_concat(distinct id) gr_id
from t
group by group_id

получаем

min(id) gr_id
500 500,501,505,511,531
201 201,511
401 401,511,521
601 601,611
701 701

2й вариант запроса - дистинкт во внутреннем подзапросе

select
  min(id),
  wm_concat(id) gr_id
from (
        select distinct id, group_id
        from t)
group by group_id

получаем то же самое только сортировка внутри списка пропала

500 501,505,531,511,500
201 201,511
401 401,521,511
601 601,611
701 701

в итоге вывод:
а) разбирайтесь со своей функой List() - почему она дистинкт не дистинктит?
б) работает ли дистинкт во внутренних подзапросах?

[Антон Ю.Б.]

soleil@mmc, к чему столько апломба, если Вы даже суть вопроса уловили не полностью? А Ваши итоговые выводы и вовсе уж в никуда.

"а дальше вы уже начинаете прикручивать дистинкт не в те места"

Почему же не в те? Вполне в те. Исходный вложенный запрос для моих целей избыточен. Он просто перемножает таблицу с самой собой, поэтому каждый ключ в группе повторяется столько раз, сколько в группе членов. В результатах полного исходного запроса списки имеют немного иную повторяемость, потому что я упустил отсечение записей еще по одному полю (а список, которым я заменял параметр - это не совсем полный список ключей), то есть надо было вот так:

select MIN(ID_) ID_, list(ID_) GROUP_ID_ from
(select M1.ID_, M1.bcch_, m1.mcc_, m1.mnc_, m1.lac_, m1.cid_
from MAINTABLE M1 left join maintable M2
on M1.bcch_=M2.bcch_ and M1.lac_=M2.lac_ AND M1.mnc_=M2.mnc_
and M1.mcc_=M2.mcc_ and M1.cid_=M2.cid_ AND M1.GSM_TYPE_=M2.GSM_TYPE_
and M1.TYPE_=1 and M2.TYPE_=1
where M2.ID_ in (:ID_)
order by CID_)
group BY BCCH_, MCC_, MNC_, LAC_, CID_

Но это ошибка со стороны предметной области, а не со стороны SQL. То есть я имею избыточный внутренний запрос, list по которому тоже будет избыточен. Возникает вопрос устранения избыточности вложенного запроса. Вы предложили сгруппировать внутренний запрос - это вполне себе вариант. Можно использовать возможности дистинкта в самой функции list - и это вариант. Но вариант сделать вложенный запрос дистинктным - это тоже вполне законный вариант. Результаты вложенного запроса с дистинктом и вложенного запроса с Вашей группировкой абсолютно идентичны. Естественно ожидать, что и полный запрос с этими вложенными подзапросами даст одинаковый результат. Однако результат в случае дистинкта во вложенном запросе абсолютно неверный и бессмысленный (последний результат в 1-м и 2-м посте). Налицо очевидный баг Firebird, потому что он пропадает, если к баг-запросу

select MIN(ID_) ID_, list(ID_) GROUP_ID_ from
(select distinct M1.ID_, M1.bcch_, m1.mcc_, m1.mnc_, m1.lac_, m1.cid_
from MAINTABLE M1 left join maintable M2
on M1.bcch_=M2.bcch_ and M1.lac_=M2.lac_ AND M1.mnc_=M2.mnc_
and M1.mcc_=M2.mcc_ and M1.cid_=M2.cid_ AND M1.GSM_TYPE_=M2.GSM_TYPE_
where M2.ID_ in (198,186,194,191,182,184,190,187,185,200,189,199,188,193,183,196,192,195,197))
group BY BCCH_, MCC_, MNC_, LAC_, CID_

всего-то добавить во вложенный запрос сортировку:

select MIN(ID_) ID_, list(ID_) GROUP_ID_ from
(select distinct M1.ID_, M1.bcch_, m1.mcc_, m1.mnc_, m1.lac_, m1.cid_
from MAINTABLE M1 left join maintable M2
on M1.bcch_=M2.bcch_ and M1.lac_=M2.lac_ AND M1.mnc_=M2.mnc_
and M1.mcc_=M2.mcc_ and M1.cid_=M2.cid_ AND M1.GSM_TYPE_=M2.GSM_TYPE_
where M2.ID_ in (198,186,194,191,182,184,190,187,185,200,189,199,188,193,183,196,192,195,197)
order by CID_)
group BY BCCH_, MCC_, MNC_, LAC_, CID_

Пропадает он и если вложенный запрос сделать дистинктным, но объединять таблицы не по left join. Пропадает он даже если я в исходный баг-запрос добавлю дополнительное условие отбора (см. начало поста). Вот и все.

[soleil@mmc]

а не подскажете в чем заключается гениальная идея умножения таблицы самой на себя в данном случае чтобы вытащить список записей, на которые наложен фильтр по айдишнику (ну и + добавлися по типу)?

раз уж покопали эту задачку, не затруднитесь и прогоните такой запрос на своих данных

select 
  min(id_) id_, 
  list(id_) group_id_ 
from
   (select distinct 
         ID_, 
         bcch_, 
         mcc_, 
         mnc_, 
         lac_, 
         cid_
     from maintable
     where id_ in       
           (198,186,194,191,182,184,190,187,185,200,189,199,188,193,183,196,192,195,197)
         and type_=1
      )
group by bcch_, mcc_, mnc_, lac_, cid_

[Антон Ю.Б.]

soleil@mmc, да, конечно Вы правы. Просто к list (и к постановке задачи в таком виде) я пришел после нескольких вариантов, и из них притащил лишнее, без чего можно было обойтись.

Upd. Да нет, это я что-то на другое отвлекся. Умножение нужно для того, чтобы выбрать все записи, которые совпадают с указанными по группируемым полям. А в Вашем запросе выбираются только указанные записи.