ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / получить первую запись из последней группы
38 сообщений из 38, показаны все 2 страниц
  все  
получить первую запись из последней группы
    #39319879
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
есть необходимость:
1) пронумеровать записи на уровне группировки PRODUCT + trunc(TIME_ID) - это делает row_number() в примере ниже
2) затем для каждой строки, где row_number() = 1,
получить на том же уровне группировки PRODUCT + trunc(TIME_ID) значение QUANT,
которое существовало для первой по TIME_ID записи из последней по времени группы (непрерывной последовательности) записей с флагом "OPER" = D

в примере ниже:
записи #4 и 5 в дате 04.04.2001 по PRODUCT=can формируют последнюю по времени группу с флагом "OPER" = D
первая из них - запись №5
соответственно, в строку 4 необходимо вывести значение из нее = 9

ВАЖНО: ниже указанной группы возможны еще записи с флагом "OPER" = D - в примере ниже это запись №7 - эти значения не интересуют, нужна строго последняя группа

вопрос - как сделать это эффективно?
задача массовая (миллионы строк), inline view не прокатит

спасибо!
Код: 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.
CREATE TABLE "INVENT_TABLE" 
 (             "PRODUCT" VARCHAR2(10 BYTE), 
                "TIME_ID" DATE, 
                "QUANT" NUMBER, 
                "OPER" VARCHAR2(1 BYTE)
   );


Insert into INVENT_TABLE (PRODUCT,TIME_ID,QUANT,OPER) values ('bottle',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'10','I');
Insert into INVENT_TABLE (PRODUCT,TIME_ID,QUANT,OPER) values ('can',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'15','I');
Insert into INVENT_TABLE (PRODUCT,TIME_ID,QUANT,OPER) values ('can',to_date('04.04.2001 19:00:00','DD.MM.RRRR HH24:MI:SS'),'5','U');
Insert into INVENT_TABLE (PRODUCT,TIME_ID,QUANT,OPER) values ('can',to_date('04.04.2001 20:00:00','DD.MM.RRRR HH24:MI:SS'),'11','D');
Insert into INVENT_TABLE (PRODUCT,TIME_ID,QUANT,OPER) values ('can',to_date('04.04.2001 21:00:00','DD.MM.RRRR HH24:MI:SS'),'7','I');
Insert into INVENT_TABLE (PRODUCT,TIME_ID,QUANT,OPER) values ('can',to_date('04.04.2001 22:00:00','DD.MM.RRRR HH24:MI:SS'),'9','D');
Insert into INVENT_TABLE (PRODUCT,TIME_ID,QUANT,OPER) values ('can',to_date('04.04.2001 23:00:00','DD.MM.RRRR HH24:MI:SS'),'11','D');
Insert into INVENT_TABLE (PRODUCT,TIME_ID,QUANT,OPER) values ('bottle',to_date('06.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'8','D');



SELECT PRODUCT, TIME_ID,  OPER,
row_number() over (partition by PRODUCT, 
                                       trunc(TIME_ID) --внутри дня
                          order by TIME_ID desc) rn
--, first_value(QUANT) over (partition by PRODUCT, 
--                                       trunc(TIME_ID) --внутри дня
--                          order by TIME_ID) fv
,QUANT
FROM INVENT_TABLE 

;

# PRODUCT    TIME_ID             Oper       RN      QUANT
 ---------- ------------------- - ---------- ----------
1 bottle     01.04.2001 00:00:00 I          1         10
2 bottle     06.04.2001 00:00:00 D          1          8
3 can        01.04.2001 00:00:00 I          1         15
4 can        04.04.2001 23:00:00 D          1         11 --здесь требуется получить 9 (QUANT из строки на одну ниже)
5 can        04.04.2001 22:00:00 D          2          9
6 can        04.04.2001 21:00:00 I          3          7
7 can        04.04.2001 20:00:00 D          4         11 
8 can        04.04.2001 19:00:00 U          5          5
...
Рейтинг: 0 / 0
03.10.2016, 19:05
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39319887
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
Alexus12,

start_of_group погугли.
...
Рейтинг: 0 / 0
03.10.2016, 19:14
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39319933
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
На аналитике можно так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
  select product, time_id, oper, rn, quant 
       , case rn 
          when 1 then 
            coalesce(
              lag(fv_ ignore nulls) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID)
              , quant
            )
          else quant
          end fv                     
  from (
    SELECT PRODUCT, TIME_ID,  OPER, QUANT
         , row_number() over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) rn
         , case lead(oper, 1, oper) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc)
           when oper then null else quant 
           end fv_
    FROM INVENT_TABLE
    ) i



Есть и другие варианты.
...
Рейтинг: 0 / 0
03.10.2016, 21:30
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320226
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
andrey_anonymous,

спасибо!
но есть проблема: если на уровне группировки PRODUCT + trunc(TIME_ID) существует только одна группа - получим неверное значение FV

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
PRODUCT    TIME_ID             O         RN      QUANT P         FV
---------- ------------------- - ---------- ---------- - ----------
bottle     01.04.2001 00:00:00 I          1         10 I         10
bottle     06.04.2001 00:00:00 D          2          8 D           
bottle     06.04.2001 01:00:00 D          1          9 D          9 --должно быть 8 из предыдущей записи
can        01.04.2001 00:00:00 I          1         15 I         15
can        04.04.2001 19:00:00 U          6          5 U           
can        04.04.2001 20:00:00 D          5         11 U           
can        04.04.2001 21:00:00 I          4          7 D           
can        04.04.2001 22:00:00 D          3          9 I           
can        04.04.2001 23:00:00 D          2         10 D           
can        04.04.2001 23:01:00 D          1         11 D          9
...
Рейтинг: 0 / 0
04.10.2016, 12:51
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320238
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Alexus12но есть проблема: если на уровне группировки PRODUCT + trunc(TIME_ID) существует только одна группа - получим неверное значение FV
Ну так поправьте.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
      , case rn 
          when 1 then 
            coalesce(
              lag(fv_ ignore nulls) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID)
              , first_value(quant) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID)
            )
          else quant
          end fv
...
Рейтинг: 0 / 0
04.10.2016, 13:15
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320526
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
andrey_anonymous,

спасибо!
...
Рейтинг: 0 / 0
04.10.2016, 18:43
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320528
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
на ASKTOM найден пример:
group records by interval of 3 seconds
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13946369553642

цитата:

