sql怎么按日期统计客户信息性别年龄段

Question

现有

用户表 user  
id  sex  birth_day   is_signed  creat_time
1   1    1990-05-02     1       2020-07-27 01:01:49
2   2    2010-05-02     1       2020-07-28 01:01:49
3   1    1980-01-02     1       2020-07-28 01:01:49
4   2    1970-01-02     0       2020-07-29 01:01:49

需要统计这样的格式

 当日统计
 日期        男   女    签约   20岁以下  20~30岁  30岁以上
2020-07-27   1    0      1       0       1        0
2020-07-28   1    1      1       1       0        1
2020-07-29   0    1      0       0       0        1

 累计统计
 日期        男   女    签约   20岁以下  20~30岁  30岁以上
2020-07-27   1    0      1       0       1        0
2020-07-28   2    1      3       1       1        1
2020-07-29   2    1      3       1       1        2

结构和数据 使用的mysql

CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `sex` tinyint(1) DEFAULT NULL COMMENT '1男2女',
  `birth_day` varchar(15) DEFAULT NULL COMMENT '生日',
  `is_signed` tinyint(1) DEFAULT NULL COMMENT '1签约0未签约',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES (1, 1, '1990-05-02', 1, '2020-07-27 01:01:49');
INSERT INTO `user` VALUES (2, 2, '2010-05-02', 1, '2020-07-28 01:01:49');
INSERT INTO `user` VALUES (3, 1, '1980-01-02', 1, '2020-07-28 01:01:49');
INSERT INTO `user` VALUES (4, 2, '1970-01-02', 0, '2020-07-29 01:01:49');

Answer 1

可以实现,但是,这种查询几乎无法优化啊.

SELECT
    DATE_FORMAT( create_time, "%Y-%m-%d" ) AS d,
    sum(
    IF
    ( sex = 1, 1, 0 )) AS male,
    sum(
    IF
    ( sex = 2, 1, 0 )) AS female,
    sum( is_signed ) as signed,
    sum(
    IF
    ( DATEDIFF( NOW(), birth_day )/ 365 < 20, 1, 0 )) AS under20,
    sum(
    IF
    ( DATEDIFF( NOW(), birth_day )/ 365 >= 20 AND DATEDIFF( NOW(), birth_day )/ 365 < 31, 1, 0 )) AS 20to30,
    sum(
    IF
    ( DATEDIFF( NOW(), birth_day )/ 365 >= 31, 1, 0 )) AS 30more 
FROM
    `user` 
GROUP BY
    d;

子查询版

SELECT
    u.d,
    sum(
    IF
    ( u.sex = 1, 1, 0 )) AS male,
    sum(
    IF
    ( u.sex = 2, 1, 0 )) AS female,
    sum( u.is_signed ) AS signed,
    sum(
    IF
    ( u.age < 20, 1, 0 )) AS under20,
    sum(
    IF
    ( u.age >= 20 AND u.age < 31, 1, 0 )) AS 20to30,
    sum(
    IF
    ( u.age >= 31, 1, 0 )) AS 30more 
FROM
    ( SELECT DATE_FORMAT( create_time, "%Y-%m-%d" ) AS d, sex, is_signed, DATEDIFF( NOW(), birth_day )/ 365 AS age FROM USER ) AS u 
GROUP BY
    u.d