|
получить первую запись из последней группы
#39320528
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
на ASKTOM найден пример:
group records by interval of 3 seconds
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13946369553642
цитата:
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
по образу и подобию решена моя задача:
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 вычислен "для красоты", для решения задачи он не требуется |
|
|