Код: 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.
You Asked 

Hi Tom, 

I am not able to find the exact answer to my question although I think I might need to use LAG and LEAD: 

I have records like this: 

Time Amount 
11/22/2003 12:22:01 100 
11/22/2003 12:22:03 200 
11/22/2003 12:22:04 300 
11/22/2003 12:22:45 100 
11/22/2003 12:22:46 200 
11/22/2003 12:23:12 100 
11/22/2003 12:23:12 200 

What I need to do is sum the amounts where the time of the records is within 3 seconds of each other. 

In the case where the data is like this: 

11/22/2003 12:22:03 200 
11/22/2003 12:22:04 200 
11/22/2003 12:22:05 200 
11/22/2003 12:22:06 200 
11/22/2003 12:22:07 200 
11/22/2003 12:22:08 200 
11/22/2003 12:22:09 200 

There would only be one row with the total for all the rows. 

(Basically, we are looking for "instances" where we define an instance such that all the records within the instance are no more than three seconds apart. So there can be 1 or many records all of the same instance and the resulting summation would have one summary record per instance.) 

Would you please point me in the right direction? 






and we said...

well, the way I decided to deal with this is: 

take the first row, if the subsequent row is within 3 seconds, it stays in this group -- else, create a new group. 

then, after "grouping", we sum.... 

Analytics rock 
Analytics roll 



ops$tkyte@ORA920PC>; create table t ( x date, y int ); 
Table created. 

ops$tkyte@ORA920PC>; declare 
 2 l_date date := to_date( '12:22:03', 'hh24:mi:ss' ); 
 3 begin 
 4 for i in 1 .. 10 
 5 loop 
 6 insert into t values ( l_date, dbms_random.value( 0, 100 ) ); 
 7 l_date := l_date + 1/24/60/60; 
 8 end loop; 
 9 
 10 l_date := l_date + 5/24/60/60; 
 11 for i in 1 .. 3 
 12 loop 
 13 insert into t values ( l_date, dbms_random.value( 0, 100 ) ); 
 14 l_date := l_date + 1/24/60/60; 
 15 end loop; 
 16 
 17 l_date := l_date + 15/24/60/60; 
 18 for i in 1 .. 12 
 19 loop 
 20 insert into t values ( l_date, dbms_random.value( 0, 100 ) ); 
 21 l_date := l_date + 1/24/60/60; 
 22 end loop; 
 23 end; 
 24 / 

PL/SQL procedure successfully completed. 

ops$tkyte@ORA920PC>; alter session set nls_date_format = 'hh24:mi:ss'; 
Session altered. 

ops$tkyte@ORA920PC>; select x, y, max(rn) over (order by x) 
 2 from ( 
 3 select x, y, 
 4 lag(x) over (order by x), 
 5 case when abs(lag(x) over (order by x) - x) > 3/24/60/60 then row_number() over (order by x) 
 6 when row_number() over (order by x) = 1 then 1 
 7 else null 
 8 end rn 
 9 from t 
 10 ) 
 11 / 

X Y MAX(RN)OVER(ORDERBYX) 
-------- ---------- --------------------- 
12:22:03 69 1 
12:22:04 62 1 
12:22:05 73 1 
12:22:06 69 1 
12:22:07 95 1 
12:22:08 87 1 
12:22:09 49 1 
12:22:10 85 1 
12:22:11 94 1 
12:22:12 57 1 
12:22:18 86 11 
12:22:19 50 11 
12:22:20 90 11 
12:22:36 63 14 
12:22:37 26 14 
12:22:38 14 14 
12:22:39 96 14 
12:22:40 18 14 
12:22:41 77 14 
12:22:42 50 14 
12:22:43 11 14 
12:22:44 1 14 
12:22:45 74 14 
12:22:46 100 14 
12:22:47 20 14 

25 rows selected. 

ops$tkyte@ORA920PC>; 
ops$tkyte@ORA920PC>; select min(x), max(x), sum(y) 
 2 from ( 
 3 select x, y, max(rn) over (order by x) max_rn 
 4 from ( 
 5 select x, y, 
 6 lag(x) over (order by x), 
 7 case when abs(lag(x) over (order by x) - x) > 3/24/60/60 then row_number() over (order by x) 
 8 when row_number() over (order by x) = 1 then 1 
 9 else null 
 10 end rn 
 11 from t 
 12 ) 
 13 ) 
 14 group by max_rn 
 15 / 

MIN(X) MAX(X) SUM(Y) 
-------- -------- ---------- 
12:22:03 12:22:12 740 
12:22:18 12:22:20 226 
12:22:36 12:22:47 550



по образу и подобию решена моя задача:


Код: 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.
with INV_TABLE as(
select 'bottle' PRODUCT,to_date('06.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS') TIME_ID,'8' QUANT,'D' OPER  from dual union all
select 'bottle',to_date('06.04.2001 01:00:00','DD.MM.RRRR HH24:MI:SS'),'9','D' from dual union all
select 'bottle',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'10','I' from dual union all
select 'can',to_date('04.04.2001 21:00:00','DD.MM.RRRR HH24:MI:SS'),'7','I' from dual union all
select 'can',to_date('04.04.2001 21:05:00','DD.MM.RRRR HH24:MI:SS'),'77','I' from dual union all
select 'can',to_date('04.04.2001 23:00:00','DD.MM.RRRR HH24:MI:SS'),'10','D' from dual union all
select 'can',to_date('04.04.2001 23:01:00','DD.MM.RRRR HH24:MI:SS'),'11','D' from dual union all
select 'can',to_date('04.04.2001 20:00:00','DD.MM.RRRR HH24:MI:SS'),'11','D' from dual union all
select 'can',to_date('04.04.2001 19:00:00','DD.MM.RRRR HH24:MI:SS'),'5','U' from dual union all
select 'can',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'15','I' from dual union all
select 'can',to_date('04.04.2001 22:00:00','DD.MM.RRRR HH24:MI:SS'),'99','D' from dual 
)

select --t2.*,
product, time_id, oper,quant,
group_number,
dense_rank() over (partition by PRODUCT, trunc(TIME_ID) order by group_number) group_number_dense, --последовательная нумерация подгрупп
first_value(quant) over (partition by PRODUCT, trunc(TIME_ID), group_number order by TIME_ID) group_first_value

from (

select t.*, max(rn) over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) group_number --протянуть подгруппу на все ее записи
from (

select product, time_id, oper,quant,
--debug
  lag(oper) over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) lag_ --предыдущее значение
