Запрос по телефонным кодам стран / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос по телефонным кодам стран

9 сообщений из 9, страница 1 из 1

Запрос по телефонным кодам стран

#40134017

Alexander Warlord

Гость

Приветствую форумчан!

Есть файл с телефонными кодами операторов/городов/стран. Для примера:

Направление КодMoldova-fix 810373Moldova-Mobile Moldcell 81037376Moldova-Mobile Moldcell 81037378Moldova-Mobile Moldcell 81037379Moldova-Mobile Orange 81037360Moldova-Mobile Orange 810373610Moldova-Mobile Orange 810373611Moldova-Mobile Orange 810373620Moldova-Mobile Orange 810373621Moldova-Mobile Orange 81037368Moldova-Mobile Orange 81037369

Запрос по каждому направлению типа такого:

Код: 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.

SELECT
    TRUNC(cll.START_TIME,'MM'),

    NVL(ROUND(SUM(CASE WHEN (cll.CALT_ID = 5
    AND (cll.DIALED LIKE '81037376%'
    OR cll.DIALED LIKE '81037378%'
    OR cll.DIALED LIKE '81037379%')) THEN cll.DURATION/60 END)),0) AS Moldova_mobile_Moldcell,

    NVL(ROUND(SUM(CASE WHEN (cll.CALT_ID = 5
    AND (cll.DIALED LIKE '81037360%'
    OR cll.DIALED LIKE '81037368%'
    OR cll.DIALED LIKE '81037369%'
    OR cll.DIALED LIKE '810373610%'
    OR cll.DIALED LIKE '810373611%'
    OR cll.DIALED LIKE '810373620%'
    OR cll.DIALED LIKE '810373621%')) THEN cll.DURATION/60 END)),0) AS Moldova_Mobile_Orange,

    NVL(ROUND(SUM(CASE WHEN (cll.CALT_ID = 5
    AND cll.DIALED LIKE '810373%'
    AND cll.DIALED NOT LIKE '81037377%'
    AND cll.DIALED NOT LIKE '81037376%'
    AND cll.DIALED NOT LIKE '81037378%'
    AND cll.DIALED NOT LIKE '81037379%'
    AND cll.DIALED NOT LIKE '81037367%'
    AND cll.DIALED NOT LIKE '81037360%'
    AND cll.DIALED NOT LIKE '810373610%'
    AND cll.DIALED NOT LIKE '810373611%'
    AND cll.DIALED NOT LIKE '81037320%'
    AND cll.DIALED NOT LIKE '81037321%'
    AND cll.DIALED NOT LIKE '81037368%'
    AND cll.DIALED NOT LIKE '81037369%') THEN cll.DURATION/60 END)),0) AS Moldova-Fix

FROM
    CALL_12_2021 cll

GROUP BY TRUNC(cll.START_TIME,'MM')
ORDER BY 1

Хотелось бы как-то облегчить такую задачу.
Направления и коды допустим я загоню в таблицу, это не проблема.
Проблема в том, что много пересекающихся кодов. И даже в конкретном примере - для каждого оператора ("Moldcell", "Orange") надо подставлять свои коды, а для Fix'ы эти самые коды убирать из выборки.
Кроме того, встречаются коды абсолютно разной длины и абсолютно разных пересечений, вроде того что:

Направление КодRussia-Mobile Beeline 810790205Russia-Mobile Beeline 810790206Russia-Mobile Beeline 810790207Russia-Mobile Beeline 810790252Russia-Mobile Beeline 8107902553Russia-Mobile Beeline 8107902554Russia-Mobile Beeline 8107902555Russia-Mobile Beeline 8107902556Russia-Mobile Beeline 8107902557Russia-Mobile Beeline 8107902559Russia-Mobile Beeline 8107902710Russia-Mobile Beeline 8107902717
А остальные 8107902 - это Теле-2. Или какие-то из серии 8107902 могут быть еще третьего оператора.

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

...

Рейтинг:

0 / 0

14.02.2022, 16:15

| Ответить | Цитировать | Написать

Запрос по телефонным кодам стран

#40134037

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Код: 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.

with operators as (
                   select 'Moldova-fix' operator,'810373' range from dual union all
                   select 'Moldova-Mobile Moldcell' operator,'81037376' range from dual union all
                   select 'Moldova-Mobile Moldcell' operator,'81037378' range from dual union all
                   select 'Moldova-Mobile Moldcell' operator,'81037379' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'81037360' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373610' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373611' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373620' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373621' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'81037368' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'81037369' range from dual
                  ),
        phones as (
                   select '81037361100' phone from dual union all
                   select '81037378987' phone from dual union all
                   select '81037368223' phone from dual union all
                   select '81037399999' phone from dual union all
                   select 'XYZ' phone from dual
                  )
