MySQL daily statistics report fills in 0 if there is no data on that day

1. Problem reproduction:

Count the total number of each day by day. If there are days with no data, then the group by return will ignore those days. How to fill them with 0? As shown in the figure below, the data for the seven days from 10-3 to 10-10 are counted, of which only the 8th and 10th have data. In this way, only 2 data are returned, which does not meet the statistical requirements of the report. Expect no value to be filled with 0

2. Change your mindset:

We use a set of consecutive days as the left table and then left join the data to be queried and finally group by: consecutive days table t1 left join business data t2 group by t1.day, as shown below:

SELECT
 t1.`day`,
 COUNT(t2.user_id) payment_num
FROM
 (SELECT
 @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY
 FROM
 (SELECT
  @cdate := DATE_ADD('20171219', INTERVAL + 1 DAY)
 FROM
  order) t0
 LIMIT 7) t1
 LEFT JOIN
 (SELECT
  DATE(a.create_time) DAY,
  a.user_id
 FROM
  pay_payment a
  JOIN dealer_store b
  ON a.order_no = b.order_no
 WHERE DATE(create_time) <= '20171219'
  AND DATE(create_time) > DATE_SUB('20171219', INTERVAL 7 DAY)
  ) t2
 ON t2.day = t1.day
GROUP BY t1.`day`;

2.1 Consecutive Days Table

SELECT
 @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY
 FROM
 (SELECT
  @cdate := DATE_ADD('20171219', INTERVAL + 1 DAY)
 FROM
  order) t0
 LIMIT 7

The execution results are as follows:

SQL analysis:

1. @cdate := 是定義名為cdate的變量并賦值（select 后面必須用：=）

2. .@cdate := DATE_ADD('20171219', INTERVAL + 1 DAY) adds one day to the passed date '20171219'

3. SELECT @cdate := DATE_ADD('20171219', INTERVAL + 1 DAY) FROM `order` and find a table with more than 10 records. The execution results are as follows:

4. .@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY decrements the number of days in the defined cdate variable by 1 (self-decrement)

5. LIMIT 7 Limit the number of records. That’s it. We have obtained the records 7 days before the specified date.

2.2 Left join and then group

left join group by t1.day

That is, according to the business data associated with the left table, the data is grouped according to the date of the left table, that is, it is divided into the specified 7 days of data. If there is a record, the number of entries is counted, and if there is no record, it is 0.

Final execution result:

Summarize

The above is the implementation code that I introduced to you for filling 0 in MySQL according to the daily statistical report if there is no data on that day. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!