,row_number() over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) rn_ --номер строки внутри большой группы, порядок обратный
--debug end
,case 
    when lag(oper) over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) <> oper --если есть разница между значениями поля oper между записями - сгенерировать новую группу 
        then row_number() over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID  desc) 
    when row_number() over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID  desc) = 1 then 1  --если это первая строка , подгруппа=1
    else null --иначе подгруппу не проставлять
end rn 
 from INV_TABLE  
 
 ) t
) t2
order by PRODUCT,  TIME_ID

PRODUC TIME_ID             O QUANT                         GROUP_NUMBER                      GROUP_NUMBER_DENSE group_first_value
------ ------------------- - -- --------------------------------------- --------------------------------------- --
bottle 01.04.2001 00:00:00 I 10                                       1                                       1 10
bottle 06.04.2001 00:00:00 D 8                                        1                                       1 8 
bottle 06.04.2001 01:00:00 D 9                                        1                                       1 8 
can    01.04.2001 00:00:00 I 15                                       1                                       1 15
can    04.04.2001 19:00:00 U 5                                        7                                       4 5 
can    04.04.2001 20:00:00 D 11                                       6                                       3 11
can    04.04.2001 21:00:00 I 7                                        4                                       2 7 
can    04.04.2001 21:05:00 I 77                                       4                                       2 7 
can    04.04.2001 22:00:00 D 99                                       1                                       1 99
can    04.04.2001 23:00:00 D 10                                       1                                       1 99
can    04.04.2001 23:01:00 D 11                                       1                                       1 99

11 rows selected



GROUP_NUMBER_DENSE вычислен "для красоты", для решения задачи он не требуется
...
Рейтинг: 0 / 0
04.10.2016, 18:45
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320532
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Alexus12, это вариация на тему обычного для нашего форума start_of_group, который Вам рекомендовали в первом ответе.
На данном форуме было принято писать чуть иначе - маркировать переключение группы единичкой, и уровнем выше суммировать нарастающим итогом - на одну аналитическую функцию меньше, чем в Вашем решении.

Решение типовое, но два inline view - from(from(from))
Ваша конкретная задача может быть аналитикой решена за from(from), это проще в сопровождении.
...
Рейтинг: 0 / 0
04.10.2016, 18:53
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320539
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
andrey_anonymousэто проще в сопровождении.Проще типовое решение, а не опирающееся на некие закономерности данных.
...
Рейтинг: 0 / 0
04.10.2016, 19:02
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320553
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Elicandrey_anonymousэто проще в сопровождении.Проще типовое решение, а не опирающееся на некие закономерности данных.
Будь оно типовым для ТС - он не задавал бы здесь этот вопрос :)
Упрощение достигнуто не за счет закономерности данных, а за счет решения строго той задачи, которую поставил ТС - я использовал в качестве маркера группы целевое значение, что исключило необходимость в нумерации групп и проведения дополнительного сеанса аналитики с указанием нового окна, отличного от исходного.
Вероятно, можно и еще упростить.
...
Рейтинг: 0 / 0
04.10.2016, 19:30
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320558
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
andrey_anonymousВероятно, можно и еще упростить.
Ну если не упростить, то поаккуратнее самовыразиться точно можно:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
  select product, time_id, oper, rn, quant 
          , case rn 
              when 1 then lead(fv_ ignore nulls,1, quant) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc)
              else quant
            end fv
  from (
    SELECT PRODUCT, TIME_ID,  OPER
         , row_number()                     over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) rn
         , QUANT
         , case lead(oper, 1, oper||chr(0)) over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc)
           when oper then null else quant 
           end fv_
    FROM INVENT_TABLE
    ) i 
order by product, trunc(time_id), time_id desc
...
Рейтинг: 0 / 0
04.10.2016, 19:45
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320856
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
andrey_anonymous,
спасибо за решение "на одну функцию меньше" - оно однозначно заслуживает внимания

мое решение (базированное на ответе Тома Кайта, выше) визуально и алгоритмически понятнее (не)квалифицированной поддержке, ваше может быть эффективнее в рантайме, будем сравнивать...
...
Рейтинг: 0 / 0
05.10.2016, 11:33
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320867
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
кстати, возможно ли решение через MODEL?
мои познания в ней стремятся к 0, а здесь http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22asktom-1518271.html Кайт публикует результаты 3 вариантов решения одной задачи (bin fitting - разложить записи в корзинки, переходя к следующей корзине при заполнении предыдущей), базирующееся на обсуждении на asktom (ссылка в статье есть), где говорится, что MODEL оказался самым эффективным вариантом (на asktom приведена статистика)
...
Рейтинг: 0 / 0
05.10.2016, 11:41
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39320961
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Alexus12кстати, возможно ли решение через MODEL?
Да, на model возможно.
Возможно также на табличной функции, на скалярном подзапросе, recursive subquery factoring и даже на connect by.
Эффективность зависит от реальных данных, наличия/отсутствия подходящих индексов и т.д.
...
Рейтинг: 0 / 0
05.10.2016, 13:02
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39321045
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
для решаемой задачи - миллионы строк на дату на входе, индексов нет
соответственно, подзапросы, recursive subquery и т.п. подходы вне игры

вариант pipelined-функции - как у Кайта в статье выше, который он предложил как свое решение для bin fitting - возможен
какое ваше экспертное мнение - стоит ли пробовать, или запрос на аналитике будет более выигрышным, чем она?

на каком месте по производительности может стоять вариант на MODEL (не знаю ограничений ее производительности - ткните носом плз)?
...
Рейтинг: 0 / 0
05.10.2016, 14:07
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39321065
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
для oracle 12c возможен также вариант на MATCH_RECOGNIZE
http://www.oracle.com/technetwork/issue-archive/2015/15-mar/o25asktom-2458830.html

у меня 11.2, увы...
...но для него Кайт в той же статье приводит вариант - The MODEL clause solution to “Finding MIN/MAX Values”:

Код: 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.
Code Listing 1: The MODEL clause solution to “Finding MIN/MAX Values”
SQL> select min(d), max(d), n, grp
  2    from
  3  (
  4  select *
  5    from
  6  ( select d, n, grp, rn
  7      from jr_tmp
  8     model dimension by(row_number() over(order by d) rn)
  9     measures(d, n, 1 grp)
 10     rules(grp[rn=1] = 1,
 11           grp[rn > 1] =
 12             case when (n[cv() - 1] <> n[cv()])
 13                  then grp[cv()-1]+1
 14                  else grp[cv()-1]
 15              end
 16          )
 17  )
 18  )
 19  group by n, grp
 20  order by 1
 21  /

MIN(D)    MAX(D)          N     GRP
————————— —————————— —————— ———————
01-DEC-14 03-DEC-14       1       1
04-DEC-14 05-DEC-14       2       2
06-DEC-14 08-DEC-14       1       3
...
Рейтинг: 0 / 0
05.10.2016, 14:31
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39321067
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Alexus12вариант pipelined
В означенных условиях (много данных, без индексов) имеет смысл использовать параллельное исполнение.
Pipelined возможно, если можете позволить себе задекларировать ее как parallel_enabled (strong ref cursor, наличие ключа по которому делить).
Надо учитывать, что у модели parallel execution от oracle есть два неприятных ограничение реализации, которые в случае pipelined проявляются нагляднее, а именно:
- oracle не умеет ставить в конвейер более двух наборов параллельных серверов, поэтому если нужен третий набор - случится промежуточная буферизация результата.
- oracle не умеет организовать порционную обработку, т.е. набор 1 встанет обслуживать шаг 3 только после полного завершения работы по шагу 1, поэтому буферизоваться будет ВЕСЬ датасет из второго набора параллельных серверов, пока не освободится первый набор для того, чтобы заняться третьим этапом.
Понимаю, на слух сложно воспринимается. Попробуйте нарисовать.

pipelined тут особенно заметна, поскольку ввиду синтаксических особенностей требуется сразу два набора серверов - первый работает над формированием входного курсора для pipelined (отбор данных и рассылка второму набору согласно спецификации parallel_enable), второй - собственно выполняет pipelined.
Так вооот...
Если делаем просто select - все в порядке.
Но если пытаемся заслать датасет в следующий этап типа merge или insert - получаем промежуточную буферизацию всего набора данных.
Большой набор в памяти не помещается и вываливается в TEMP, что приводит к катастрофическому падению производительности.

Аналитика тоже может запросто привести к аналогичному результату - но тут надо смотреть предметно. Если удастся объяснить ораклу, что параллельные процессы не пересекаются по данным (получить combined with parent для 2 и третьего этапов), то буферизации может быть удастся избежать - а с pipelined точно не удастся ...

По model - тема сложная.
Слишком много вариантов у модельки - итеративная, sequential rules, референсные модели... Куча условностей, влияющих на порядок выполнения.
По сути еще один движок, в плане сложности мало уступающий SQL-engine - при практически полном отсутствии исследований/публикаций на тему internals.
Black Box.
Я до сих пор в прод запустил только одну совсем простую модельку с единственным правилом - без нее было очень сложно решить конкретную задачу.

...по Вашей задаче - еще можно попробовать MATCH_RECOGNIZE.
Вроде неплохо отрабатывала объемы на тестах - не хуже сортировки, по крайней мере.
...
Рейтинг: 0 / 0
05.10.2016, 14:34
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39321175
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
andrey_anonymous,
спасибо за детальные пояснения по pipelined

вариант на MODEL на основе примера Кайта выше:
Код: 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.
with INV_TABLE as(
select 'bottle' PRODUCT,to_date('06.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS') TIME_ID,'8' QUANT,'D' OPER  from dual union all
select 'bottle',to_date('06.04.2001 01:00:00','DD.MM.RRRR HH24:MI:SS'),'9','D' from dual union all
select 'bottle',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'10','I' from dual union all
select 'can',to_date('04.04.2001 21:00:00','DD.MM.RRRR HH24:MI:SS'),'7','I' from dual union all
select 'can',to_date('04.04.2001 21:05:00','DD.MM.RRRR HH24:MI:SS'),'77','I' from dual union all
select 'can',to_date('04.04.2001 23:00:00','DD.MM.RRRR HH24:MI:SS'),'10','D' from dual union all
select 'can',to_date('04.04.2001 23:01:00','DD.MM.RRRR HH24:MI:SS'),'11','D' from dual union all
select 'can',to_date('04.04.2001 20:00:00','DD.MM.RRRR HH24:MI:SS'),'11','D' from dual union all
select 'can',to_date('04.04.2001 19:00:00','DD.MM.RRRR HH24:MI:SS'),'5','U' from dual union all
select 'can',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'15','I' from dual union all
select 'can',to_date('04.04.2001 22:00:00','DD.MM.RRRR HH24:MI:SS'),'99','D' from dual 
)

     select t.*
, first_value(quant) over (partition by PRODUCT, trunc(TIME_ID), grp order by TIME_ID) group_first_value
       from
     (
     select product, time_id, oper,quant, rn, grp
     
   
         from INV_TABLE
       model dimension by(--поля, формирующие группу
                           PRODUCT,
                           trunc(TIME_ID) operday,
                           --номер строки внутри большой группы, порядок обратный:
                           row_number() over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) rn 
                           )
       measures(TIME_ID, oper, QUANT, 1 grp)
       
       rules(grp[ any,any,rn=1] = 1,--если это первая строка , подгруппа=1
             grp[ any,any,rn > 1] = --иначе:
                                       case when (oper[cv(), cv(), cv() - 1] <> oper[cv(), cv(), cv()]) --если есть разница между значениями поля oper (маркера подгруппы) между записями 
                                            then grp[cv(), cv(), cv()-1]+1 -- то сгенерировать новую группу 
                                            else grp[cv(), cv(), cv()-1]   -- иначе использовать предыдущую
                                        end
            )
    ) t
order by PRODUCT,  TIME_ID


