ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вопрос по вложенным запросам
22 сообщений из 22, страница 1 из 1
Вопрос по вложенным запросам
    #40002805
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
У меня есть список адресов в виде строки.
Формат строки регламентирован, возможны следующие варианты:
<Город>; <Улица>, <Дом>, кв. <Номер>[; Комментарии] — для многоквартирного дома
<Город>; <Улица>, <Дом>, (офис|кабинет) <Место>[; Комментарии] — для коммерческих зданий
<Город>; <Улица>, <Дом>[; Комментарии] — для не многоквартирного дома
(секция с комментарием опциональна)

Мне нужно посчитать общее количество зданий (домов), а затем определить количество многоквартирных домов и коммерческих зданий. Дом будет считаться многоквартирным, если по указанному адресу имеется хотя бы одна квартира. Если квартир нет, но есть несколько адресов с разными "местами", то здание будет считаться коммерческим. Уникальные адреса будут считаться частными домами.

Эти результаты можно получить, сделав два уровня вложений запросов — на 2 уровне будет парсинг адреса с помощью regexp_replace, на 1 уровне будет классификация адреса (признаки МКД, частного дома или коммерческого здания), а на верхнем уровне будет группировка.
Либо это можно сделать без подзапросов или с одним уровнем вложений, используя более сложные выражения группировки.
Как лучше сделать (исключая вариант с нормализацией адреса)?
...
Рейтинг: 0 / 0
26.09.2020, 11:11
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40002816
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Alibek B.,

CASE Expressions
...
Рейтинг: 0 / 0
26.09.2020, 11:56
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40002818
dmdmdm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dmdmdm
Участник
на 2 уровне будет парсинг адреса с помощью regexp_replace, на 1 уровне будет классификация адреса

Мой выбор.

Предпочитаю читаемость производительности, за редкими исключениями.
...
Рейтинг: 0 / 0
26.09.2020, 11:58
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40002840
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
Мне кажется regexp это не совсем про "парсинг"

Я изначально бы брал PL/SQL, а не пытаясь все сделать одним запросом.
...
Рейтинг: 0 / 0
26.09.2020, 13:28
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40002893
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
В моем решении я бы объединил парсинг и классификацию, т.к. полный парсинг дорого для определения только типа. Эвристика сойдёт.

Select * from buildings b where b.type_id=1

Если можно добавить виртуальное поле в таблицу/представление, или

Select * from buildings b where GetBldgType(b.row)=1

если нельзя.

Я использовал оба решения. Второе считается более медленным из-за контекстных переключений, но я этого не замечал.
...
Рейтинг: 0 / 0
26.09.2020, 17:00
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40002924
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Leonid Kudryavtsev
Я изначально бы брал PL/SQL, а не пытаясь все сделать одним запросом.

Я бы тоже так сделал, тем более что результат всегда можно выдать табличной функцией, еще бы добавил статус успешного/неуспешного парсинга, потому что если в записи мусор ее тоже нужно как то учитывать.
...
Рейтинг: 0 / 0
26.09.2020, 18:50
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003038
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Alibek B.
Либо это можно сделать без подзапросов или с одним уровнем вложений, используя более сложные выражения группировки.
Как лучше сделать (исключая вариант с нормализацией адреса)?

Лучше, можно, более сложные выражения группировки не нужны, пример:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
with
  function address_type(p_addr varchar2) return number is
  begin
    if p_addr = 'многоквартирный дом' then
      return 1;
    end if;
    
    if p_addr = 'коммерческое здание' then
      return 2;
    end if;

    return 0;
  end;
src (addr) as
(
            select 'коммерческое здание' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'коммерческое здание' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'частный дом' from dual
)
select count(*) "Всего зданий"
     , count(case when address_type(addr) = 1 then 1 end) "Многоквартирных домов"
     , count(case when address_type(addr) = 2 then 1 end) "Коммерческих зданий"
     , count(case when address_type(addr) = 0 then 1 end) "Частных домов"
  from src


