помогите составить запрос

[iceman]

помогите составить запрос

есть 2 таблицы,

регоинов {
  region_id,
  parent_id,
  region_name,
  [b]LEVEL[/b]
}

и например,

магазинов {
   shop_id,
   [B]REGION_ID[/b],
   shop_name,
   my_flag (in 'Y', 'N')
}

таблица магазинов связана с регионами

так вот, требуется вывыести регионы 1го уровня, магазины связанных с этим регионом, либо с его дочерними - имеют флаг my_flag = 'Y'

т.е. в левел = 1 в таблице регионов - это грубо говоря страны, нужно вывести страны в которых есть хотябы один магазин в любом в его из дочерних регионов у которого my_flag = 'Y'

я мозг сломал =(

бд: Oracle!

я разделом попутался!

 

[fixxxer]

а что значит shops.parent_id?

 

[iceman]

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

бд: Oracle 10g!

я разделом попутался!

 

[fixxxer]

ы =) перенес

-~{}~ 09.10.10 23:58:

специфику оракла я хз. так что в рамках ansi sql

берем max(regions.level) - допустим 2 (при level=0 для корня)
хрен с ним с флагом, это where добавить

генерим в зависимости от левела такой запрос

[sql]
select s.id, s.region_id, r0.id, r0.level, r1.id, r1.level, r2.id, r2.level from regions r0 left join regions r1 on(r1.parent_id=r0.id) left join regions r2 on(r2.parent_id=r1.id) inner join shops s on (s.region_id=r1.id or s.region_id=r2.id) where r0.level=0
[/sql]

то, не то?

-~{}~ 10.10.10 00:00:

а.. не в дочерних тоже надо? тогда


INNER JOIN shops s ON ( s.region_id = r0.id OR s.region_id = r1.id OR s.region_id = r2.id )

но конечно это не очень быстро мне кажется)

 

[phprus]

Возможно поможет примерно такой запрос:
[sql]
SELECT REG.ROOT_ID FROM (
SELECT
R.REGION_ID,
CONNECT_BY_ROOT R.REGION_ID AS ROOT_ID
FROM REGIONS R
START WITH R.PARENT_ID IS NULL OR R."LEVEL" = 1 /* Условие поиска корня дерева регионов по вкусу */

CONNECT BY PRIOR R.REGION_ID = R.PARENT_ID
) REG
JOIN SHOPS S ON (S.REGION_ID = REG.REGION_ID)
WHERE S.my_flag = 'Y'
GROUP BY REG.ROOT_ID
[/sql]

 

[iceman]

есть магазины у которых my_flag = Y
вот вывести регионы первого уровня (страны) в которых хотябы один регион имеет магазин с флагом my_flag = Y

phprus
вот твой запрос кажется прокатит

но не желательно юзать OR, записей в таблице регионов > 100к и так же в таблице магазинов > 100к

еще можно, если сделать сначало выбор страны, т.е. ID региона первого уровня нам известен... но это я наверно сам допилю...

спасибо!

 

[phprus]

iceman
OR тут показан как пример условия поиска корня. Если есть более точное условие поиска корня, то его и надо сюда включить. START WITH - это условие, по которому будут выбраны записи, к которым потом будут приклеиваться дочерние записи.
В START WITH части у тебя должно быть условие, которое выберет из таблицы регионы нужного уровня. В твоем случае страны.

еще можно, если сделать сначало выбор страны, т.е. ID региона первого уровня нам известен... но это я наверно сам допилю...

А это как? В приведенном подзапросе то выбирается вся иерархия при помощи connect by, а в START WITH - условие поиска стран.

А можно ради интереса узнать сколько времени выполняется такой запрос и посмотреть его план?

 

[iceman]

можно, но во вторник...

-~{}~ 12.10.10 10:01:

select
 distinct *
from region r
where r.parent_id = 1
start with r.region_id in (
  SELECT REG.REGION_ID
  FROM region REG
  INNER JOIN bank S ON S.REGION_ID = REG.REGION_ID
  WHERE s.my_flag = 'Y' 
  GROUP BY REG.REGION_ID
)
connect by prior r.parent_id = r.region_id

пришлось так сделать...

-~{}~ 12.10.10 10:04:

Администратор установил, что отредактировать сообщение можно только в течении 1440 минут после его публикации. Этот лимит времени прошел. Если Вы все же хотите отредактировать сообщение - свяжитесь с администратором.

-~{}~ 12.10.10 13:38:

phprus
в твоем запросе - подзапрос выводит все регионы в древовидной форме. а дальше все джоинтся с магазинами.
так вот, у РОССИИ (регион_ид = 15) может быть где то на Камчатке в маленьком городе (например с регион_ид = 2565 и левел = 4) - быть магазин с этим флагом.
При джоине связь - регион_ид = 15 - отпадет, хотя интересуют меня как раз верхушки дерева...

в своем запросе я сначало нашел все регионы с этим флагом и относительно них прорисовал дерево - до ВЕРХУШКИ и отсеял ненужные.

но использовать IN в сочетание connect_by это не есть гуд =(

 

[phprus]

iceman

При джоине связь - регион_ид = 15 - отпадет, хотя интересуют меня как раз верхушки дерева...

Почему отпадает? Подзапрос-же выбирает две колонки - region_id и id корня верки, которая заканчивается на текущем region_id. По region_id идет JOIN, а по id_корня группировка и в итоге выбираются ID-корней.
Может быть ты в условии start with выставлял условие r.parent_id = 15, когда надо было r.region_id = 15? Или если так сделать тоже получается неверный результат?

но использовать IN в сочетание connect_by это не есть гуд =(

Теоретически часть start with должна выполниться один раз или Oracle думает по другому? Мне не доводилось оптимизировать запросы с connect by, по этому и задаю этот возможно глупый вопрос.

 

[iceman]

> Теоретически часть start with должна выполниться один раз или Oracle думает по другому? Мне не доводилось оптимизировать запросы с connect by, по этому и задаю этот возможно глупый вопрос.

1) на патченной 10g это позволяется делать, ты не поверишь запрос, оказывается, работает гораздо быстрее =), чем join+connect_by где начало - один корень.
т.к. регионы не имееют тенденцию "разрастатся" дальше, то меня устраивает и такой запрос =)

2) это не верхушки, это "льстья" - запрос сделан наоборот, от листьев ищем корни, а потом отрубаем лишнее


> Почему отпадает?
я же написал почему

м.б. возможно и попутался, щас еще раз вникну в запрос =)

 

[phprus]

iceman
Я понял, что твой запрос идет от листьев к корю и согласен, что он должен работать быстрее чем вначале разворачивание всего дерева.

Так что дальнейший интерес к моему варианту запроса является скорее теоретическим, чем практическим.

я же написал почему

В моем запросе в ROOT_ID должны попадать те REGION_ID, которые отобрались условием start with, так что возможно было не совсем верно указано это условие. Хотя я тестировал свой запрос на 11gR2 (без патчей) и возможно в ней что-то отличается от 10g. Или я не прав?

 

[iceman]

phprus
да, это я касякнул с твои запросом, он выводит все правильно =)