年龄层统计SQL

-- 处理名单丢失

SELECT 

  u.`user_id` u_userid,

  o.`user_id` o_userid,

  o.* 

FROM

  `tb_gh_orders` o 

  LEFT JOIN `tb_gh_user` u 

    ON o.`user_id` = u.`user_id` 

WHERE o.`course_id` = 128 

  AND o.`order_state` = '1' ;

-- 年龄段统计

SELECT 

  T1.*,

  CONCAT(LEFT (T1.C1 / T2.C2 * 100, 5), '%') P 

FROM

  (SELECT CASE WHEN SUBSTRING(U.ID_NUM, 7, 3)='' THEN '无×××' ELSE 

    CONCAT(SUBSTRING(U.ID_NUM, 9, 1), '0后') END AGE,

    SUBSTRING(U.ID_NUM, 7, 3) YEAR1,

    COUNT(*) C1 

  FROM

    TB_GH_USER U 

  GROUP BY SUBSTRING(U.ID_NUM, 7, 3)) T1,(SELECT COUNT(*) C2 FROM TB_GH_USER U2) T2 

---------------CRM年龄层统计

SELECT T1.*,CONCAT(LEFT (T1.C1 / T2.C2 * 100, 5), '%') P FROM

(SELECT CASE 

WHEN tt.age BETWEEN 1 AND 10 THEN '1-10'

WHEN tt.age BETWEEN 11 AND 20 THEN '11-20'

WHEN tt.age BETWEEN 21 AND 30 THEN '21-30'

WHEN tt.age BETWEEN 31 AND 40 THEN '31-40'

WHEN tt.age BETWEEN 41 AND 50 THEN '41-50'

WHEN tt.age BETWEEN 51 AND 60 THEN '51-60'

WHEN tt.age BETWEEN 61 AND 70 THEN '61-70'

WHEN tt.age BETWEEN 71 AND 80 THEN '71-80'

WHEN tt.age BETWEEN 81 AND 90 THEN '81-90'

END year_range,

COUNT(*) C1

 FROM (SELECT TIMESTAMPDIFF(YEAR, u.`birthDate`, CURDATE()) age,u.birthDate,o.`org_name`

 FROM `tb_crm_businessuser` u,`tb_crm_organ` o WHERE u.`organid`=o.`org_id`  ) tt 

 WHERE tt.age IS NOT NULL 

 GROUP BY year_range ) T1

 ,(SELECT 

    COUNT(*) C2 

  FROM

    tb_crm_businessuser U2 WHERE u2.birthDate IS NOT NULL) T2 

---------------CRM按机构统计

SELECT tt.org_name,

COUNT(*) orgtotal,

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 11 AND 20 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p11-20',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 21 AND 30 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p21-30',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 31 AND 40 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p31-40',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 41 AND 50 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p41-50',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 51 AND 60 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p51-60',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 61 AND 70 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p61-70',

CONCAT(LEFT (SUM(CASE WHEN age BETWEEN 71 AND 80 THEN 1 ELSE 0 END )/COUNT(*)*100, 5), '%') AS 'p71-80',

SUM(CASE WHEN age BETWEEN 11 AND 20 THEN 1 ELSE 0 END ) AS '11-20',

SUM(CASE WHEN age BETWEEN 21 AND 30 THEN 1 ELSE 0 END ) AS '21-30',

SUM(CASE WHEN age BETWEEN 31 AND 40 THEN 1 ELSE 0 END ) AS '31-40',

SUM(CASE WHEN age BETWEEN 41 AND 50 THEN 1 ELSE 0 END ) AS '41-50',

SUM(CASE WHEN age BETWEEN 51 AND 60 THEN 1 ELSE 0 END ) AS '51-60',

SUM(CASE WHEN age BETWEEN 61 AND 70 THEN 1 ELSE 0 END ) AS '61-70',

SUM(CASE WHEN age BETWEEN 71 AND 80 THEN 1 ELSE 0 END ) AS '71-80'

FROM (SELECT TIMESTAMPDIFF(YEAR, u.birthDate, CURDATE()) age,u.birthDate,o.org_name

 FROM tb_crm_businessuser u,tb_crm_organ o WHERE u.organid=o.org_id AND u.`birthDate` IS NOT NULL ) tt 

 GROUP BY tt.org_name