Home  >  Article  >  Backend Development  >  PHP+MySQL timing data statistics optimization

PHP+MySQL timing data statistics optimization

php中世界最好的语言
php中世界最好的语言Original
2018-03-24 11:54:022286browse

This time I will bring you PHP+MySQL timing data statistics optimization. What are the precautions for PHP+MySQL timing data statistics optimization? The following is a practical case, let’s take a look.

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 date_format function of mysql to first find out all the orders of the current month in the subquery, and convert the enterdate into days using the date_format function, and then group 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);

I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other related articles on the PHP Chinese website!

Recommended reading:

Mysqld_multi deployment stand-alone detailed explanation

How to query different databases with one SQL statement

How to use React to develop component libraries


The above is the detailed content of PHP+MySQL timing data statistics optimization. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn