Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MS Access > Запрос - "типа биллинга"

Автор: Andrei_V 28.8.2007, 06:55

Имеются таблицы (для простоты структура таблиц сокращена) 1 таблица "ack_cdr" (данные о телефонных звонках): Кому - тип текстовый (набранный номер, например "83425651357") Длительность - числовой (длительность разговора в секундах, например 36) 2 таблица "ТЛФ - Коды" (коды городов): префикс - тип текстовый (префикс города, куда был звонок, например "8342") тарифная зона - тип текстовый (тарифная зона, к которой относится префикс, например "Россия 2") 3 таблица "ТЛФ - Зоны" (тарифные зоны): тарифная зона - тип текстовый (тарифная зона, например "Россия 2") тариф - тип числовой (стоимость в руб минуты звонка в эту зону, например 5) Надо провести обработку файла cdr и определить стоимости звонков. Проблема в том, что в таблице codes есть префиксы  8342 - пермская область и 8 - россия Причем префиксы имеют произвольную длину - от 1 до 6 символов Если делать выборку конструктором с условием выборки  WHERE ((ack_cdr.[Длительность])>0) AND ((ack_cdr.Кому) Like [ТЛФ - Коды].[Префикс] & "*") то, например звонок на номер "83425651357" тарифицируется дважды: как "8 - россия" и как "8342 - пермская область". Разумеется такой звонок надо протарифицировать только по префиксу с максимальной длиной (т.е. по "8342"). Как написать запрос, чтобы обработка проводилась по префиксу максимальной длины? Заранее благодарен за подсказки.

Автор: kapbepucm 28.8.2007, 08:55

Следующий шаг, сделать группировку по номеру, где Len(вытащенного_префикса) максимален.

Автор: Andrei_V 28.8.2007, 09:04

Слишком общий совет.

Автор: Akina 28.8.2007, 09:31

Цитата(Andrei_V @  28.8.2007,  07:55 ) префиксы имеют произвольную длину - от 1 до 6 символов Значит, надо производить разделение номера на префикс и собственно локальный номер, выравнивать отделенный префикс и префикс из справочника пробелами до максимально возможной длины, и затем связывать уже по этому вычисляемому полю. Цитата(Andrei_V @  28.8.2007,  07:55 ) Как написать запрос, чтобы обработка проводилась по префиксу максимальной длины? Код Where Len(Prefix) = Max(Len(Prefix)) В случае MS Access - для этого придется использовать отдельный подзапрос.

Автор: Andrei_V 29.8.2007, 08:31