Парсинг добавьте сами, если достаточно нахождения вхождений в строку ', кв.' или ', офис' или ', кабинет', то и регулярные выражения не нужны.
...
Рейтинг: 0 / 0
27.09.2020, 12:56
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003053
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
graycode

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


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
with
  function address_type(p_addr varchar2) return number is
  begin
dbms_output.put_line('Fire!');
    if p_addr = 'многоквартирный дом' then
      return 1;
    end if;
    
    if p_addr = 'коммерческое здание' then
      return 2;
    end if;

    return 0;
  end;
src (addr) as
(
            select 'коммерческое здание' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'коммерческое здание' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'частный дом' from dual
)
select count(*) "Всего зданий"
     , count(case when address_type(addr) = 1 then 1 end) "Многоквартирных домов"
     , count(case when address_type(addr) = 2 then 1 end) "Коммерческих зданий"
     , count(case when address_type(addr) = 0 then 1 end) "Частных домов"
  from src
/

Всего зданий Многоквартирных домов Коммерческих зданий Частных домов
------------ --------------------- ------------------- -------------
           6                     3                   2             1

Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
SQL>



Функция выполняется 3 x число строк в таблице. А так 1 x число строк в таблице:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
with
  function address_type(p_addr varchar2) return number is
  begin
dbms_output.put_line('Fire!');
    if p_addr = 'многоквартирный дом' then
      return 1;
    end if;
    
    if p_addr = 'коммерческое здание' then
      return 2;
    end if;

    return 0;
  end;
src (addr) as
(
            select 'коммерческое здание' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'коммерческое здание' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'частный дом' from dual
),
t as (
      select  /*+ materialize */
              address_type(addr) addr_type
        from  src
     )
select  "Многоквартирных домов" + "Коммерческих зданий" + "Частных домов" "Всего зданий",
        x.*
  from  t
  pivot(
        count(*)
        for addr_type in (1 "Многоквартирных домов",2 "Коммерческих зданий",0 "Частных домов")
       ) x
/

Всего зданий Многоквартирных домов Коммерческих зданий Частных домов
------------ --------------------- ------------------- -------------
           6                     3                   2             1

Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
SQL>



А если не пользуясь недокументированным MATERIALIZE:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
with
  function address_type(p_addr varchar2) return number is
  begin
dbms_output.put_line('Fire!');
    if p_addr = 'многоквартирный дом' then
      return 1;
    end if;
    
    if p_addr = 'коммерческое здание' then
      return 2;
    end if;

    return 0;
  end;
src (addr) as
(
            select 'коммерческое здание' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'коммерческое здание' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'частный дом' from dual
),
t as (
      select  address_type(addr) addr_type,
              count(*) cnt
        from  src
        group by address_type(addr)
     )
select  "Многоквартирных домов" + "Коммерческих зданий" + "Частных домов" "Всего зданий",
        x.*
  from  t
  pivot(
        sum(cnt)
        for addr_type in (1 "Многоквартирных домов",2 "Коммерческих зданий",0 "Частных домов")
       ) x
/

Всего зданий Многоквартирных домов Коммерческих зданий Частных домов
------------ --------------------- ------------------- -------------
           6                     3                   2             1

Fire!
Fire!
Fire!
Fire!
Fire!
Fire!
SQL>



А вообще-то нужно смотреть насколько сложна логика определения типа и если не очень то чистый SQL чтобы не тратить время на context switch. А уж совсем правильно было бы просто добавить поле "Тип Здания" (либо calculated либо с BEFORE INSERT OR UPDATE триггером) плюс индекс.

SY.
...
Рейтинг: 0 / 0
27.09.2020, 13:55
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003073
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
Для данного случая, почему бы не так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
With
  function address_type(p_addr varchar2) return number is
  begin
dbms_output.put_line('Fire!');
    if p_addr = 'многоквартирный дом' then
      return 1;
    end if;
    
    if p_addr = 'коммерческое здание' then
      return 2;
    end if;

    return 0;
  end;
