MySQL分析各个星座用户数统计(mysql个星座用户数)

MySQL分析:各个星座用户数统计

随着互联网的发展,数据分析已成为各个行业中不可或缺的一环。在互联网应用中,对不同性格、不同偏好的用户进行分析是十分重要的。本文将以MySQL为例,介绍如何对各个星座用户数进行统计分析。

我们需要准备好相关的数据。可以通过以下SQL语句创建一个名为“users”的表:

CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

`birthday` date NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

接下来,我们需要向“users”表中添加一些测试数据。例如:

INSERT INTO `users`(`name`, `birthday`) VALUES (‘张三’, ‘1997-03-27’);

INSERT INTO `users`(`name`, `birthday`) VALUES (‘李四’, ‘1998-04-25’);

INSERT INTO `users`(`name`, `birthday`) VALUES (‘王五’, ‘1996-02-15’);

INSERT INTO `users`(`name`, `birthday`) VALUES (‘赵六’, ‘2000-05-13’);

接着,我们需要通过MySQL的DATE_FORMAT()函数将用户的生日转化为对应的星座。假设我们采用的是中国传统星座分类标准,可以使用以下SQL语句进行转化:

SELECT

name,

CASE

WHEN ((MONTH(birthday)=1) AND (DAY(birthday)=22)) THEN ‘摩羯座’

WHEN ((MONTH(birthday)=1) AND (DAY(birthday)>20)) OR ((MONTH(birthday)=2) AND (DAY(birthday)

WHEN ((MONTH(birthday)=2) AND (DAY(birthday)>18)) OR ((MONTH(birthday)=3) AND (DAY(birthday)

WHEN ((MONTH(birthday)=3) AND (DAY(birthday)>20)) OR ((MONTH(birthday)=4) AND (DAY(birthday)

WHEN ((MONTH(birthday)=4) AND (DAY(birthday)>19)) OR ((MONTH(birthday)=5) AND (DAY(birthday)

WHEN ((MONTH(birthday)=5) AND (DAY(birthday)>20)) OR ((MONTH(birthday)=6) AND (DAY(birthday)

WHEN ((MONTH(birthday)=6) AND (DAY(birthday)>21)) OR ((MONTH(birthday)=7) AND (DAY(birthday)

WHEN ((MONTH(birthday)=7) AND (DAY(birthday)>22)) OR ((MONTH(birthday)=8) AND (DAY(birthday)

WHEN ((MONTH(birthday)=8) AND (DAY(birthday)>22)) OR ((MONTH(birthday)=9) AND (DAY(birthday)

WHEN ((MONTH(birthday)=9) AND (DAY(birthday)>22)) OR ((MONTH(birthday)=10) AND (DAY(birthday)

WHEN ((MONTH(birthday)=10) AND (DAY(birthday)>23)) OR ((MONTH(birthday)=11) AND (DAY(birthday)

WHEN ((MONTH(birthday)=11) AND (DAY(birthday)>21)) OR ((MONTH(birthday)=12) AND (DAY(birthday)

END AS constellation

FROM

users;

通过以上SQL语句,我们就可以将各个用户的星座找出来。接下来,我们可以使用GROUP BY和COUNT函数将不同星座的用户数统计出来:

SELECT

constellation,

COUNT(*) AS user_count

FROM

(SELECT

CASE

WHEN ((MONTH(birthday)=1) AND (DAY(birthday)=22)) THEN ‘摩羯座’

WHEN ((MONTH(birthday)=1) AND (DAY(birthday)>20)) OR ((MONTH(birthday)=2) AND (DAY(birthday)

WHEN ((MONTH(birthday)=2) AND (DAY(birthday)>18)) OR ((MONTH(birthday)=3) AND (DAY(birthday)

WHEN ((MONTH(birthday)=3) AND (DAY(birthday)>20)) OR ((MONTH(birthday)=4) AND (DAY(birthday)

WHEN ((MONTH(birthday)=4) AND (DAY(birthday)>19)) OR ((MONTH(birthday)=5) AND (DAY(birthday)

WHEN ((MONTH(birthday)=5) AND (DAY(birthday)>20)) OR ((MONTH(birthday)=6) AND (DAY(birthday)

WHEN ((MONTH(birthday)=6) AND (DAY(birthday)>21)) OR ((MONTH(birthday)=7) AND (DAY(birthday)

WHEN ((MONTH(birthday)=7) AND (DAY(birthday)>22)) OR ((MONTH(birthday)=8) AND (DAY(birthday)

WHEN ((MONTH(birthday)=8) AND (DAY(birthday)>22)) OR ((MONTH(birthday)=9) AND (DAY(birthday)

WHEN ((MONTH(birthday)=9) AND (DAY(birthday)>22)) OR ((MONTH(birthday)=10) AND (DAY(birthday)

WHEN ((MONTH(birthday)=10) AND (DAY(birthday)>23)) OR ((MONTH(birthday)=11) AND (DAY(birthday)

WHEN ((MONTH(birthday)=11) AND (DAY(birthday)>21)) OR ((MONTH(birthday)=12) AND (DAY(birthday)

END AS constellation

FROM

users

) a

GROUP BY

constellation;

以上SQL语句将会输出每个星座的用户数。

通过这种方法,我们可以轻松地对各个星座的用户数进行分析,让我们更好地理解用户的心理偏好,并为用户提供更符合他们需求的服务。

版权声明:本文采用知识共享 署名4.0国际许可协议 [BY-NC-SA] 进行授权
文章名称:《MySQL分析各个星座用户数统计(mysql个星座用户数)》
文章链接:https://zhuji.vsping.com/193983.html
本站资源仅供个人学习交流,请于下载后24小时内删除,不允许用于商业用途,否则法律问题自行承担。