创建视图/表：3万亿行个体相似性表的分档统计需求咨询

哇，3万亿行的表可真是个大家伙！要解决这个指定个体的相似度区间统计需求，绝对不能用视图——视图每次查询都得扫全表，3万亿行的话，估计你等到天荒地老都出不来结果。咱们得用预聚合的物理表，提前把统计结果算好，这样查指定个体的时候秒出结果。下面给你一步步拆解方案：

视图是虚拟表，本质上每次查询都会重新计算，3万亿行的计算量完全不可接受。预聚合表是把统计结果提前落地成物理表，后续查询直接读结果就行，性能天差地别。

假设你的原表叫pair_similarity，字段是id1（个体ID，比如bigint类型）、id2（另一个体ID）、similarity（smallint，0-100）。咱们直接用条件聚合来生成每个个体的各区间统计：

-- 先创建空表（如果一次性聚合压力大，可以先建空表再分批插入）
CREATE TABLE individual_similarity_stats (
    individual_id bigint PRIMARY KEY,
    cnt_le_10 bigint NOT NULL DEFAULT 0,
    cnt_le_20 bigint NOT NULL DEFAULT 0,
    cnt_le_30 bigint NOT NULL DEFAULT 0,
    cnt_le_40 bigint NOT NULL DEFAULT 0,
    cnt_le_50 bigint NOT NULL DEFAULT 0,
    cnt_le_60 bigint NOT NULL DEFAULT 0,
    cnt_le_70 bigint NOT NULL DEFAULT 0,
    cnt_le_80 bigint NOT NULL DEFAULT 0,
    cnt_le_90 bigint NOT NULL DEFAULT 0,
    cnt_le_100 bigint NOT NULL DEFAULT 0
);

-- 一次性聚合（如果原表太大，建议分批执行，看下面的优化点）
INSERT INTO individual_similarity_stats
SELECT
    id1 AS individual_id,
    COUNT(CASE WHEN similarity <= 10 THEN 1 END) AS cnt_le_10,
    COUNT(CASE WHEN similarity <= 20 THEN 1 END) AS cnt_le_20,
    COUNT(CASE WHEN similarity <= 30 THEN 1 END) AS cnt_le_30,
    COUNT(CASE WHEN similarity <= 40 THEN 1 END) AS cnt_le_40,
    COUNT(CASE WHEN similarity <= 50 THEN 1 END) AS cnt_le_50,
    COUNT(CASE WHEN similarity <= 60 THEN 1 END) AS cnt_le_60,
    COUNT(CASE WHEN similarity <= 70 THEN 1 END) AS cnt_le_70,
    COUNT(CASE WHEN similarity <= 80 THEN 1 END) AS cnt_le_80,
    COUNT(CASE WHEN similarity <= 90 THEN 1 END) AS cnt_le_90,
    COUNT(CASE WHEN similarity <= 100 THEN 1 END) AS cnt_le_100
FROM pair_similarity
GROUP BY id1;

给统计表加个主键索引（其实上面建表的时候已经设了PRIMARY KEY，主键默认会建索引），这样查询指定个体的时候直接命中索引，速度拉满：

-- 如果没设主键，单独加索引
CREATE INDEX idx_individual_stats_id ON individual_similarity_stats(individual_id);

3万亿行一次性聚合肯定会撑爆内存或者跑几天几夜，咱们可以按id1的范围分批处理，比如每次处理1000万ID的区间：

-- 示例：处理id1从1到10000000的批次
INSERT INTO individual_similarity_stats
SELECT
    id1 AS individual_id,
    COUNT(CASE WHEN similarity <= 10 THEN 1 END) AS cnt_le_10,
    -- 其他区间的COUNT语句和上面一样
    COUNT(CASE WHEN similarity <= 100 THEN 1 END) AS cnt_le_100
FROM pair_similarity
WHERE id1 BETWEEN 1 AND 10000000
GROUP BY id1;

-- 接着处理下一批：10000001到20000000，以此类推

如果你的原表中(id1,id2)和(id2,id1)都存在（也就是每个配对存了两次），那上面的统计会漏算id2作为主体的情况。这时候可以用UNION ALL把两个方向的配对都纳入统计：

INSERT INTO individual_similarity_stats
WITH all_individual_pairs AS (
    SELECT id1 AS individual_id, similarity FROM pair_similarity
    UNION ALL
    SELECT id2 AS individual_id, similarity FROM pair_similarity
)
SELECT
    individual_id,
    COUNT(CASE WHEN similarity <= 10 THEN 1 END) AS cnt_le_10,
    -- 其他区间的COUNT语句
    COUNT(CASE WHEN similarity <= 100 THEN 1 END) AS cnt_le_100
FROM all_individual_pairs
GROUP BY individual_id;

如果原表会新增/修改/删除数据，那统计表也需要同步更新：

• 增量更新：记录上次处理的最大id1或者时间戳，每次只处理新增的数据，用ON CONFLICT来更新现有个体的统计值：

INSERT INTO individual_similarity_stats
SELECT
    id1 AS individual_id,
    COUNT(CASE WHEN similarity <= 10 THEN 1 END) AS cnt_le_10,
    -- 其他区间的COUNT语句
    COUNT(CASE WHEN similarity <= 100 THEN 1 END) AS cnt_le_100
FROM pair_similarity
WHERE id1 > (SELECT MAX(individual_id) FROM individual_similarity_stats)
GROUP BY id1
ON CONFLICT (individual_id) DO UPDATE
SET
    cnt_le_10 = individual_similarity_stats.cnt_le_10 + EXCLUDED.cnt_le_10,
    cnt_le_20 = individual_similarity_stats.cnt_le_20 + EXCLUDED.cnt_le_20,
    -- 其他字段同理，依次更新
    cnt_le_100 = individual_similarity_stats.cnt_le_100 + EXCLUDED.cnt_le_100;

• 全量刷新：如果数据更新不频繁，可以定期（比如每天/每周）重新生成整个统计表。

要查指定个体的统计结果，直接跑这条SQL就行，秒出结果：

SELECT * FROM individual_similarity_stats WHERE individual_id = '你的指定个体ID';

内容的提问来源于stack exchange，提问作者Wandré Veloso