ухожу тестировать производительность на миллионах строк...
...
Рейтинг: 0 / 0
05.10.2016, 16:18
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39321180
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Моделька тоже позволяет partition by.
Упрощает правила и позволяет серверу разделить работу на части.
Ну и аналитика на выходе модельки - ИМХО не очень.
...
Рейтинг: 0 / 0
05.10.2016, 16:25
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39321186
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
andrey_anonymousМоделька тоже позволяет partition by
Код: 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.
with INV_TABLE as(
select 'bottle' PRODUCT,to_date('06.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS') TIME_ID,'8' QUANT,'D' OPER  from dual union all
select 'bottle',to_date('06.04.2001 01:00:00','DD.MM.RRRR HH24:MI:SS'),'9','D' from dual union all
select 'bottle',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'10','I' from dual union all
select 'can',to_date('04.04.2001 21:00:00','DD.MM.RRRR HH24:MI:SS'),'7','I' from dual union all
select 'can',to_date('04.04.2001 21:05:00','DD.MM.RRRR HH24:MI:SS'),'77','I' from dual union all
select 'can',to_date('04.04.2001 23:00:00','DD.MM.RRRR HH24:MI:SS'),'10','D' from dual union all
select 'can',to_date('04.04.2001 23:01:00','DD.MM.RRRR HH24:MI:SS'),'11','D' from dual union all
select 'can',to_date('04.04.2001 20:00:00','DD.MM.RRRR HH24:MI:SS'),'11','D' from dual union all
select 'can',to_date('04.04.2001 19:00:00','DD.MM.RRRR HH24:MI:SS'),'5','U' from dual union all
select 'can',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'15','I' from dual union all
select 'can',to_date('04.04.2001 22:00:00','DD.MM.RRRR HH24:MI:SS'),'99','D' from dual 
)

select t.*
, first_value(quant) over (partition by PRODUCT, trunc(TIME_ID), grp order by TIME_ID) group_first_value
  from
     (
     select product, time_id, oper,quant, rn, grp
       from INV_TABLE
       model
       partition by (PRODUCT, trunc(TIME_ID) oper_day)
       dimension by(--поля, формирующие группу
                           --номер строки внутри большой группы, порядок обратный:
                           row_number() over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) rn 
                           )
       measures(TIME_ID, oper, QUANT, 1 grp)
       
       rules(grp[ rn=1] = 1,--если это первая строка , подгруппа=1
             grp[ rn > 1] = --иначе:
                                       case when (oper[cv() - 1] <> oper[cv()]) --если есть разница между значениями поля oper (маркера подгруппы) между записями 
                                            then grp[cv()-1]+1 -- то сгенерировать новую группу 
                                            else grp[cv()-1]   -- иначе использовать предыдущую
                                        end
            )
    ) t
order by PRODUCT,  TIME_ID
...
Рейтинг: 0 / 0
05.10.2016, 16:31
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39321218
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
И first_value убрать
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select * --product, time_id, oper,quant, rn, grp
  from INV_TABLE
 model
   partition by (PRODUCT, trunc(TIME_ID) oper_day)
   dimension by (row_number() over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) rn )
    measures    (TIME_ID, oper, QUANT, cast(null as varchar2(20)) f)
       rules update sequential order
         ( f[ any ] order by rn desc = 
                         case when (oper[cv() + 1] <> oper[cv()]) then quant[cv()]
                              else coalesce( f[cv() + 1], quant[cv()] )
                          end
         , f[ rn>1 ] = quant[cv()]
         )
order by PRODUCT, oper_day, TIME_ID desc
...
Рейтинг: 0 / 0
05.10.2016, 16:57
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39321301
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
andrey_anonymous,

вероятно, нужно убрать строку f[ rn>1 ] = quant[cv()] - она корежит данные, без нее все верно...

итоги серии замеров на моих объемах (7 млн строк):
аналитика - ваш вариант = 6+ сек
аналитика - мой вариант = 27+ сек
model - ваш вариант = 28+ сек
model - мой вариант = 57+ сек

вывод - аналитика рулит, причем не типовое решение, а заточенное под конкретную задачу
снимаю шляпу ;)
...
Рейтинг: 0 / 0
05.10.2016, 18:31
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39321357
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Alexus12вероятно, нужно убрать строку f[ rn>1 ] = quant[cv()] - она корежит данные, без нее все верно...
Это очень странно, поскольку данное правило возвращает f значение quant для всех строк, которые "не первые".
Иначе f содержит "первое значение из последней группы" для всех записей.
...
Рейтинг: 0 / 0
05.10.2016, 19:39
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39321363
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
andrey_anonymous,

если данная тема http://www.sql.ru/forum/1233510-a/vakansii-na-kontrol-kachestva-dannyh-moskva заинтересует , прошу сообщить по указанному в ней адресу
...
Рейтинг: 0 / 0
05.10.2016, 19:42
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39323934
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
Alexus12andrey_anonymous,

итоги серии замеров на моих объемах (7 млн строк):
аналитика - ваш вариант = 6+ сек
аналитика - мой вариант = 27+ сек
model - ваш вариант = 28+ сек
model - мой вариант = 57+ сек

вывод - аналитика рулит, причем не типовое решение, а заточенное под конкретную задачу
снимаю шляпу ;)

при дальнейшей разработке выяснился неприятный момент: lag(fv_ ignore nulls) плохо масштабируется - скорость обработки падает не пропорционально увеличению кол-ва строк на входе, а быстрее.

у first_value такой проблемы нет - вариант из моей адаптации примера Кайта 19743456 показывает пропорциональный рост времени.
...
Рейтинг: 0 / 0
10.10.2016, 17:35
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39323962
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andrey_anonymousПо model - тема сложная.
Слишком много вариантов у модельки - итеративная, sequential rules, референсные модели... Куча условностей, влияющих на порядок выполнения.
По сути еще один движок, в плане сложности мало уступающий SQL-engine - при практически полном отсутствии исследований/публикаций на тему internals.
Black Box.
Я до сих пор в прод запустил только одну совсем простую модельку с единственным правилом - без нее было очень сложно решить конкретную задачу.Если не мешать все в кучу, то все достаточно тривиально.

Прежде всего модель означает загрузку всего набора данных в PGA для последующих speadsheet-like calculations.
При этом создается workarea c operation_type равным 'SPREADSHEET'.
Если в модели присутствует аналитика/агрегаты, то ясное дело создаются дополнительные рабочие области для сортировки и прочего.

Для обычной моедли правила вполняются столько раз сколько указано в rules.
Как правило указано не более одного правила на одну меру.
В случае итеративной выполнения правил соотвественно вся кухня выполняется n раз.
Правила бывают разного типа (с точки зрения перфоманса тоже), не буду в это счас углубляться.
"Условности" косвенно понимаются по плану {ORDERED [FAST] | ACYCLIC [FAST] | CYCLIC}.

Референснсные модели не более чем "рюшечка" и в них никакой необходимости вообще нет имхо - можно разрулить дополнительным соединением при получении набора данных для модели.

Главный жирный минус - нелинейный рост там, где recursive subquery factoring или PL/SQL solutions имеют линейный (я молчу про случаи когда workarea не влезает в опаративку и уходит в TEMP TS).

