use_id reg_time1 2015-12-01 2 2015-12-02 3 2015-12-02 4 2015-12-03 5 2015-12-03 6 2015-12-03 7 2015-11-30
要得到
`
reg_time count count(2周內)2015-12-01 0 02015-11-29 0 02015-11-30 1 12015-11-31 0 12015-12-01 1 22015-12-02 2 42015-12-03 3 7
`
目的就是取出當天註冊 以及當天往前推14天的註冊數量
現在的做法是迴圈count
`
$lasttime=strtotime(date("Y-m-d",time()))-14*3600*24;$firsttime=strtotime(date("Y-m-d",time()))+3600*24;$result = array()for($i=13;$i>=0;$i--){ $first=date("Y-m-d",$firsttime-$i*3600*24); $last=date("Y-m-d",$lasttime-$i*3600*24); $today=date("Y-m-d",time()-$i*24*3600); $tmp1 = "select count(1) from table where reg_time > $last and reg_time < $last"; $tmp2 = "select count(1) from table where reg_time = $today " $result[][date] =$today; $result[][count1] =$tmp1; $result[][count2] =$tmp2; }
`
這樣做的結果就是一共執行了28次count 感覺效率相當低下 求最佳化思路
回複內容:
use_id reg_time1 2015-12-01 2 2015-12-02 3 2015-12-02 4 2015-12-03 5 2015-12-03 6 2015-12-03 7 2015-11-30
要得到
`
reg_time count count(2周內)2015-12-01 0 02015-11-29 0 02015-11-30 1 12015-11-31 0 12015-12-01 1 22015-12-02 2 42015-12-03 3 7
`
目的就是取出當天註冊 以及當天往前推14天的註冊數量
現在的做法是迴圈count
`
$lasttime=strtotime(date("Y-m-d",time()))-14*3600*24;$firsttime=strtotime(date("Y-m-d",time()))+3600*24;$result = array()for($i=13;$i>=0;$i--){ $first=date("Y-m-d",$firsttime-$i*3600*24); $last=date("Y-m-d",$lasttime-$i*3600*24); $today=date("Y-m-d",time()-$i*24*3600); $tmp1 = "select count(1) from table where reg_time > $last and reg_time < $last"; $tmp2 = "select count(1) from table where reg_time = $today " $result[][date] =$today; $result[][count1] =$tmp1; $result[][count2] =$tmp2; }
`
這樣做的結果就是一共執行了28次count 感覺效率相當低下 求最佳化思路
select reg_time,count(1) from table where reg_time > $last and reg_time < $last group by reg_time
用sql分組查詢
針對你的問題,建議你直接擷取每天的更新資料,在PHP自己加一下。這樣SQL能減少到只剩一個。