powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Форумы / Oracle [игнор отключен] [закрыт для гостей] / анализ дат нужны идеи как составить запрос
7 сообщений из 7, страница 1 из 1
анализ дат нужны идеи как составить запрос
    #40094632
HappySanta
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Коллеги, добрый день.
Помогите пожалуйста с идеями, как можно с помощью SQL реализовать следующую вещь.

Имею таблицу:

WITH t1 AS ( select
date'2020-04-29' AS calendar_dt
,'W' AS DT_TYPE from dual
union all
SELECT date'2020-04-30' AS calendar_dt
,'W' AS DT_TYPE from dual
union all
SELECT date'2020-05-01' AS calendar_dt
,'R' AS DT_TYPE from dual
union all
SELECT date'2020-05-02' AS calendar_dt
,'R' AS DT_TYPE from dual
union all
SELECT date'2020-05-03' AS calendar_dt
,'R' AS DT_TYPE from dual
union all
SELECT date'2020-05-04' AS calendar_dt
,'W' AS DT_TYPE from dual
)
SELECT * FROM t1
order by 1 desc


Задача:
Для всех дат, которые имеют DT_TYPE = 'R', определить первую следующую дату, где DT_TYPE = 'W' и подставить ее отдельным столбцом.
Т.е в приведенной таблице, напротив дат 01.05.2021, 02.05.2021, 03.05.2021 должна появиться дата 04.05.2021

В примере только часть таблицы.
Количество подряд идущих дат с типом 'R' случайно и может не повторяться в остальных данных таблицы.

Что лучше использовать для построения такого запроса и возможно ли вообще получить желаемый результат?
...
Рейтинг: 0 / 0
анализ дат нужны идеи как составить запрос
    #40094650
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Код: 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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
WITH t1 AS
 (                    select date '2020-04-29' AS calendar_dt, 'W' AS DT_TYPE
  from dual union all SELECT date '2020-04-30' AS calendar_dt, 'W' AS DT_TYPE
  from dual union all SELECT date '2020-05-01' AS calendar_dt, 'R' AS DT_TYPE
  from dual union all SELECT date '2020-05-02' AS calendar_dt, 'R' AS DT_TYPE
  from dual union all SELECT date '2020-05-03' AS calendar_dt, 'R' AS DT_TYPE
  from dual union all SELECT date '2020-05-04' AS calendar_dt, 'W' AS DT_TYPE
  from dual union all SELECT date '2020-05-02' AS calendar_dt, 'W' AS DT_TYPE
  from dual)
select tr.*
     , decode( dt_type,'R'
             , lead(decode(dt_type,'W',calendar_dt) ignore nulls,1,null) over(order by calendar_dt, dt_type desc)
             ) nearest_w_dt
     , (select min(calendar_dt) from t1 where dt_type = 'W' and tr.dt_type = 'R' and calendar_dt > tr.calendar_dt) nearest_w_dt2
from t1 tr
order by 1 desc, 2
;

CALENDAR_DT DT_TYPE NEAREST_W_DT NEAREST_W_DT2
----------- ------- ------------ -------------
04.05.20    W                    
03.05.20    R       04.05.20     04.05.20
02.05.20    R       04.05.20     04.05.20
02.05.20    W                    
01.05.20    R       02.05.20     02.05.20
30.04.20    W                    
29.04.20    W                    

7 rows selected

----------------------------------

WITH t1 AS
 (                    select date '2020-04-29' AS calendar_dt, 'W' AS DT_TYPE
  ...
  from dual)
select tr.*, nearest_w_dt
  from t1 tr
     , lateral(select calendar_dt nearest_w_dt
                 from t1
                where dt_type = 'W' and tr.dt_type = 'R'
                  and calendar_dt > tr.calendar_dt
                order by calendar_dt
                fetch first '1' row only
               )(+) w
order by 1 desc, 2
;

CALENDAR_DT DT_TYPE NEAREST_W_DT
----------- ------- ------------
04.05.20    W       
03.05.20    R       04.05.20
02.05.20    R       04.05.20
02.05.20    W       
01.05.20    R       02.05.20
30.04.20    W       
29.04.20    W       