Цитата(Akina @  28.8.2007,  09:31 ) надо производить разделение номера на префикс и собственно локальный номер В этом-то и основная сложность. :(

Автор: Akina 29.8.2007, 08:44

Цитата(Andrei_V @  29.8.2007,  09:31 ) В этом-то и основная сложность. :(  Если ты не можешь написАть вменяемый алгоритм отделения префикса от номера - не проси этого от программы. И вообще - пока нет алгоритма, код писАть бессмысленно.  Программа - дура, и делает только то и так, что и как ты ей сказал делать. Цитата(Andrei_V @  28.8.2007,  07:55 ) звонок на номер "83425651357" тарифицируется дважды: как "8 - россия" и как "8342 - пермская область".  А что, есть звонки, где "8", а потом сразу номер?

Автор: kapbepucm 29.8.2007, 09:18

C алгоритмом помочь? Код Public Function ExtractPrefiks(Nomer As String) As String   ExtractPrefiks = ""   Dim DBS As Database   Dim RST As Recordset   Set DBS = CurrentDb   Set RST = DBS.OpenRecordset("TLF_Kodi")   If RST.RecordCount > 0 Then     RST.MoveLast     RST.MoveFirst     Dim I As Long     For I = 1 To RST.RecordCount       If RST![prefiks] = Left(Nomer, Len(RST![prefiks])) Then         If Len(RST![prefiks]) > Len(ExtractPrefiks) Then ExtractPrefiks = RST![prefiks]       End If       If I < RST.RecordCount Then RST.MoveNext     Next I   End If   RST.Close   DBS.Close End Function Конечно, надо придумать что-нибудь продуктивнее, это будет страшно тормозить, только для теста.

Автор: bopoha 29.8.2007, 23:50

kapbepucm,  Я в шоке! Все намного проще. Код Do While Not rst.EOF      ...      rst.MoveNext Loop Основной тормоз тут MoveLast после открытия. И еще, зачем эта функция? Походу такое можно и в запрос засунуть. Andrei_V,  Под "определить стоимости звонков" понимается записать в поле какой-то таблицы сумму. То получается, что посчитать это будет проще процедурой VBA, чем зарпосом (При его помощи можно оптимизировать вычисления). Тарифы телефонных кодов можно закешировать (Collection или Recordset статический) - летать будет . Цитата(Andrei_V @  29.8.2007,  08:31 ) надо производить разделение номера на префикс и собственно локальный номер В этом-то и основная сложность. :( Почему это сложность? Если в цикле обрабатывать номера, то можно делать разбор номера по шаблону. Код If номер Like шаблон then ... End If Примерный алгоритм: 1. Цикл по номерам     1.1. Выделить из номера код     1.2. Получить тариф звонка     1.3  Расчитать стоимость      .....

Автор: kapbepucm 30.8.2007, 09:07

Частично взяв идеи bopoha, коррекция (хотя я предупреждал, что вариант неидеальный): Код Public Function ExtractPrefiks2(Nomer As String) As String   ExtractPrefiks2 = ""   Dim DBS As Database   Dim RST As Recordset   Set DBS = CurrentDb   Set RST = DBS.OpenRecordset("TLF_Kodi")   Do While Not RST.EOF     If Nomer Like (RST![prefiks] & "*") Then       If Len(RST![prefiks]) > Len(ExtractPrefiks2) Then ExtractPrefiks2 = RST![prefiks]     End If     RST.MoveNext   Loop   RST.Close   DBS.Close End Function Всё же тормозит, надо ещё лучше что нибудь сделать... To bopoha Цитата(bopoha @  29.8.2007,  23:50 ) И еще, зачем эта функция? Был у меня случай, где я долго искал ошибку (там этим способом определялось количество записей, но Recordset другого типа, естественно), так теперь это, как рефлекс  Цитата(bopoha @  29.8.2007,  23:50 ) Тарифы телефонных кодов можно закешировать (Collection или Recordset статический) - летать будет Можешь привести пример?

Автор: bopoha 31.8.2007, 01:19

Цитата(kapbepucm @  30.8.2007,  09:07 ) Всё же тормозит, надо ещё лучше что нибудь сделать... Вариант номер 1.  Код Public Function ExtractPrefiks2(ByRef RST As Recordset, ByVal Nomer As String) As String   ExtractPrefiks2 = ""   RST.MoveFirst   Do While Not RST.EOF     If Nomer Like (RST![prefiks] & "*") Then       If Len(RST![prefiks]) > Len(ExtractPrefiks2) Then ExtractPrefiks2 = RST![prefiks]     End If     RST.MoveNext   Loop End Function На открытие таблицы тратится куча времени! Где-то перед задолго до многочисленного вызова функции откываем набор как статический (т.е. все данные читаем в память): Код ... Set RST = DBS.OpenRecordset("TLF_Kodi", dbOpenSnapshot) ... Если оставаться при предыдущем варианте следовало бы открывать как dbOpenForwardOnly - для прохода один раз. Вариант номер 2. Если исходить из того факта, что нужны не все коды. Их в таблице гораздо больше, чем сравниваемых.  То:  В.2.1. Отфильтровать набор при открытии по возрастанию кодов. Перед поиском находить запись по первой цифре искомого номера. В.2.1. Открывать набор в функции с фильтром по первой цифре номера (или минимальному количеству цифр). Цитата(kapbepucm @  30.8.2007,  09:07 ) Был у меня случай, где я долго искал ошибку (там этим способом определялось количество записей, но Recordset другого типа, естественно), так теперь это, как рефлекс  Это не объясняет назначение функции. Меня интересует что она даст? Зачем она? Т.е. подразумевается ответ: "Функция получает ... находит .... и возвращает ....". Цитата(kapbepucm @  30.8.2007,  09:07 ) Можешь привести пример? На тему статического набора я пример привел. По поводу Collection Код Public Function CreateCache() As Collection '-- создать кэш     Dim col As Collection     Dim rst As DAO.Recordset          Set col = New Collection     Set rst = ....     Do While Not rst.EOF         col.Add rst("значение"), rst("искомое поле")         rst.MoveNext     Loop     rst.Close     Set CreateCache = col End Function Public Function GetValFromColl(ByRef col As Collection, ByVal stKey As String) As String '-- прочитать значение из коллекции по ключу '-- в случае отсутствия ключа возвращает пустую строку On Error GoTo Err_     GetValFromColl = col(stKey) Exit_:     Exit Function Err_:     GetValFromColl = ""     Resume Exit_ End Function При этом нужно учитывать что Collection находится в памяти и не стоит туда загянять очень много. Естественно все это будет работать при вычислении в коде.

Автор: Andrei_V 31.8.2007, 08:51

Цитата(Akina @  29.8.2007,  08:44 ) А что, есть звонки, где "8", а потом сразу номер?  Номера все имеют вид 8-код_города_номер. Но дело в тарифах. Например (очень упрощенно): 83422 - 1 руб/мин 83512 - 2 руб/мин остальная 8  - 3 руб/мин Цитата(bopoha @  29.8.2007,  23:50 ) Под "определить стоимости звонков" понимается записать в поле какой-то таблицы сумму. То получается, что посчитать это будет проще процедурой VBA, чем зарпосом (При его помощи можно оптимизировать вычисления). Как-то не силен я пока код писать в MS Access. До сих пор делал все конструкторами - таблицы, запросы, отчеты. Большего не требовалось. И мне казалось, что и мою задачу можно решить не прибегая к непосредственному написанию кода на VB. Цитата(bopoha @  31.8.2007,  01:19 ) Вариант номер 1. Функция-то ясна. Сам был программистом, правда лет 10 назад.  Как ее прикрутить в базу Access-а...

Автор: kapbepucm 31.8.2007, 09:14

To bopoha Цитата(bopoha @  31.8.2007,  01:19 ) Меня интересует что она даст? Зачем она? Случай был подобным: Код   Dim DBS As Database   Dim RST As Recordset   Set DBS = CurrentDb   Set RST = DBS.OpenRecordset("SELECT TLF_Kodi.Prefiks FROM TLF_Kodi;")   'здесь, кроме как использавать RST.MoveLast, чтобы RST.RecordCount   'был похож на реальную цифру, способов не знаю   RST.Close   DBS.Close Естественно, к этой ситуации не имеет отношения, да и Recorset другого типа. To Andrei_V Цитата(Andrei_V @  31.8.2007,  08:51 ) Как ее прикрутить в базу Access-а А где и как данные будут выводится (вырисовывается 2 варианта: лёгкий, но тормознутый; и более сложный, но быстро летающий)?

Автор: Akina 31.8.2007, 12:04

ДЛя ускорения работы надо использовать Код Set RST = DBS.OpenRecordset("SELECT TLF_Kodi.Prefiks FROM TLF_Kodi ORDER BY Len(TLF_Kodi.Prefiks) DESC;") Тогда можно просто бежать по рекордсету до первого совпадения - все следующие будут с заведомо более коротким префиксом. И не потребуется использования Like - можно сразу Код If Prefix = Left(Phone, Len(Prefix)) Then

Автор: Andrei_V 5.9.2007, 14:21

Цитата(kapbepucm @  31.8.2007,  09:14 ) А где и как данные будут выводится (вырисовывается 2 варианта: лёгкий, но тормознутый; и более сложный, но быстро летающий)? Кратко об обоих вариантах можно узнать? Пока выводится просто как результат запроса в виде таблицы в среде MS Access

Автор: bopoha 5.9.2007, 23:28

До меня дошел алгоритм поиска тарифа за код: Начинать поиск нужно с самого длинного кода (84320), если не найден убрать одну цифру ((84320) поискать. Если опять не нашли повторить. Правильно? Очень рекомендую разобраться с VBA, такое просто запросами тяжеловато. 1. Можно отдельной функцией реализовать выше указанный алгоритм и в запросе вызывать ее, передвая функции код. Это очень медленный вариант. 2. Либо программно все сделать. Это вариант будет быстрее. Ничего сложного в этом нет.

Автор: kapbepucm 10.9.2007, 14:21

bopoha, второй вариант оказывается относительно быстрее. А что, если данные о произведённых звонках дополняютя 1 раз в 5 секунд, а запросы надо делать тоже часто?

Автор: bopoha 11.9.2007, 23:04

Какая стоит задача? Если есть возможность, то вообще идеальным было бы при добавлении и расчитывать стоимость. В противном случае, только перед использованием стоимости звонков. Расчитывать же можно только звонки без сумм.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)