每日统计门店用户数存储过程

 

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