Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос с перебором дат / 15 сообщений из 15, страница 1 из 1
07.09.2020, 17:52
    #39996301
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Приветствую!
И в очередной раз прошу помощи у форумчан..
Есть запрос:

Код: 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.
SELECT UNIQUE
    sr.SERV_NAME AS SERV_NAME,
    TRUNC(ssh.CRE_DATE) AS CRE_DATE,
    tp.TRPL_NAME AS TRPL_NAME,
    ph.MSISDN AS MSISDN,
    ssh.SUBS_ID AS SUBS_ID,
    ssh.SSTAT_ID AS SSTAT_ID,
    sst.SSTAT_NAME AS SSTAT_NAME
FROM
    SUBS_SERV_HISTORY ssh,
    SUBS_HISTORY sh,
    TARIFF_PLAN tp,
    SERVICE sr,
    SERV_STATUS sst,
    PHONE ph
WHERE
    ssh.SERV_ID IN (557)
    AND ssh.SSTAT_ID = 1
    AND sh.SUBS_ID = ssh.SUBS_ID
    AND sh.TRPL_ID = tp.TRPL_ID
    AND tp.TRPL_ID IN (207,208,209,210)
    AND sh.PHONE_ID = ph.PHONE_ID
    AND sr.SERV_ID = ssh.SERV_ID
    AND ssh.SSTAT_ID = sst.SSTAT_ID
    AND sh.STAT_ID = 1
    AND TRUNC(:REP_DATE) BETWEEN sh.STIME AND sh.ETIME - 1 / 86400
    AND TRUNC(:REP_DATE) BETWEEN ssh.STIME AND ssh.ETIME - 1 / 86400
--ORDER BY ssh.CRE_DATE, ph.MSISDN



Можно ли тут вместо TRUNC(:REP_DATE) подставить перебор дат:

Код: plsql
1.
2.
3.
4.
5.
SELECT beg_date + LEVEL - 1 AS DAY_MONTH
FROM   (SELECT TO_DATE('01.08.2020', 'dd.mm.yyyy') beg_date,
               TO_DATE('31.08.2020', 'dd.mm.yyyy') end_date
        FROM   dual)
CONNECT BY LEVEL <= end_date - beg_date + 1



Одним словом, надо получить все ph.MSISDN со статусом sh.STAT_ID = 1 на каждый день за период. Как бы это сделать в цикле за указанный диапазон дат?..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2020, 18:00
    #39996304
oragraf
oragraf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Alexander Warlord,

Не надо цикла.
Код: plsql
1.
... join (select trunc(<start_date> +level) from dual connect by level < 10) d on ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2020, 18:16
    #39996306
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Alexander Warlord,

я б перебором не делал

брал пересечение "отчет за период" с BETWEEN sh.STIME AND sh.ETIME - 1 / 86400 ...

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2020, 18:36
    #39996308
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Не совсем понял решение с join.
Ведь ситуация в основном такая: номер 7777777 был со статусом 1 в период с ssh.STIME = '15.07.2020' и ssh.STIME = '05.09.2020'.
Т.е., при ssh.STIME = '14.07.2020' или скажем ssh.STIME = '06.09.2020' - статус уже 2.

Если я задаю период с 1 по 7 сентября, то должен получить:

01.09.2020 - 7777777
02.09.2020 - 7777777
03.09.2020 - 7777777
04.09.2020 - 7777777
05.09.2020 - 7777777

06.09.2020
07.09.2020 - тут его уже не будет.

С чем же джойнить, если в SUBS_SERV_HISTORY только две даты..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2020, 18:37
    #39996310
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Alexander Warlord
ssh.STIME = '05.09.2020'

ssh.ETIME = '05.09.2020'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 09:24
    #39996425
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Видимо я что-то не так делаю:

Код: 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.
SELECT * FROM
(SELECT UNIQUE
    --sr.SERV_NAME AS SERV_NAME,
    TRUNC(ssh.CRE_DATE) AS CRE_DATE,
    --tp.TRPL_NAME AS TRPL_NAME,
    ph.MSISDN AS MSISDN,
    ssh.SUBS_ID AS SUBS_ID,
    ssh.SSTAT_ID AS SSTAT_ID,
    sst.SSTAT_NAME AS SSTAT_NAME
FROM
    SUBS_SERV_HISTORY ssh,
    SUBS_HISTORY sh,
    TARIFF_PLAN tp,
    SERVICE sr,
    SERV_STATUS sst,
    PHONE ph
WHERE
    ssh.SERV_ID IN (557)
    AND ssh.SSTAT_ID = 1
    AND sh.SUBS_ID = ssh.SUBS_ID
    AND sh.TRPL_ID = tp.TRPL_ID
    AND tp.TRPL_ID IN (207,208,209,210)
    AND sh.PHONE_ID = ph.PHONE_ID
    AND ph.MSISDN = '7257840'
    AND sr.SERV_ID = ssh.SERV_ID
    AND ssh.SSTAT_ID = sst.SSTAT_ID
    AND sh.STAT_ID = 1
    AND ssh.CRE_DATE BETWEEN sh.STIME AND sh.ETIME - 1 / 86400
    --AND TRUNC(:REP_DATE) BETWEEN ssh.STIME AND ssh.ETIME - 1 / 86400
    ) r1
join
(select trunc(:rep_date +level) as dat from dual connect by level < 10) r2
on r2.dat = r1.CRE_DATE
ORDER BY r2.dat



Дает только один результат:

CRE_DATE MSISDN SUBS_ID SSTAT_ID SSTAT_NAME DAT02.09.2020 7257840 1854953 1 Активна 02.09.2020

Хотя для него STIME = 02.09.2020 0:00:40 и ETIME = 05.09.2020 0:00:43

