如何在Dremio视图逻辑中生成固定不变的唯一序列编号？

核心思路 #

要保证同一记录的序列编号每次查询都固定，不能依赖无排序的动态生成函数，必须基于记录的唯一标识字段生成稳定编号。常用两种可靠方案：基于哈希值生成固定编号，或基于唯一键排序生成连续编号。

实现方案 #

假设：

• 源1表名source1，取字段account_name（作为关联及唯一标识核心字段）、user_email
• 源2表名source2，取字段account_balance、last_login
• 两表通过account_name关联

方案1：哈希值生成固定编号 #

利用HASH()对唯一标识字段计算哈希值，转换为正整数作为编号，确保同一记录哈希值固定：

CREATE OR REPLACE VIEW fixed_seq_view AS
SELECT
    -- 基于唯一标识生成固定序列ID
    ABS(HASH(s1.account_name)) AS fixed_seq_id,
    -- 源1字段
    s1.account_name,
    s1.user_email,
    -- 源2字段
    s2.account_balance,
    s2.last_login
FROM
    source1 s1
JOIN
    source2 s2 ON s1.account_name = s2.account_name;

说明：若唯一标识是多个字段，可组合计算哈希，比如HASH(s1.account_name, s1.user_email)；ABS()用于确保编号为正数。

方案2：连续固定编号（基于排序） #

如果需要连续的整数编号（如1、2、3...），用DENSE_RANK()基于唯一键排序，排序字段固定则编号稳定：

CREATE OR REPLACE VIEW fixed_seq_view AS
SELECT
    DENSE_RANK() OVER(ORDER BY s1.account_name) AS fixed_seq_id,
    s1.account_name,
    s1.user_email,
    s2.account_balance,
    s2.last_login
FROM
    source1 s1
JOIN
    source2 s2 ON s1.account_name = s2.account_name;

说明：排序字段必须是记录的唯一标识，确保每条记录的排序位置固定，这样DENSE_RANK()返回的编号不会随查询次数变化。

注意事项 #

• 用于生成编号的字段必须是记录的唯一标识，否则不同记录可能出现重复编号。
• 禁止使用无固定排序的ROW_NUMBER()，否则可能因数据扫描顺序变化导致编号不稳定。
• 源表新增记录时，新记录的编号会按现有规则自动生成，不会影响原有记录的编号稳定性。

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