SQL语句统计9/5计费值

公司IDC出口流量是按照1分钟的时候间隔取值的。第三方运营商(CDN、IDC)一般都是按照5分钟的时间间隔统计流量的,计费方式是每月按照95计费或者峰值计费,不同的运营商计费方式不一样。

Zabbix监控流量定期通过API获取数据存入本地MySQL数据库当中,方便后期数据统计和分析。下面通过SQL语句根据第三方标准(5min取值)统计出口流量的95计费或者峰值计费。

1)由于公司每条链路都是两根线接入,每根线都有流量,如果一根线路出现问题,流量会全部切到另外一条线路上面。先用SQL语句统计两根线路流量的总和,按照每分钟统计。

mysql> select RunDate,RunTime,round((bjtel_line1_out + bjtel_line2_out)/1000/1000,2) as Totalsum from t_netmx where RunDate>='2018-12-01' and RunDate<'2019-01-01' group by 1,2 limit 30;
+------------+---------+--------+
| RunDate    | RunTime | Totalsum |
+------------+---------+--------+
| 2018-12-01 | 00:00   | 133.14 |
| 2018-12-01 | 00:01   | 190.65 |
| 2018-12-01 | 00:02   | 152.80 |
| 2018-12-01 | 00:03   | 137.99 |
| 2018-12-01 | 00:04   | 162.81 |
| 2018-12-01 | 00:05   | 129.08 |
| 2018-12-01 | 00:06   | 152.86 |
| 2018-12-01 | 00:07   | 143.34 |
| 2018-12-01 | 00:08   | 139.36 |
| 2018-12-01 | 00:09   | 133.31 |
| 2018-12-01 | 00:10   | 121.75 |
| 2018-12-01 | 00:11   | 147.79 |
| 2018-12-01 | 00:12   | 129.35 |
| 2018-12-01 | 00:13   | 137.92 |
| 2018-12-01 | 00:14   | 117.28 |
| 2018-12-01 | 00:15   | 127.25 |
| 2018-12-01 | 00:16   | 266.33 |
| 2018-12-01 | 00:17   | 214.06 |
| 2018-12-01 | 00:18   | 241.38 |
| 2018-12-01 | 00:19   | 220.78 |
| 2018-12-01 | 00:20   | 168.99 |
| 2018-12-01 | 00:21   | 146.52 |
| 2018-12-01 | 00:22   | 108.88 |
| 2018-12-01 | 00:23   | 100.35 |
| 2018-12-01 | 00:24   | 112.49 |
| 2018-12-01 | 00:25   | 121.06 |
| 2018-12-01 | 00:26   | 111.48 |
| 2018-12-01 | 00:27   | 111.46 |
| 2018-12-01 | 00:28   | 111.30 |
| 2018-12-01 | 00:29   | 115.38 |
+------------+---------+--------+
30 rows in set (0.00 sec)

2)根据第三方的标准取值,每5min取一个平均值,然后打印输出,这里非常感谢我们公司两位非常帅气的小哥哥帮我写这条SQL语句。

                                                                                                                                                                            
mysql> select FROM_UNIXTIME(unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) - (unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) % (5 * 60))) as DateTime,round(avg(bjtel_line1_out + bjtel_line2_out)/1000/1000,2) as avg5min from t_netmx where RunDate>='2018-12-01' and RunDate<'2019-01-01' group by DateTime limit 6;
+---------------------+---------+
| DateTime            | avg5min |
+---------------------+---------+
| 2018-12-01 00:00:00 |  155.48 |
| 2018-12-01 00:05:00 |  139.59 |
| 2018-12-01 00:10:00 |  130.82 |
| 2018-12-01 00:15:00 |  213.96 |
| 2018-12-01 00:20:00 |  127.45 |
| 2018-12-01 00:25:00 |  114.14 |
+---------------------+---------+
6 rows in set (0.32 sec)

3)把avg5min的值按照从大到小排序,通过limit取出流量的峰值或者9/5值,2018年12月份流量的峰值为1215.04M,9/5值为646.85M。

