每日统计门店用户数存储过程
BEGIN #每天统计一次门店用户数据 DECLARE branch_id INt(10) DEFAULT 0; -- 门店id DECLARE branch_count INt(10) DEFAULT 0; -- 门店id DECLARE done INT DEFAULT FALSE; -- 默认游标读出有记录 DECLARE cur_branch CURSOR for (SELECT storeid,count(*) count from ims_bj_shopn_member GROUP BY storeid);-- 定义游标并输入结果集 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 绑定控制变量到游标,游标循环结束自动转true SET @insert_value = '';-- 记录插入的记录总行 open cur_branch;-- 打开游标 myLoop:LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到 FETCH cur_branch INTO branch_id,branch_count; -- 将游标当前读取行的数据顺序赋予自定义变量 IF done THEN -- 判断是否继续循环 LEAVE myLoop; -- 结束循环 END IF; -- 将统计数据拼接 SET @insert_value = concat(@insert_value,'("',branch_id,'","',DATE(CURDATE()),'",',branch_count,',"',now(),'"),'); END LOOP myLoop;-- 结束自定义循环体 CLOSE cur_branch;-- 关闭游标 -- 拼接SQL语句并执行 SET @exesql = concat("insert into pt_branch_user_sum(storeid,d_time,total,insert_time) values ",left(@insert_value,CHAR_LENGTH(@insert_value)-1)); -- SELECT @exesql; PREPARE stmt FROM @exesql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END
统计每日订单
BEGIN #每天统计一次门店拼购及活动订单 DECLARE branch_id INt(10) DEFAULT 0; -- 门店id DECLARE yesterday_begin VARCHAR(30) DEFAULT 0; -- 开始时间 DECLARE yesterday_end VARCHAR(30) DEFAULT 0; -- 结束时间 DECLARE pg_order_sum decimal(10,2) DEFAULT 0; -- 拼购金额 DECLARE activity_order_sum decimal(10,2) DEFAULT 0; DECLARE done INT DEFAULT FALSE;#默认游标读出有记录 DECLARE cur_branch CURSOR for (SELECT id from ims_bwk_branch);-- 定义游标并输入结果集 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 绑定控制变量到游标,游标循环结束自动转true SET yesterday_begin=DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'); -- 昨天开始 SET yesterday_end=DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59'); -- 昨天结束 SET @insert_value = '';-- 记录插入的记录总行 -- select current_day; open cur_branch;-- 打开游标 myLoop:LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到 FETCH cur_branch INTO branch_id; -- 将游标当前读取行的数据顺序赋予自定义变量 IF done THEN -- 判断是否继续循环 LEAVE myLoop; -- 结束循环 END IF; -- 统计拼购订单数据 SELECT SUM(tuan_price) INTO pg_order_sum FROM pt_tuan_list where storeid=branch_id and status=2 and success_time between UNIX_TIMESTAMP(yesterday_begin) and UNIX_TIMESTAMP(yesterday_end); IF pg_order_sum THEN set pg_order_sum=pg_order_sum; ELSE set pg_order_sum=0; END IF; -- 统计活动订单数据 SELECT SUM(pay_price) INTO activity_order_sum FROM pt_activity_order where storeid=branch_id and pay_status=1 and channel='missshop' and insert_time between UNIX_TIMESTAMP(yesterday_begin) and UNIX_TIMESTAMP(yesterday_end); IF activity_order_sum THEN set activity_order_sum=activity_order_sum; ELSE set activity_order_sum=0; END IF; -- 将以上的统计数据相加后存入门店销售统计表 SET @insert_value = concat(@insert_value,'("',branch_id,'","',DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d'),'",',pg_order_sum,',"',activity_order_sum,'","',pg_order_sum+activity_order_sum,'","',now(),'"),'); -- INSERT INTO pt_branch_sale_sum (storeid,data_year,data_month,data_day,pingou_total,activity_total,total,insert_time) VALUES (branch_id,YEAR(NOW()),MONTH(NOW()),DAY(NOW()),pg_order_sum,activity_order_sum,pg_order_sum+activity_order_sum,now()); END LOOP myLoop;-- 结束自定义循环体 CLOSE cur_branch;-- 关闭游标 SET @exesql = concat("insert into pt_branch_sale_sum(storeid,d_time,pingou_total,activity_total,total,insert_time) values ",left(@insert_value,CHAR_LENGTH(@insert_value)-1)); -- SELECT @exesql; PREPARE stmt FROM @exesql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END