扒出多年前的關係型資料庫統計巔峰之作

竇再興發表於2020-11-11
資料庫

需求點（資料為瞎造）需要獲取如下圖的複雜統計：

 總人數生日為空18歲以下18到2930到3940到4950到5960到6970歲以上
研究生1012222010
本科100102002020505
大專200202040-----
高中50--------
未填學歷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如下(自己細品)：

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執行時間會越來越長，建議使用定時執行結果入庫，每一個定時期一條結果，做到非實時，降低多次呼叫對資料庫的壓力。

相關文章