mysql统计年龄段分布

最新推荐文章于 2022-05-27 21:18:30 发布

CC慕斯

最新推荐文章于 2022-05-27 21:18:30 发布

阅读量2.2k

点赞数

分类专栏： sql 文章标签： mysql 统计年龄段

本文链接：https://blog.csdn.net/nbczw8750/article/details/84813661

版权

sql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文介绍如何使用MySQL根据用户生日字段（时间戳）统计各年龄段的人数分布情况。

摘要由CSDN通过智能技术生成

mysql 根据生日统计年龄分布。birthday字段为时间戳

select '(-∞,20)' value,sum(case when user_age<20 then 1 else 0 end) counts from (
        SELECT FROM_UNIXTIME(birthday, '%Y-%m-%d') as t_birth,CURDATE(), YEAR(CURDATE())-YEAR(FROM_UNIXTIME(birthday, '%Y-%m-%d') ) as user_age from wp_fuser where 1
    ) TA
union
select '[20,30)' value,sum(case when (user_age>=20 and user_age<30) then 1 else 0 end) counts  from  (
        SELECT FROM_UNIXTIME(birthday, '%Y-%m-%d') as t_birth,CURDATE(), YEAR(CURDATE())-YEAR(FROM_UNIXTIME(birthday, '%Y-%m-%d') ) as user_age from wp_fuser where 1
    ) TA
union
select '[30,35)' value,sum(case when (user_age>=30 and user_age<35) then 1 else 0 end) counts from (
        SELECT FROM_UNIXTIME(birthday, '%Y-%m-%d') as t_birth,CURDATE(), YEAR(CURDATE())-YEAR(FROM_UNIXTIME(birthday, '%Y-%m-%d') ) as user_age from wp_fuser where 1
    ) TA
union
select '[35,+∞)' value,sum(case when user_age>=35 then 1 else 0 end) counts from (
        SELECT FROM_UNIXTIME(birthday, '%Y-%m-%d') as t_birth,CURDATE(), YEAR(CURDATE())-YEAR(FROM_UNIXTIME(birthday, '%Y-%m-%d') ) as user_age from wp_fuser where 1
    ) TA

最终结果：