Думаю я не очень много нового сообщил. :)
Вообще работа почти написана, надеюсь в ближйший месяц опубликовать.
...
Рейтинг: 0 / 0
10.10.2016, 18:21
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39323981
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
+ попробовали нагрузочный тест варианта через MODEL 19747142 - оказался в 3 раза быстрее варианта моей адаптации примера Кайта 19743456 и при этом линейно масштабируемым


>Главный жирный минус - нелинейный рост там, где recursive subquery factoring или PL/SQL solutions имеют линейный (я молчу про случаи когда workarea не влезает в опаративку и уходит в TEMP TS).

dbms_photoshop , можете ли привести примеры / скрипты, чтобы оценить, как этот вариант нагружает систему / когда вылезает в TEMP?
вопрос важный, т.к. объемы большие (миллионы строк)
...
Рейтинг: 0 / 0
10.10.2016, 18:53
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39323993
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Alexus12dbms_photoshop , можете ли привести примеры / скрипты, чтобы оценить, как этот вариант нагружает систему / когда вылезает в TEMP?
вопрос важный, т.к. объемы большие (миллионы строк)Странный несколько вопрос. Я ж не знаю вашей конфигурации.
Если есть желание, чтоб ушло в темп, можно сделать в сессии примерно следующее
Код: plaintext
1.
alter session set workarea_size_policy=manual
alter session set sort_area_size=<очень мало>
А можно просто увеличивать объемы и смотреть динамику pga_allocated, temp_space_allocated из v$active_session_history для разных подходов.

Нагрузочное тестирование тоже звучит несколько странно.
Пока будет хватать оперативки и CPU для каждого из процессов все может быть относительно хорошо.
А вот с ростом data volumes модель будет все больше уступать альтернативным решениям.
Я не совсем согласен с выводами Кайта, как будет время посмотрю детальнее.
...
Рейтинг: 0 / 0
10.10.2016, 19:11
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39324025
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
dbms_photoshop,

>А вот с ростом data volumes модель будет все больше уступать альтернативным решениям.

вот про это и интерес - есть информация, когда это начинается / как узнать и измерить?
...
Рейтинг: 0 / 0
10.10.2016, 19:49
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39324134
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Alexus12dbms_photoshop,

>А вот с ростом data volumes модель будет все больше уступать альтернативным решениям.

вот про это и интерес - есть информация, когда это начинается / как узнать и измерить?Я сказал, что зависит от настроек.
Я рассказал про инструментарий и как пользоваться (как узнать и измерить).
Я объяснил некорректность вопроса.
Ты продолжаешь задавать то же самое.
Предполагается, что надо все прожевать и в рот положить?

Тест ниже для демонстрации не масштабируемости для задачи Кайта.
(мотивацией для нижеследующего было убедиться, что Кайт неправ)
Код: 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.
set serveroutput on

declare
  l_create_sql varchar2(1000) := 'create table t as
select rownum study_site, trunc(dbms_random.value(1,10+1))*5000 cnt
from
(select * from dual connect by level <= 1e2) t1,
(select * from dual connect by level <= 1e#) t2';
  s            timestamp;
begin

  for i in 0 .. 4 loop

    dbms_output.put_line('Iteration ' || i || ' Records ' || 1e3 * 10 ** i);

    execute immediate 'drop table t';
    execute immediate replace(l_create_sql, '#', i);

    execute immediate 'truncate table z';

    s := systimestamp;
    insert --+ append
    --+ plsql zzz
    into z
      select min(study_site) mi, max(study_site) ma, sum(cnt) su
        from (select *
                from table(foo(cursor (select study_site, cnt
                                  from t
                                 order by study_site),
                               65000)))
       group by the_group
       order by the_group;
    dbms_output.put_line(rpad('PL/SQL', 10, ' ') ||
                         to_char(systimestamp - s));

    execute immediate 'truncate table z';

    s := systimestamp;
    -- NoFormat Start
    insert --+ append
    --+ model zzz
    into z
    SELECT s, MAX(e), MAX(sm) FROM (
        SELECT s, e, cnt, sm FROM t
         MODEL DIMENSION BY(row_number()
                                over(order by study_site) rn)
               MEASURES(study_site s, study_site e, cnt, cnt sm)
               RULES(sm[rn > 1] =
                       CASE WHEN (sm[cv() - 1] + cnt[cv()]) > 65000
                                  OR cnt[cv()] > 65000
                            THEN cnt[cv()]
                            ELSE sm[cv() - 1] + cnt[cv()]
                        END,
                     s[rn > 1] =
                       CASE WHEN(sm[cv() - 1] + cnt[cv()]) > 65000
                                 OR cnt[cv()] > 65000
                            THEN s[cv()]
                            ELSE s[cv() - 1]
                        END))
      GROUP BY s ORDER BY s;
    -- NoFormat End
    dbms_output.put_line(rpad('MODEL', 10, ' ') ||
                         to_char(systimestamp - s));

  end loop;

end;
/
Iteration 0 Records 1000
PL/SQL    +000000000 00:00:00.036000000
MODEL     +000000000 00:00:00.014000000
Iteration 1 Records 10000
PL/SQL    +000000000 00:00:00.031000000
MODEL     +000000000 00:00:00.028000000
Iteration 2 Records 100000
PL/SQL    +000000000 00:00:00.072000000
MODEL     +000000000 00:00:00.228000000
Iteration 3 Records 1000000
PL/SQL    +000000000 00:00:00.543000000
MODEL     +000000000 00:00:02.217000000
Iteration 4 Records 10000000
PL/SQL    +000000000 00:00:04.362000000
MODEL     +000000000 00:00:22.500000000

PL/SQL procedure successfully completed.


set lines 100 pages 100
column sign format a15
select ash.*,
       regexp_substr(sql_text, '--\+ [^ ]+', 1, 2) sign
  from (select sql_id,
               count(*) cnt,
               max(ash.pga_allocated) / 1024 pga_kb,
               nullif(max(ash.temp_space_allocated) / 1024, 1024) temp_kb,
               sql_exec_id
          from v$active_session_history ash
         where ash.sample_time > sysdate - 5 / (24 * 60)
         group by ash.sql_id, sql_exec_id) ash
  join v$sql v
    on ash.sql_id = v.sql_id
   and sql_text like '%--+ append%zzz%'
   and sql_text not like '%v$sql%'
 order by sql_text, sql_exec_id;

SQL_ID               CNT     PGA_KB    TEMP_KB SQL_EXEC_ID SIGN
------------- ---------- ---------- ---------- ----------- ---------------
6buf0spwk6tzn          3      11955               16777219 --+ model
6buf0spwk6tzn         22      96947               16777220 --+ model
d7ysg6gr2mt5r          4      54771               16777220 --+ plsql

Первые две итерации PL/SQL проигрывает в пределах погрешности.
Потом модель проигрывает в три раза, потом в четыре, потом в пять.
...
Рейтинг: 0 / 0
11.10.2016, 01:45
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39324141
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Еще пару моментов.
1) тестировать вариант с recursive subquery factoring для данной задачи без индекса на таблице - это вообще не серьезно, поэтому я его не включал.
2) если добавить к тесту выше еще одну итерацию (то есть на последней итерации будет 1М строк), результаты выглядят следующим образом.