И мне надо было получить по крайней мере записи за 3 дня: 02.09, 03.09, 04.09.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 10:42
    #39996456
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Alexander Warlord

Если я задаю период с 1 по 7 сентября, то должен получить:



имхо
самое простое, получите диапазоны

с 01 по 05 -1
с 06 по 06 -<>1
c 07 по 07 -1

и размножте (если надо)
вариантов несколько, один из CONNECT BY LEVEL <= end_date - beg_date + 1

....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 12:57
    #39996519
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Вообщем, выкрутился так:

Код: 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.
SELECT
    ssh.CRE_DATE AS CRE_DATE,
    TRUNC(ssh.STIME) AS SSH_STIME,
    TRUNC(ssh.ETIME) AS SSH_ETIME,
    DECODE(TRUNC(ssh.ETIME),TO_DATE('31.12.2999','DD.MM.YYYY'),TRUNC(SYSDATE)-TRUNC(ssh.STIME),TRUNC(ssh.ETIME)-TRUNC(ssh.STIME)) AS KOL_DAYS,
    sr.SERV_NAME AS SERV_NAME,
    tp.TRPL_NAME AS TRPL_NAME,
    ssh.SUBS_ID AS SUBS_ID,
    ssh.SSTAT_ID AS SSTAT_ID,
    sst.SSTAT_NAME AS SSTAT_NAME
FROM
    SUBS_SERV_HISTORY ssh,
    SUBS_HISTORY sh,
    TARIFF_PLAN tp,
    SERVICE sr,
    SERV_STATUS sst,
    PHONE ph
WHERE
    ssh.SERV_ID IN (557)
    AND ssh.SSTAT_ID = 1
    AND sh.SUBS_ID = ssh.SUBS_ID
    AND sh.TRPL_ID = tp.TRPL_ID
    AND tp.TRPL_ID IN (207,208,209,210)
    AND sh.PHONE_ID = ph.PHONE_ID
    AND sr.SERV_ID = ssh.SERV_ID
    AND ssh.SSTAT_ID = sst.SSTAT_ID
    AND sh.STAT_ID = 1
    AND ssh.CRE_DATE BETWEEN sh.STIME AND sh.ETIME - 1 / 86400
    AND ssh.CRE_DATE >= TRUNC(:DATE1)
    AND ssh.CRE_DATE <= TRUNC(:DATE2) + 1 - 1 / 86400
ORDER BY ph.MSISDN, ssh.CRE_DATE



И чего мне это раньше в голову не пришло.. :) Просто считаю количество дней активности услуги. Потом уже сгруппирую по SUBS_ID и месяцам.
Рассуждение в компании веселее, как известно)) а то эта самоизоляция и удалёнка уже туманит мозги)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 13:26
    #39996545
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Alexander Warlord

Потом уже сгруппирую по SUBS_ID и месяцам.


KOL_DAYS не совсем "на каждый день за период"

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 13:33
    #39996551
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Это да :) В целом изначально моя задача была выявить количество дней активности по абонентам за месяц.
Для целостной картины хотел сделать прям по дням. Позже добью конечно. Сейчас главное отправить отчет и избавиться ))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 14:03
    #39996567
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Подскажите, а можно ли расположить данные с помощью запроса:

CRE_DATE SSH_STIME SSH_ETIME KOL_DAYS SUBS_ID SSTAT_ID SSTAT_NAME03.09.2020 0:00 02.09.2020 07.09.2020 5 1771687 1 Активна

в виде:

CRE_DATE SSH_STIME SSH_ETIME KOL_DAYS SUBS_ID SSTAT_ID SSTAT_NAME03.09.2020 0:00 02.09.2020 03.09.2020 5 1771687 1 Активна03.09.2020 0:00 03.09.2020 04.01.1900 5 1771687 1 Активна03.09.2020 0:00 04.01.1900 05.09.2020 5 1771687 1 Активна03.09.2020 0:00 05.09.2020 06.09.2020 5 1771687 1 Активна03.09.2020 0:00 06.09.2020 07.09.2020 5 1771687 1 Активна
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 14:06
    #39996570
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Alexander Warlord
Подскажите, а можно ли расположить данные с помощью запроса:



22193397

что не получается?

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 14:33
    #39996589
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Alexander Warlord,

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

  1  with t as (
  2  select 1 id, date '2020-09-01' db,date '2020-09-07' de, 100 val from dual union all
  3  select 2 id, date '2020-09-10' db,date '2020-09-11' de, 101 val from dual
  4  )
  5  select id,db+(level-1) b,db+(level) e,val from t
  6  CONNECT BY LEVEL <= de-db
  7    and id=prior id
  8*   and prior sys_guid() is not null --dbms_random
SQL> /

        ID B          E                 VAL
---------- ---------- ---------- ----------
         1 01.09.2020 02.09.2020        100
         1 02.09.2020 03.09.2020        100
         1 03.09.2020 04.09.2020        100
         1 04.09.2020 05.09.2020        100
         1 05.09.2020 06.09.2020        100
         1 06.09.2020 07.09.2020        100
         2 10.09.2020 11.09.2020        101

7 rows selected.



ps
есть много вариантов, мне нравится с хмл

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 14:46
    #39996594
Alexander Warlord
Alexander Warlord
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Ух! благодарю)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 15:29
    #39996612
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с перебором дат
Alexander Warlord
Ух! благодарю)
https://www.sql.ru/forum/1311294/kak-podavit-connect-by-loop-i-razmnozhit-stroki-po-id-v-12-versii?mid=21858048&sid=541cd4b2f0fb4&hl=?????????#21858048
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос с перебором дат / 15 сообщений из 15, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 156ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1880918]:
 0ms
get settings:
 8ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 49ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 14ms
total:  156ms
x
x
Закрыть


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