SQL中多月份两两共同会员数的高效计算方案问询

针对每月200万条记录、共16个月的场景，放弃逐对循环Join的方案，推荐以下几种通用高效的计算思路：

1. 会员活跃月份预聚合 + 月份对关联统计 #

核心是先压缩单会员的多月份数据，再一次性关联所有合法月份对：

• 预聚合会员活跃月份：将每个会员的所有活跃月份聚合为数组/有序列表，把分散的单月记录压缩为单条会员记录。示例逻辑（通用SQL风格）：

  SELECT MemberID, ARRAY_AGG(Month ORDER BY Month) AS active_months
  FROM member_month
  GROUP BY MemberID;
  

  这一步将原3200万条数据压缩为约250万条（基于80%重叠率的unique会员数），大幅减少后续计算的数据量。
• 生成合法月份对：先提取所有唯一月份，生成满足B>A的所有月份组合（共16*15/2=120对，数据量极小）：

  SELECT m1.Month AS month_a, m2.Month AS month_b
  FROM (SELECT DISTINCT Month FROM member_month) m1
  CROSS JOIN (SELECT DISTINCT Month FROM member_month) m2
  WHERE m2.Month > m1.Month;
  

• 关联统计共同会员：将预聚合表与月份对表关联，统计每个月份对中活跃月份数组同时包含两个月份的会员数：

  SELECT mp.month_a, mp.month_b, COUNT(m.MemberID) AS common_members
  FROM month_pairs mp
  JOIN member_active_months m ON m.active_months @> ARRAY[mp.month_a, mp.month_b]
  GROUP BY mp.month_a, mp.month_b;
  

  此方法仅需两次全表扫描（预聚合+一次关联），替代原方案120次全表Join，性能提升显著。

2. 二进制位掩码映射法 #

利用二进制位标记会员的活跃月份，通过位运算快速判断会员是否同时属于两个月份：

• 月份位映射：给每个月份分配唯一的二进制位位置（如按时间顺序从1开始编号）：

  SELECT Month, ROW_NUMBER() OVER(ORDER BY Month) AS bit_pos
  FROM (SELECT DISTINCT Month FROM member_month) t;
  

• 计算会员掩码：将每个会员的活跃月份转换为二进制掩码（对应位设为1）：

  SELECT MemberID, BIT_OR(1 << (bit_pos - 1)) AS month_mask
  FROM member_month mm
  JOIN month_bit mb ON mm.Month = mb.Month
  GROUP BY MemberID;
  

• 统计共同会员：结合月份对，通过位运算判断掩码是否同时包含两个月份的位，再计数：

  SELECT mp.month_a, mp.month_b, COUNT(m.MemberID) AS common_members
  FROM month_pairs mp
  JOIN month_bit mb_a ON mp.month_a = mb_a.Month
  JOIN month_bit mb_b ON mp.month_b = mb_b.Month
  JOIN member_mask m 
    ON (m.month_mask & (1 << (mb_a.bit_pos - 1))) != 0 
    AND (m.month_mask & (1 << (mb_b.bit_pos - 1))) != 0
  GROUP BY mp.month_a, mp.month_b;
  

  位运算的计算效率远高于数组匹配，且掩码存储占用空间极小（16个月仅需2字节存储）。

3. 离线内存集合交叉计数 #

适合用脚本/程序离线处理的场景：

• 加载每个月份的会员ID到内存集合（如Python的set）；
• 遍历所有B>A的月份对，直接计算两个集合的交集大小；
• 输出所有月份对的交集计数结果。

此方法利用内存集合的高效交集运算，16个月份的集合仅需几十MB内存，计算速度极快，适合一次性批量处理。

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