cnt - время выполнения в секундах
число строк соответственно 10 000, 100 000 и 1 000 000. В последнем случае начинает уходить в темп для обоих подходов.
PL/SQL выполнился за минуту, для модели после получаса выполнения прибил сессию.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
select ash.*, regexp_substr(sql_text, '--\+ [^ ]+', 1, 2) sign
  from (select sql_id,
               count(*) cnt,
               max(ash.pga_allocated) / 1024 pga_kb,
               nullif(max(ash.temp_space_allocated) / 1024, 1024) temp_kb,
               sql_exec_id
          from v$active_session_history ash
         where ash.sample_time > sysdate - 1 / (24)
         group by ash.sql_id, sql_exec_id) ash
  join v$sql v
    on ash.sql_id = v.sql_id
   and sql_text like '%--+ append%yyyyy%'
   and sql_text not like '%v$sql%'
 order by 6, 2;

SQL_ID               CNT     PGA_KB    TEMP_KB SQL_EXEC_ID SIGN
------------- ---------- ---------- ---------- ----------- ---------------
abnq6xymjag8v          3      12356               16777219 --+ model
abnq6xymjag8v         24     102084               16777220 --+ model
abnq6xymjag8v       1793     191155    1025024    16777221 --+ model
0muc2rqf46yp6          1       5875                        --+ plsql
0muc2rqf46yp6          4      51908               16777220 --+ plsql
0muc2rqf46yp6         58      95795     361472    16777221 --+ plsql
...
Рейтинг: 0 / 0
11.10.2016, 02:23
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39324147
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andrey_anonymous,
 
pattern matching
Код: 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.
with INVENT_TABLE as(
select 'bottle' PRODUCT,to_date('06.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS') TIME_ID,'8' QUANT,'D' OPER  from dual union all
select 'bottle',to_date('06.04.2001 01:00:00','DD.MM.RRRR HH24:MI:SS'),'9','D' from dual union all
select 'bottle',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'10','I' from dual union all
select 'can',to_date('04.04.2001 21:00:00','DD.MM.RRRR HH24:MI:SS'),'7','I' from dual union all
select 'can',to_date('04.04.2001 21:05:00','DD.MM.RRRR HH24:MI:SS'),'77','I' from dual union all
select 'can',to_date('04.04.2001 23:00:00','DD.MM.RRRR HH24:MI:SS'),'10','D' from dual union all
select 'can',to_date('04.04.2001 23:01:00','DD.MM.RRRR HH24:MI:SS'),'11','D' from dual union all
select 'can',to_date('04.04.2001 20:00:00','DD.MM.RRRR HH24:MI:SS'),'11','D' from dual union all
select 'can',to_date('04.04.2001 19:00:00','DD.MM.RRRR HH24:MI:SS'),'5','U' from dual union all
select 'can',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'15','I' from dual union all
select 'can',to_date('04.04.2001 22:00:00','DD.MM.RRRR HH24:MI:SS'),'99','D' from dual
)
select product, time_id, oper, rn, quant, decode(rn, 1, q, quant) fv, total - local_rn + 1 local_rn
from (select it.*, trunc(time_id) x,
             row_number() over (partition by product, trunc(time_id) order by time_id desc) rn
        from invent_table it)
match_recognize
(
  partition by product, x
  order by time_id
  measures
    count(*) local_rn,
    final count(*) total,
    first(quant) as q
  all rows per match
  pattern (grp+)
  define
    grp as oper = first(oper)
)
order by product, trunc(time_id), time_id desc;

PRODUC TIME_ID   O         RN QU FV   LOCAL_RN
------ --------- - ---------- -- -- ----------
bottle 01-APR-01 I          1 10 10          1
bottle 06-APR-01 D          1 9  8           1
bottle 06-APR-01 D          2 8  8           2
can    01-APR-01 I          1 15 15          1
can    04-APR-01 D          1 11 99          1
can    04-APR-01 D          2 10 10          2
can    04-APR-01 D          3 99 99          3
can    04-APR-01 I          4 77 77          1
can    04-APR-01 I          5 7  7           2
can    04-APR-01 D          6 11 11          1
can    04-APR-01 U          7 5  5           1

11 rows selected.

Одним только match_recognize решить несколько затруднительно.
Потому как если делать, чтоб был матч на каждую группу I/D то нельзя посчитать rn (она будет в рамках группы, а не всей секции).
А если делать матч на всю секцию, то нельзя посчитать "первую запись из последней группы" (будет первая запись в рамках всей секции, а не последней группы).

А поскольку в вашем решении во всех аналитических функциях одинаковое окно, то смысла в match_recognize нет.
Ну разве что таки можно извратиться и достичь нужного одним match_recognize без аналитики...
...
Рейтинг: 0 / 0
11.10.2016, 04:01
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39324301
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
dbms_photoshop,

спасибо!
...
Рейтинг: 0 / 0
11.10.2016, 11:07
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39324627
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
dbms_photoshopandrey_anonymous,...нельзя посчитать rn (она будет в рамках группы, а не всей секции).
Если я ничего не путаю, то по постановке этого не требовалось.
rn появился в экспериментах ТС как побочный эффект от sog.
...что касается sog, то вариант, многократно вылизанный на данном форуме, мне как-то больше по душе, чем вариант с asktom...
...
Рейтинг: 0 / 0
11.10.2016, 15:33
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39324634
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andrey_anonymousrn появился в экспериментах ТС как побочный эффект от sog
У него на это заточена логика.
Alexus12затем для каждой строки, где row_number() = 1 ...Речь не про sog.
Замечание было по поводу невозможности решения одним pattern matching и без аналитики.
Если я неправ - просьба опровергнуть с помощью конкретного решения. Вот и всё.
...
Рейтинг: 0 / 0
11.10.2016, 15:40
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39324637
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
>затем для каждой строки, где row_number() = 1

