使用sql现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

假设有以下表格：

users表格：包含用户信息，其中school表示所在学校，gender表示性别，created_at表示用户创建时间。

posts表格：包含帖子信息，其中user_id表示发帖用户的id，created_at表示帖子创建时间。

首先需要计算每个学校每种性别的用户数：

SELECT school, gender, COUNT(*) AS user_count FROM users GROUP BY school, gender;

然后计算每个用户的活跃天数：

SELECT user_id, COUNT(DISTINCT DATE(created_at)) AS active_days FROM posts WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY user_id;

接着将上面的查询结果与users表格连接起来，得到每个用户所在学校和性别：

SELECT u.school, u.gender, p.active_days FROM users u JOIN ( SELECT user_id, COUNT(DISTINCT DATE(created_at)) AS active_days FROM posts WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY user_id ) p ON u.id = p.user_id;

最后可以在上面的查询结果上计算每个学校每种性别的用户的平均活跃天数和平均发帖数量：

SELECT school, gender, AVG(active_days) AS avg_active_days, AVG(posts_count) AS avg_posts_count FROM ( SELECT u.school, u.gender, p.active_days, COUNT(*) AS posts_count FROM users u JOIN ( SELECT user_id, COUNT(DISTINCT DATE(created_at)) AS active_days FROM posts WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY user_id ) p ON u.id = p.user_id JOIN posts ON u.id = posts.user_id WHERE posts.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY u.school, u.gender, p.active_days ) t GROUP BY school, gender