MySQL教程 2023-05-15

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