мне нужна эта строка для дальнейшей обработки, да
...
Рейтинг: 0 / 0
11.10.2016, 15:45
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39324649
Alexus12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexus12
Участник
andrey_anonymous,

слегка изменили ваш вариант отсюда 19743612

получилось так:
Код: 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.
вариант через last_value

with INV_TABLE as(
 select 'bottle' PRODUCT,to_date('06.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS') TIME_ID,'8' QUANT,'D' OPER  from dual union all
 select 'bottle',to_date('06.04.2001 01:00:00','DD.MM.RRRR HH24:MI:SS'),'9','D' from dual union all
 select 'bottle',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'10','I' from dual union all
 select 'can',to_date('04.04.2001 21:00:00','DD.MM.RRRR HH24:MI:SS'),'7','I' from dual union all
 select 'can',to_date('04.04.2001 21:05:00','DD.MM.RRRR HH24:MI:SS'),'77','I' from dual union all
 select 'can',to_date('04.04.2001 23:00:00','DD.MM.RRRR HH24:MI:SS'),'10','D' from dual union all
 select 'can',to_date('04.04.2001 23:01:00','DD.MM.RRRR HH24:MI:SS'),'11','D' from dual union all
 select 'can',to_date('04.04.2001 20:00:00','DD.MM.RRRR HH24:MI:SS'),'11','D' from dual union all
 select 'can',to_date('04.04.2001 19:00:00','DD.MM.RRRR HH24:MI:SS'),'5','U' from dual union all
 select 'can',to_date('01.04.2001 00:00:00','DD.MM.RRRR HH24:MI:SS'),'15','I' from dual union all
 select 'can',to_date('04.04.2001 22:00:00','DD.MM.RRRR HH24:MI:SS'),'99','D' from dual 


)
--select count(*) from INV_TABLE;

select product, time_id, oper, rn, quant 
       ,prev_oper
 --      , fv_
       , fv_2      
     --  , lag(fv_ ignore nulls) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID) lag--значение из предыд записи
     -- , lead(fv_ ignore nulls) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) lead--значение из предыд записи
--   , case rn 
 --          when 1 then 
 --            coalesce(
 --              lag(fv_ ignore nulls) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID)
 --               , first_value(quant) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID)
 --            )
 --         --else quant
 --          end fv    
     , last_value(fv_2 ignore nulls) over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID ) lv            
   from (
     SELECT /*+parallel(16)*/PRODUCT, TIME_ID,  OPER, QUANT
          , row_number() over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) rn
          ,lead(oper, 1) over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc) prev_oper--предыдущий oper
 --         , case lead(oper, 1, oper) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc)
 --           when oper then null else quant --если подгруппа та же , вернуть нулл, иначе quant
 --           end fv_
          
          , case lead(oper, 1) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID desc)
            when oper then null --если подгруппа та же , вернуть нулл
           else quant --, иначе quant (если первая запись или если группа изменилась)
            end fv_2  
            
     FROM INV_TABLE
     ) i 
 --) group by PRODUCT, trunc(TIME_ID)
 ;



изменения:
1) во внутреннем запросе в выражении case lead(oper, 1, oper) убран последний oper - поэтому для крайнего значения возвращается null, что приводит к выводу в fv_2 значения _в том числе_ для первой записи первой подгруппы, а не только при смене подгрупп

2) во внешнем : выражение

case rn
-- when 1 then
-- coalesce(
-- lag(fv_ ignore nulls) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID)
-- , first_value(quant) over(partition by PRODUCT, trunc(TIME_ID) order by TIME_ID)


заменено на

, last_value(fv_2 ignore nulls) over (partition by PRODUCT, trunc(TIME_ID) order by TIME_ID ) lv

это протягивает найденные во внутреннем запросе значения fv_2 от первой записи подгруппы на все ее записи

в результате код избавился от тормозов, вносимых lag(fv_ ignore nulls) , и масштабируется линейно
...
Рейтинг: 0 / 0
11.10.2016, 15:56
| Ответить | Цитировать | Написать  
получить первую запись из последней группы
    #39481364
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
dbms_photoshopОдним только match_recognize решить несколько затруднительно.Все решается элементарно если чуть больше поразмыслить.

Код: 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.
SQL> select product, time_id, quant, oper, decode(c,1,q) fv, bug_case, bug_decode
  2  from (select it.*, trunc(time_id) x from invent_table it)
  3  match_recognize
  4  (
  5    partition by product, x
  6    order by time_id desc
  7    measures
  8      case when count(*) = 1 then 2 else 3 end bug_case,
  9      decode(count(*), 1, 2, 3) bug_decode,
 10      count(*) c,
 11      final last(d.quant) q
 12    all rows per match with unmatched rows
 13    pattern (D+ dummy*)
 14    define
 15      d as d.oper = 'D'
 16  )
 17  order by product, trunc(time_id), time_id;

PRODUC TIME_ID   QU O FV   BUG_CASE BUG_DECODE
------ --------- -- - -- ---------- ----------
bottle 01-APR-01 10 I
bottle 06-APR-01 8  D
bottle 06-APR-01 9  D 8
can    01-APR-01 15 I
can    04-APR-01 5  U
can    04-APR-01 11 D
can    04-APR-01 7  I
can    04-APR-01 77 I
can    04-APR-01 99 D
can    04-APR-01 10 D
can    04-APR-01 11 D 99

11 rows selected.


В решении предполагается, что последняя группа состоит из D. Если это может быть не так - то просто чуть усложняется шаблон.

Как выяснилось case/decode в measures всегда возвращают null.
Еще баг из той же оперы: 20561938 . Но там one row per match и криво работает nullif.
...
Рейтинг: 0 / 0
02.07.2017, 22:31
| Ответить | Цитировать | Написать  
38 сообщений из 38, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / получить первую запись из последней группы
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1885682]:
 0ms
get settings:
 8ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 181ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 47ms
get tp. blocked users:
 1ms
others: 228ms
total:  495ms
созд. / загр.: 495ms
x
x
Закрыть


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