src0 (addr) as
(
            select 'коммерческое здание' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'коммерческое здание' from dual
  union all select 'многоквартирный дом' from dual
  union all select 'частный дом' from dual
),
src(addr_type, addr) as (
      select 
            (Select address_type(addr) from dual)  addr_type,  -- <==
            addr
      from src0            
),   
t as (
      select  addr_type,
              count(*) cnt
        from  src
        group by addr_type
     )
select  "Многоквартирных домов" + "Коммерческих зданий" + "Частных домов" "Всего зданий",
        x.*
  from  t
  pivot(
        sum(cnt)
        for addr_type in (1 "Многоквартирных домов",2 "Коммерческих зданий",0 "Частных домов")
       ) x
/


Тут 3 Fire будет
...
Рейтинг: 0 / 0
27.09.2020, 14:37
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003084
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
booby

Тут 3 Fire будет


Ты всерьез думаешь у всех квартир, офисов, частных домов один адрес?

SY.
...
Рейтинг: 0 / 0
27.09.2020, 14:59
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003085
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
booby,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
With
  function address_type(p_addr varchar2) return number is
  begin
dbms_output.put_line('Fire! ' || p_addr);
    if p_addr like '% apt.%' then
      return 1;
    end if;

    if p_addr like '% off.%' then
      return 2;
    end if;

    return 0;
  end;
src0 (addr) as
(
            select 'commercial building 1 off. 1' from dual
  union all select 'apartment building 1 apt. 1' from dual
  union all select 'apartment building 1 apt. 2' from dual
  union all select 'commercial building 1 off. 2' from dual
  union all select 'commercial building 2 off. 1' from dual
  union all select 'apartment building 2 apt. 2' from dual
  union all select 'private building 1' from dual dual
  union all select 'private building 2' from dual
),
src(addr_type, addr) as (
      select
            (Select address_type(addr) from dual)  addr_type,  -- <==
            addr
      from src0
),
t as (
      select  addr_type,
              count(*) cnt
        from  src
        group by addr_type
     )
select  "Apartment Buildings" + "Commercial Buildings" + "Private buildings" "Total buildings",
        x.*
  from  t
  pivot(
        sum(cnt)
        for addr_type in (1 "Apartment Buildings",2 "Commercial Buildings",0 "Private buildings")
       ) x
/

Total buildings Apartment Buildings Commercial Buildings Private buildings
--------------- ------------------- -------------------- -----------------
              8                   3                    3                 2

Fire! commercial building 1 off. 1
Fire! apartment building 1 apt. 1
Fire! apartment building 1 apt. 2
Fire! commercial building 1 off. 2
Fire! commercial building 2 off. 1
Fire! apartment building 2 apt. 2
Fire! private building 1
Fire! private building 2
SQL>
SQL>



SY.
...
Рейтинг: 0 / 0
27.09.2020, 15:03
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003087
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
SY
booby

Тут 3 Fire будет


Ты всерьез думаешь у всех квартир, офисов, частных домов один адрес?

SY.

Конечно нет по всем позициям сразу.
Я только в текст конкретного запроса смотрю.
...
Рейтинг: 0 / 0
27.09.2020, 15:13
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003097
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
booby

Я только в текст конкретного запроса смотрю.


А надо-бы смотреть на текст конкретного запроса в контексте заданной задачи.

SY.
...
Рейтинг: 0 / 0
27.09.2020, 16:38
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003098
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
SY,

в контексте задачи, для предотвращения трехкратного выполнения функции на строке, достаточно deterministic на функции.
тогда
Код: plsql
1.
2.
3.
4.
5.
6.
7.
...
select count(*) "Всего зданий"
     , count(case when address_type(addr) = 1 then 1 end) "Многоквартирных домов"
     , count(case when address_type(addr) = 2 then 1 end) "Коммерческих зданий"
     , count(case when address_type(addr) = 0 then 1 end) "Частных домов"
  from src
...


хорошо сработает.

Но сама задача - "парсинг строки адреса" мне сейчас не интересна.
...
Рейтинг: 0 / 0
27.09.2020, 16:48
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003104
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
SY,

