扒出多年前的关系型数据库统计巅峰之作

需求点（数据为瞎造）需要获取如下图的复杂统计：

	总人数(男/女)	生日为空(男/女)	18岁以下(男/女)	18到29(男/女)	30到39(男/女)	40到49(男/女)	50到59(男/女)	60到69(男/女)	70岁以上(男/女)
研究生	10/10	1/1	2/2	2/2	2/2	2/2	0/1	1/0	0/0
本科	100/100	10/10	20/20	0/0	20/20	20/20	5/5	0/0	5/5
大专	200/200	20	20	40	-	-	-	-	-
高中	50/50	-	-	-	-	-	-	-	-
未填学历	30/30	-	-	-	-	-	-	-	-

 表结构如下：

CREATE TABLE `sys_user` (
  `id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '编号',
  `company_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '归属公司',
  `office_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '归属部门',
  `login_name` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '登录名',
  `password` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
  `no` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '工号',
  `name` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '姓名',
  `email` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '邮箱',
  `volk` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '民族',
  `entry_time` datetime DEFAULT NULL COMMENT '入司时间',
  `phone` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '电话',
  `mobile` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '手机',
  `user_type` char(1) COLLATE utf8_bin DEFAULT '3' COMMENT '用户类型',
  `sex` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT '性别',
  `photo` varchar(1000) COLLATE utf8_bin DEFAULT NULL COMMENT '用户头像',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `card` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '身份证号',
  `degree` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '最高学历',
  `address` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '家庭住址',
  `social_status` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT '社保状态',
  `login_ip` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '最后登陆IP',
  `login_date` datetime DEFAULT NULL COMMENT '最后登陆时间',
  `login_flag` varchar(64) COLLATE utf8_bin DEFAULT '0' COMMENT '是否可登录',
  `create_by` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '创建者',
  `create_date` datetime NOT NULL COMMENT '创建时间',
  `update_by` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '更新者',
  `update_date` datetime NOT NULL COMMENT '更新时间',
  `remarks` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '备注信息',
  `leave_time` datetime DEFAULT NULL COMMENT '离职时间',
  `leave_flag` char(1) COLLATE utf8_bin NOT NULL DEFAULT '0' COMMENT '减员标记',
  `del_flag` char(1) COLLATE utf8_bin NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `sys_user_office_id` (`office_id`),
  KEY `sys_user_login_name` (`login_name`),
  KEY `sys_user_company_id` (`company_id`),
  KEY `sys_user_update_date` (`update_date`),
  KEY `sys_user_del_flag` (`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表';

统计sql如下(自己细品)，java中直接使用的sql，数据库执行请换'& gt;'为'>','& lt;'为'<'：

select
    IFNULL((select label from sys_dict where type='user_education_type' AND VALUE = degree), '学历没录入') deg,
    
    SUM(CASE WHEN t.sex=1 THEN 1 ELSE 0 END) AS manAll,  
    SUM(CASE WHEN t.sex=2 THEN 1 ELSE 0 END) AS womanAll,
    
    SUM(CASE WHEN t.sex=1 AND t.birthday is NULL THEN 1 ELSE 0 END) AS manNull,
    SUM(CASE WHEN t.sex=2 AND t.birthday is NULL THEN 1 ELSE 0 END) AS womanNull,
    
    SUM(CASE WHEN t.sex=1 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=18 THEN 1 ELSE 0 END) AS man18,
    SUM(CASE WHEN t.sex=2 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=18 THEN 1 ELSE 0 END) AS woman18,
    
    SUM(CASE WHEN t.sex=1 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=18 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=29 THEN 1 ELSE 0 END) AS man1829,
    SUM(CASE WHEN t.sex=2 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=18 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=29 THEN 1 ELSE 0 END) AS woman1829,
    
    SUM(CASE WHEN t.sex=1 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=30 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=39 THEN 1 ELSE 0 END) AS man3039,
    SUM(CASE WHEN t.sex=2 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=30 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=39 THEN 1 ELSE 0 END) AS woman3039,
    
    SUM(CASE WHEN t.sex=1 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=40 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=49 THEN 1 ELSE 0 END) AS man4049,
    SUM(CASE WHEN t.sex=2 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=40 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=49 THEN 1 ELSE 0 END) AS woman4049,
    
    SUM(CASE WHEN t.sex=1 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=50 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=59 THEN 1 ELSE 0 END) AS man5059,
    SUM(CASE WHEN t.sex=2 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=50 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=59 THEN 1 ELSE 0 END) AS woman5059,
    
    SUM(CASE WHEN t.sex=1 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=60 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=69 THEN 1 ELSE 0 END) AS man6069,
    SUM(CASE WHEN t.sex=2 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=60 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) <=69 THEN 1 ELSE 0 END) AS woman6069,
    
    SUM(CASE WHEN t.sex=1 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=70 THEN 1 ELSE 0 END) AS man70,
    SUM(CASE WHEN t.sex=2 AND ROUND(DATEDIFF(CURDATE(), t.birthday)/365.2422) >=70 THEN 1 ELSE 0 END) AS woman70
from sys_user t
WHERE t.del_flag = 0 and t.leave_flag = 0
GROUP BY deg ORDER BY t.degree DESC;

总结

随着用户表的数量持续增大，直接调用sql执行时间会越来越长，建议使用定时执行结果入库，每一个定时期一条结果，做到非实时，降低多次调用对数据库的压力。