How does mysql count orders within seven days and daily orders?

My order table uses the create_time time stamp to indicate the order time. if the order on the current day is good, but what if the order within seven days is used? How can I calculate the number of orders per day in seven days? It's really depressing. I hope you can answer this question. thank you. My order table uses the create_time time stamp to indicate the order time. if the order on the current day is good, but what if the order within seven days is used?

How can I calculate the number of orders per day in seven days? It's really depressing. I hope you can answer this question. thank you.

Reply content:

My order table uses the create_time time stamp to indicate the order time. if the order on the current day is good, but what if the order within seven days is used?

How can I calculate the number of orders per day in seven days? It's really depressing. I hope you can answer this question. thank you.

Orders within seven days:

SELECT * FROM t_order  WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(create_time);

The number of orders per day within seven days. First, use the where condition to find the orders within seven days, and then use group by to group the orders every day:

SELECT DATE(create_time) createtime,COUNT(*) FROM t_order  WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(create_time) GROUP BY createtime;

Loop fetch is definitely not good, at least not the best.

Slightly improved: take seven days at a time, and then split it in the code. the code still splits the array very quickly.

If the data volume is too large to run the array, it is not realistic to use the SQL statement. it can be obtained at one time.

And other experts to demonstrate SQL.

You can also set the condition DATEDIFF (CURDATE (), create_time) <= 7

If you want others to write data, you have to tell others the date field, sales field, and so on.

If you ask, you can only use the where condition ..

You can calculate a date in the program. if strtotime (date ('Y-m-d 00:00:00 ', strtotime ('-7 days ')) get the timestamp at seven days ago, and then do not go to where.

We do not recommend that you use MySQL for statistics. you can place the order in redis every time you generate an order.
The solution can be a set every day. put the daily order number in this set.
Regardless of how the statistics are made, it is very convenient. for example, to count the orders of a day, you can directly obtain the order data in the collection of that day without using MySQL.