mysql - 试图获得申请年龄范围工作的男/女的百分比

很抱歉，标题让人费解，我想达到的是，通过下表得到一份工作的申请总数：

CREATE TABLE IF NOT EXISTS `applications` (
  `application_id` int(11) NOT NULL AUTO_INCREMENT,
  `application_user` varchar(100) NOT NULL,
  `application_date` datetime NOT NULL,
  `application_job` int(11) NOT NULL,
  `application_status` varchar(10) DEFAULT 'pending',
  `application_enabled` int(2) NOT NULL DEFAULT '1',
  `application_resume` int(11) NOT NULL,
  `application_description` text NOT NULL,
  PRIMARY KEY (`application_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

为了了解他们的年龄，我将加入user_personal_information继续application_user，因为他们是申请工作的用户。我的问题：

SELECT count(*) as total,
       user_gender as gender,
       TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE()) AS age,
       application_date
FROM applications
LEFT JOIN user_personal_information
    ON user_personal_information_user = application_user
WHERE application_job = ?

我的用户表user_gender可以等于male/female和user_birthdate在上面的语句中，我将其转换为一个年龄。
我正在尝试将所有年龄段的应用程序分组，例如：

16 - 21
22 - 30
31 - 45
45 - 64
65+

以及那个年龄的男女比例。要用于需要这样的数据的数据图表：

 "dataProvider": [
 {
   "age": "85+",
   "male": 25, //
   "female": 25
 }, {
   "age": "80-54",
   "male": 25,//percentage
   "female": 25//percentage
}]

因此，从以上情况来看，有25%的男性申请过85岁及以上的职位，而女性申请过的比例为25%。你明白要点了，所以这就是我试图让我的精选语句发挥作用的方式。
它将创建一个这样的图表：

所以为了澄清这一点，我想统计一下申请总数，并根据性别和年龄组计算出申请的百分比。如何使用上面的select语句执行此操作？

最佳答案

几个嵌套的子查询分组可以帮您完成这项工作。注意，计算的百分比是针对性别的：

select a.age, a.gender, a.cnt, 100*a.cnt/b.sm as percentage from
    (
        SELECT user_gender as gender,
               TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE()) AS age,
               count(*) as cnt,
        FROM applications
        LEFT JOIN user_personal_information
        ON user_personal_information_user = application_user
        WHERE application_job = ?
        GROUP BY user_gender, TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE())
    ) a,
    (
        SELECT TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE()) AS age,
               count(*) as sm
        FROM applications
        LEFT JOIN user_personal_information
        ON user_personal_information_user = application_user
        WHERE application_job = ?
        GROUP BY TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE())
    ) b
    where a.age = b.age;

如果您要查找特定于应用程序总数的百分比，则需要如下内容：

select a.age,
       a.gender,
       a.cnt,
100*a.cnt/(
            select count(TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE()))
            from
            applications
            LEFT JOIN user_personal_information
            ON user_personal_information_user = application_user
            WHERE application_job = ?
         ) as percentage
from
    (
        SELECT user_gender as gender,
               TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE()) AS age,
               count(*) as cnt,
        FROM applications
        LEFT JOIN user_personal_information
        ON user_personal_information_user = application_user
        WHERE application_job = ?
        GROUP BY user_gender, TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE())
    ) a;