如何通过组合现有列值为物化视图创建唯一ID列？

问题背景 #

现有两张表trips和segments，通过session_id关联创建物化视图时，尝试通过算术组合列值生成唯一ID均出现重复，原因是算术相加会因数值进位、组合重叠导致不同行生成相同ID。

表结构与数据 #

CREATE TABLE trips (trip_id INT, session_ids INT[], distance DOUBLE PRECISION);

INSERT INTO trips(trip_id, session_ids, distance) 
VALUES (537165, '{14749,14778}', 1986.56),(542000, '{17577}', 1753.1),
  (545600, '{80652,80782}', 1550),(574674,'{146530}', 2000.3),
  (574679, '{146480}', 1799.1);

CREATE TABLE segments(session_id INT, segment_id INT, length DOUBLE PRECISION);

INSERT INTO segments(session_id, segment_id, length)
VALUES (14749, 1, 89.3),(14749,3,201),(14749,5,500.7),(14778,1,300),
  (14778,2,401),(17577,1,134.9),(17577,3,232.1),(80652,1,102.1),
  (80652,2,300),(80782,1,400),(80782,3,45.89), (146530, 1, 1209.6),
  (146530, 7, 126.7),(146480, 5, 207.4), (146480, 7, 1507.4);

可行解决方案 #

方案1：字符串拼接（最稳妥，无重复风险） #

将trip_id、session_id、segment_id用分隔符拼接成字符串ID，三者的组合天然唯一，完全避免重复：

CREATE MATERIALIZED VIEW example_view4
AS 
SELECT CONCAT_WS('_', t.trip_id, s.session_id, s.segment_id) AS id,
       s.session_id,
       s.segment_id,
       s.length,
       t.distance
FROM trips t
JOIN segments s ON s.session_id = ANY(t.session_ids);

如果需要数值型ID，可结合哈希函数转换（哈希冲突概率极低）：

CREATE MATERIALIZED VIEW example_view4
AS 
SELECT ('x' || SUBSTRING(MD5(CONCAT_WS('_', t.trip_id, s.session_id, s.segment_id)) FROM 1 FOR 16))::BIT(64)::BIGINT AS id,
       s.session_id,
       s.segment_id,
       s.length,
       t.distance
FROM trips t
JOIN segments s ON s.session_id = ANY(t.session_ids);

方案2：位偏移法（数值型ID，基于原列值） #

通过位运算将三个字段分配到独立的位段，避免数值重叠：

CREATE MATERIALIZED VIEW example_view4
AS 
SELECT (t.trip_id << 22) | (s.session_id << 4) | s.segment_id AS id,
       s.session_id,
       s.segment_id,
       s.length,
       t.distance
FROM trips t
JOIN segments s ON s.session_id = ANY(t.session_ids);

• trip_id << 22：将行程ID移到高位，确保不被后续字段覆盖
• s.session_id << 4：将会话ID移到中间位，与行程ID、分段ID位段完全独立
• 最后用|（按位或）合并三个字段，保证每个组合的ID唯一

方案3：自增唯一ID（无需基于原列值） #

如果仅需要唯一标识，无需关联原列值，可使用窗口函数生成自增ID：

CREATE MATERIALIZED VIEW example_view4
AS 
SELECT ROW_NUMBER() OVER () AS id,
       s.session_id,
       s.segment_id,
       s.length,
       t.distance
FROM trips t
JOIN segments s ON s.session_id = ANY(t.session_ids);

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