Гость
Войти | Профиль | Очистить
Map
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос по телефонным кодам стран / 9 сообщений из 9, страница 1 из 1
14.02.2022, 16:15
    #40134017
Alexander Warlord
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, 17:30
    #40134037
SY
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.
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:33
    #40134038
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос по телефонным кодам стран
Ух! Посижу теперь поразбираюсь в конструкции, чтобы понять. Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.02.2022, 17:49
    #40134044
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос по телефонным кодам стран
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, 18:02
    #40134048
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос по телефонным кодам стран
Думал про это. Но на деле статистика таких номеров абсолютно не влияет на общую статистику.
Вероятно переходы фиксируются в некоей БД, где в качестве принадлежности номера выступает некий идентификатор, а не код оператора. Но к таким данным у меня доступа нет, поэтому откладываем сей вопрос :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.02.2022, 18:23
    #40134052
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос по телефонным кодам стран
SY,

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

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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.02.2022, 19:47
    #40134079
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос по телефонным кодам стран
я б сделал так:
Код: 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, 21:06
    #40134098
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос по телефонным кодам стран
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
| Ответить | Цитировать | Написать  
15.02.2022, 09:26
    #40134139
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос по телефонным кодам стран
Спасибо всем за отличные идеи!!
Всё получилось просто идеально!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос по телефонным кодам стран / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 739ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1879520]:
 0ms
get settings:
 24ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 79ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 221ms
get tp. blocked users:
 2ms
others: 375ms
total:  739ms
x
x
Закрыть


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