任何人都可以
指导我以更优化的方式查询注册用户的统计信息，我目前缺乏创建更好查询的知识。现在它工作正常，但我想更多地了解做同样事情并改进它的其他方法，这样我就可以扩展我对 MySQL 的了解，并研究其他功能。
SELECT
# Count users with complete profile registered current month and year
( SELECT COUNT(*) FROM users WHERE completed_registration = 1 AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE()) ) AS completed_0,
# Count users with complete profile registered 1 month ago
( SELECT COUNT(*) FROM users WHERE completed_registration = 1 AND MONTH(created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(CURDATE() - INTERVAL 1 MONTH) ) AS completed_1,
# Count users with complete profile registered 2 months ago ( this is repeated 3 more times but for the sake of brevity is not here)
( SELECT COUNT(*) FROM users WHERE completed_registration = 1 AND MONTH(created_at) = MONTH(CURDATE() - INTERVAL 2 MONTH) AND YEAR(created_at) = YEAR(CURDATE() - INTERVAL 2 MONTH) ) AS completed_2,
# Count users with incomplete profile and verified email registered current month and year
( SELECT COUNT(*) FROM users WHERE completed_registration = 0 AND verified_email = 1 AND MONTH(created_at) = MONTH(NOW()) AND YEAR(created_at) = YEAR(NOW()) ) AS incomplete_0,
# Count users with incomplete profile and verified email registered 1 month ago
( SELECT COUNT(*) FROM users WHERE completed_registration = 0 AND verified_email = 1 AND MONTH(created_at) = MONTH(NOW() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(NOW()) ) AS incomplete_1,
# Count users with incomplete profile and verified email registered 2 month ago ( this is repeated 3 more times but for the sake of brevity is not here)
( SELECT COUNT(*) FROM users WHERE completed_registration = 0 AND verified_email = 1 AND MONTH(created_at) = MONTH(NOW() - INTERVAL 2 MONTH) AND YEAR(created_at) = YEAR(NOW()) ) AS incomplete_2,
# Count users with unverified email registered current month and year
( SELECT COUNT(*) FROM users WHERE verified_email = 0 AND MONTH(created_at) = MONTH(NOW()) AND YEAR(created_at) = YEAR(NOW()) ) AS unverified_0,
# Count users with unverified email registered 1 month ago
( SELECT COUNT(*) FROM users WHERE verified_email = 0 AND MONTH(created_at) = MONTH(NOW() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(NOW()) ) AS unverified_1,
# Count users with unverified email registered 2 month ago ( this is repeated 3 more times but for the sake of brevity is not here)
( SELECT COUNT(*) FROM users WHERE verified_email = 0 AND MONTH(created_at) = MONTH(NOW() - INTERVAL 2 MONTH) AND YEAR(created_at) = YEAR(NOW()) ) AS unverified_2,
# Count all registered users
( SELECT COUNT(*) FROM users ) AS total_users,
# Count all complete profiles
( SELECT COUNT(*) FROM users WHERE completed_registration = '1' ) AS total_registered_users,
# Count all incomplete profiles
( SELECT COUNT(*) FROM users WHERE verified_email = 1 AND completed_registration = 0 ) AS total_incomplete_users,
# Count all users with unverified email
( SELECT COUNT(*) FROM users WHERE verified_email = '0' ) AS total_unverified_users
例如，在同一查询中，它计算与用户相关的其他表中的信息，在另一个SELECT子查询中，它连接profile_type表以计算有多少人注册为教师或口译员或笔译员。
你多次打你的桌子，所以难怪它很慢：
SELECT
SUM(CASE WHEN completed_registration = 1 AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE()) THEN 1 ELSE 0 END) AS completed_0,
SUM(CASE WHEN completed_registration = 1 AND MONTH(created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN 1 ELSE 0 END ) AS completed_1,
SUM(CASE WHEN completed_registration = 1 AND MONTH(created_at) = MONTH(CURDATE() - INTERVAL 2 MONTH) AND YEAR(created_at) = YEAR(CURDATE() - INTERVAL 2 MONTH) THEN 1 ELSE 0 END ) AS completed_2,
SUM(CASE WHEN completed_registration = 0 AND verified_email = 1 AND MONTH(created_at) = MONTH(NOW()) AND YEAR(created_at) = YEAR(NOW()) THEN 1 ELSE 0 END) AS incomplete_0,
SUM(CASE WHEN completed_registration = 0 AND verified_email = 1 AND MONTH(created_at) = MONTH(NOW() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(NOW()) THEN 1 ELSE 0 END) AS incomplete_1,
SUM(CASE WHEN completed_registration = 0 AND verified_email = 1 AND MONTH(created_at) = MONTH(NOW() - INTERVAL 2 MONTH) AND YEAR(created_at) = YEAR(NOW()) THEN 1 ELSE 0 END) AS incomplete_2,
SUM(CASE WHEN verified_email = 0 AND MONTH(created_at) = MONTH(NOW()) AND YEAR(created_at) = YEAR(NOW()) THEN 1 ELSE 0 END) AS unverified_0,
SUM(CASE WHEN verified_email = 0 AND MONTH(created_at) = MONTH(NOW() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(NOW()) THEN 1 ELSE 0 END) AS unverified_1,
SUM(CASE WHEN verified_email = 0 AND MONTH(created_at) = MONTH(NOW() - INTERVAL 2 MONTH) AND YEAR(created_at) = YEAR(NOW()) THEN 1 ELSE 0 END) AS unverified_2,
COUNT(*) AS total_users,
SUM(CASE WHEN completed_registration = '1' THEN 1 ELSE 0 END) AS total_registered_users,
SUM(CASE WHEN verified_email = 1 AND completed_registration = 0 THEN 1 ELSE 0 END) AS total_incomplete_users,
SUM(CASE WHEN verified_email = '0' THEN 1 ELSE 0 END ) AS total_unverified_users
FROM users