大规模双模态Count Distinct：批量生成ID交叉统计表方案咨询

我有如下星型模式结构的数据：

需要生成以id2同时作为行和列的交叉表，每个单元格统计满足对应条件的去重id1数量（count distinct(id1)），规则如下：

• 行是id2的某个值（比如q1），列是id2的某个值（比如q2）时，统计Value=1且id2等于行值或列值的去重id1数量
• 当行和列值相同时，仅统计Value=1且id2等于该行/列值的去重id1数量

目标交叉表结构如下：

目前只能手动筛选计算每个单元格，想知道如何一次性生成所有统计结果。

方案1：SQL 实现（通用关系型数据库） #

通过CTE生成行-列组合，结合条件统计后转置为交叉表：

-- 提取所有唯一id2值
WITH id2_list AS (
    SELECT DISTINCT id2 FROM your_table
),
-- 生成所有行-列的笛卡尔积组合
cross_pairs AS (
    SELECT a.id2 AS row_id2, b.id2 AS col_id2
    FROM id2_list a
    CROSS JOIN id2_list b
),
-- 预处理有效数据：只保留Value=1的去重id1-id2记录
valid_records AS (
    SELECT DISTINCT id1, id2
    FROM your_table
    WHERE Value = 1
),
-- 统计每个行-列组合的去重id1数量
stats AS (
    SELECT
        cp.row_id2,
        cp.col_id2,
        COUNT(DISTINCT vr.id1) AS distinct_count
    FROM cross_pairs cp
    LEFT JOIN valid_records vr
        ON vr.id2 = cp.row_id2 OR vr.id2 = cp.col_id2
    GROUP BY cp.row_id2, cp.col_id2
)
-- 转置为目标交叉表格式
SELECT
    row_id2 AS id1,
    MAX(CASE WHEN col_id2 = 'q1' THEN distinct_count END) AS q1,
    MAX(CASE WHEN col_id2 = 'q2' THEN distinct_count END) AS q2,
    MAX(CASE WHEN col_id2 = 'q3' THEN distinct_count END) AS q3
FROM stats
GROUP BY row_id2
ORDER BY row_id2;

若id2有新增值，只需修改最后转置部分的CASE条件，或用动态SQL实现全自动化适配。

方案2：Python Pandas 实现 #

利用Pandas的筛选和统计逻辑快速生成结果：

import pandas as pd

# 加载原始数据
df = pd.DataFrame([
    ['p1', 'q1', 1],
    ['p2', 'q2', 1],
    ['p3', 'q3', 1],
    ['p1', 'q2', 1],
    ['p2', 'q3', 0],
    ['p3', 'q1', 1],
    ['p1', 'q3', 0],
    ['p2', 'q1', 1],
    ['p3', 'q2', 0],
], columns=['id1', 'id2', 'Value'])

# 过滤有效数据并去重
valid_df = df[df['Value'] == 1].drop_duplicates(subset=['id1', 'id2'])
id2_values = valid_df['id2'].unique()

# 构建结果字典
result = {'id1': id2_values}
for col_id2 in id2_values:
    counts = []
    for row_id2 in id2_values:
        # 统计符合条件的去重id1数量
        filtered = valid_df[valid_df['id2'].isin([row_id2, col_id2])]
        counts.append(filtered['id1'].nunique())
    result[col_id2] = counts

# 转换为DataFrame并输出
result_df = pd.DataFrame(result)
print(result_df)

运行后直接输出目标交叉表，且自动适配id2的新增值。

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