оставил на закуску)) по идее функция определения типа адреса не должна для одного и того же адреса давать разные результаты, т.е. она зависит только от входного параметра и больше ни от каких других факторов не зависит и ее можно объявить детерминированной.
Код: plsql
1.
function address_type(p_addr varchar2) return number deterministic is
...
Рейтинг: 0 / 0
27.09.2020, 17:28
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003110
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Если есть простое правило определения типа здания, то выдумывать вообще ничего не нужно
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with src (addr) as
(
            select '<Город>; <Улица>, <Дом>, кв. <Номер 1>[; Комментарии] — для многоквартирного дома' from dual
  union all select '<Город>; <Улица>, <Дом>, кв. <Номер 2>[; Комментарии] — для многоквартирного дома' from dual
  union all select '<Город>; <Улица>, <Дом>, (офис|кабинет) <Место>[; Комментарии] — для коммерческих зданий' from dual
  union all select '<Город>; <Улица>, <Дом>, (кабинет) <Место>[; Комментарии] — для коммерческих зданий' from dual
  union all select '<Город>; <Улица>, <Дом>, (офис) <Место>[; Комментарии] — для коммерческих зданий' from dual
  union all select '<Город>; <Улица>, <Дом>[; Комментарии] — для не многоквартирного дома' from dual
)
select count(*) "Всего зданий"
     , count(case when instr(addr, ', кв.') > 0 then 1 end) "Многоквартирных домов"
     , count(case when instr(addr, 'офис') > 0 or instr(addr, 'кабинет') > 0 then 1 end) "Коммерческих зданий"
     , count(case when not( instr(addr, ', кв.') > 0 or instr(addr, 'офис') > 0 or instr(addr, 'кабинет') > 0) then 1 end) "Частных домов"
  from src



SY
А вообще-то нужно смотреть насколько сложна логика определения типа и если не очень то чистый SQL чтобы не тратить время на context switch. А уж совсем правильно было бы просто добавить поле "Тип Здания" (либо calculated либо с BEFORE INSERT OR UPDATE триггером) плюс индекс.

SY.

А совсем совсем правильно было бы парсить на входе и раскладывать валидные данные в табличку со структурой для распрасенного адреса, включая поле тип здания, а не валидные, в виде строки в другую таблицу для дальнейшего анализа. Но поскольку постановка задачи неизвестна, не будем усложнять))
...
Рейтинг: 0 / 0
27.09.2020, 18:02
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003134
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Вы неправильно прочитали условие.
count (и даже count distinct) не дадут правильного результата. Как минимум нужна оконная функция count по "адрес дом", потому что в МКД на первом этаже могут быть коммерческие помещения, в адресе которых не будет номера квартиры.
У меня, собственно, вопрос можно свести к такому - стоит ли серьезное усложнение запроса и снижение читаемости (если не использовать подзапросы) выгоды в производительности и эффективности.
Завтра покажу первый вариант, с подзапросами. На рабочих данных он выполняется за терпимое время и я как раз думаю, сколько я выгадаю, если избавлюсь от подзапросов.
...
Рейтинг: 0 / 0
27.09.2020, 20:51
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003142
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
То, что оптимизатор сам умеет переписывать запросы Вы не слышали?

Что SELECT * FROM dual, что SELECT * FROM (SELECT * FROM.... dual))))))))))
для Oracle будет одно и то же. AFAIK
...
Рейтинг: 0 / 0
27.09.2020, 21:19
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003145
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Alibek B.

count (и даже count distinct) не дадут правильного результата. Как минимум нужна оконная функция count по "адрес дом", потому что в МКД на первом этаже могут быть коммерческие помещения, в адресе которых не будет номера квартиры.


