查无关键字统计报表
查无关键字统计报表
create or replace procedure P_114_SEARCHNT(
o_Cursor Out Pack_Service.t_Retdataset,
i_Begindate In varchar2, -- 开始时间
i_Enddate In varchar2, -- 结束时间
i_citycode In varchar2 -- 地址代码
)
/********************************************
功能：查无关键字统计报表(详单)
作者：FC
*********************************************/
is
v_Begindate Date := to_Date(i_Begindate, 'yyyy-mm-dd');
v_Enddate Date := to_Date(i_Enddate, 'yyyy-mm-dd');
v_citycode varchar2(20) := substr(i_citycode, 0, Instr(i_citycode, '|') - 1);
begin
if v_citycode is null then
v_citycode := i_citycode;
end if;
open o_Cursor for
select Keyword, count(1) as Searchtime, Searchcyc, Cityname
from (
select case
when t.title is null then
replace(replace(replace(t.content, '查询条件', ''), '：', ''), ' ', '')
else
replace(replace(replace(t.title, '查询条件', ''), '：', ''), ' ', '')
end as Keyword,
i_Begindate || '至' || i_Enddate as Searchcyc,
c.cityname as Cityname
from T_WORKSHEET_LOG t, T_CITIES c
where t.citycode = c.citycode
and t.handledate >= v_Begindate
and t.handledate < v_Enddate
and t.action = 'A'
and (t.citycode = v_citycode or 'gz' = v_citycode))
group by Cityname, Keyword, Searchcyc;
end P_114_SEARCHNT;
