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

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语句将会输出每个星座的用户数。
通过这种方法，我们可以轻松地对各个星座的用户数进行分析，让我们更好地理解用户的心理偏好，并为用户提供更符合他们需求的服务。2024-08-13