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

Answer 2

select `date`,sum(if(sex=1,1,0)) male,sum(if(sex=2,1,0)) female,sum(is_signed) signed,
   sum(if(age<20,1,0)) under20,sum(if(age>=20 and age<=30,1,0)) 20to30, sum(if(age>30,1,0)) 30more 
   from (select date(create_time) `date`,sex,is_signed,
          timestampdiff(year,birth_day,create_time) age 
         from `user`
        ) t
group by date

SQL基本是基于楼上@幽灵写的。
我谈点不一样的，计算年龄的方式可以用timestampdiff(year,date1,date2);另外年龄虽然是一直在变，但需求正常应该是计算签约时那个时候的年龄，这样才能用于按日定时统计和累计统计，所以我上面计算年龄是用create_time去做差。