ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Посчитать сумму периодов с учетом перекрытия
3 сообщений из 3, страница 1 из 1
Посчитать сумму периодов с учетом перекрытия
    #40046172
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Необходимо вести посуточный учет подписок на внешнем сервисе.
Статус подписки (активно/неактивно) ведется в "исторической" таблице (статус, дата_начала, дата_окончания).
При первом подключении дается демо-период на 14 суток, демо-период нужно учитывать отдельно.
Есть такой запрос:
Код: 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.
with MGG as
(
select ...
, CAL.M
, SS.STATUS_ID
, SS.DATE_BEG as STATUS_BEG
, SS.DATE_END as STATUS_END
, greatest(CAL.M, trunc(SS.DATE_BEG)) as PERIOD_BEG
, least(add_months(CAL.M,1)-1, nvl(trunc(SS.DATE_END), add_months(CAL.M,1)-1)) as PERIOD_END
, least(add_months(CAL.M,1)-1, nvl(trunc(SS.DATE_END), add_months(CAL.M,1)-1)) - greatest(CAL.M, trunc(SS.DATE_BEG)) + 1 as PERIOD_LEN
, case when CAL.M = trunc(min(greatest(CAL.M, trunc(SS.DATE_BEG))) over (partition by S.SERVICE_ID), 'MONTH') then 1 end as IS_DEMO
from ...
join (select add_months(trunc(sysdate,'MM'),-NUM+1) as M from BM_NUMBER where NUM between 1 and 12) CAL on (CAL.M is not null)
where ...
and SS.STATUS_ID > 0
and trunc(SS.DATE_BEG,'MM') <= CAL.M
and (SS.DATE_END is null or trunc(SS.DATE_END,'MM') >= CAL.M)
and CAL.M < sysdate
)
select M, TARIFF_ID, TARIFF
, count(*) as QTY
, sum(PERIOD_LEN) as LEN
, sum(decode(IS_DEMO,1,null,PERIOD_LEN)) as WORK
, sum(decode(IS_DEMO,1,PERIOD_LEN)) as DEMO
from MGG
where M = add_months(trunc(sysdate,'MM'),-1)
group by M, TARIFF_ID, TARIFF
order by M, TARIFF_ID, TARIFF


В подзапросе берутся данные за последний год, для месячного периода считается количество дней, в течении которых услуга была активна, затем во внешнем запросе эти данные группируются по типу подписки и только за последний месяц.
Все работает, но есть один нюанс.
Например услуга была активна в период с 1 по 14, а с 14 до 31 была неактивна. Число дней считаем как (14-1+1)=14 суток. Тут все правильно.
Но возможны более сложные случаи. Например услуга была активна в период с 1 по 14, затем в середине дня она была отключена, а вечером снова включена до конца месяца. Тогда активные периоды получатся 1-14 и 14-31, а общая продолжительность соответственно 14 и 18 дней, суммарно 32 дня. То есть больше месяца, потому что 14 учитывалось дважды.

Ранее в подобных случаях я обычно "разворачивал" периоды по суткам, оставлял сутки с активным состоянием, а затем группировал и суммировал. Но тут число строк с услугами/подписками ожидается большое, поэтому такой подход будет неоптимальным.
Можно использовать аналитику, чтобы в подзапросе определять, имеются ли подобные "граничные" смены состояний, но как-то это сложновато. Можно ли исключить двойной подсчет во внешнем запросе (ориентируясь исключительно на PERIOD_BEG и PERIOD_END)?
...
Рейтинг: 0 / 0
17.02.2021, 12:43
| Ответить | Цитировать | Написать  
Посчитать сумму периодов с учетом перекрытия
    #40046195
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Alibek B.,

Код: 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.
SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2  select  1 id, 1 b, 14 e from dual union all
  3  select 1,14,31 from dual union all
  4  select 2 id, 3 b, 4 e from dual union all
  5  select 2,4,4 from dual union all
  6  select 2,4,7 from dual union all
  7  select 2,8,10 from dual
  8  )
  9  select
 10    t.*
 11    ,e-b+case when lag(e) over (partition by id order by b)=b then 0 /*when ... */ else 1 end  len
 12  from t
 13* order by 1,2
SQL> /

        ID          B          E        LEN
---------- ---------- ---------- ----------
         1          1         14         14
         1         14         31         17
         2          3          4          2
         2          4          4          0
         2          4          7          3
         2          8         10          3

6 rows selected.



......
stax
...
Рейтинг: 0 / 0
17.02.2021, 13:02
| Ответить | Цитировать | Написать  
Посчитать сумму периодов с учетом перекрытия
    #40046230
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Спасибо за пример, идея понятна.
Я думал сделать немного по другому — суммировать по прежнему, но затем из суммы вычитать число промежуточных интервалов.
...
Рейтинг: 0 / 0
17.02.2021, 13:28
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Посчитать сумму периодов с учетом перекрытия
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=25&tid=1880430]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 27ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 64ms
get tp. blocked users:
 2ms
others: 14ms
total:  149ms
созд. / загр.: 149ms
x
x
Закрыть


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