#存储过程 每月AP和 FPV和 平均量 DROP PROCEDURE IF EXISTS pro_save_ap_manage_result_; CREATE PROCEDURE pro_save_ap_manage_result_ () #名称：pro_save_ap_manage_result_ BEGIN #开始 -- --------------内容开始-------------------------- insert into wbo_ap_manage_result SELECT DISTINCT count(DISTINCT a.AP_CODE) AP_CODE, -- a.SSID, sum(a.pv) pv, sum(a.uv) uv, sum(a.cpc) cpc, sum(a.cpm) cpm, sum(a.flow) flow, sum(a.P_FLOW) P_FLOW, sum(a.DURATION) DURATION, sum(a.RPV) RPV, sum(a.FPV) FPV, a.`MONTH`, a.`YEAR`, ROUND(sum(a.FPV) / count(DISTINCT a.AP_CODE)) pvAverage FROM wbo_ap_dtl_day a WHERE a. YEAR = year(curdate()) #本年 AND a. MONTH = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y%m') #上月 AND a. DAY >= date_format(date_sub(date_sub(date_format(now(),'%y%m%d'),interval extract( day from now())-1 day),interval 1 month),'%Y%m%d') #上月第一天 AND a. DAY <= date_format(date_sub(date_sub(date_format(now(),'%y%m%d'),interval extract( day from now()) day),interval 0 month),'%Y%m%d') #上月最后一天 GROUP BY a.`YEAR`, a.`MONTH`; #以';'结束 #多条语句 select now();#以';'结束 -- --------------注意“;”内容结束-------------------------- END;#结束
#事件 定时执行 DROP EVENT IF EXISTS eve_run_ap_manage_result_; CREATE EVENT `eve_run_ap_manage_result_` ON SCHEDULE EVERY 1 month STARTS '2016-10-01 00:00:00'#每月1号执行 ON COMPLETION NOT PRESERVE ENABLE DO CALL `pro_save_ap_manage_result_` /* DROP EVENT IF EXISTS eve_run_ap_manage_result_; CREATE EVENT `eve_run_ap_manage_result_` ON SCHEDULE EVERY 5 SECOND STARTS '2016-09-22 00:00:00'#每5s执行 ON COMPLETION NOT PRESERVE ENABLE DO CALL `pro_save_ap_manage_result_` */
#存储过程 日均维度表月表分表 DROP PROCEDURE IF EXISTS pro_ap_month_dtl_day_; CREATE PROCEDURE pro_ap_month_dtl_day_(in pvAverage VARCHAR (32),in tab_name VARCHAR (32)) BEGIN set @STMT:=concat("INSERT INTO ",getApMonthTabName(tab_name), #动态表名 " SELECT DISTINCT a.AP_CODE AP_CODE, -- a.SSID, SUM(a.pv) pv, SUM(a.uv) uv, SUM(a.cpc) cpc, SUM(a.cpm) cpm, SUM(a.flow) flow, SUM(a.P_FLOW) P_FLOW, SUM(a.DURATION) DURATION, SUM(a.RPV) RPV, SUM(a.FPV) FPV, a.`MONTH`, a.`YEAR`, CASE WHEN ROUND( SUM(a.fpv) / COUNT(DISTINCT a.AP_CODE) ) > 0 and ROUND( SUM(a.fpv) / COUNT(DISTINCT a.AP_CODE) ) < ",pvAverage," THEN 2 WHEN ROUND( SUM(a.fpv) / COUNT(DISTINCT a.AP_CODE) ) >=",pvAverage," THEN 1 ELSE 3 END apState FROM wbo_ap_dtl_day a WHERE a. YEAR = year(curdate())#本年 AND a. MONTH = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y%m') #上月 AND a. DAY >= date_format(date_sub(date_sub(date_format(now(),'%y%m%d'),interval extract( day from now())-1 day),interval 1 month),'%Y%m%d') #上月第一天 AND a. DAY <= date_format(date_sub(date_sub(date_format(now(),'%y%m%d'),interval extract( day from now()) day),interval 0 month),'%Y%m%d') #上月最后一天 GROUP BY a.AP_CODE, a.`YEAR`, a.`MONTH`"); PREPARE stmt FROM @STMT; EXECUTE stmt ; end;
- 调用存储过程
call pro_ap_month_dtl_day_('4','8'); call pro_ap_month_dtl_day_(getpvAverage(),date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%c'));
#存储过程 日均维度月临时表 DROP PROCEDURE IF EXISTS pro_wbo_ap_dtl_month_tmp_; CREATE PROCEDURE pro_wbo_ap_dtl_month_tmp_(in pvAverage VARCHAR (32)) BEGIN INSERT INTO wbo_ap_dtl_month_tmp SELECT DISTINCT a.AP_CODE AP_CODE, -- a.SSID, SUM(a.pv) pv, SUM(a.uv) uv, SUM(a.cpc) cpc, SUM(a.cpm) cpm, SUM(a.flow) flow, SUM(a.P_FLOW) P_FLOW, SUM(a.DURATION) DURATION, SUM(a.RPV) RPV, SUM(a.FPV) FPV, a.`day`, a.`MONTH`, a.`YEAR`, CASE WHEN ROUND( SUM(a.fpv) / COUNT(DISTINCT a.AP_CODE) ) > 0 and ROUND( SUM(a.fpv) / COUNT(DISTINCT a.AP_CODE) ) < pvAverage THEN 2 WHEN ROUND( SUM(a.fpv) / COUNT(DISTINCT a.AP_CODE) ) >= pvAverage THEN 1 ELSE 3 END apState FROM wbo_ap_dtl_day a WHERE a. YEAR = year(curdate())#本年 AND a. MONTH = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y%m') #上月 AND a. DAY >= date_format(date_sub(date_sub(date_format(now(),'%y%m%d'),interval extract( day from now())-1 day),interval 1 month),'%Y%m%d') #上月第一天 AND a. DAY <= date_format(date_sub(date_sub(date_format(now(),'%y%m%d'),interval extract( day from now()) day),interval 0 month),'%Y%m%d') #上月最后一天 GROUP BY a.AP_CODE, a.`YEAR`, a.`MONTH`; end;
- 调用
call pro_wbo_ap_dtl_month_tmp_('4'); call pro_wbo_ap_dtl_month_tmp_(getpvAverage());
#事件 定时执行 DROP EVENT IF EXISTS eve_ap_month_dtl_day_; CREATE EVENT `eve_ap_month_dtl_day_` ON SCHEDULE EVERY 1 month STARTS '2016-10-01 00:30:00'#每月1号执行 ON COMPLETION NOT PRESERVE ENABLE DO CALL `pro_ap_month_dtl_day_(getpvAverage(),fn_last_month())`
#函数 根据月份获得ap月分表名称 DROP FUNCTION IF EXISTS getApMonthTabName; CREATE FUNCTION `getApMonthTabName`(_month varchar(32)) RETURNS varchar(32) READS SQL DATA BEGIN DECLARE cnt varchar(32) DEFAULT ''; IF _month = '1' THEN SET cnt = 'wbo_ap_dtl_day_m1'; ELSEIF _month = '2' THEN SET cnt = 'wbo_ap_dtl_day_m2'; ELSEIF _month = '3' THEN SET cnt = 'wbo_ap_dtl_day_m3'; ELSEIF _month = '4' THEN SET cnt = 'wbo_ap_dtl_day_m4'; ELSEIF _month = '5' THEN SET cnt = 'wbo_ap_dtl_day_m5'; ELSEIF _month = '6' THEN SET cnt = 'wbo_ap_dtl_day_m6'; ELSEIF _month = '7' THEN SET cnt = 'wbo_ap_dtl_day_m7'; ELSEIF _month = '8' THEN SET cnt = 'wbo_ap_dtl_day_m8'; ELSEIF _month = '9' THEN SET cnt = 'wbo_ap_dtl_day_m9'; ELSEIF _month = '10' THEN SET cnt = 'wbo_ap_dtl_day_m10'; ELSEIF _month = '11' THEN SET cnt = 'wbo_ap_dtl_day_m11'; ELSEIF _month = '12' THEN SET cnt = 'wbo_ap_dtl_day_m12'; END IF ; RETURN cnt ; END;
#函数 获得平均数 DROP FUNCTION IF EXISTS getpvAverage; CREATE FUNCTION `getpvAverage`() RETURNS int READS SQL DATA BEGIN DECLARE cnt int DEFAULT 0; select a.pvAverage into cnt from wbo_ap_manage_result a where a.YEAR = year(curdate()) #本年 AND a. MONTH = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y%m'); #上月 RETURN cnt ; END;
#函数 获得上月 DROP FUNCTION IF EXISTS fn_last_month; CREATE FUNCTION `fn_last_month`() RETURNS int READS SQL DATA BEGIN DECLARE cnt int DEFAULT 0; select date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%c') into cnt;#上月2016-09-11 RETURN cnt ; END;
#函数 获得上月 DROP FUNCTION IF EXISTS fn_last_month1; CREATE FUNCTION `fn_last_month1`() RETURNS int -- READS SQL DATA BEGIN DECLARE cnt int DEFAULT 0; select date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y%m')#上月201609 into cnt ; RETURN cnt ; END;
#获得上月 201609 select fn_last_month1();
#函数 上月天数 DROP FUNCTION IF EXISTS fn_last_month_num; CREATE FUNCTION `fn_last_month_num`() RETURNS int -- READS SQL DATA BEGIN DECLARE cnt int DEFAULT 0; select (DATEDIFF(DATE_ADD(curdate(), INTERVAL - DAY(curdate())+ 1 DAY), date_add(curdate()- DAY(curdate())+ 1, INTERVAL -1 MONTH))) ##上月天数 into cnt ; RETURN cnt ; END;
-- 上月天数 select fn_last_month_num();
