数据量不大的话，写个函数，滑动窗口参数调节：
SQL>
SQL> create table t_data as select * from (
2 select 'aaa' deviceid,'a1' sessionid, 'url1' url,1 rn from dual union all
3 select 'aaa' deviceid,'a1' sessionid, 'url2' url,2 rn from dual union all
4 select 'aaa' deviceid,'a1' sessionid, 'url1' url,3 rn from dual union all
5 select 'aaa' deviceid,'a1' sessionid, 'url2' url,4 rn from dual union all
6 select 'aaa' deviceid,'a1' sessionid, 'url1' url,5 rn from dual union all
7 select 'aaa' deviceid,'a1' sessionid, 'url2' url,6 rn from dual union all
8 select 'aaa' deviceid,'a2' sessionid, 'url1' url,1 rn from dual union all
9 select 'aaa' deviceid,'a2' sessionid, 'url2' url,2 rn from dual union all
10 select 'aaa' deviceid,'a2' sessionid, 'url2' url,3 rn from dual union all
11 select 'aaa' deviceid,'a2' sessionid, 'url2' url,4 rn from dual
12 );
Table created
SQL> create or replace type t_tab_url is table of varchar2(100);
2 /
Type created
SQL>
SQL> create or replace function f_max_url (p_deviceid in varchar2,p_sessionid in varchar2,p_rn in int, p_offset_n in int)
2 return varchar2
3 is
4 l_tab t_tab_url;
5 l_res t_data.url%type;
6 begin
7 if p_rn in (1,2) then
8 l_res := 'null';
9 else
10 select url
11 bulk collect into l_tab
12 from t_data x
13 where x.deviceid = p_deviceid
14 and x.sessionid = p_sessionid
15 and x.rn between p_rn - p_offset_n + 1 and p_rn;
16
17 select max(column_value) keep (dense_rank first order by count(column_value) desc)
18 into l_res
19 from table(l_tab)
20 group by column_value;
21 end if;
22
23 return l_res;
24
25 end;
26 /
Function created
SQL>
SQL> create or replace function f_max_cnt (p_deviceid in varchar2,p_sessionid in varchar2,p_rn in int, p_offset_n in int)
2 return int
3 is
4 l_tab t_tab_url;
5 l_res int := 0;
6 begin
7 if p_rn in (1,2) then
8 l_res := 0;
9 else
10 select url
11 bulk collect into l_tab
12 from t_data x
13 where x.deviceid = p_deviceid
14 and x.sessionid = p_sessionid
15 and x.rn between p_rn - p_offset_n + 1 and p_rn;
16
17 select max(count(column_value))
18 into l_res
19 from table(l_tab)
20 group by column_value;
21 end if;
22
23 return l_res;
24
25 end;
26 /
Function created
SQL>
SQL> select t.deviceid,t.sessionid,t.url,t.rn,
2 f_max_url(t.deviceid,t.sessionid,t.rn,3) max_url,
3 f_max_cnt(t.deviceid,t.sessionid,t.rn,3) max_cnt
4 from t_data t
5 /
DEVICEID SESSIONID URL RN MAX_URL MAX_CNT
-------- --------- ---- ---------- -------------------------------------------------------------------------------- ----------
aaa a1 url1 1 null 0
aaa a1 url2 2 null 0
aaa a1 url1 3 url1 2
aaa a1 url2 4 url2 2
aaa a1 url1 5 url1 2
aaa a1 url2 6 url2 2
aaa a2 url1 1 null 0
aaa a2 url2 2 null 0
aaa a2 url2 3 url2 2
aaa a2 url2 4 url2 3
10 rows selected
1422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