select  phone,
        (
         select  max(operator) keep(dense_rank last order by case instr(phone,range) when 1 then length(range) end nulls first)
           from  operators
           having max(case instr(phone,range) when 1 then length(range) end) is not null
        ) operator
  from  phones
/

PHONE       OPERATOR
----------- -----------------------
81037361100 Moldova-Mobile Orange
81037378987 Moldova-Mobile Moldcell
81037368223 Moldova-Mobile Orange
81037399999 Moldova-fix
XYZ

SQL>

SY.

...

Рейтинг:

0 / 0

14.02.2022, 17:30

| Ответить | Цитировать | Написать

Запрос по телефонным кодам стран

#40134038

Alexander Warlord

Гость

Ух! Посижу теперь поразбираюсь в конструкции, чтобы понять. Спасибо!

...

Рейтинг:

0 / 0

14.02.2022, 17:33

| Ответить | Цитировать | Написать

Запрос по телефонным кодам стран

#40134044

SQL*Plus

Участник

Откуда: Россия, Москва

Сообщения: 8 665

Рейтинг: 0 / 0

Alexander Warlord

Кроме того, встречаются коды абсолютно разной длины и абсолютно разных пересечений, вроде того что:
Направление КодRussia-Mobile Beeline 810790205Russia-Mobile Beeline 810790206Russia-Mobile Beeline 810790207Russia-Mobile Beeline 810790252Russia-Mobile Beeline 8107902553Russia-Mobile Beeline 8107902554Russia-Mobile Beeline 8107902555Russia-Mobile Beeline 8107902556Russia-Mobile Beeline 8107902557Russia-Mobile Beeline 8107902559Russia-Mobile Beeline 8107902710Russia-Mobile Beeline 8107902717

А остальные 8107902 - это Теле-2. Или какие-то из серии 8107902 могут быть еще третьего оператора.

Что будете делать, когда есть конкретные телефонные номера,
не укладывающиеся ни в какие правила?

Например,
810791632145678 - Билайн
810797732145678 - Мегафон
810792632145678 - МТС
810790332145678 - Теле2

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

...

Рейтинг:

0 / 0

14.02.2022, 17:49

| Ответить | Цитировать | Написать

Запрос по телефонным кодам стран

#40134048

Alexander Warlord

Гость

Думал про это. Но на деле статистика таких номеров абсолютно не влияет на общую статистику.
Вероятно переходы фиксируются в некоей БД, где в качестве принадлежности номера выступает некий идентификатор, а не код оператора. Но к таким данным у меня доступа нет, поэтому откладываем сей вопрос :)

...

Рейтинг:

0 / 0

14.02.2022, 18:02

| Ответить | Цитировать | Написать

Запрос по телефонным кодам стран

#40134052

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

SY,

вместо having прописать where like ... не луче ли будет?

меньше группировать

.....
stax

...

Рейтинг:

0 / 0

14.02.2022, 18:23

| Ответить | Цитировать | Написать

Запрос по телефонным кодам стран

#40134079

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

я б сделал так:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

select/*+ use_nl(a) */ *
from phones p
    ,lateral(
       select/*+ no_decorrelate */ *
       from (
         select
            c_operator
         from operators o
         where o.c_range<=p.phone
           and p.phone like o.c_range||'%'
         order by o.c_range desc
       )
       where rownum=1
     )(+) a;

тестовая таблица с нужным индексом

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

create table operators(c_operator,c_range) as (
                   select 'Moldova-fix' c_operator,'810373' c_range from dual union all
                   select 'Moldova-Mobile Moldcell' operator,'81037376' range from dual union all
                   select 'Moldova-Mobile Moldcell' operator,'81037378' range from dual union all
                   select 'Moldova-Mobile Moldcell' operator,'81037379' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'81037360' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373610' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373611' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373620' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373621' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'81037368' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'81037369' range from dual
                  )
/
alter table operators
 add constraint uq_operators 
 unique(c_range) 
 using index(create index uq_operators on operators(c_range,c_operator))
/

план

Код: 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.

with phones as (
                   select '81037361100' phone from dual union all
                   select '81037378987' phone from dual union all
                   select '81037368223' phone from dual union all
                   select '81037399999' phone from dual union all
                   select 'XYZ' phone from dual
)
select/*+ use_nl(a) */ *
from phones p
    ,lateral(
       select/*+ no_decorrelate */ *
       from (
         select
            c_operator
         from operators o
         where o.c_range<=p.phone
           and p.phone like o.c_range||'%'
         order by o.c_range desc
       )
       where rownum=1
     )(+) a;

