SQL多规则逐次叠加条件计数的最高效实现方案咨询

针对170万行人口表、800组规则前缀的计数场景，以下方案性能比现有前缀复用的逐查询方案高1~2个数量级，覆盖SQL、Python、R三类技术栈：

SQL原生：原子规则位图预计算 #

这是纯数据库场景下性能最高的方案，核心思路是把重复扫表的开销降到最低：

• 先给population表加连续无缺口的整型主键pop_id，取值范围1~1700000。
• 提取sql_rules中所有非NA的原子条件（去重后通常只有数百到一千条左右，远低于规则前缀的总数量），一次性扫描population表，给每个原子条件生成对应的匹配位图：用二进制位的0/1标记对应pop_id的行是否匹配该条件，170万行的位图仅占约210KB存储空间，内存开销可以忽略。主流数据库均支持位运算：PostgreSQL用bit varying类型，MySQL用bit_count配合位操作，ClickHouse原生提供Bitmap类型和聚合函数。
• 所有前缀规则的匹配计数，仅需要把前k个原子条件对应的位图做按位与，再统计结果中位为1的数量即可，位运算的执行速度比逐行扫描快3个数量级以上，且全程不需要重复扫描population表。
• 核心实现参考（PostgreSQL）：

-- 预生成所有原子规则的匹配位图
CREATE TEMP TABLE atom_rule_bitmap AS
SELECT rule_id, bit_or(1 << pop_id) AS match_mask
FROM (
  -- 批量展开所有去重后的原子规则，一次性扫表计算匹配ID
  SELECT 1 AS rule_id, pop_id FROM population WHERE hair_color = 'brown'
  UNION ALL
  SELECT 2 AS rule_id, pop_id FROM population WHERE age < 27
  UNION ALL
  SELECT 3 AS rule_id, pop_id FROM population WHERE origin IN ('US', 'UK')
  -- 其余原子规则按相同格式拼接即可
) t
GROUP BY rule_id;

-- 任意规则组合的计数直接通过位运算完成，无需扫表
SELECT bit_count(r1.match_mask & r2.match_mask & r3.match_mask) AS match_cnt
FROM 
  (SELECT match_mask FROM atom_rule_bitmap WHERE rule_id=1) r1,
  (SELECT match_mask FROM atom_rule_bitmap WHERE rule_id=2) r2,
  (SELECT match_mask FROM atom_rule_bitmap WHERE rule_id=3) r3;

• 额外优化：处理规则前缀时，直接复用前序位与结果即可，比如前2个规则的位与结果存下来，计算前3个规则的结果时直接和第3个规则的位图做与，不需要从头开始计算。

Python实现：内存布尔数组复用 #

170万行30字段的数据集压缩后仅占200MB左右内存，普通消费级笔记本即可轻松承载，性能优于绝大多数数据库查询方案：

• 读取population表到Pandas DataFrame后，先做内存压缩：枚举类字段转category类型，整型字段转int32、浮点字段转float32，压缩后内存占用可以降到原始大小的1/5~1/3。
• 提取所有非NA原子规则，用numexpr引擎（编译执行表达式，比Pandas原生快3~5倍）预计算每个规则对应的布尔匹配数组：长度为170万的numpy布尔数组，标记每一行是否匹配该规则，单个数组仅占1.7MB内存，1000个规则的数组总占用不到2GB，完全在内存承载范围内。
• 逐行处理sql_rules的规则序列时，维护一个当前匹配状态的布尔数组，每叠加一个规则就和对应原子规则的布尔数组做逐元素与，直接对结果数组求和就是当前前缀的匹配计数。遇到NA规则直接记0，同时把当前匹配数组全置为False，后续规则不需要再计算直接返回0即可。
• 核心实现参考：

import pandas as pd
import numpy as np
import numexpr as ne

# 读取并压缩人口表
pop = pd.read_sql("SELECT * FROM population", db_conn)
for col in pop.select_dtypes(include="object").columns:
    pop[col] = pop[col].astype("category")
for col in pop.select_dtypes(include="int64").columns:
    pop[col] = pop[col].astype("int32")

# 提取所有去重的原子规则
sql_rules = pd.read_sql("SELECT * FROM sql_rules", db_conn)
rule_cols = [c for c in sql_rules.columns if c.startswith("rule_")]
all_atom_rules = set()
for col in rule_cols:
    all_atom_rules.update(sql_rules[col][sql_rules[col] != "NA"].unique())

# 预计算所有原子规则的布尔掩码
rule_mask_map = {}
pop_series_dict = {col: pop[col].to_numpy() for col in pop.columns}
for rule in all_atom_rules:
    rule_mask_map[rule] = ne.evaluate(rule, local_dict=pop_series_dict)

# 逐行计算各前缀计数
result = []
for _, rule_row in sql_rules.iterrows():
    current_mask = np.ones(len(pop), dtype=bool)
    row_cnt = []
    for col in rule_cols:
        r = rule_row[col]
        if r == "NA":
            row_cnt.append(0)
            current_mask[:] = False
            continue
        current_mask &= rule_mask_map[r]
        row_cnt.append(current_mask.sum())
    result.append(row_cnt)

R实现：data.table向量计算 #

R技术栈下直接用data.table实现，逻辑和Python方案一致，原生C级别的向量运算性能比未优化的Pandas更高：

• 用data.table::fread或DBI接口读取population表，自动压缩因子类型字段降低内存占用。
• 预解析所有原子规则，生成对应的逻辑向量存入列表，避免重复解析表达式。
• 逐规则叠加时直接做向量逻辑与，用sum()统计匹配数即可，全程不需要做数据切片或重复扫表。

避坑提示 #

• 不要采用逐行迭代过滤人口表缩小数据集的方案：如果规则前缀没有强包含关系，过滤出的子集无法跨规则组复用，反复切片的开销远高于预计算掩码的方案。
• 不要给人口表建大量单列B树索引：当单条件匹配率超过10%时，索引回表的开销会高于全表扫描，对该场景的性能提升非常有限。
• 不要串行执行拼接后的count查询：即使做了前缀去重，每个查询仍会触发一次表扫描，总耗时远高于一次预计算原子掩码的方案。

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