Т.е. если по адресу есть квартира и офис то все равно многоквартирный дом и если есть адрес без квартиры и тот-же адрес с офисом то это все равно частный дом. Тогда:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
with src (addr) as
(
            select 'appartment building 1 with offices off. 1' from dual
  union all select 'appartment building 1 with offices apt. 1' from dual
  union all select 'appartment building 1 with offices apt. 2' from dual
  union all select 'appartment building 2 no offices apt. 1' from dual
  union all select 'appartment building 2 no offices apt. 2' from dual
  union all select 'commercial building 1 off. 1' from dual
  union all select 'commercial building 1 off. 2' from dual
  union all select 'commercial building 2 off. 1' from dual
  union all select 'commercial building 2 off. 2' from dual
  union all select 'private building 1 with offices' from dual dual
  union all select 'private building 1 with offices off. 1' from dual
  union all select 'private building 2 no offices' from dual
),
t as (
      select  max(
                  case
                    when addr like '% off.%' then 1
                    when addr like '% apt.%' then 3
                    else 2
                 end
                ) addr_type
        from  src
        group by regexp_replace(addr,' (apt|off)\. \d+$')
     )
select  "Apartment Buildings" + "Commercial Buildings" + "Private buildings" "Total buildings",
        x.*
  from  t
  pivot(
        count(addr_type)
        for addr_type in (3 "Apartment Buildings",1 "Commercial Buildings",2 "Private buildings")
       ) x
/

Total buildings Apartment Buildings Commercial Buildings Private buildings
--------------- ------------------- -------------------- -----------------
              6                   2                    2                 2

SQL>



SY.
...
Рейтинг: 0 / 0
27.09.2020, 21:50
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003166
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Alibek B.,

Процедурно в один проход по отсортированной выборке:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
declare
    cursor c_addr is select full_addr from (
                select '<Город 3>; <Улица 1>, <Дом 1>, кв. 1' as full_addr from dual
      union all select '<Город 3>; <Улица 1>, <Дом 1>, офис 123' from dual
      union all select '<Город 5>; <Улица 1>, <Дом 2>, офис 3' from dual
      union all select '<Город 5>; <Улица 1>, <Дом 2>, место 5' from dual
      union all select '<Город 1>; <Улица 1>, <Дом 3>, кв. 10' from dual
      union all select '<Город 1>; <Улица 1>, <Дом 3>, кв. 20' from dual
      union all select '<Город 1>; <Улица 1>, <Дом 7>' from dual
      union all select '<Город 1>; <Улица 1>, <Дом 2>' from dual
      union all select '<Город 9>; <Улица 1>, <Дом 1>' from dual
      ) order by full_addr;
    
    full_addr       varchar2(200);
    addr_start_with varchar2(200);
    
    total_building_counter      number := 0;
    apartment_building_counter  number := 0;
    commercial_building_counter number := 0;
    private_building_counter    number := 0;

    has_apartment boolean := false;
    has_office    boolean := false;
    
    procedure increment_counters is
    begin
        total_building_counter := total_building_counter + 1;
        if has_apartment then
            apartment_building_counter := apartment_building_counter + 1;
        elsif has_office then
            commercial_building_counter := commercial_building_counter + 1;
        else
            private_building_counter := private_building_counter + 1;
        end if;
        
        addr_start_with := '';
        has_apartment := false;
        has_office := false;
    end;
    
    procedure set_addr_start_with is
        pos number := instr(full_addr, ',', 1, 2);
    begin
        if pos > 0 then
            addr_start_with := substr(full_addr, 1, pos);
        else
            addr_start_with := full_addr;
        end if;
    end;
begin
    open c_addr;
    loop
        fetch c_addr into full_addr;
        if c_addr%notfound then 
            if addr_start_with is not null then
                increment_counters;
            end if;
            exit;
        end if;
        
        if addr_start_with is not null then
            if instr(full_addr, addr_start_with) != 1 then
                increment_counters;
                set_addr_start_with;
            end if;
        else
            set_addr_start_with;
        end if;
        
        if instr(full_addr, ', кв.') > 0 then
            has_apartment := true;
        elsif instr(full_addr, ', офис') > 0 then
            has_office := true;
        elsif instr(full_addr, ', место') > 0 then
            has_office := true;
        end if;
    end  loop;
    close c_addr;

    dbms_output.put_line('Total buildings - ' || total_building_counter);
    dbms_output.put_line('Apartment buildings - ' || apartment_building_counter);
    dbms_output.put_line('Commercial buildings - ' || commercial_building_counter);
    dbms_output.put_line('Private buildings - ' || private_building_counter);
