如何高效构建多活动参会者交叉匹配计数矩阵？

针对你需要统计4个票务活动购票者共现情况的需求，我来分享几个高效、可复用的实现方案，避免暴力逐个查询的低效问题：

方案1：构建统一参与视图/临时表，基于自连接统计 #

首先把所有活动的购票数据聚合到一个统一的临时表中，这样后续的统计只需要操作这个预处理后的表，避免重复扫描原始表：

-- 创建临时表，存储每个邮箱对应的参与活动
CREATE TEMPORARY TABLE user_event_participation AS
SELECT email, 'EventA-2018' AS event FROM `event-a-2018-buyers`
UNION ALL
SELECT email, 'EventB-2018' AS event FROM `event-b-2018-buyers`
UNION ALL
SELECT email, 'EventC-2018' AS event FROM `event-c-2018-buyers`
UNION ALL
SELECT email, 'EventD-2018' AS event FROM `event-d-2018-buyers`;

-- 给临时表加复合索引，提升后续连接效率
CREATE INDEX idx_email_event ON user_event_participation(email, event);

然后通过自连接分组，直接生成完整的4×4共现矩阵：

-- 生成共现矩阵：X活动和Y活动的共同参与者数量
SELECT
    u1.event AS `参加活动X`,
    u2.event AS `参加活动Y`,
    COUNT(DISTINCT u1.email) AS `共同参与人数`
FROM user_event_participation u1
JOIN user_event_participation u2 ON u1.email = u2.email
GROUP BY u1.event, u2.event
ORDER BY u1.event, u2.event;

这个方案的优势是一次预处理，多次复用，如果后续需要重新统计或者扩展统计维度，直接基于这个临时表操作即可，比暴力逐个查询节省大量重复扫描原始表的时间。

方案2：动态生成SQL（适配活动数量变化场景） #

如果以后可能新增活动，不想硬编码所有两两组合的查询，可以用MySQL的动态SQL自动生成统计语句，扩展性极强：

SET @sql = '';
-- 生成所有活动的两两组合查询语句
SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT "', x.event_name, '" AS `参加活动X`, "', y.event_name, '" AS `参加活动Y`, COUNT(DISTINCT a.email) AS `共同参与人数` FROM `', x.table_name, '` a JOIN `', y.table_name, '` b ON a.email = b.email'
    ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM (
    -- 这里列出所有活动的名称和对应的表名
    SELECT 'EventA-2018' AS event_name, 'event-a-2018-buyers' AS table_name UNION ALL
    SELECT 'EventB-2018' AS event_name, 'event-b-2018-buyers' AS table_name UNION ALL
    SELECT 'EventC-2018' AS event_name, 'event-c-2018-buyers' AS table_name UNION ALL
    SELECT 'EventD-2018' AS event_name, 'event-d-2018-buyers' AS table_name
) x
CROSS JOIN (
    SELECT 'EventA-2018' AS event_name, 'event-a-2018-buyers' AS table_name UNION ALL
    SELECT 'EventB-2018' AS event_name, 'event-b-2018-buyers' AS table_name UNION ALL
    SELECT 'EventC-2018' AS event_name, 'event-c-2018-buyers' AS table_name UNION ALL
    SELECT 'EventD-2018' AS event_name, 'event-d-2018-buyers' AS table_name
) y;

-- 执行动态生成的SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

以后新增活动时，只需要在两个子查询中各添加一行活动信息即可，无需修改其他代码，非常适合需要频繁调整活动列表的场景。

方案3：位图（Bitmask）优化统计（小活动数量场景） #

因为你只有4个活动，可以给每个活动分配一个唯一的位标识，通过位运算快速统计共现情况，性能非常出色：

-- 生成每个邮箱的参与位图：每个活动对应一个位，比如EventA=1(2^0), EventB=2(2^1), 以此类推
CREATE TEMPORARY TABLE user_event_bitmask AS
SELECT
    email,
    SUM(
        CASE event WHEN 'EventA-2018' THEN 1
                   WHEN 'EventB-2018' THEN 2
                   WHEN 'EventC-2018' THEN 4
                   WHEN 'EventD-2018' THEN 8
        END
    ) AS participation_bitmask
FROM (
    SELECT email, 'EventA-2018' AS event FROM `event-a-2018-buyers`
    UNION ALL
    SELECT email, 'EventB-2018' AS event FROM `event-b-2018-buyers`
    UNION ALL
    SELECT email, 'EventC-2018' AS event FROM `event-c-2018-buyers`
    UNION ALL
    SELECT email, 'EventD-2018' AS event FROM `event-d-2018-buyers`
) t
GROUP BY email;

-- 基于位图生成共现矩阵：判断位图是否同时包含X和Y活动的位
SELECT
    x.event_name AS `参加活动X`,
    y.event_name AS `参加活动Y`,
    COUNT(u.email) AS `共同参与人数`
FROM (
    SELECT 'EventA-2018' AS event_name, 1 AS bit_value UNION ALL
    SELECT 'EventB-2018' AS event_name, 2 AS bit_value UNION ALL
    SELECT 'EventC-2018' AS event_name, 4 AS bit_value UNION ALL
    SELECT 'EventD-2018' AS event_name, 8 AS bit_value
) x
CROSS JOIN (
    SELECT 'EventA-2018' AS event_name, 1 AS bit_value UNION ALL
    SELECT 'EventB-2018' AS event_name, 2 AS bit_value UNION ALL
    SELECT 'EventC-2018' AS event_name, 4 AS bit_value UNION ALL
    SELECT 'EventD-2018' AS event_name, 8 AS bit_value
) y
LEFT JOIN user_event_bitmask u 
    ON (u.participation_bitmask & x.bit_value) > 0 
    AND (u.participation_bitmask & y.bit_value) > 0
GROUP BY x.event_name, y.event_name
ORDER BY x.event_name, y.event_name;

位运算的计算速度极快，而且临时表存储的是唯一邮箱及其位图，数据量远小于原始表，统计效率很高，适合活动数量不多的场景。

额外优化建议 #

• 给每个活动表的email字段添加索引，这样在UNION ALL、JOIN等操作时能大幅提升速度；
• 如果数据量极大，临时表可以使用MEMORY引擎（注意内存容量限制），进一步加快查询；
• 如果需要定期统计，可以将预处理的表改为永久表，通过定时任务定期刷新数据，避免每次统计都重复预处理。

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