MySQL查询优化：按月统计月活动量≥6的用户数量

你需要先筛选出每个月内活动次数≥6次的用户，再对月份进行聚合统计数量。原查询的问题在于仅按用户分组，没有结合月份做二次统计，而且冗余的子查询可以直接简化。

修改后的查询语句 #

SELECT 
    monthly_stats.MONTH,
    COUNT(monthly_stats.user) AS USER_COUNT
FROM (
    -- 先筛选出每个月中活动次数≥6的用户
    SELECT 
        MONTH(12_main.timestamp) AS MONTH,
        12_main.user
    FROM 12_main
    INNER JOIN 12_pack ON 12_main.pack = 12_pack.pack_id 
    INNER JOIN 12_ubication_pack ON 12_pack.pack_id = 12_ubication_pack.pack 
    INNER JOIN 10_working_ubication ON 12_ubication_pack.ubication = 10_working_ubication.id 
    INNER JOIN 12_productive_areas ON 10_working_ubication.working_line = 12_productive_areas.working_line 
    WHERE 12_main.status_cert = '4' 
      AND YEAR(12_main.timestamp) = '2023'
    GROUP BY MONTH(12_main.timestamp), 12_main.user
    HAVING COUNT(*) >= 6
) AS monthly_stats
-- 再按月统计符合条件的用户数
GROUP BY monthly_stats.MONTH;

逻辑说明 #

1. 内层子查询：按月份+用户分组，统计每个用户在对应月份的活动次数，通过HAVING COUNT(*) >=6筛选出满足条件的用户及其所属月份。
2. 外层查询：对筛选后的结果按月份分组，用COUNT(monthly_stats.user)统计每个月符合条件的用户总数，得到你需要的统计结果。

执行后即可得到如下目标结果：

内容的提问来源于stack exchange，提问作者Daniel León