mysql> select FROM_UNIXTIME(unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) - (unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) % (5 * 60))) as DateTime,round(avg(bjtel_line1_out + bjtel_line2_out)/1000/1000,2) as avg5min from t_netmx where RunDate>='2018-12-01' and RunDate<'2019-01-01' group by DateTime order by avg5min desc limit 1,1;
+---------------------+---------+
| DateTime            | avg5min |
+---------------------+---------+
| 2018-12-25 11:00:00 | 1215.04 |
+---------------------+---------+
1 row in set (0.37 sec)

mysql> select FROM_UNIXTIME(unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) - (unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) % (5 * 60))) as DateTime,round(avg(bjtel_line1_out + bjtel_line2_out)/1000/1000,2) as avg5min from t_netmx where RunDate>='2018-12-01' and RunDate<'2019-01-01' group by DateTime order by avg5min desc limit 446,1;
+---------------------+---------+
| DateTime            | avg5min |
+---------------------+---------+
| 2018-12-17 16:20:00 |  646.85 |
+---------------------+---------+
1 row in set (0.36 sec)

4)通过SHELL脚本自动获取上个月IDC出口流量的峰值和9/5值。

[root@monitor idc]# cat idc_pk_95_values.sh 
#!/bin/sh
#create by sfzhang 20180131

PATH=$PATH:/usr/local/mysql/bin

CurrentMouthDay=`date +%Y-%m-01`
LastMouthStartDay=`date -d "$CurrentMouthDay last month" +%Y-%m-%d`
LastMouthEndDay=`date -d "$CurrentMouthDay last day" +%Y-%m-%d`

LastMouthStartDayToNum=`echo $LastMouthStartDay|sed 's/-//g'`
LastMouthEndDayToNum=`echo $LastMouthEndDay|sed 's/-//g'`


DiffDays=`expr $LastMouthEndDayToNum - $LastMouthStartDayToNum + 1`


P95=$(echo "scale=0;$[$DiffDays*24*12*5]/100" | bc) 


echo $LastMouthStartDayToNum-$LastMouthEndDayToNum "Total":$DiffDays "days" "P95":$P95

function getIDCNetflowFromDB
{

BJTELPkMonth=$(mysql -hx.x.x.x -uxxxx -pxxxx -D xxxx -e "select FROM_UNIXTIME(unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) - (unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) % (5 * 60))) as DateTime,round(avg(bjtel_line1_out + bjtel_line2_out)/1000/1000,2) as avg5min from xxxxxx where RunDate>='$LastMouthStartDay' and RunDate<'$CurrentMouthDay' group by DateTime order by avg5min desc limit 1,1"|awk '{print $NF}' |egrep "^[0-9]")

BJTEL95Month=$(mysql -hx.x.x.x -uxxxx -pxxxx -D xxxx -e "select FROM_UNIXTIME(unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) - (unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) % (5 * 60))) as DateTime,round(avg(bjtel_line1_out + bjtel_line2_out)/1000/1000,2) as avg5min from xxxxxx where RunDate>='$LastMouthStartDay' and RunDate<'$CurrentMouthDay' group by DateTime order by avg5min desc limit $P95,1"|awk '{print $NF}' |egrep "^[0-9]")



printf "%s %.2f %s\n" BJTELPkMonth: $BJTELPkMonth Mbps
printf "%s %.2f %s\n" BJTEL95Month: $BJTEL95Month Mbps


}

getIDCNetflowFromDB

5)执行脚本获取上个月IDC出口流量的峰值和9/5值。

[root@monitor idc]# sh idc_pk_95_values.sh 
20181201-20181231 Total:31 days P95:446
BJTELPkMonth: 1215.04 Mbps
BJTEL95Month: 646.85 Mbps
----------------本文结束 感谢阅读----------------
听说,打赏我的人最后都成了大神(* ̄▽ ̄*)

1条评论

朴实的追梦者
朴实的追梦者 says: 回复

下面的SQL语句统计的是上个月IDC出口流量的均值流量。

select avg(avg5min) from (select FROM_UNIXTIME(unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) - (unix_timestamp(str_to_date(concat(rundate,' ',runtime),'%Y-%m-%d %H:%i')) % (5 * 60))) as DateTime,round(avg(wxl_3850_out + wxl_3850_2_out)/1000/1000,2) as avg5min from t_netmx where RunDate>='$LastMouthStartDay' and RunDate<'$CurrentMouthDay' group by DateTime order by avg5min) as temp_table;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

昵称 *