ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / пятничный запросик
6 сообщений из 6, страница 1 из 1
пятничный запросик
    #39820928
Фотография Anton_Demin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Anton_Demin
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
-- имеем таблицу с картами в фитнес клуб (id карты, номер, срок действия с/по)
CREATE TABLE cards(id_card NUMBER, num_card VARCHAR2(100), dt_from DATE, dt_to DATE);
-- таблицу с заморозками по картам (id заморозки, id карты, срок действия с/по)
CREATE TABLE blocks(id_block NUMBER, id_card NUMBER, dt_from DATE, dt_to DATE);
-- данные cards  
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY'));
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY'));
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY'));
-- данные blocks 
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY'));
COMMIT;



надо для конкретной карты(id_card = 1) получить все отрезки посещения и заморозок, сделать это надо SQL-запросом
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
 ID_CARD   DT_FROM        DT_TO        ID_BLOCK
 1         01.09.2016     19.03.2017                   -- с момента заведения карты до начала заморозки
 1         20.03.2017     09.04.2017   101             -- первая заморозка
 1         10.04.2017     13.01.2018                   -- посещение между заморозками
 1         14.01.2018     03.02.2018   102             -- вторая заморозка
 1         04.02.2018     17.02.2019                   -- посещение между заморозками
 1         18.02.2019     10.03.2019   103             -- третья заморозка
 1         11.03.2019     01.09.2019                   -- последний отрезок между последней заморозкой до закрытия


Мои "творения":
Код: 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.
-- посчитает отрезки с заморозкой
SELECT c.id_card, b.dt_from,b.dt_to,b.id_block
  FROM cards c, blocks b
 WHERE c.id_card = 1
   AND c.id_card = b.id_card
UNION
(
-- первый отрезок + между заморозками
SELECT b.id_card,
       LAG (b.dt_to + 1, 1 , c.dt_from) OVER (ORDER BY b.dt_from) df,
       b.dt_from - 1 dt,
       NULL
  FROM cards c, blocks b
 WHERE c.id_card = 1
   AND c.id_card = b.id_card
UNION ALL
-- между заморозками + последний отрезок
SELECT b.id_card,
       b.dt_to + 1 df,
       LEAD(b.dt_from - 1, 1, c.dt_to) OVER(ORDER BY b.dt_to) dt,
       NULL
  FROM cards c, blocks b
 WHERE c.id_card = 1
   AND c.id_card = b.id_card
)
ORDER BY 2


Похожий запрос только без UNION ALL
Код: 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.
-- посчитает отрезки с заморозкой
SELECT c.id_card, b.dt_from,b.dt_to,b.id_block
  FROM cards c, blocks b
 WHERE c.id_card = 1
   AND c.id_card = b.id_card
UNION
-- первый отрезок + между заморозками
SELECT b.id_card,
       LAG (b.dt_to + 1, 1 , c.dt_from) OVER (ORDER BY b.dt_from) df,
       b.dt_from - 1 dt,
       NULL
  FROM cards c, blocks b
 WHERE c.id_card = 1
   AND c.id_card = b.id_card
UNION
-- последний отрезок
SELECT b.id_card,
       MAX(b.dt_to) + 1 df,
       c.dt_to dt,
       NULL
  FROM cards c, blocks b
 WHERE c.id_card = 1
   AND c.id_card = b.id_card
GROUP BY b.id_card, c.dt_to
ORDER BY 2



Поделитесь своими идеями для решения задачи
...
Рейтинг: 0 / 0
31.05.2019, 17:42
| Ответить | Цитировать | Написать  
пятничный запросик
    #39820955
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Anton_Demin,

