天生我才必有用

在各类应用中，数据汇总是经常会使用到的功能，对每天、每周、每月的数据汇总后产出报表。实现的方法当然很多，也并不是非常困难，但如何有效的利用数据库，让其来帮你分类统计这些数据，还是具有一定的技巧性。

1.使用高级语言汇总：

这种方法可能是最方便，也是最简单的方法。通过使用查询数据对满足条件的数据进行查询，接着使用其他高级语言，如:PHP、C#、Java对符合条件的数据进行汇总。

$today = getdate();
if ($date_range == "week") {
    $start_date = mktime(0 , 0 , 0, $today['mon'],
                $today['mday'] - $today['wday'] , $today['year']);
    $end_date	= strtotime("+1 week" , $start_date);
}
else if ($date_range == "month") {
    $start_date = mktime(0 , 0 , 0, $today['mon'], 1 , $today['year']);
    $end_date	= strtotime("+1 month" , $start_date);
}
else if ($date_range == "year") {
    $start_date = mktime(0 , 0 , 0, 1 , 1, $today['year']);
    $end_date	= strtotime("+1 year" , $start_date);
}
$end_date = strtotime("-1 second" , $end_date);

while($date < $end_date) {
    $min_date = $date;
    if ($date_interval == "day")
        $max_date = strtotime("1 day" , $min_date);
    elseif ($date_interval == "week")
        $max_date = strtotime("1 week" , $min_date);
    elseif ($date_interval == "month")
	$max_date = strtotime("1 month" , $min_date);
    elseif ($date_interval == "year")
	$max_date = strtotime("1 year" , $min_date);
    $date = $max_date;
    $max_date = strtotime("-1 sec" , $max_date);
    $sql = sprintf("SELECT id , total FROM orders WHERE  order_date >= '%s'
        AND order_date <= '%s'" , date("Y-m-d H:i:s",$min_date ),
        date("Y-m-d H:i:s" ,$max_date));
    ....
    $orders = $db->query($sql);
    ....
    $total_falcon = 0;
    $total_sales  = 0;
    for ($next = 0 ; $next < count($orders) ; $next ++){
	$total_sales += $order['total'];
        $sum_order  += $total_order;
    }
    $sum_falcon += $total_falcon;
    $sum_sales  += $total_sales;
}

以上所举的例子，不容质疑主要问题就是效率，完全没有使用数据库的sum，count函数。同时带来的问题就是代码过于累赘繁琐。

2.使用SQL语句

$today = getdate();
if ($this->date_range == "week") {
    $this->start_date = mktime(0 , 0 , 0, $today['mon'],
         $today['mday'] - $today['wday'] , $today['year']);
    $this->end_date	  = strtotime("+1 week" , $this->start_date);
}
else if ($this->date_range == "month") {
    $this->start_date = mktime(0 , 0 , 0, $today['mon'],
          1 , $today['year']);
    $this->end_date	  = strtotime("+1 month" , $this->start_date);
}
else if ($this->date_range == "year") {
    $this->start_date = mktime(0 , 0 , 0, 1 , 1, $today['year']);
    $this->end_date	  = strtotime("+1 year" , $this->start_date);
}
$this->end_date = strtotime("-1 second" , $this->end_date);

switch($this->date_interval){
    case "year":
	$this->group = "%Y";
	break;
    case "month":
	$this->group = "%m-%Y";
	break;
    case "week":
	$this->group = "Week %v - %Y";
	break;
    case "day":
    default:
	$this->group = "%m-%d-%Y";
	break;
}

$query  = "SELECT COUNT(o.id) as total_order ,";
if ($interval){
    $query  .= " DATE_FORMAT(o.order_data , '{$this->group}') as report_date , ";
}
$query .= " SUM(o.total) as total_sale, ";
$query .= " SUM(o.quantity) as total_quantity ";
$query .= " FROM orders AS o ";
$query .= sprintf(" WHERE o.order_date >= '%s' AND o.order_date <= '%s' " ,
     date("Y-m-d H:i:s" , $this->start_date) , date("Y-m-d H:i:s" , $this->end_date));
$query .= " GROUP BY report_date";
$query .= " ORDER BY report_date";
$db->query($query);

从以上的例子中可以看出，我们完全避免了使用for循序来对数据进行各种累加，取而代之的是充分的使用了sum或total等SQL函数来对数据进行累加，由于这些函数是数据库内置函数，只需增加一下栏位的索引，产出的结果会比使用方法1快几个数量级，同时也节省了第一种情况的频繁访问数据库的开销。通过使用DATE_FORMAT函数和Group语句，实现了对数据的分类检索，完全可以使用非常简单的几种日期格式化实现原来高级语言无法实现或者实现会非常繁琐的分类检索。

BTW:慎用英文字母的格式化，不要以为这样可以加少日期显示上的一些问题，因为Order会按字母顺序排列，这样一月（January)就会排序在二月(Feburary)后面。