PHP and MySQL implement optimized statistics of daily data

In Internet projects, data analysis of the project is essential. Usually, the trend of total daily data changes within a certain period of time is calculated to adjust marketing strategies. Let’s look at the following cases.

Case

There is usually an order form in the e-commerce platform to record all order information. Now we need to count the number of orders and sales amount per day in a certain month to draw the following statistical chart for data analysis.

The order table data structure is as follows:

order_id	order_sn	total_price	enterdate
25396	A4E610E250C2D378D7EC94179E14617F	2306.00	2017-04-01 17:23:26
#25397	EAD217C0533455EECDDE39659ABCDAE9	17.90	2017-04-01 22:15:18
25398	032E6941DAD44F29651B53C41F6B48A0	163.03	2017-04-02 07:24:36

At this time, how to query the number of orders placed on each day of a certain month and the total amount?

General method

The first and easiest way to think of is to use the php function <a href="http://www.php.cn/wiki/1230.html" target="_blank">cal_days_in_month</a>() Get the number of days in the month, then construct an array of all days in the month, then query the total number of each day in the loop, and construct a new array.

The code is as follows:

$month = '04';
$year = '2017';
$max_day = cal_days_in_month(CAL_GREGORIAN, $month, $year);   //当月最后一天
//构造每天的数组
$days_arr = array();
for($i=1;$i<=$max_day;$i++){
  array_push($days_arr, $i);
}
$return = array();
//查询
foreach ($days_arr as $val){
  $min = $year.&#39;-&#39;.$month.&#39;-&#39;.$val.&#39; 00:00:00&#39;;
  $max = $year.&#39;-&#39;.$month.&#39;-&#39;.$val.&#39; 23:59:59&#39;;
  $sql = "select count(*) as total_num,sum(`total_price`) as amount from `orders` where `enterdate` >= {$min} and `enterdate` <= {$max}";
  $return[] = mysqli_query($sql);
}
return $return;

This SQL is simple, but it requires 30 queries each time, which seriously slows down the response time.

Optimization

How to use a sql to directly query the total quantity of each day?

At this time, you need to use the <a href="http://www.php.cn/wiki/1410.html" target="_blank">date_format</a> function of mysql to first find out all the orders of the current month in the subquery, and use the date_format function to convert the enterdate into days, and then press Daygroup by Group statistics. The code is as follows:

$month = '04';
$year = '2017';
$max_day = cal_days_in_month(CAL_GREGORIAN, $month, $year);   //当月最后一天
$min = $year.'-'.$month.'-01 00:00:00';
$max = $year.'-'.$month.'-'.$max_day.' 23:59:59';
$sql = "select t.enterdate,count(*) as total_num,sum(t.total_price) as amount (select date_format(enterdate,'%e') as enterdate,total_price from orders where enterdate between {$min} and {$max}) t group by t.enterdate order by t.enterdate";
$return = mysqli_query($sql);

In this way, 30 queries are reduced to 1, and the response time will be greatly improved.

Note:

1. Since all the data of the current month needs to be queried, this method is not suitable when the amount of data is too large.

2. In order to avoid data loss caused by no data on that day, after querying, the data should be processed according to needs.

Related recommendations:

Detailed explanation of using Echarts to generate data statistical reports in PHP

Detailed explanation of usage examples of the PHP version of WeChat data statistics interface

php Data Statistics Chart Class Example Code Detailed Explanation_PHP Tutorial

The above is the detailed content of PHP and MySQL implement optimized statistics of daily data. For more information, please follow other related articles on the PHP Chinese website!