mysql统计近四周的数据（解决跨年问题）

1 近四周的规则

​​​​​​​

 

2 mysql表和数据

SET FOREIGN_KEY_CHECKS=0;

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

-- Table structure for statistics

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

DROP TABLE IF EXISTS `statistics`;

CREATE TABLE `statistics` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `num_type` int(11) DEFAULT NULL,

  `created_at` datetime(6) DEFAULT NULL,

  `policy_id` bigint(20) DEFAULT NULL,

  `area` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4;

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

-- Records of statistics

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

INSERT INTO `statistics` VALUES ('13', '0', '2021-11-03 14:59:34.909000', '97', null);

INSERT INTO `statistics` VALUES ('14', '1', '2021-11-03 02:19:56.649000', '36', null);

INSERT INTO `statistics` VALUES ('15', '0', '2021-11-03 02:21:40.943000', '17', null);

INSERT INTO `statistics` VALUES ('16', '0', '2021-11-15 00:15:50.282000', '59', null);

INSERT INTO `statistics` VALUES ('17', '2', '2021-11-15 21:46:18.108000', '77', null);

INSERT INTO `statistics` VALUES ('18', '0', '2021-11-15 21:46:49.685000', '78', null);

INSERT INTO `statistics` VALUES ('19', '0', '2021-11-18 01:22:39.669000', '75', null);

INSERT INTO `statistics` VALUES ('20', '0', '2021-11-19 02:52:18.768000', '131', null);

INSERT INTO `statistics` VALUES ('21', '1', '2021-11-24 14:35:03.050000', '174', null);

INSERT INTO `statistics` VALUES ('22', '2', '2021-11-30 12:28:17.099000', '205', null);

INSERT INTO `statistics` VALUES ('23', '0', '2021-11-30 12:28:23.622000', '206', null);

INSERT INTO `statistics` VALUES ('24', '0', '2021-11-30 21:19:09.591000', '173', null);

INSERT INTO `statistics` VALUES ('25', '0', '2021-12-17 12:00:29.000000', '1', null);

INSERT INTO `statistics` VALUES ('26', '0', '2021-12-29 12:00:35.929000', '199', null);

INSERT INTO `statistics` VALUES ('27', '0', '2021-12-29 12:00:46.320000', '220', null);

INSERT INTO `statistics` VALUES ('28', '3', '2021-12-29 14:16:48.587000', '220', '1');

INSERT INTO `statistics` VALUES ('29', '3', '2022-01-07 14:17:29.000000', '220', '2');

3 早先的我的统计方式（出现了跨年问题）：

 SELECT

(

SELECT

count(id)

FROM

statistics

WHERE

YEARWEEK(

date_format(created_at, '%Y-%m-%d')

) = YEARWEEK(now())

) AS one,

(

SELECT

count(id)

FROM

statistics

WHERE

YEARWEEK(

date_format(created_at, '%Y-%m-%d')

) = YEARWEEK(now()) - 1

) AS two,

(

SELECT

count(id)

FROM

statistics

WHERE

YEARWEEK(

date_format(created_at, '%Y-%m-%d')

) = YEARWEEK(now()) - 2

) AS three,

(

SELECT

count(id)

FROM

statistics

WHERE

YEARWEEK(

date_format(created_at, '%Y-%m-%d')

) = YEARWEEK(now()) - 3

) AS four;

对这一问题详细描述如转载的博客所示 https://blog.youkuaiyun.com/u010004317/article/details/54426836

4 以下mysql可以解决这一问题

SELECT

(

SELECT

IFNULL(sum(num_type), 0)

FROM

statistics

WHERE

YEARWEEK(

date_format(created_at, '%Y-%m-%d'),

1

) = YEARWEEK(now(), 1)

) AS four,

(

SELECT

IFNULL(sum(num_type), 0)

FROM

statistics

WHERE

YEARWEEK(

date_format(created_at, '%Y-%m-%d'),

1

) = YEARWEEK(

DATE_SUB(NOW(), INTERVAL 7 DAY),

1

)

) AS three,

(

SELECT

IFNULL(sum(num_type), 0)

FROM

statistics

WHERE

YEARWEEK(

date_format(created_at, '%Y-%m-%d'),

1

) = YEARWEEK(

DATE_SUB(NOW(), INTERVAL 14 DAY),

1

)

) AS two,

(

SELECT

IFNULL(sum(num_type), 0)

FROM

statistics

WHERE

YEARWEEK(

date_format(created_at, '%Y-%m-%d'),

1

) = YEARWEEK(

DATE_SUB(NOW(), INTERVAL 21 DAY),

1

)

) AS one;

5 其中

5.1IFNULL () 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

5.2DATE_SUB() 函数是从日期减去指定的时间间隔。

5.3对YEARWEEK()函数的详细解释如这篇博客：https://vimsky.com/examples/usage/yearweek-function-in-mysql.html#