mysql年龄段统计_mysql统计年龄段分布

最新推荐文章于 2023-11-18 12:34:01 发布

陈思er

最新推荐文章于 2023-11-18 12:34:01 发布

阅读量985

点赞数

文章标签： mysql年龄段统计

本文链接：https://blog.csdn.net/weixin_35196909/article/details/113155162

版权

该博客介绍了如何使用MySQL根据用户生日字段（时间戳格式）来统计各年龄段的用户分布。通过UNION ALL结合CASE WHEN语句，将用户分为'(-∞,20)', '[20,30)', '[30,35)'和'[35,+∞)'四个年龄段进行计数，从而得到各个年龄段的用户数量。" 115672706,8543710,LeetCode 242：Python实现字母异位词判断,"['Python', '算法', '字符串处理']

摘要由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

最终结果：