Plan hash value: 3028188581

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |      1 |        |       |    15 (100)|          |      5 |00:00:00.01 |       5 |
|   1 |  NESTED LOOPS OUTER             |                 |      1 |      5 |   100 |    15   (0)| 00:00:01 |      5 |00:00:00.01 |       5 |
|   2 |   VIEW                          |                 |      1 |      5 |    35 |    10   (0)| 00:00:01 |      5 |00:00:00.01 |       0 |
|   3 |    UNION-ALL                    |                 |      1 |        |       |            |          |      5 |00:00:00.01 |       0 |
|   4 |     FAST DUAL                   |                 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   5 |     FAST DUAL                   |                 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   6 |     FAST DUAL                   |                 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   7 |     FAST DUAL                   |                 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   8 |     FAST DUAL                   |                 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   9 |   VIEW                          | VW_LAT_5E5D704D |      5 |      1 |    13 |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       5 |
|* 10 |    COUNT STOPKEY                |                 |      5 |        |       |            |          |      4 |00:00:00.01 |       5 |
|  11 |     VIEW                        |                 |      5 |      1 |    13 |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       5 |
|* 12 |      INDEX RANGE SCAN DESCENDING| UQ_OPERATORS    |      5 |      1 |    31 |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  10 - filter(ROWNUM=1)
  12 - access("O"."C_RANGE"<="P"."PHONE")
       filter("P"."PHONE" LIKE "O"."C_RANGE"||'%')


40 rows selected.

еще возможно хорошо было бы сделать функцию с result_cache возвращающую макс длину range, чтобы в подзапрос передавать уже не полный номер, а только substr(phone,1,max_range_length()) для scalar subquery caching

...

Рейтинг:

0 / 0

14.02.2022, 19:47

| Ответить | Цитировать | Написать

Запрос по телефонным кодам стран

#40134098

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Match recognize:

Код: 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.
85.
86.
87.

with phones as (
                select '81037361100' phone from dual union all
                select '81037378987' phone from dual union all
                select '81037368223' phone from dual union all
                select '81037399999' phone from dual union all
                select 'XYZ' phone from dual
               ),
          t as (
                select  /*+
                           use_nl(p,o)
                           index(o,uq_operators)
                        */
                        p.phone,
                        o.operator,
                        o.range
                  from  phones p,
                        operators o
                  where o.range(+) <= p.phone
                    and p.phone like range(+) || '%'
               )
select  phone,
        operator
  from  t
  match_recognize(
                  partition by phone
                  order by range
                  measures
                    last(operator) operator
                  one row per match
                  pattern (p+)
                  define p as 1 = 1
                 )
/

PHONE       OPERATOR
----------- -----------------------
81037361100 Moldova-Mobile Orange
81037368223 Moldova-Mobile Orange
81037378987 Moldova-Mobile Moldcell
81037399999 Moldova-fix
XYZ


Execution Plan
----------------------------------------------------------
Plan hash value: 186320197

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                     |              |     5 |   100 |    16   (7)| 00:00:01 |
|   1 |  VIEW                                                |              |     5 |   100 |    16   (7)| 00:00:01 |
|   2 |   MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTOMATON|              |     5 |   130 |    16   (7)| 00:00:01 |
|   3 |    VIEW                                              |              |     5 |   130 |    15   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER                               |              |     5 |   190 |    15   (0)| 00:00:01 |
|   5 |      VIEW                                            |              |     5 |    35 |    10   (0)| 00:00:01 |
|   6 |       UNION-ALL                                      |              |       |       |            |          |
|   7 |        FAST DUAL                                     |              |     1 |       |     2   (0)| 00:00:01 |
|   8 |        FAST DUAL                                     |              |     1 |       |     2   (0)| 00:00:01 |
|   9 |        FAST DUAL                                     |              |     1 |       |     2   (0)| 00:00:01 |
|  10 |        FAST DUAL                                     |              |     1 |       |     2   (0)| 00:00:01 |
|  11 |        FAST DUAL                                     |              |     1 |       |     2   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN                                | UQ_OPERATORS |     1 |    31 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  12 - access("O"."RANGE"(+)<="P"."PHONE")
       filter("P"."PHONE" LIKE "RANGE"(+)||'%')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        805  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL>

SY.

...

Рейтинг:

0 / 0

14.02.2022, 21:06

| Ответить | Цитировать | Написать

Запрос по телефонным кодам стран

#40134139

Alexander Warlord

Гость

Спасибо всем за отличные идеи!!
Всё получилось просто идеально!

...

Рейтинг:

0 / 0

15.02.2022, 09:26

| Ответить | Цитировать | Написать

9 сообщений из 9, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос по телефонным кодам стран

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

Start [/forum/topic.php?fid=52&tid=1879520&gotonew=1]:	0ms
get settings:	6ms
get forum list:	8ms
check forum access:	1ms
check topic access:	1ms
track hit:	14ms
get topic data:	2ms
get first new msg:	1ms
get forum data:	1ms
get page messages:	13ms
get tp. blocked users:	0ms
others:	90ms

total:	137ms