PAYATHON 2026

标签关联度表如何设计：增加行数还是增加列数？

数据小陈

建议先采用第一种结构：每个标签对、时间范围和年龄段的组合保存一行，以两个标签 ID、时间范围和年龄段组成联合主键。按约 2500 对标签、4 个时间范围和 4 个年龄段计算，一套完整的统计结果约有 40000 行。仅凭这个规模，没有必要优先采用 16 个数值列的宽表。

第三种结构可以减少重复的维度值和行开销，但实际能节省多少空间，需要把表和索引一起测量。不能直接根据行数推断“第一种占用超过四倍空间”。

为什么优先选择每个值一行

三种结构保存的是同一个关系：

(field_a_id, field_b_id, time_range, age_bracket) → relatedness

第一种结构直接表达了这个关系，时间范围和年龄段都是可以用来筛选、分组和连接的数据值。

第二种结构把年龄段放进列名，第三种则把时间范围和年龄段都放进列名。这样可以减少行数，但查询需要依赖列名。例如，要取某个年龄段的数据，就必须选择对应的 relatedness_brA 或 relatedness_raA_brA 列；要把所有年龄段统一排序或分组，还需要先将这些列转换成行。

采用第一种结构，新增年龄段通常只需要增加数据；采用后两种结构，则需要增加列，并调整相关查询和写入逻辑。

可以采用的 PostgreSQL 表结构

下面的示例用 day、week、month、all_time 表示四个时间范围，用 A、B、C、D 表示四个年龄段。这些只是示例编码，实际年龄边界应由业务定义。

CREATE TABLE tag_relatedness (
    field_a_id  integer NOT NULL,
    field_b_id  integer NOT NULL,
    time_range  varchar NOT NULL,
    age_bracket varchar NOT NULL,
    relatedness decimal,

    PRIMARY KEY (
        field_a_id,
        field_b_id,
        time_range,
        age_bracket
    ),

    CHECK (field_a_id < field_b_id),
    CHECK (time_range IN ('day', 'week', 'month', 'all_time')),
    CHECK (age_bracket IN ('A', 'B', 'C', 'D')),
    CHECK (relatedness >= 0)
);

这个表结构有几个前提：

  • 关联度对两个标签是对称的，因此统一将较小的标签 ID 放在 field_a_id，避免同时保存 (a, b) 和 (b, a)。
  • 不统计标签与自身的关联度，所以使用 field_a_id < field_b_id。
  • 每个维度组合只保留当前统计结果。如果需要保存历史快照，还应增加统计时点，并把它纳入唯一性约束。
  • relatedness 允许为 NULL，可用于表示分母为零、无法计算的情况。如果业务不保存这类结果，也可以采用其他明确约定。

如果标签对具有方向性，或者需要统计自身关联，就应相应调整标签 ID 的约束。

查询和更新都可以保持统一

例如，查询标签 12 和 35 在过去一周、年龄段 A 下的关联度：

SELECT relatedness
FROM tag_relatedness
WHERE field_a_id = 12
  AND field_b_id = 35
  AND time_range = 'week'
  AND age_bracket = 'A';

查询同一标签对的全部 16 个值：

SELECT time_range, age_bracket, relatedness
FROM tag_relatedness
WHERE field_a_id = 12
  AND field_b_id = 35
ORDER BY time_range, age_bracket;

写入最新统计值时，可以通过主键处理冲突：

INSERT INTO tag_relatedness (
    field_a_id,
    field_b_id,
    time_range,
    age_bracket,
    relatedness
)
VALUES (12, 35, 'week', 'A', 0.25)
ON CONFLICT (
    field_a_id,
    field_b_id,
    time_range,
    age_bracket
)
DO UPDATE SET relatedness = EXCLUDED.relatedness;

这个主键适合按标签对读取数据。如果常见需求是“查找某个标签在指定时间范围和年龄段下关联度最高的其他标签”，还需要根据具体查询设计索引；仅凭表结构，无法确定最合适的索引组合。

行数更多，不代表空间按同样比例增加

三种方案完整存储所有组合时，行数分别约为：

方案行数每行关联度值数量
每个值一行400001
每个时间范围一行100004
每个标签对一行250016

三种方案保存的关联度值总数相同，都是约 40000 个。空间差异主要来自重复的维度值、行开销、对齐以及索引。

第一种结构通常会有更多行开销和主键索引项，但最终比例还取决于字段类型、实际数值、索引数量和数据分布。如果空间确实成为问题，可以先将重复的时间范围和年龄段编码改为较小的整数类型，并通过约束或维度表维护含义。

在 PostgreSQL 中，可以用下面的查询查看包含索引在内的关系总大小：

SELECT pg_size_pretty(
    pg_total_relation_size('tag_relatedness'::regclass)
);

比较三种结构时，应装入相同的统计数据，并建立满足相同查询需求的索引。仅比较字段数量或行数，不能得出可靠的存储结论。

什么时候可以考虑宽表

如果四个时间范围和四个年龄段长期固定，应用几乎总是一次读取某对标签的全部 16 个值，而且实测表明减少行数有明确收益，也可以使用第三种结构。

第二种结构适用于查询通常以时间范围为单位、一次读取所有年龄段的情况。但它仍把年龄段写进列名，新增年龄段时需要修改表结构。

如果只是展示层需要 16 列，可以保留第一种存储结构，再通过条件聚合生成展示结果。例如：

SELECT
    field_a_id,
    field_b_id,
    MAX(relatedness) FILTER (
        WHERE time_range = 'day' AND age_bracket = 'A'
    ) AS relatedness_raA_brA,
    MAX(relatedness) FILTER (
        WHERE time_range = 'day' AND age_bracket = 'B'
    ) AS relatedness_raA_brB
FROM tag_relatedness
GROUP BY field_a_id, field_b_id;

这里只展示两列，其余组合可以按同样方式添加。主键保证每个组合最多有一个值，因此这里用 MAX 将行转换为列。

先明确关联度的分母

按照问题中“仅包含两个标签中的一个”的字面含义，设：

  • n_a：包含标签 A 的条目数，包括同时包含 B 的条目。
  • n_b：包含标签 B 的条目数，包括同时包含 A 的条目。
  • n_ab：同时包含 A、B 的条目数。

则公式为：

relatedness = n_ab / (n_a + n_b - 2 * n_ab)

这个比值可能大于 1，分母也可能为 0，因此不能直接把字段限制在 [0, 1]。SQL 计算时可以使用：

n_ab::numeric / NULLIF(n_a + n_b - 2 * n_ab, 0)

如果你实际想表达的是“同时包含两个标签的条目数，除以至少包含其中一个标签的条目数”，分母应为 n_a + n_b - n_ab，那是另一种指标。

无论选择哪种表结构，同一批时间范围的统计都应使用一致的截止时点。过去一天、过去一周和过去一个月的窗口会重叠，窗口移动后也需要刷新统计值。

备注：内容仅供参考。