这里简单记录下本次业务解决过程中使用mysql的存储过程,从里面可以看到循环、分支控制,视图创建,变量定义及赋值,2个游标定义及使用相关的过程,存储过程定义及调用。
统计的基表:
CREATE TABLE `assets_hist` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增',
`purchase_id` bigint(20) DEFAULT NULL COMMENT '购买资产Id',
`skyroam_id` bigint(20) DEFAULT NULL COMMENT '用户名称',
`group_id` bigint(20) DEFAULT NULL COMMENT '客户集团',
`group_name` varchar(50) DEFAULT NULL COMMENT '集团名称',
`prod_id` bigint(20) DEFAULT NULL COMMENT '产品Id',
`prod_name` varchar(50) DEFAULT NULL COMMENT '产品名称',
`priority` int(11) DEFAULT NULL COMMENT '资产使用优先级',
`sku_profile` varchar(512) DEFAULT NULL COMMENT '销售品(SKU)',
`subs_id` bigint(20) DEFAULT NULL COMMENT '订单Id',
`subs_time` datetime DEFAULT NULL COMMENT '购买时间',
`subs_exptime` datetime DEFAULT NULL COMMENT '订单失效时间',
`type` int(11) DEFAULT NULL COMMENT '类型 0:购买1:激活2:退订',
`subs_count` int(11) DEFAULT NULL COMMENT '购买个数',
`effective_count` int(11) DEFAULT NULL COMMENT '有效个数',
`affect_count` int(11) DEFAULT NULL COMMENT '本次影响数量(激活标识为-1,退订标识为-N,购买标识为+N)',
`proc_code` varchar(20) DEFAULT NULL COMMENT '接口命令码',
`cons_order_id` bigint(20) DEFAULT NULL COMMENT '确认使用Id',
`cons_sn` varchar(32) DEFAULT NULL COMMENT '终端的sn',
`cons_mcc` int(11) DEFAULT NULL COMMENT '确认使用发生国家',
`effect_time` datetime DEFAULT NULL COMMENT '生效时间',
`expire_time` datetime DEFAULT NULL COMMENT '失效时间',
`data_usage` bigint(20) DEFAULT NULL COMMENT '本次确认使用的累积流量 byte',
`data_speed` bigint(20) DEFAULT NULL COMMENT '限速信息 Bit/s',
`create_date` datetime DEFAULT NULL COMMENT '创建时间',
`update_date` datetime DEFAULT NULL COMMENT '最后修改时间',
`version` int(11) DEFAULT NULL COMMENT '数据版本号',
`sku_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `skyroam_id` (`skyroam_id`),
KEY `group_id` (`group_id`),
KEY `purchase_id` (`purchase_id`),
KEY `effect_time` (`effect_time`),
KEY `prod_subs_id` (`subs_id`,`prod_id`),
KEY `index_create_date` (`create_date`),
KEY `index_prod_id` (`prod_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7095 DEFAULT CHARSET=utf8;
存储过程:
DELIMITER $$
-- USE `assets`$$
-- SELECT LAST_INSERT_ID() from `assets_hist`
-- 计算统计日期 -1为昨天
CREATE OR REPLACE VIEW v_yesterday AS SELECT CURDATE() + INTERVAL -1 DAY AS tjrq FROM DUAL $$
-- 计算各group的开始和结束日期
CREATE OR REPLACE VIEW v_sync_group AS
SELECT group_id,group_name,IFNULL(time_zone, '-8') time_zone,
DATE_ADD(tjrq,INTERVAL IFNULL(b.time_zone, '-8') HOUR) tjstart,
DATE_ADD( DATE_ADD(tjrq, INTERVAL 1 DAY),INTERVAL IFNULL(b.time_zone, '-8') HOUR) tjend,
tjrq
FROM sync_group b,v_yesterday a $$
-- 插入本次任务基础数据
DELETE FROM sync_user_product_day WHERE DATE=(SELECT tjrq FROM v_yesterday) $$
INSERT INTO sync_user_product_day(skyroam_id,product_id,group_id,DATE)
SELECT a.skyroam_id,a.prod_id,a.group_id,b.tjrq
FROM (SELECT DISTINCT skyroam_id,prod_id,group_id FROM assets_hist) a
INNER JOIN v_sync_group b ON a.group_id=b.group_id $$
-- 任务辅助表
DROP TABLE IF EXISTS day_task_assist1 $$
CREATE TABLE day_task_assist1 AS SELECT id,skyroam_id,group_id,prod_id,subs_id,TYPE,subs_count,effective_count,affect_count,cons_sn,create_date FROM assets_hist WHERE 1=2 $$
DROP TABLE IF EXISTS day_task_assist2 $$
CREATE TABLE day_task_assist2 AS SELECT * FROM `sync_user_product_day` WHERE 1=2 $$
-- 执行过程处理今天有数据的用户
DROP PROCEDURE IF EXISTS `sp_stat_day`$$
CREATE PROCEDURE sp_stat_day()
BEGIN
DECLARE v_skyroam_id,v_skyroam_id_bak BIGINT(20);
DECLARE v_group_id,v_group_id_bak BIGINT(20);
DECLARE v_prod_id,v_prod_id_bak BIGINT(20);
DECLARE v_time_begin,v_time_end DATETIME;
DECLARE v_date DATE;
DECLARE v_null_sn_cnt,v_notnull_sn_count INT DEFAULT 0;
DECLARE v_minid_nullsn INT DEFAULT 0;
DECLARE v_first_notnull_sn VARCHAR(32) DEFAULT NULL;
DECLARE v_subs_count INT DEFAULT 0;
DECLARE v_effective_count INT DEFAULT 0;
DECLARE v_affect_count INT DEFAULT 0;
DECLARE v_sn VARCHAR(32) DEFAULT NULL;
DECLARE is_over INT DEFAULT 0;
DECLARE cur_skyroam_day CURSOR FOR
SELECT skyroam_id,group_id,product_id FROM sync_user_product_day WHERE DATE=(SELECT tjrq FROM v_yesterday);
DECLARE cur_upd_day CURSOR FOR
SELECT skyroam_id,product_id,group_id,purchase,remain_amount,cons_count,sn FROM day_task_assist2 ORDER BY skyroam_id,product_id,group_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_over = 1;
OPEN cur_skyroam_day;
FETCH cur_skyroam_day INTO v_skyroam_id,v_group_id,v_prod_id;
outer1:WHILE is_over=0 DO
-- 保存任务的时间范围
SELECT tjstart,tjend INTO v_time_begin,v_time_end FROM v_sync_group WHERE group_id=v_group_id;
SELECT tjrq INTO v_date FROM v_yesterday;
-- 转移数据
INSERT INTO day_task_assist1 SELECT id,skyroam_id,group_id,prod_id,subs_id,TYPE,subs_count,effective_count,affect_count,cons_sn,create_date FROM assets_hist a
WHERE skyroam_id=v_skyroam_id AND group_id=v_group_id AND prod_id=v_prod_id AND create_date >=v_time_begin AND create_date<v_time_end;
-- 处理sn为空的
SELECT COUNT(1) INTO v_null_sn_cnt FROM day_task_assist1 WHERE cons_sn IS NULL;
SELECT COUNT(1) INTO v_notnull_sn_count FROM day_task_assist1 WHERE cons_sn IS NOT NULL;
-- 用户今天没数据则取前天数据
IF v_null_sn_cnt+v_notnull_sn_count=0 THEN
SELECT remain_amount,sn INTO v_effective_count,v_sn FROM sync_user_product_day WHERE skyroam_id=v_skyroam_id AND product_id=v_prod_id AND group_id=v_group_id AND DATE=(v_date + INTERVAL -1 DAY);
UPDATE sync_user_product_day SET purchase=0,remain_amount=v_effective_count,cons_count=0,sn=v_sn WHERE skyroam_id=v_skyroam_id AND product_id=v_prod_id AND group_id=v_group_id AND DATE=v_date;
FETCH cur_skyroam_day INTO v_skyroam_id,v_group_id,v_prod_id;
ITERATE outer1;
END IF;
WHILE v_null_sn_cnt>0 AND v_notnull_sn_count>0 DO
SELECT MIN(id) INTO v_minid_nullsn FROM day_task_assist1 WHERE cons_sn IS NULL;
SELECT cons_sn INTO v_first_notnull_sn FROM day_task_assist1 WHERE cons_sn IS NOT NULL AND id>v_minid_nullsn LIMIT 1;
UPDATE day_task_assist1 SET cons_sn=v_first_notnull_sn WHERE id = v_minid_nullsn;
SELECT COUNT(1) INTO v_null_sn_cnt FROM day_task_assist1 WHERE cons_sn IS NULL;
-- SELECT COUNT(1) INTO v_notnull_sn_count FROM day_task_assist1 WHERE cons_sn IS NOT NULL;
-- set v_null_sn_cnt=0;
END WHILE;
-- 初步统计
INSERT INTO day_task_assist2(skyroam_id,product_id,group_id,sn,purchase,cons_count,remain_amount,DATE)
SELECT m.skyroam_id,m.prod_id,m.group_id,m.sn,m.rechargeAmount,m.usageAmount,n.effective_cout,v_date FROM
(SELECT skyroam_id,prod_id,group_id,cons_sn sn,
SUM((CASE TYPE WHEN 0 THEN affect_count ELSE 0 END)) + SUM((CASE TYPE WHEN 2 THEN affect_count ELSE 0 END)) rechargeAmount,
SUM((CASE TYPE WHEN 1 THEN -1*affect_count ELSE 0 END)) usageAmount
FROM day_task_assist1 a GROUP BY cons_sn,skyroam_id,prod_id,group_id
)m LEFT JOIN
(SELECT skyroam_id,group_id,prod_id,cons_sn sn,effective_cout FROM
(SELECT skyroam_id,group_id,prod_id,SUM(effective_count) effective_cout
FROM assets_hist
WHERE id IN (SELECT MAX(id) FROM assets_hist WHERE prod_id=v_prod_id AND group_id=v_group_id AND skyroam_id=v_skyroam_id GROUP BY subs_id)
)xx
LEFT JOIN (SELECT cons_sn FROM assets_hist WHERE id=(SELECT MAX(id) FROM assets_hist WHERE prod_id=v_prod_id AND group_id=v_group_id AND skyroam_id=v_skyroam_id)) yy ON 1=1
) n ON m.skyroam_id=n.skyroam_id AND m.sn=n.sn;
-- 初始化
TRUNCATE TABLE day_task_assist1;
FETCH cur_skyroam_day INTO v_skyroam_id,v_group_id,v_prod_id;
END WHILE;
CLOSE cur_skyroam_day;
-- 更新
SET is_over=0;
OPEN cur_upd_day;
FETCH cur_upd_day INTO v_skyroam_id,v_group_id,v_prod_id,v_subs_count,v_effective_count,v_affect_count,v_sn;
WHILE is_over=0 DO
IF v_skyroam_id=v_skyroam_id_bak AND v_prod_id=v_prod_id_bak AND v_group_id=v_group_id_bak THEN
INSERT INTO sync_user_product_day(skyroam_id,product_id,group_id,purchase,remain_amount,cons_count,sn,DATE)VALUES(v_skyroam_id,v_prod_id,v_group_id,v_subs_count,v_effective_count,v_affect_count,v_sn,v_date);
ELSE
UPDATE sync_user_product_day SET purchase=v_subs_count,remain_amount=v_effective_count,cons_count=v_affect_count,sn=v_sn WHERE skyroam_id=v_skyroam_id AND product_id=v_prod_id AND group_id=v_group_id AND DATE=v_date;
END IF;
SET v_skyroam_id_bak=v_skyroam_id,v_group_id_bak=v_group_id,v_prod_id_bak=v_prod_id;
FETCH cur_upd_day INTO v_skyroam_id,v_group_id,v_prod_id,v_subs_count,v_effective_count,v_affect_count,v_sn;
END WHILE;
CLOSE cur_upd_day;
END $$
DELIMITER ;
CALL sp_stat_day();