Здравствуйте.
Встала задача перевести абонентов Интернет провайдера одного тарифного плана (льготного) на тариф-наследник после прошествия пяти месяцев, но необходимо учитывать тот факт что пользователь мог в этот период отключаться (уезжал – попросил отключить его, дабы не начислялась абонентская плата), в это время его тарифный план имел ID=0. То время, которое он не работал нужно вычитать из общего времени работы пользователя. Например, он отключался на 10 дней, то и перевести на тариф-наследник его нужно через 5 месяцев 10 дней.

Базы данных: биллинговая система – Oracle и система управления (надстройка на биллингом) – MySQL, поэтому сделать в один запрос данное действие не получится.
Тогда будем определять, каких абонентов необходимо переключить на тариф-наследник, а уже языком скрипта, с учётом информации из базы системы управления, переключать необходимого пользователя в БД биллинга.

Выстроил следующий алгоритм:
1. В истории изменений тарифных планов выбираем всех абонентов с текущим тарифом равному одному из льготных.
2. Просматриваем всю историю абонента, вычитаем время отключенного состояния.
3. Если сумма времени работы абонента равно, либо превышает 5 месяцев, то переводим его с льготного тарифа на тариф-наследник.

Таблица «CONNECT_TARIFS_HIST» изменений тарифных планов биллинговой системы:
Провожу эксперименты над абонентом, ID_CONNECT=7443 (ID_подключения).
Сначала ему назначали льготный тариф ID_TARIF=244, затем после 5 месяцев поменяли на тариф-наследник (в данный момент это делается вручную) ID_TARIF=226.

Пытаюсь написать простенький запрос, без учёта возможных отключения абонента за этот срок:Именно в таком виде он работает, но смысла от него ноль, так как выводится оба тарифа у данного ID_CONNECT (ID_подключения), что в принципе верно исходя из запроса, но что-то на иные варианты для нахождения именно последнего тарифного плана не хватает навыков. 

Если возможно, то пожалуйста приведите вариант запроса на нахождения абонентов для изменения тарифного плана на льготный, с учётом возможного отключения от сети по просьбе самого абонента и вычитания этого срока из общего срока работы.

На сколько я смог осмыслить заданный Вами вопрос.

Вы слишком многословны. Не всякий прасполагает достаточным количеством времени, чтобы разобраться в блужданиях ваших мыслей. Постарайтесь впредь быть более лаконичным по существу.

Приведенный Вами набор данных не репрезентативен. Было бы не плохо, если бы в приведенном Вами примере были бы приведены как данные которые подлежат отсеву, так и те, которые подлежат отбору и пояснение каков должен быть результат и почему. Было бы не плохо, если бы представленный набор данных содержал бы все варианты, которые следует предусмотреть. Было бы просто замечательно, если бы приведенный вами набор легко бы был воспроизведен теми, кто решит попытаться Вам помочь.Делают это обычно либо серией инсертов, либо select ... union all select.... Я, лично, предпочитаю второе.

По существу:
Вам следует определиться с понятием 5 месяцев.
Допустим клиент был подключен с первого по 27 февраля, затем на 28е февраля добровольно отключился, а первого марта включился обратно. Можно ли считать что на момент второго марта клиент был подключен один месяц? 

По данному запросу получаю список, где отсутствуют id_connect, которых уже перевели на тариф-наследник и все остальные id_connect с не льготными тарифными планами – это верно, но в этом же списке присутствуют id_connect, которых подключили меньше 150 дней.


По id_connect=9348 найдем тарифную историю пользователя:

В графическом виде:

То есть данного пользователя (id_connect) подключили сегодня (16.07.2010), а он присутствует в списке на смену тарифного плана.


Данный период лучше взять за 150 дней и рассчитывать именно этот срок – я так понял вы так и сделали.



Впредь буду внимательнее к составлению вопроса. Считал что короткий вопрос, без объяснения и введения в курс дела, малоинформативен, а данные что предоставил являются достаточными.

Возьмём заново часть таблицы истории:


id_connect=7869 – идеал картины,  12.02.2010 был подключен, выставлен один из льготных тарифов (ID_TARIF=244), отключился 14.06.2010 (кстати, я ошибся в первом сообщении о ID=0 – при отключении выставляют тариф ID=76), затем заново его включили 01.07.2010. По прошествии 5 месяцев перевели на тариф-наследник 12.07.2010 (в данный момент, при переводе абонентов, не учитывается, то что они возможно не работали какой то период). Данный пользователь не попал в список выведенным вашим запросом – это верно.
id_connect=8000 – (добавил в таблицу в качестве примера) пользователь, который должен быть переведен на новый тариф, так как со дня его работы прошло 151 день.
id_connect=9348 – подключен сегодня 16.07.2010, выставлен один из льготных тарифов (ID_TARIF=204).

Что необходимо изменить в запросе, для вывода id_connect лишь тех абонентов, отвечающих условию:

• в данный момент выставлен льготный тариф
• срок работы данного тарифного плана больше 150 дней, с учётом дней, когда абонент просил отключить его от сети

У данного условия проблема - пользователь мог сменить с льготного на другой льготный тариф (вроде как скорость/абонентская плата не понравилась, перешел на иной тариф) – тогда условие, что привёл выше работать не будет.
Уфф...

Это сообщение отредактировал(а) PriZraK - 16.7.2010, 15:47

я ошибся в операторе сравнения во втором условии. вместо "больше" следовало бы использовать "меньше".

Заработало!
Что можно почитать на русском про такие конструкции:


Это сообщение отредактировал(а) PriZraK - 16.7.2010, 15:55

Это аналитическая функция. Возвращеет первое следующее  по порядку dt значение dt в пределах id_connect. В случае отсутствия следующего значения, вернет sysdate.

Это аггрегатная фунция. Возвращает минимальное значение id_tarif из строк, в пределах группы имеющих наибольший ранг по dt.

Где почитать по русски не знаю.

Спасибо вам, не в первый раз выручаете.