本文講解Linux下對數據庫的數據進行統計計算並將結果入庫到另一個表中。本文分3部分進行講解:第一部分介紹:linux下執行php的命令;第二部分介紹:通過腳本實現統計前幾個月的數據並入庫;第三部分介紹:如何實現每日凌晨對昨日的數據進行統計並入庫。
linux下執行php文件的命令為:php filename arguments。
即:php 文件名 參數
Eg:/usr/local/php/bin/php /home/zhaolincheung/insert_talkcountdata.php
例如,將之前60天內的數據統計入庫。
首先,寫一個shell文件talkcount_insert_db.sh,循環計算日期,並將該日期傳給php文件,進行統計並入庫。shell如下:
#!/bin/sh
for((i=1;i<=60;i++));
do
echo -e $i":";
day=`date -d "-"$i" day" "+%Y-%m-%d"`;
/usr/local/php/bin/php /home/zhaolincheung/insert_talkcountdata.php $day;
echo "";
done
而insert_talkcountdata.php是對某一天的數據統計並入庫,如下:
<?php
include_once('/data/analys/DB/MyDB.class.php');
//定義大類的編號
$cidArr = array(0,2,1,11,15,14,7,6,4,12,5,13,16,3,9,1001,1002);
$yesterday = $_SERVER["argv"][1]; //獲取php執行文件的參數
echo $yesterday . "\n";
//遍歷大類並入庫
foreach($cidArr as $cid){
//統計大類
insert_db($cid,$yesterday);
}
//根據指定的大類和日期,計算該天的消息量並入庫
function insert_db($category, $date){
$dateArr = explode("-", $date);
if(checkdate($dateArr[1], $dateArr[2], $dateArr[0]) == false){
return false;
}
//將日期轉為時間戳
$thisdate = mktime(0, 0, 0, $dateArr[1], $dateArr[2], $dateArr[0]);
$nextdate = $thisdate + 3600 * 24;
$sql = "select talkid,count(*) as msgcount from table1 where postcategoryid=$category ";
$sql .= " and updatetime >= $thisdate and updatetime < $nextdate group by talkid order by msgcount desc";
$db = new MyDB();
$res = $db->query($sql);
//初始化關聯數組
$mc["c20"]=$mc["c10"]=$mc["c6"]=$mc["c5"]=$mc["c4"]=$mc["c3"]=$mc["c2"]=$mc["c1"]=0;
//分段統計消息數對應的會話數
while(($result = $db->fetch_assoc($res)))
{
$msgcount = $result["msgcount"];
if($msgcount > 20)
$mc["c20"]++;
else if($msgcount > 10 && $msgcount <= 20)
$mc["c10"]++;
else if($msgcount >= 6 && $msgcount <= 10)
$mc["c6"]++;
else if($msgcount == 5)
$mc["c5"]++;
else if($msgcount == 4)
$mc["c4"]++;
else if($msgcount == 3)
$mc["c3"]++;
else if($msgcount == 2)
$mc["c2"]++;
else if($msgcount == 1)
$mc["c1"]++;
}
//insert table 'talkcount'
$sql_insert = "insert into talkcount(analysisdate,postcategoryid,c20,c10,c6,c5,c4,c3,c2,c1) values('";
$sql_insert .= $date . "',";
$sql_insert .= $category . ",";
$sql_insert .= $mc["c20"] . ",";
$sql_insert .= $mc["c10"] . ",";
$sql_insert .= $mc["c6"] . ",";
$sql_insert .= $mc["c5"] . ",";
$sql_insert .= $mc["c4"] . ",";
$sql_insert .= $mc["c3"] . ",";
$sql_insert .= $mc["c2"] . ",";
$sql_insert .= $mc["c1"] . ")";
echo $sql_insert . "\n";
$db->query($sql_insert);
$db->close();
}
?>