Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Помогите с запросом, плиз  [new]
Alexey Koptenkov
Member

Откуда:
Сообщений: 18
Доброго дня!

В наличии 3 таблички (DESTINATIONS, AREACODES, CALLS) с данными:


DESTINATIONS
ID NAME
1 Russia
2 Russia mobile
3 Kazakhstan

AREACODES
ID DID MASK
1 1 7
2 2 79
3 3 731
4 3 732

CALLS
ID DNIS DURATION
1 79046462656 123
2 79221423058 356
3 73134221562 68
4 73131331515 79
5 73261867715 694
6 73472541752 354
7 73472541753 123

В результате необходимо получить следующее:

NAME		SUM(DURATION)
Russia 477
Kazakhstan 841
Russia mobile 479

Возможно ли вообще решить посредством SQL или нужно PL/SQL?

Заранее спасибо!
12 сен 06, 13:17    [3123901]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
malabay
Member

Откуда:
Сообщений: 113
Мах длина номера известна и постояна?Да-10. Юзай substr
12 сен 06, 13:32    [3124043]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
Elic
Member

Откуда:
Сообщений: 29977
Alexey Koptenkov
Возможно ли вообще решить посредством SQL
Да: join по like-у + row_number() over (partition by CALLS.ID order by length(AREACODES.MASK) desc) as rn + (rn = 1) + ...
12 сен 06, 13:34    [3124056]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
Alexey Koptenkov
Member

Откуда:
Сообщений: 18
Максимальная длина номера известна, но не постоянна, в разных страннах свой подход к делению нумерационных ресурсов.
substr использую, но проблема в том, что в код России входят коды Казахстана, и если делать такое объединение, то получится не совсем то, что хочется, а имено:

NAME		SUM(DURATION)
Russia 1797
Kazakhstan 841
Russia mobile 479

:/
12 сен 06, 13:38    [3124086]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
malabay
Member

Откуда:
Сообщений: 113
Кстати, а тарификация происходит по минимальному(менее детализированному) направлению?
12 сен 06, 13:40    [3124091]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
contr
Member

Откуда:
Сообщений: 1909
Alexey Koptenkov
Возможно ли вообще решить посредством SQL или нужно PL/SQL?

Ну если только извратиться, в production такое все одно пускать нельзя:
SQL> with DESTINATIONS as(
  2  select 1 id,	'Russia' name from dual union all select
  3  2, 'Russia mobile' from dual union all select
  4  3, 'Kazakhstan' from dual),
  5  AREACODES as (
  6  select 1 id,	1 did,	'7' mask  from dual union all select
  7  2,	2,	'79' from dual union all select
  8  3,	3,	'731' from dual union all select
  9  4,	3,	'732' from dual),
 10  CALLS as (select
 11  1 ID,	'79046462656' DNIS,	123 DURATION from dual union all select
 12  2,	'79221423058',	356 from dual union all select
 13  3,	'73134221562',	68 from dual union all select
 14  4,	'73131331515',	79 from dual union all select
 15  5,	'73261867715',	694 from dual union all select
 16  6,	'73472541752',	354 from dual union all select
 17  7,	'73472541753',	123 from dual)
----------------------<Тестовые данные закончились>----------------------------
 18  select min(name) name, sum(duration) duration from(
 19  select min(a.did) keep (dense_rank first order by length(a.mask) desc) areacode_did
 20       , min(d.name) keep (dense_rank first order by length(a.mask) desc) name
 21       , min(c.duration) keep (dense_rank first order by length(a.mask) desc) duration
 22  from destinations d
 23     , areacodes a
 24     , calls c
 25  where d.id=a.did
 26    and c.dnis like a.mask||'%'
 27  group by c.id
 28  ) group by areacode_did
 29  ;

NAME            DURATION
------------- ----------
Russia               477
Russia mobile        479
Kazakhstan           841

SQL> 
12 сен 06, 13:55    [3124195]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116100
Вроде решил

Тестовые данные

with tab1 as
(select 1 ID, 'Russia' name from dual
 union all
 select 2 ID, 'Russia mobile' name from dual
 union all
 select 3 ID, 'Kazakhstan' name from dual
 )
 , tab2 as
 (select 1 id, 1 did, 7 mask1 from dual
  union all
  select 2 id, 2 did, 79 mask1 from dual
  union all
  select 3 id, 3 did, 731 mask1 from dual
  union all
  select 4 id, 3 did, 732 mask1 from dual
  ),
  tab3 as
  (select 1 ID, 79046462656 dnis, 123 duration
   from dual
   union all 
   SELECT 2	,79221423058,	356 from dual
   union all
   SELECT  3,	73134221562,	68  from dual
   union all
   SELECT 4,	73131331515,	79 from dual
   union all
   SELECT 5,  73261867715,	694 from dual
   union all
   SELECT 6,	73472541752,	354 from dual
   union all
   SELECT 7,	73472541753,	123 from dual
  )

