业务背景

越来越多的业务系统或app开始对各功能点击量有统计的需求，或是用于展示或是用于功能点分析。一般实现方式为一张表用来存储各菜单id，一张表用来记录每个功能的点击记录，然后通过对点击记录表汇总查询得出结果。

最直接的想法就是对指定日期间隔内的数据group by分别查出每个功能点的点击次数，但无法同时统计多天的点击量情况，下面就该问题进行分析~

首先将业务模式进行简化，例如有如下两张表：

• t_menu_dict 用于存储菜单选项
• t_click_record 用于记录每个菜单功能点击记录

--建表
create table t_menu_dict --菜单字典表
(
menu_id int,		--菜单id
menu_name varchar(20)	--菜单名称
);

create table t_click_record --点击记录表
(
menu_id int,		--菜单id
click_time timestamp,	--点击时间
user_id int 		--用户id
);

--构造数据
insert into t_menu_dict values(1,'首页');
insert into t_menu_dict values(2,'我的');
insert into t_menu_dict values(3,'消息');
insert into t_menu_dict values(4,'发现');

insert into t_click_record select trunc(rand()*4)+1,to_date('2022-01-01')+rand()*5,1000+trunc(rand()*100) from dual connect by level<=10000;
insert into t_click_record select trunc(rand()*4)+1,to_date('2022-01-06')+rand()*5,1000+trunc(rand()*100) from dual connect by level<=20000;
commit;

数据示例

select * from t_menu_dict;
select * from t_click_record;

需求

统计给定日期范围内每天每个菜单功能的点击量

• e.g. 统计 2022-01-01 至 2022-01-07 每日点击量

需求分析

可借助case when实现行转列功能，形如：

select menu_id,
sum(case trunc(click_time) when to_date('2022-01-01') then 1 else 0 end)  as "2022_01_01" ,
sum(case trunc(click_time) when to_date('2022-01-02') then 1 else 0 end)  as "2022_01_02"
from t_click_record group by menu_id order by menu_id;

但由于给定时间范围不固定，导致查询项列数不确定，且列名无法动态变化，无法通过固定sql实现，借助存储过程实现动态语句拼接。

存储过程实现

create or replace procedure p_click_static(b_time date,e_time date) as
V_DATE DATE;		--每天日期变量
V_SQL_SELECT VARCHAR2;	--拼接查询项
BEGIN
V_DATE:=B_TIME;
WHILE V_DATE<=E_TIME 
LOOP
--PRINT V_DATE;
--拼接查询项
V_SQL_SELECT:=V_SQL_SELECT||',sum(case trunc(click_time) when to_date('''||trunc(V_DATE)||''') then 1 else 0 end)  as "'||to_char(V_DATE,'YYYY_MM_DD')||'"';
V_DATE=V_DATE+1;
END LOOP;
execute immediate 'select d.menu_id,d.menu_name'||V_SQL_SELECT||'from t_click_record r,t_menu_dict d where d.menu_id=r.menu_id  group by d.menu_id,d.menu_name order by d.menu_id';
END;

使用方式

call p_click_static('2022-01-01','2022-01-05')

其它

• 可在查询语句中增加其它过滤条件实现更多功能
• 由于存储过程中为拼接sql，受字符串长度限制，当查询跨度过大时拼接sql会出现字符串截断错误，可分段进行查询，目前测试跨度一个月无问题