每天把A表的数据写入到B表

#!/bin/bash
 source /etc/profile

if [ -z $1 ];
  then
  date=`date -d yesterday +"%Y-%m-%d"`
  else
  date=$1
 fi

 

echo "=================== ${date} start ==================="

 

#系统、服务器、动作都为全部的情况
sqlOne="
 INSERT INTO st_las_chart_ds
 (sdate,system,server,request,access_count,avg_duration,avg_response,system_tot_access_count,system_act_tot_access_count,system_avg_duration,system_avg_response)
 SELECT D1.sdate,'全部' AS system,'全部' server,'全部' AS request,0 AS access_count,0 AS avg_duration,0 AS avg_response,
  D1.tot_access_count AS system_tot_access_count,
  D1.act_tot_access_count AS system_act_tot_access_count,
  round(IFNULL(D1.tot_sum_duration/D1.tot_access_count,0),3) AS system_avg_duration,
         round(IFNULL(D1.tot_sum_response/D1.D1.tot_access_count,0),3) AS system_avg_response
 FROM(
 SELECT sdate,
  sum(sum_duration) tot_sum_duration,
  sum(CASE WHEN request LIKE '%.do' THEN sum_duration
  WHEN request LIKE '/rpc/%' THEN sum_duration
  WHEN request LIKE '/openrpc/%' THEN sum_duration
  WHEN request LIKE '/webservice/%' THEN sum_duration
  WHEN request LIKE '/ws/%' THEN sum_duration
  ELSE 0 END) act_tot_sum_duration,
         sum(sum_response) tot_sum_response,
  sum(access_count) tot_access_count,
  sum(CASE WHEN request LIKE '%.do' THEN access_count
  WHEN request LIKE '/rpc/%' THEN access_count
  WHEN request LIKE '/openrpc/%' THEN access_count
  WHEN request LIKE '/webservice/%' THEN access_count
  WHEN request LIKE '/ws/%' THEN access_count
  ELSE 0 END) act_tot_access_count
 FROM lasmyisam.st_las_request_ds
 WHERE sdate='${date}'
 GROUP BY sdate
 ) D1
 ON DUPLICATE KEY UPDATE
 access_count=values(access_count),
 avg_duration=values(avg_duration),
 avg_response=values(avg_response),
 system_tot_access_count=values(system_tot_access_count),
 system_act_tot_access_count=values(system_act_tot_access_count),
 system_avg_duration=values(system_avg_duration),
 system_avg_response=values(system_avg_response);"

 

mysql --host='test.com' -uroot -p123456789 -e "${sqlOne}" --default-character-set=utf8 testdb

echo "sqlOne: $sqlOne"

echo "===================DONE==================="