7 rows selected

----------------------------------

WITH t1 AS
 (                    select date '2020-04-29' AS calendar_dt, 'W' AS DT_TYPE
  ...
  from dual)
, cte (calendar_dt, dt_type, nearest_w_dt, prev_dt) as(
     select calendar_dt, dt_type, cast(null as date)
          , lag(calendar_dt,1,date'0001-01-01') over(order by calendar_dt)
       from t1 where dt_type = 'W'
     union all
     select t1.calendar_dt, t1.dt_type, cte.calendar_dt, null
       from cte, t1
      where t1.dt_type = 'R'
        and cte.dt_type = 'W'
        and t1.calendar_dt between cte.prev_dt and cte.calendar_dt -1/86400
)
select calendar_dt, dt_type, nearest_w_dt
from cte
order by 1 desc, 2
;

CALENDAR_DT DT_TYPE NEAREST_W_DT
----------- ------- ------------
04.05.20    W       
03.05.20    R       04.05.20
02.05.20    R       04.05.20
02.05.20    W       
01.05.20    R       02.05.20
30.04.20    W       
29.04.20    W       

7 rows selected

----------------------------------

WITH t1 AS
 (                    select date '2020-04-29' AS calendar_dt, 'W' AS DT_TYPE
  ...
  from dual)
select calendar_dt, DT_TYPE, nearest_w_dt
  from t1
  model
	dimension by (DT_TYPE, calendar_dt dt)
	measures (cast(null as date) nearest_w_dt, calendar_dt)
	rules(nearest_w_dt['R',any] = min(calendar_dt)['W', dt > cv()])
 order by calendar_dt desc, dt_type
;

CALENDAR_DT DT_TYPE NEAREST_W_DT
----------- ------- ------------
04.05.20    W       
03.05.20    R       04.05.20
02.05.20    R       04.05.20
02.05.20    W       
01.05.20    R       02.05.20
30.04.20    W       
29.04.20    W       

7 rows selected

----------------------------------

WITH t1 AS
 (                    select date '2020-04-29' AS calendar_dt, 'W' AS DT_TYPE
  ...
  from dual)
SELECT tr.*
  FROM t1 match_recognize( order by calendar_dt desc, dt_type
                           measures case dt_type 
						              when 'R' then w.calendar_dt 
									end as nearest_w_dt
                           all rows per match WITH UNMATCHED ROWS
                           pattern(w r+)
                           define w as dt_type = 'W'
                                , r as dt_type = 'R'
                         ) tr
order by 1 desc
;

CALENDAR_DT DT_TYPE NEAREST_W_DT
----------- ------- ------------
04.05.20    W       
03.05.20    R       04.05.20
02.05.20    R       04.05.20
02.05.20    W       
01.05.20    R       02.05.20
30.04.20    W       
29.04.20    W       

7 rows selected


SQL> 
...
Рейтинг: 0 / 0
анализ дат нужны идеи как составить запрос
    #40094661
oragraf
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
andrey_anonymous
Код: plsql
1.
                fetch first '1' row only

Интересно, зачем кавычки?
...
Рейтинг: 0 / 0
анализ дат нужны идеи как составить запрос
    #40094690
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
andrey_anonymous,

вопрос не по селекту и его результату,
а о сортировке (как обойти знаю)
фича или баг?
Код: 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.
39.
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>

WITH t1 AS
 (                    select date '2020-04-29' AS calendar_dt, 'W' AS DT_TYPE
  from dual union all SELECT date '2020-04-30' AS calendar_dt, 'W' AS DT_TYPE
  from dual union all SELECT date '2020-05-01' AS calendar_dt, 'R' AS DT_TYPE
  from dual union all SELECT date '2020-05-02' AS calendar_dt, 'R' AS DT_TYPE
  from dual union all SELECT date '2021-05-03' AS calendar_dt, 'R' AS DT_TYPE
  from dual union all SELECT date '2020-05-04' AS calendar_dt, 'W' AS DT_TYPE
  from dual union all SELECT date '2020-05-02' AS calendar_dt, 'W' AS DT_TYPE
  from dual)
