3,491
社区成员
发帖
与我相关
我的任务
分享
procedure sp_SearchAnalytics(
p_start_date in date,
p_end_date in date,
p_keyword in varchar2,
p_domain in varchar2,
p_return out cur_retrieve
)
as
begin
open p_return for
select t.page_url_no_domain_no_para,
t.view_count,
(
select count(distinct guid)
from s_tvsn_analytics_history
where page_url_no_domain_no_para = t.page_url_no_domain_no_para
and (p_start_date is null or open_date >= p_start_date)
and (p_end_date is null or open_date <= p_end_date)
and (p_domain is null or domain = p_domain)
) user_count,
--以下这个select效率非常低,是算跳出次数的
(
select count(*)
from s_tvsn_analytics_history tah
where page_url_no_domain_no_para = t.page_url_no_domain_no_para
and (p_start_date is null or open_date >= p_start_date)
and (p_end_date is null or open_date <= p_end_date)
and (p_domain is null or domain = p_domain)
and (
select count(*)
from s_tvsn_analytics_history
where guid = tah.guid
and ip = tah.ip
and ref_url = tah.page_url
and open_date > tah.open_date
) = 0
) exit_count
from (
select page_url_no_domain_no_para,
sum(1) view_count
from s_tvsn_analytics_history
where (p_start_date is null or open_date >= p_start_date)
and (p_end_date is null or open_date <= p_end_date)
and (p_keyword is null or page_url_no_para like '%' || p_keyword || '%')
and (p_domain is null or domain = p_domain)
group by page_url_no_domain_no_para) t;
end sp_SearchAnalytics;