Unpivot + start of group (оба с небольшими нюансами)
...
Рейтинг: 0 / 0
31.05.2019, 18:11
| Ответить | Цитировать | Написать  
пятничный запросик
    #39821301
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Anton_Demin,

 
just for fun, рыба без нюансов
Код: 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.
35.
36.
37.
38.
with 
 cards (id_card, num_card, dt_from, dt_to) as (
   select 1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY') from dual union all
   select 2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual union all
   select 3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual 
),blocks (id_block, id_card, dt_from, dt_to) as (
   select 101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY') from dual union all
   select 102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY') from dual union all
   select 103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY') from dual union all
   select 201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY') from dual
)
,data as (
   select *
   from (
         select * 
         from (
            select id_card,dt_from,dt_to,'c' t from cards
            union all
            select id_card,dt_to,dt_from,'b' from blocks
         ) v
         --where v.id_card=1 -- тут фильтруем по карте, если нужно
        ) piv
   unpivot(dt for x in (dt_from as 'dt_from', dt_to as 'dt_to'))
)
select * 
from data
match_recognize (
   partition by id_card
   order by dt /* для решения "нюансов" можно добавить сортировку по t */
   measures
      match_number() as N,
      first(dt) as dt_from,
      last(dt) as dt_to
   pattern (x_start+ x_end)
   define
      x_start as x = 'dt_from'
     ,x_end   as x = 'dt_to'
);

...
Рейтинг: 0 / 0
02.06.2019, 13:30
| Ответить | Цитировать | Написать  
пятничный запросик
    #39821487
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Anton_Demin,

если заморозки не пересекаюся и в рамках карты, задача упрощается

unpivit + lead (обеденить интервалы)

зы
для конкретной карты, проще, не надо partition

.....
stax
...
Рейтинг: 0 / 0
03.06.2019, 09:23
| Ответить | Цитировать | Написать  
пятничный запросик
    #39831204
Elina_C
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elina_C
Гость
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT CASE WHEN lvl =1 THEN dt_from ELSE COALESCE(LAG(dt_to) OVER(ORDER BY dt_from) + 1, MIN(dt_from) OVER()) END AS dt_from, 
       CASE WHEN lvl =1 THEN dt_to   ELSE COALESCE(LEAD(dt_from) OVER(ORDER BY dt_from) - 1, MAX(dt_to) OVER()) END AS dt_to
FROM
(
    SELECT dt_from, dt_to, lvl
    FROM blocks, (SELECT LEVEL AS lvl FROM DUAL CONNECT BY LEVEL <= 2)
    WHERE id_card = 1
    UNION ALL
    SELECT dt_from, dt_to, 2 
    FROM cards
    WHERE id_card = 1    
)
ORDER BY 1,2
...
Рейтинг: 0 / 0
27.06.2019, 15:22
| Ответить | Цитировать | Написать  
пятничный запросик
    #39831547
UScorp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
UScorp
Участник
Вот так как то:

Код: 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.
with cards as (
  select null as id_card, null as num_card, null as dt_from, null as dt_to from dual where 1=0 union all
  select 1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY') from dual union all
  select 2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual union all
  select 3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual
),
blocks as (
  select null as id_block, null as id_card, null as dt_from, null as dt_to from dual where 1=0 union all 
  select 101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY') from dual union all 
  select 102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY') from dual union all 
  select 103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY') from dual union all 
  select 201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY') from dual 
),
ttt as (
  select decode(t.lvl, 1, nvl(lag(b.dt_to) over (order by b.dt_from) + 1, c.dt_from), 2, b.dt_from, 3, max(b.dt_to) over() + 1) as StartDate,
         decode(t.lvl, 1, b.dt_from - 1, 2, b.dt_to, 3, c.dt_to) as EndDate,
         decode(t.lvl, 2, b.id_block) as IdBlock
    from cards c
    join blocks b on b.id_card = c.id_card
    cross join (select level as lvl from dual connect by level < 4) t
 where c.id_card = 1
 order by b.dt_from, t.lvl
)
select distinct * from ttt order by 1
...
Рейтинг: 0 / 0
28.06.2019, 11:32
| Ответить | Цитировать | Написать  
6 сообщений из 6, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / пятничный запросик
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=73&tid=1882351]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 31ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 48ms
get tp. blocked users:
 2ms
others: 259ms
total:  387ms
созд. / загр.: 387ms
x
x
Закрыть


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