MySQL中如何对UNION ALL合并数据集做多维度分组及各维度计数

两种方案均仅扫描1次原表，避免重复访问大表造成性能损耗。

MySQL 8.0+ 最优方案（窗口函数实现） #

核心逻辑：先通过单次表扫描过滤2020年的出行记录，合并出发/目的地数据，再用窗口函数按不同维度聚合计数，无需重复访问原表。

WITH combined_loc AS (
    -- 合并出发地、目的地数据，仅扫描1次trips表
    SELECT o__continent as continent, o__country as country, o__state_name as state, o__city_name as city
    FROM db.trips
    -- 替换函数运算为范围查询，tripdate有索引时可直接命中
    WHERE tripdate >= '2020-01-01' AND tripdate < '2021-01-01'
    UNION ALL
    SELECT d__continent as continent, d__country as country, d__state_name as state, d__city_name as city
    FROM db.trips
    WHERE tripdate >= '2020-01-01' AND tripdate < '2021-01-01'
)
SELECT DISTINCT
    continent,
    country,
    state,
    city,
    -- 按各维度分区统计总出现次数
    COUNT(*) OVER (PARTITION BY continent) AS continent_count,
    COUNT(*) OVER (PARTITION BY continent, country) AS country_count,
    COUNT(*) OVER (PARTITION BY continent, country, state) AS state_count,
    COUNT(*) OVER (PARTITION BY continent, country, state, city) AS city_count
FROM combined_loc;

MySQL 5.x 兼容方案 #

核心逻辑：先把合并后的数据集存为派生表，再分别按各维度聚合后关联得到结果，全程仅访问1次原表。

SELECT 
    combined_loc.continent,
    combined_loc.country,
    combined_loc.state,
    combined_loc.city,
    continent_cnt.cnt AS continent_count,
    country_cnt.cnt AS country_count,
    state_cnt.cnt AS state_count,
    city_cnt.cnt AS city_count
FROM (
    -- 仅扫描1次原表合并数据
    SELECT o__continent as continent, o__country as country, o__state_name as state, o__city_name as city
    FROM db.trips
    WHERE tripdate >= '2020-01-01' AND tripdate < '2021-01-01'
    UNION ALL
    SELECT d__continent as continent, d__country as country, d__state_name as state, d__city_name as city
    FROM db.trips
    WHERE tripdate >= '2020-01-01' AND tripdate < '2021-01-01'
) AS combined_loc
-- 所有聚合均基于上方合并后的派生表，不会重复访问原表
JOIN (SELECT continent, COUNT(*) AS cnt FROM combined_loc GROUP BY continent) AS continent_cnt USING(continent)
JOIN (SELECT continent, country, COUNT(*) AS cnt FROM combined_loc GROUP BY continent, country) AS country_cnt USING(continent, country)
JOIN (SELECT continent, country, state, COUNT(*) AS cnt FROM combined_loc GROUP BY continent, country, state) AS state_cnt USING(continent, country, state)
JOIN (SELECT continent, country, state, city, COUNT(*) AS cnt FROM combined_loc GROUP BY continent, country, state, city) AS city_cnt USING(continent, country, state, city)
GROUP BY combined_loc.continent, combined_loc.country, combined_loc.state, combined_loc.city;

• 优先使用范围查询替代YEAR()函数运算，当tripdate字段建有索引时可以直接命中索引，避免全表扫描
• 可针对查询场景创建联合索引：idx_tripdate_loc (tripdate, o_continent, o_country, o_state, o_city, d_continent, d_country, d_state, d_city)，查询时直接走索引覆盖，无需回表读取数据
• 若该统计为高频查询，可创建定时任务预生成统计结果存入中间表，查询时直接读取中间表即可，无需每次实时计算

内容的提问来源于stack exchange，提问作者GoOutside