langchao_fayuan/procedures/wk_bi_app_access_stat.sql

CREATE PROCEDURE lawe.wk_bi_app_access_stat(in bg_time varchar(300), in ed_time varchar(300))
BEGIN
	DELETE FROM bi_app_access_stat WHERE dtype='HOUR' and thedvalue between bg_time and ed_time;
	insert into bi_app_access_stat
	SELECT
		'HOUR' AS dtype,
	    DATE_FORMAT(A.time_mark,'%Y%m%d%H') as thedvalue,
		B.ITM_NETGROUP_ID AS NET_ID,
		C.NAME AS net_name,
		A.ORGAN_ID AS ORGAN_ID,
		D.short_name AS ORGAN_name,
		A.app_id as app_id,
		B.ITM_APP_NAME AS app_name,
		sum(A.visit_count) as visit_count,
		sum(A.operate_count) as operate_count ,
	CASE
			B.PRIORITY 
			WHEN 'low' THEN
			'一般应用' 
			WHEN 'important' THEN
			'重要应用' 
			WHEN 'high' THEN
			'核心应用' ELSE '未知' 
		END AS PRIORITY,
		F.NAME
	FROM  app_visit_log_region as A
		LEFT JOIN itm_app B on A.app_id=B.itm_app_id
		LEFT JOIN ( SELECT VALUE, NAME FROM m_com_dict WHERE dict_index = 'itm_resources_netcode' ) C ON C.VALUE = B.ITM_NETGROUP_ID
		LEFT JOIN pub_organ D ON D.id = A.ORGAN_ID
		LEFT JOIN ( SELECT temp1.ITM_APP_ID, temp2.NAME FROM itm_app temp1 LEFT JOIN m_com_dict temp2 ON temp1.app_type = temp2.VALUE ) AS F ON A.app_id = F.itm_app_id 
	WHERE
		DATE_FORMAT(A.time_mark,'%Y%m%d%H') between bg_time and ed_time
	GROUP BY
		A.app_id, A.ORGAN_ID, B.ITM_NETGROUP_ID, B.ITM_APP_NAME, C.NAME, D.short_name, B.priority, F.NAME, B.PRIORITY, A.time_mark, DATE_FORMAT(A.time_mark,'%Y%m%d%H');

	DELETE FROM bi_app_access_stat WHERE dtype='DAY' and thedvalue=SUBSTRING(bg_time, 1, 8);
	insert into bi_app_access_stat
	SELECT
		'DAY' AS dtype,
	  	substring(thedvalue,1,8) AS thedvalue, 
		NET_ID,
		 net_name,
		 ORGAN_ID,
		 ORGAN_name,
		app_id,
		app_name,
		sum(visit_count) as visit_count,
		sum(operate_count) as operate_count ,
	CASE
			PRIORITY 
			WHEN 'low' THEN
			'一般应用' 
			WHEN 'important' THEN
			'重要应用' 
			WHEN 'high' THEN
			'核心应用' ELSE '未知' 
		END AS PRIORITY,
		app_type
	FROM bi_app_access_stat 
	WHERE dtype = 'HOUR' and substring(thedvalue,1,8)= SUBSTRING(bg_time, 1, 8)
	GROUP BY dtype,thedvalue,NET_ID,net_name,ORGAN_ID,ORGAN_name,app_id,app_name,priority,app_type,substring(thedvalue,1,8);

	DELETE FROM bi_app_access_stat WHERE dtype='MONTH' and thedvalue=SUBSTRING(bg_time, 1, 6);
	insert into bi_app_access_stat
	SELECT
		'MONTH' AS dtype,
	  	substring(thedvalue,1,6) AS thedvalue, 
		NET_ID,
		 net_name,
		 ORGAN_ID,
		 ORGAN_name,
		app_id,
		app_name,
		sum(visit_count) as visit_count,
		sum(operate_count) as operate_count ,
	CASE
			PRIORITY 
			WHEN 'low' THEN
			'一般应用' 
			WHEN 'important' THEN
			'重要应用' 
			WHEN 'high' THEN
			'核心应用' ELSE '未知' 
		END AS PRIORITY,
		app_type
	FROM bi_app_access_stat 
	WHERE dtype = 'DAY' and substring(thedvalue,1,6)= SUBSTRING(bg_time, 1, 6)
	GROUP BY dtype,thedvalue,NET_ID,net_name,ORGAN_ID,ORGAN_name,app_id,app_name,priority,app_type,substring(thedvalue,1,6);

	DELETE FROM bi_app_access_stat WHERE dtype='YEAR' and thedvalue=SUBSTRING(bg_time, 1, 4);
	insert into bi_app_access_stat
	SELECT
		'YEAR' AS dtype,
	  	substring(thedvalue,1,4) AS thedvalue, 
		NET_ID,
		 net_name,
		 ORGAN_ID,
		 ORGAN_name,
		app_id,
		app_name,
		sum(visit_count) as visit_count,
		sum(operate_count) as operate_count ,
	CASE
			PRIORITY 
			WHEN 'low' THEN
			'一般应用' 
			WHEN 'important' THEN
			'重要应用' 
			WHEN 'high' THEN
			'核心应用' ELSE '未知' 
		END AS PRIORITY,
		app_type
	FROM bi_app_access_stat 
	WHERE dtype = 'MONTH' and substring(thedvalue,1,4)= SUBSTRING(bg_time, 1, 4)
	GROUP BY dtype,thedvalue,NET_ID,net_name,ORGAN_ID,ORGAN_name,app_id,app_name,priority,app_type,substring(thedvalue,1,4);
END