SELECT tr.*
  FROM t1 match_recognize( order by calendar_dt desc, dt_type
                           measures case dt_type
                                                         when 'R' then w.calendar_dt
                                                                   end as nearest_w_dt
                           all rows per match WITH UNMATCHED ROWS
                           pattern(w r+)
                           define w as dt_type = 'W'
                                , r as dt_type = 'R'
                         ) tr
order by calendar_dt ASC
/

CALENDAR_D D NEAREST_W_
---------- - ----------
03.05.2021 R
04.05.2020 W
02.05.2020 R
02.05.2020 W
01.05.2020 R
30.04.2020 W
29.04.2020 W

7 rows selected.

SQL>



.....
stax
...
Рейтинг: 0 / 0
анализ дат нужны идеи как составить запрос
    #40094801
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Stax
фича или баг?

ИМХО баг
...
Рейтинг: 0 / 0
анализ дат нужны идеи как составить запрос
    #40094809
Фотография SeaGate
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Staxфича или баг?
Bug 30645590 - Using descending order within the row_pattern_order_by syntax of a match_recognize query gives wrong final order (Doc ID 30645590.8)
SORT ORDER BY нет в плане выполнения в 19.8, например:
Код: 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.
Plan hash value: 2091187608

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                     |      |     7 |   147 |    16  (13)| 00:00:01 |
|   1 |  VIEW                                                |      |     7 |   147 |    15   (7)| 00:00:01 |
|   2 |   MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTOMATON|      |     7 |    84 |    15   (7)| 00:00:01 |
|   3 |    VIEW                                              |      |     7 |    84 |    14   (0)| 00:00:01 |
|   4 |     UNION-ALL                                        |      |       |       |            |          |
|   5 |      FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |      FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |      FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |      FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|   9 |      FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|  10 |      FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|  11 |      FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "TR"."CALENDAR_DT"[DATE,7], "TR"."DT_TYPE"[CHARACTER,1], "TR"."NEAREST_W_DT"[DATE,7]
   2 - "CALENDAR_DT"[DATE,7], "DT_TYPE"[CHARACTER,1], CASE  WHEN "DT_TYPE"='R' THEN
       "W"."CALENDAR_DT" END [7]
   3 - "CALENDAR_DT"[DATE,7], "DT_TYPE"[CHARACTER,1]
   4 - STRDEF[7], STRDEF[1]


Для сравнения, вот план из 21.3:
Код: 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.
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                      |      |     7 |   147 |    16  (13)| 00:00:01 |
|   1 |  SORT ORDER BY                                        |      |     7 |   147 |    16  (13)| 00:00:01 |
|   2 |   VIEW                                                |      |     7 |   147 |    15   (7)| 00:00:01 |
|   3 |    MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTOMATON|      |     7 |    84 |    15   (7)| 00:00:01 |
|   4 |     VIEW                                              |      |     7 |    84 |    14   (0)| 00:00:01 |
|   5 |      UNION-ALL                                        |      |       |       |            |          |
|   6 |       FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |       FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|   9 |       FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|  10 |       FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|  11 |       FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
|  12 |       FAST DUAL                                       |      |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "TR"."CALENDAR_DT"[DATE,7], "TR"."DT_TYPE"[CHARACTER,1], "TR"."NEAREST_W_DT"[DATE,7]
   2 - "TR"."CALENDAR_DT"[DATE,7], "TR"."DT_TYPE"[CHARACTER,1], "TR"."NEAREST_W_DT"[DATE,7]
   3 - "CALENDAR_DT"[DATE,7], "DT_TYPE"[CHARACTER,1], CASE  WHEN "DT_TYPE"='R' THEN "W"."CALENDAR_DT"
       END [7]
   4 - "CALENDAR_DT"[DATE,7], "DT_TYPE"[CHARACTER,1]
   5 - STRDEF[7], STRDEF[1]

...
Рейтинг: 0 / 0
анализ дат нужны идеи как составить запрос
    #40094820
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SeaGate, andrey_anonymous

Доступ к металинку забрали, так шо спасибо

.....
stax
...
Рейтинг: 0 / 0
7 сообщений из 7, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / анализ дат нужны идеи как составить запрос
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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