end;

...
Рейтинг: 0 / 0
28.09.2020, 01:14
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003240
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
SY
select max(...) addr_type

Хорошая идея, я не сообразил, использовал оконные функции.

graycode
Процедурно в один проход по отсортированной выборке:

Спасибо, процедурно я бы и сам осилил, но предпочитаю запросы на чистом SQL.
...
Рейтинг: 0 / 0
28.09.2020, 10:45
| Ответить | Цитировать | Написать  
Вопрос по вложенным запросам
    #40003247
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
SY , спасибо за идею.
Сменил оконную функцию на обычную агрегатную, избавился от одного уровня вложенности, запрос стал работать существенно быстрее.
Текущий запрос:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
select ADR_CITY, ADR_MAIN
, max(ADR_CLASS) as CLS
, decode(max(ADR_CLASS), 0,'ч/д', 1,'ТЦ', 2,'МКД') as "CLASS"
, count(*) as CNT
, case
    max(ADR_CLASS)
    when 0 then sum(decode(ADR_CLASS,0,1)) 
    when 1 then sum(decode(ADR_CLASS,1,1)) 
    when 2 then sum(decode(ADR_CLASS,2,1)) 
  end as QTY
from (
select CUSTOMER_ID as "CLIENT"
, VALUE as "ADDRESS"
, trim(regexp_replace(VALUE, '^(([[:alnum:] -]+); )?.*$', '\2')) as "ADR_CITY"
, trim(regexp_replace(VALUE, '^([[:alnum:] -]+; )?([[:alnum:] -]+)(, [0-9]+([[:alnum:]/-]+)?)?.*$', '\2\3')) as "ADR_MAIN"
, trim(regexp_replace(VALUE, '^([[:alnum:] -]+; )?([[:alnum:] -]+)(, [0-9]+([[:alnum:]/-]+)?)?(, ((кв\.|каб\.|офис) ?[[:alnum:]]+))?(; +(.*))?.*$', '\6'||CHR(10)||'\9')) as "ADR_EXTRA"
, trim(regexp_replace(VALUE, '^([[:alnum:] -]+; )?(([[:alnum:] -]+)(, [0-9]+([[:alnum:]/-]+)?)?(, ((кв\.|каб\.|офис) ?[[:alnum:]]+))?)(; +(.*))?.*$', '\2')) as "ADR_SHORT"
, trim(regexp_replace(VALUE, '^([[:alnum:] -]+; )?([[:alnum:] -]+)(, [0-9]+([[:alnum:]/-]+)?)?(, ((кв\.|каб\.|офис) ?[[:alnum:]]+))?(; +(.*))?.*$', '\7')) as "ADR_TYPE"
, case
    trim(regexp_replace(VALUE, '^([[:alnum:] -]+; )?([[:alnum:] -]+)(, [0-9]+([[:alnum:]/-]+)?)?(, ((кв\.|каб\.|офис) ?[[:alnum:]]+))?(; +(.*))?.*$', '\7'))
    when 'кв.' then 2
    when 'каб.' then 1
    when 'офис' then 1
    else 0
  end as "ADR_CLASS"
from BM_CUSTOMER_CONTACT
where CONTACT_DICT_ID = 3
)
group by ADR_CITY, ADR_MAIN
order by 1, 2



Примеры исходных данных:
 

