Recently, we want to implement a function that, given the start and end dates, counts the number of new users and new orders per day, as shown in the figure:

Here’s how to start being stupid:

  • Calculate the middle list of dates based on the start and end times
  • Iterate through the list, take the current date and the next date, and query the number of new users and orders in this period

As can be imagined, the efficiency of this query is very low, fortunately, SQL has a KIND of SQL statement just meet this demand, for example, I need to query the order table daily new order number, can write query statement like this:

  • Statistics are made in days
SELECT DATE_FORMAT(created_at "% % m - Y % d"),COUNT(*)FROM order_table WHERE status= 'SUCCESS'GROUP BY DATE_FORMAT(created_at, "%Y-%m-%d");Copy the code
  • Statistics are calculated in hours
SELECT DATE_FORMAT(created_at, "%Y-%m-%d %H"),COUNT(*)FROM order_table WHERE status= 'SUCCESS'GROUP BY DATE_FORMAT(created_AT, "%Y-%m-%d %H");Copy the code

This allows you to get all the data you need in a single query statement, making it much more efficient.