Запрос

select tab1.id, sum(tab3.duration) duration
   from tab1, tab2 t2, tab3
  where tab1.id = t2.did
    and INSTR(tab3.dnis, t2.mask1) != 0
    AND NOT EXISTS
  (SELECT null
           from tab2
          WHERE INSTR(tab3.dnis, tab2.mask1) != 0
            and length(tab2.mask1) > length(t2.mask1))
  group by tab1.id
/

ID	DURATION
1	477
2	479
3	841
12 сен 06, 13:58    [3124222]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
kennethr
Member

Откуда:
Сообщений: 175
2 Alexey Koptenkov: Да там и похуже ситуации бывают. Одна обработка 810 чего стоит. Мой тебе совет, сразу делай логику на PL\SQL.
12 сен 06, 14:02    [3124254]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
contr
Member

Откуда:
Сообщений: 1909
dmidek
Вроде решил

Не поверите, но телефонные номера в NUMBER не хранятЪ
Как бывший телефонист говорю ;)
12 сен 06, 14:04    [3124269]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
contr
Member

Откуда:
Сообщений: 1909
kennethr
2 Alexey Koptenkov: Да там и похуже ситуации бывают. Одна обработка 810 чего стоит. Мой тебе совет, сразу делай логику на PL\SQL.

Да ничего она не стоит.
Надо приводить к Е.164 и не париться.
12 сен 06, 14:06    [3124279]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
OdinNNM
Member

Откуда:
Сообщений: 12
Вот, насколько правда это поможет...
select name
, (select sum(c.duration)
from areacodes a
, calls c
where a.did = destinations.id
and c.dnis like a.mask || '%'
and not exists (
select 1
from areacodes
where c.dnis like areacodes.mask || '%'
and areacodes.id <> a.id
and length(areacodes.mask) > length(a.mask))) as b
from destinations
/
12 сен 06, 14:06    [3124280]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116100
contr
dmidek
Вроде решил

Не поверите, но телефонные номера в NUMBER не хранятЪ
Как бывший телефонист говорю ;)

Аа. Так это даже хорошо. Запрос быстрее будет :-)
12 сен 06, 14:07    [3124293]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
malabay
Member

Откуда:
Сообщений: 113
А можно так (при условии, что напрвление выбирается самое детализированное)
SELECT t1.mask,sum(t2.duration) FROM  
(select DISTINCT b.dnis,
                MAX(a.mask1)  mask
   from
   (select 1 id, 1 did, 7 mask1 from dual
  union all
  select 2 id, 2 did, 79 mask1 from dual
  union all
  select 3 id, 3 did, 731 mask1 from dual
  union all
  select 4 id, 3 did, 732 mask1 from dual
  ) a
  ,
(select 1 ID, 79046462656 dnis, 123 duration
   from dual
   union all 
   SELECT 2,79221423058,356 from dual
   union all
   SELECT  3,73134221562,68  from dual
   union all
   SELECT 4,73131331515,79 from dual
   union all
   SELECT 5,  73261867715,694 from dual
   union all
   SELECT 6,73472541752,354 from dual
   union all
   SELECT 7,73472541753,123 from dual)  b 
  where 
     a.mask1 IN (substr(b.dnis,1,1)
	                      ,substr(b.dnis,1,2)
						  ,substr(b.dnis,1,3)
						  ,substr(b.dnis,1,4)
						  ,substr(b.dnis,1,5)
						  ,substr(b.dnis,1,6)
						  ,substr(b.dnis,1,7)
						  ,substr(b.dnis,1,8)
						  ,substr(b.dnis,1,9)
						  ,substr(b.dnis,1,10)
 						  ,substr(b.dnis,1,11)
						  )
GROUP BY b.dnis) t1
,
(select 1 ID, 79046462656 dnis, 123 duration
   from dual
   union all 
   SELECT 2,79221423058,356 from dual
   union all
   SELECT  3,73134221562,68  from dual
   union all
   SELECT 4,73131331515,79 from dual
   union all
   SELECT 5,  73261867715,694 from dual
   union all
   SELECT 6,73472541752,354 from dual
   union all
   SELECT 7,73472541753,123 from dual) t2
