MySQL按照日期统计报表

Leefs 2021-07-23 PM 2150℃ 0条

# MySQL按照日期统计报表

### 前言

这篇文章主要介绍了mysql按照天统计报表当天没有数据填0的实现方法,需要的朋友可以参考下

### 一、问题复现

按照天数统计每天的总数，如果其中有几天没有数据，那么group by 返回会忽略那几天，如何填充0？如下图，统计的10-3~10-10 7天的数据，其中只有8号和10号有数据，这样返回，数据只有2个，不符合报表统计的需求。期望没有值填0

![6.mysql按照天统计报表当天没有数据填0的实现代码01.png](https://lilinchao.com/usr/uploads/2021/07/3945075582.png)

### 2.按天分组

我们用一组连续的天数作为左表然后left join 要查询的数据 最后group by.：`连续天数表 t1 left join 业务数据 t2 group by t1.day` ,如下：

```sql
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 连续天数表

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

执行结果如下：

![6.mysql按照天统计报表当天没有数据填0的实现代码02.png](https://lilinchao.com/usr/uploads/2021/07/148787826.png)

SQL分析：

1.`@cdate := 是定义名为cdate的变量并赋值（select 后面必须用：=）`

2`.@cdate := DATE_ADD('20171219', INTERVAL + 1 DAY) `按照传入的日期'20171219'，加一天

3.`SELECT @cdate := DATE_ADD('20171219', INTERVAL + 1 DAY) FROM order`找一张表记录肯定大于10条的即可，执行结果如下：

![6.mysql按照天统计报表当天没有数据填0的实现代码03.png](https://lilinchao.com/usr/uploads/2021/07/3789156545.png)

4.`@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY ` 把定义的cdate变量天数-1（自减）

5.LIMIT 7 限制一下条数，大功告成，我们得到了指定日期往前7天的记录

#### 2.2 左关联然后分组

`left join group by t1.day`

即按照左表关联业务数据，根据左表的日期分组，即分成了指定的7天数据，有记录就统计条数，没有记录就是0

最终执行结果：

![6.mysql按照天统计报表当天没有数据填0的实现代码04.png](https://lilinchao.com/usr/uploads/2021/07/2348584970.png)

### 三、按月统计

#### 需求

> 根据当前月份开始时间，统计前六个月的数据

#### 代码

```mysql
SELECT
	t1.`MONTH` as `time`,
	IFNULL(t2.count,0) number
FROM
	(SELECT
	DATE_FORMAT(@cdate := DATE_ADD(@cdate, INTERVAL - 1 MONTH), '%Y-%m') as `MONTH`
	FROM
	(SELECT
	@cdate := DATE_ADD('20210701', INTERVAL + 1 MONTH)
	FROM
	user_code) t0
	LIMIT 6) t1
LEFT JOIN
	(
		select createTime,count(*) as count
	from
		(select
		DATE_FORMAT(create_ime, '%Y-%m') as `createTime`,
		count(*)
		from user
		GROUP BY user_code,`createTime`) a
		GROUP BY `createTime`
		) t2
ON t2.createTime = t1.MONTH
```

**说明：**

**DATE_FORMAT()** ：函数用于以不同的格式显示日期/时间数据。

**DATE_ADD()** ：函数向日期添加指定的时间间隔。

**INTERVAL关键字：可以用于计算时间间隔**

可用的时间单位有：

- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
- SECOND_MICROSECOND
- MINUTE_MICROSECOND
- MINUTE_SECOND
- HOUR_MICROSECOND
- HOUR_SECOND
- HOUR_MINUTE
- DAY_MICROSECOND
- DAY_SECOND
- DAY_MINUTE
- DAY_HOUR
- YEAR_MONTH

*附：参考文章链接地址*

https://www.jb51.net/article/133762.htm

标签: MySQL

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://lilinchao.com/archives/1374.html

上一篇 10.【转载】SparkSQL之Join实现介绍

下一篇四、Stream流List和Map互转

MySQL按照日期统计报表

评论已关闭

栏目分类

标签云

友情链接申请

MySQL按照日期统计报表

评论已关闭

 栏目分类

标签云

友情链接申请

栏目分类

标签云

友情链接申请