web汇总信息处理

前端通过web来汇总信息要占用 web服务器的资源，故使用脚本来做处理。 每月月初使用 crontab 来做数据库的表的汇总信息 的脚本。备份下留给以后看。

月初的初始化记录shell

#!/bin/shlogfile=/var/www/html/temp/shell.logif [ -f $logfile ];then    touch $logfile    chmod 777 $logfilefi  writelog() {   echo -e `date "+%Y-%m-%d %H:%M:%S"`" $1"|tee -a $logfile} writelog "开始运行脚本$0......."MYSQL=`which mysql`user=rootpwd=456123xyzcur_year=`date +"%Y"`cur_mon=`date +"%m"`day=`date +"%Y-%m-%d"`##去年最后一个月pre_lastmon=`expr "${cur_year}" - 1`"-12"##前一个月if [ `expr ${cur_mon} - 1` -gt 9 ];then   pre_mon="${cur_year}-"`expr ${cur_mon} - 1`else  pre_mon="${cur_year}-0"`expr ${cur_mon} - 1`fi  ##本月第一天 今年第一天mon_start=`date +"%Y-%m"`"-01"year_start=`date +"%Y"`"-01-01"#今年的一月 本月year_first_month=${cur_year}"-01"year_current_month=${cur_year}"-"${cur_mon}echo ${year_current_month}###echo `date -d ${day} +%s` 转换为时间戳再比较 如果当前时间为 今年第一天  或当前时间为当月第一天if [ `date -d ${day} +%s` -et `date -d ${year_start} +%s` ];then     sql="insert into ecs_affiliate_summary (select user_id,user_name,afficalite_level,sum_order,sum_commission,0 as year_order ,0 as year_commission ,0 as mon_order,0 as mon_commission,'${year_first_month}'  as month  from ecs_affiliate_summary where month='${pre_lastmon}')"    writelog "创建年初汇总记录成功 sql :${sql}."elif [ `date -d $day +%s` -eq `date -d $mon_start +%s` ]; then     sql="insert into ecs_affiliate_summary (select user_id,user_name,afficalite_level,sum_order,sum_commission,year_order,year_commission,0 as mon_order,0 as mon_commission,'${year_current_month}' as month from ecs_affiliate_summary where month='${pre_mon}')"    writelog "创建月初汇总记录成功。sql :${sql}."   fi $MYSQL -u${user} -p${pwd} <<EOF  use g5wholesale7;  $sql;  $sql1;EOFwritelog '运行结束!\n'

建表

-------------------信息汇总----前台做汇总信息查询性能肯定不行，建表affiliate_summary 存储 分成汇总信息CREATE TABLE `affiliate_summary`  (`user_id`  int(8) NOT NULL DEFAULT 0 COMMENT '用户id' ,`user_name`  varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,`afficalite_level`  varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联盟会员类型 关联推荐设置中的类型' ,`sum_order`  int(4) NULL DEFAULT NULL COMMENT '总的订单' ,`sum_commission`  float(8,2) NULL DEFAULT 0.00 COMMENT '总的分成' ,`year_order`  tinyint(4) NULL DEFAULT NULL COMMENT '年度订单' ,`year_commission`  float(8,2) NULL DEFAULT NULL COMMENT '年度佣金' ,`mon_order`  tinyint(4) NULL DEFAULT NULL COMMENT '月度订单' ,`mon_commission`  float(8,2) NULL DEFAULT NULL COMMENT '月度佣金' ,`month`  char(7) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '2013-08' ,PRIMARY KEY (`user_id`, `month`),INDEX `user_name` USING BTREE (`sum_order`) )ENGINE=MyISAMDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ciCHECKSUM=0ROW_FORMAT=DYNAMICDELAY_KEY_WRITE=0;

建表 后初始化汇总

---建表 初始化汇总分成记录到  affiliate_summary表中insert into ecs_affiliate_summary  select user_id,user_name,'',sum(sum_order),sum(sum_commission),sum(year_order),sum(year_commission),sum(mon_order),sum(mon_commission) from  ( (select user_id,user_name,     count(order_id) as sum_order,     sum(money) as sum_commission,     0 as year_order ,     0 as year_commission ,      0 as mon_order,     0 as mon_commission,     'sum' as type      from ecs_affiliate_log  group by user_id)    union ALL     (select user_id,user_name,     0 as sum_order,    0 as sum_commission ,     count(order_id) as year_order,     sum(money) as year_commission,     0 as mon_order,     0 as mon_commission ,     'year' as type     from ecs_affiliate_log  where  year( FROM_UNIXTIME(time)) = year(curdate()) group by user_id )    union ALL      (select user_id,user_name,    0 as sum_order,    0 as sum_commission ,    0 as year_order,    0 as year_commission,    count(order_id) as mon_order,    sum(money) as mon_commission,     'mon' as type  from ecs_affiliate_log where  year( FROM_UNIXTIME(time)) = year(curdate()) and month( FROM_UNIXTIME(time)) = month(curdate()) group by user_id)) as sum group bysum.user

（对了 这个可以full join来关联这样要美观点）