WHERE t1.dnis=t2.dnis   
GROUP BY t1.mask

Правда базу можно положить, так как запрос будет работать ,как я понимаю, по логу звонков...
12 сен 06, 14:22    [3124416]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
contr
Member

Откуда:
Сообщений: 1909
malabay
А можно так

Лучше не позорьтесь.
1) поиск маски:
where a.mask1 = (substr(b.dnis,1,length(a.mask1)) 
будет достаточно.
2) Вот это действительно важно:
WHERE t1.dnis=t2.dnis  
Ну-ка добавьте десяток звоночков на один и тот же номер, посмотрим на результатик
12 сен 06, 14:35    [3124529]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
Elic
Member

Откуда:
Сообщений: 29977
malabay
но почему неправильное-то?
length(a.mask1)>=12
12 сен 06, 14:39    [3124570]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
malabay
Member

Откуда:
Сообщений: 113
contr

Ну-ка добавьте десяток звоночков на один и тот же номер, посмотрим на результатик


Согласен, косяк...Достаточно будет двух одинаковых звонков
12 сен 06, 14:40    [3124575]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
malabay
Member

Откуда:
Сообщений: 113
Elic
malabay
но почему неправильное-то?
length(a.mask1)>=12

Не разу не видел направления больше 11 :-). Но косяк ясен...

malabay
contr

Ну-ка добавьте десяток звоночков на один и тот же номер, посмотрим на результатик


Согласен, косяк...Достаточно будет двух одинаковых звонков


Кстати, обшибься. Добавил, все нормально...

В любом случае всем спасибо за поправки....Будем работать
12 сен 06, 14:58    [3124741]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
Alexey Koptenkov
Member

Откуда:
Сообщений: 18
Не разу не видел направления больше 11 :-).

Есть больше 11. Я просто к 7 привязался чтобы было ближе так сказать, а так считай весь мир от 1 до 9.
12 сен 06, 15:08    [3124828]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
malabay
Member

Откуда:
Сообщений: 113
Alexey Koptenkov

Есть больше 11. Я просто к 7 привязался чтобы было ближе так сказать, а так считай весь мир от 1 до 9.

Да не, мы про длину направления...
12 сен 06, 15:14    [3124868]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
Alexey Koptenkov
Member

Откуда:
Сообщений: 18
Elic
Alexey Koptenkov
Возможно ли вообще решить посредством SQL
Да: join по like-у + row_number() over (partition by CALLS.ID order by length(AREACODES.MASK) desc) as rn + (rn = 1) + ...


Чтото не соображу как.. Извиняюсь, но с Ораклом маленькая практика у меня..
12 сен 06, 16:25    [3125376]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
Elic
Member

Откуда:
Сообщений: 29977
Alexey Koptenkov
Elic
Alexey Koptenkov
Возможно ли вообще решить посредством SQL
Да: join по like-у + row_number() over (partition by CALLS.ID order by length(AREACODES.MASK) desc) as rn + (rn = 1) + ...
Чтото не соображу как.. Извиняюсь, но с Ораклом маленькая практика у меня..
Тоже самое, но немного по-другому уже продемонстрировал contr, за что ему (но не тебе Картинка с другого сайта.) спасибо. Через rn "буквов" меньше :)
select name, sum(duration) as duration 
  from
  ( select d.name, c.duration,
        row_number() over (partition by c.id order by length(a.mask) desc) as rn
      from destinations d, areacodes a, calls c
      where d.id = a.did
        and c.dnis like a.mask || '%'
  )
  where rn = 1
  group by name
12 сен 06, 17:00    [3125665]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
Alexey Koptenkov
Member

Откуда:
Сообщений: 18
спасибо всем!
12 сен 06, 17:42    [3125948]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, плиз  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116100
dmidek
Вроде решил

Запрос

select tab1.id, sum(tab3.duration) duration
   from tab1, tab2 t2, tab3
  where tab1.id = t2.did
    and INSTR(tab3.dnis, t2.mask1) != 0
    AND NOT EXISTS
  (SELECT null
           from tab2
          WHERE INSTR(tab3.dnis, tab2.mask1) != 0
            and length(tab2.mask1) > length(t2.mask1))
  group by tab1.id
/

ID	DURATION
1	477
2	479
3	841


Утром стукнуло в голову.
Если уж решил зачем- то пользоваться INSTR, то нужно правильно
делать. Конечно же = 1!

Очередная "магия исходных данных". Забавно, что я на них практически не
обращал внимания :-)
13 сен 06, 10:15    [3127969]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить