|
Блокировки при применении фильтров IR
#38958252
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
blkangel,
Кстати, собирал на коленке как-то запрос пару лет назад (прошу к алгоритмам серьезно не относиться, скорее так, глянуть чисто ради интереса)
avg_sec_per_view и avg_sec_per_active_time_view (среднее кол. сек. на запрос и среднее на запрос в самый активный час) сильно отличаются, из-за этого ковыряете, или из-за чего-то другого ?
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.
with active_hour as (
select max(trunc(act.view_date,'hh24')) keep (dense_rank last order by count(*)) val
from apex_workspace_activity_log act
group by trunc(act.view_date,'hh24')
)
, active_time as (
select t.val begins
, t.val+1/24 ends
from active_hour t
)
, active_day as (
select max(trunc(act.view_date,'dd')) keep (dense_rank last order by count(*)) val
from apex_workspace_activity_log act
group by trunc(act.view_date,'dd')
)
, work_time as (
select min(active_time) begins
, max(active_time)+1/24 ends
from (
select case when count(act.view_date) - avg(count(act.view_date)) over () > 0
then trunc(act.view_date,'hh24')
else null end active_time
from apex_workspace_activity_log act
, active_day t
where act.view_date between t.val and t.val+1
group by trunc(act.view_date,'hh24')
)
)
select to_char(sysdate,'dd.mm.yyyy hh24:mi') "DATE"
, count(ses.apex_session_id) current_apex_sessions
, count(distinct ses.user_name) current_apex_users
, count(distinct max(act.ip_address)) current_computers
, round(avg(count(ses.apex_session_id))) avg_page_views_per_session
, round(24*60*avg(sysdate - ses.session_created)) avg_session_duration_min
, round(24*60*avg( decode( ses.apex_session_id
, null
, null
, max(act.view_date)-min(act.view_date)))) avg_activity_duration_min
, to_char(max(work_time.begins),'"("dd.mm.yyyy")" hh24:mi')||' - '||to_char(max(work_time.ends),'hh24:mi') work_time
, to_char(max(active_time.begins),'"("dd.mm.yyyy")" hh24:mi')||' - '||to_char(max(active_time.ends),'hh24:mi') active_time_by_hour
, round(sum(sum(case when act.view_date between active_time.begins and active_time.ends then 1 else 0 end)) / max(24*60*(active_time.ends-active_time.begins))) views_per_min_in_active_time
, round(sum(count(*)) / (24*60*(sysdate-min(min(act.view_date))))) views_per_min_in_2week
, sum(count(*)) views_in_2week
, to_char(100*sum(count(case when act.view_date between active_time.begins and active_time.ends then act.elapsed_time else null end))/sum(count(case when act.view_date between trunc(active_time.begins) and trunc(active_time.begins+1) then act.elapsed_time else null end)),'99.9') || '%' views_in_active_time
, round(avg(avg(act.elapsed_time)),2) avg_sec_per_view
, round(avg(avg(case when act.view_date between active_time.begins and active_time.ends then act.elapsed_time else null end)),2) avg_sec_per_active_time_view
from apex_workspace_sessions ses
, apex_workspace_activity_log act
, active_time active_time
, work_time work_time
where act.apex_session_id = ses.apex_session_id(+)
group by ses.apex_session_id, ses.session_created, ses.user_name, active_time.begins, active_time.ends, work_time.begins, work_time.ends
|
|
|
