mysql-按年月日统计数据并填充数据

╰+哭是因爲堅強的太久メ 2022-05-13 04:30 314阅读 0赞

# MySQL之按年、月、日统计数据并进行数据填充 #

--------------------

## 一、准备工作：建表 ##

1.  建表sql：

CREATE TABLE num ( i INT ( 11 ) NULL DEFAULT NULL COMMENT '序号' ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '“存储数字工具表”' ROW_FORMAT = Dynamic;

1.  设置数据：

INSERT INTO num (i) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

--------------------

## 二、方法阐述 ##

使用mysql进行按日期分组统计数据，并进行填充数据比较简单，在这里对该方法进行一个先后顺序的讲解：

### 1. 首先，根据笛卡尔乘积（也就是CROSS JOIN），获取0-99的数据列表。 ###

*  SQL语句：

SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10

*  执行效果：查询出来的就是0至99的数据，如下图所示  
    ![PrSei9S.png][]
 *  如果对数据的需求量大，可使用0-999的数据列表，SQL语句为：`SELECT n1.i + n10.i * 10 + n100.i * 100 AS id FROM num n1 CROSS JOIN num AS n10 CROSS JOIN num AS n100`

### 2. 获取时间段（这里举例获取9月份的所有日期） ###

> 这里使用ADDDATE(expr,days)日期函数，其中expr为指定日期，days为数字，正数为增加，负数为减少。

*  获取九月份的所有日期SQL：

SELECT adddate( '2018-09-01', numlist.id ) AS 'date' FROM ( SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10 ) AS numlist WHERE adddate( '2018-09-01', numlist.id ) <= date_add( '2018-09-01', INTERVAL 1 MONTH )

*  执行效果：

![iG43kX5.png][]

*  **注意**：因为sql函数查询的是从2018年9月1日起，到一个月后的数据，所以会多计算一天，可在代码中对结果进行处理。

### 3. 原sql统计结果 ###

*  我们进行数据统计时，会面临某一天没有数据的情况。这里为了方便，我们使用 ‘**result\_jay**‘这个单词作为原SQL语句的标识
 *  原SQL-‘result\_jay’的统计结果，如下图：  
    ![aAhb2wy.png][]
 *  **可以观察到，在我们原来的SQL数据统计结果： 1.统计结果不精确（例子是只需统计18年9月份数据）；2.九月份很多天是没有数据的**

### 4. 完整SQL ###

*  此处为完整SQL，为上述步骤结合。先获取查询的时间段范围，然后左关联我们的统计结果–》大体就是:SELECT `[结果列]` FROM `[时间段范围]` LEFT JOIN `[原SQL统计结果]` ON `[时间段范围.日期]` = `[原SQL统计结果.日期]` ORDER BY `[时间段范围.日期]`
 *  SQL语句：

SELECT temp.date AS 'name', COALESCE ( u.num, 0 ) 'y' FROM ( SELECT adddate( '2018-09-01', numlist.id ) AS 'date' FROM ( SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10 ) AS numlist WHERE adddate( '2018-09-01', numlist.id ) <= date_add( '2018-09-01', INTERVAL 1 MONTH ) ) temp LEFT JOIN ( result_jay ) u ON temp.date = u.date ORDER BY temp.date

*  执行效果  
    ![J71QXuK.png][]

> COALESCE ( u.num, 0 )函数是用来填充结果，若查询为null,则赋值0

### 5. 按年统计（提供三个参数值，如2018年的数据统计，则参数分别为：2018-01-01，2018-01-01，2018-12-01） ###

同理，有区分的就是时间段范围，即完整SQL中from后的主体部分，这里就不贴完整SQL了。

*  时间段SQL

SELECT adddate( '2018-01-01', INTERVAL numlist.id MONTH ) AS 'date' FROM ( SELECT * FROM ( SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10 ) a WHERE a.id <= 11 ) AS numlist WHERE adddate( '2018-01-01', INTERVAL numlist.id MONTH ) <= '2018-12-01

### 6. 按周统计 ###

*  需求为：选中一个日期，获取该日期所在周的数据统计。即选择2018-09-11 星期二，那么最终的数据统计范围应为：2018-09-10 星期一至2018-09-16 星期日七天的数据
 *  时间段SQL：

SELECT adddate( ( SELECT subdate( '2018-09-11', date_format( '2018-09-11', '%w' ) - 1 ) ), INTERVAL numlist.id DAY ) AS 'date' FROM ( SELECT * FROM ( SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10 ) a WHERE a.id <= 6 ) AS numlist WHERE adddate( ( SELECT subdate( '2018-09-11', date_format( '2018-09-11', '%w' ) - 1 ) ), INTERVAL numlist.id DAY ) <= ( SELECT subdate( '2018-09-11', date_format( '2018-09-11', '%w' ) - 9 ) )

*  执行结果：![K62BunY.png][]

--------------------

暂时记录到这里，2018年9月10日16:33:42。

[PrSei9S.png]: https://i.imgur.com/PrSei9S.png
[iG43kX5.png]: https://i.imgur.com/iG43kX5.png
[aAhb2wy.png]: https://i.imgur.com/aAhb2wy.png
[J71QXuK.png]: https://i.imgur.com/J71QXuK.png
[K62BunY.png]: https://i.imgur.com/K62BunY.png