如何基于另一张表的数据构建WHERE子句条件查询并统计记录数？

需求核心：

• 对观测描述表中的每个id1，提取其所有category的集合和统一的year阈值（每个id1的year值完全相同）
• 在基础群体表中统计满足category属于该集合 且 year > 阈值的记录数量

一、Pandas 实现方式 #

1. 预处理观测描述表，提取每个id1的条件参数 #

因为每个id1对应的year一致，先按id1聚合得到所需条件：

import pandas as pd

# 构造示例数据
obs_df = pd.DataFrame(
    {'category': ['A', 'B', 'C', 'A', 'B'],
     'year': [2016, 2016, 2016, 2017, 2017]},
    index=pd.Index([1,1,1,2,2], name='id1')
)

base_df = pd.DataFrame(
    {'category': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B'],
     'year': [2014, 2016, 2017, 2017, 2014, 2017, 2018, 2017]},
    index=pd.Index(range(8), name='id2')
)

# 聚合得到每个id1的category集合和year阈值
agg_obs = obs_df.groupby('id1').agg(
    category_set=('category', set),
    threshold_year=('year', 'first')  # 因id1的year统一，取任意值均可
).reset_index()

2. 统计符合条件的记录数 #

方法1：逐行遍历计算（直观易读）

def count_matching_records(row):
    # 构建筛选掩码
    mask = (base_df['category'].isin(row['category_set'])) & (base_df['year'] > row['threshold_year'])
    return mask.sum()

# 生成结果
result = agg_obs.apply(count_matching_records, axis=1).rename('count').set_index('id1')
print(result)

输出结果：

count
id1
1        6
2        3

方法2：交叉连接+筛选统计（大数据量更高效）

# 交叉连接关联条件与基础表所有记录
cross_join = base_df.reset_index().merge(agg_obs, how='cross')
# 筛选符合条件的记录
filtered = cross_join[(cross_join['category'].isin(cross_join['category_set'])) & (cross_join['year'] > cross_join['threshold_year'])]
# 按id1统计数量
result = filtered.groupby('id1').size().rename('count')

二、SQL 实现方式 #

假设两张表定义如下：

• observation：包含字段 id1, category, year
• base_population：包含字段 id2, category, year

1. 聚合得到每个id1的条件参数 #

通过CTE（公共表表达式）预处理观测表：

WITH obs_agg AS (
    SELECT 
        id1,
        ARRAY_AGG(DISTINCT category) AS category_list,
        MAX(year) AS threshold_year  -- 因id1的year统一，MAX/MIN/AVG结果一致
    FROM observation
    GROUP BY id1
)

2. 统计符合条件的记录数 #

通过交叉连接关联条件与基础表，筛选后统计：

SELECT 
    o.id1,
    COUNT(b.id2) AS count
FROM obs_agg o
CROSS JOIN base_population b
WHERE b.category = ANY(o.category_list) 
  AND b.year > o.threshold_year
GROUP BY o.id1
ORDER BY o.id1;

执行结果：

id1 | count
-----+-------
   1 |     6
   2 |     3

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