#!/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==================="