ADDRESSГород; ***н**, 51; ч/дГород; ***ябрьск**, 346, кв. 80; п. 2, эт. 10Город; ***о**, 23, каб. 241; г-ца ***Город; ***о**, 23, каб. 249; г-ца ***Город; ***ахоно**, 39, кв. 65; п. 2, эт. 8Город; ***ахоно**, 46; эт. 2Город; ***дународн**, 117, кв. 11; п. 1, эт. 3Город; ***жданск**, 24, кв. 88; п. 3, эт. 3Город; ***сск**, 11/АГород; ***ернациональн**, 16, кв. 143; п. 4, эт. 9Город; ***ра Алие**, 20, кв. 5; п. 1, эт. 2Город; ***ла Марк**, 112, кв. 6; п. 1, эт. 2Город; ***сноармейск**, 52, кв. 16; п. 1, эт. 5Город; ***и**, 83/А, кв. 15; п. 1, эт. 4Город; ***пск**, 75, кв. 40; п. 4, эт. 5Город; ***ябрьск**, 317/А2, кв. 72; п. 2, эт. 6Город; ***сск**, 11/А; эт. цокольныйГород; ***якуло**, 36/АГород; ***сноармейск**, 52, кв. 59; п. 4, эт. 4Город; ***сомольск**, 27, кв. 31; п. 1, эт. 6Город; ***олюционн**, 30; ч/дГород; ***вропольск**, 45, кв. 28; п. 2, эт. 4Город; ***сомольск**, 27, кв. 16; п. 1, эт. 3Город; ***якуло**, 13/А, кв. 6; п. 1, эт. 1Город; ***дународн**, 176, кв. 35; п. 1, эт. 9Город; ***дународн**, 85, кв. 91; п. 4Город; ***окубанск**, 17; ч/дГород; ***казск**, 30, кв. 33; п. 2, эт. 4Город; ***ра Алие**, 20, кв. 28; п. 2, эт. 5Город; ***азинн**, 26Город; ***инн**, 32; ч/дГород; ***ахоно**, 41, кв. 111; п. 4, эт. 1Город; ***оди**, 86Город; ***оди**, 86/АГород; ***ла Марк**, 112, кв. 141; п. 4, эт. 9Город; ***ла Марк**, 112, кв. 57; п. 2, эт. 6Город; ***вомайск**, 100/4; ч/дГород; ***ернациональн**, 16/А, кв. 70; п. 2, эт. 9Город; ***и**, 63, кв. 27; п. 1, эт. 5Город; ***ябрьск**, 32, кв. 101; п. 3, эт. 6Город; ***окубанск**, 24; ч/дГород; ***ла Марк**, 101, кв. 108; п. 3, эт. 8Город; ***ябрьск**, 32, кв. 109; п. 3, эт. 8Город; ***сомольск**, 27, кв. 49; п. 1, эт. 9Город; ***жданск**, 56, кв. 17; п. 2, эт. 1Город; ***иден**, 142, кв. 45; п. 3, эт. 5Город; ***ошило**, 47, кв. 9; п. 1, эт. 3Город; ***ябрьск**, 32, кв. 73; п. 2, эт. 9Город; ***пск**, 86, кв. 56; п. 2, эт. 4Город; ***о**, 22; АдвокатыГород; ***ябрьск**, 317/А2, кв. 78; п. 2, эт. 7Город; ***и**, 54, кв. 6; п. 1, эт. 2Город; ***оди**, 45/А, кв. 24; п. 2, эт. 1Город; ***оди**, 65, кв. 36; п. 4, эт. 1Город; ***якуло**, 20, кв. 32; п. 1, эт. 8Город; ***сомольск**, 27, кв. 29; п. 1, эт. 6Город; ***монавт**, 49/В, кв. 52; п. 2, эт. 5Город; ***сск**, 14, кв. 19; п. 2, эт. 2Город; ***сноармейск**, 52, кв. 56; п. 4, эт. 3Город; ***ричн**, 107/А, кв. 8; п. 1
...
Рейтинг: 0 / 0
28.09.2020, 11:04
| Ответить | Цитировать | Написать  
22 сообщений из 22, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вопрос по вложенным запросам
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=36&tid=1880857]:
 0ms
get settings:
 8ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 44ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 11ms
total:  145ms
созд. / загр.: 145ms
x
x
Закрыть


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