如何在Netezza SQL中利用_v_vector_idx表添加唯一递增ID列

纠正：ROW_NUMBER()无需依赖无重复排序列 #

你提到的ROW_NUMBER()依赖无重复排序列是误解——即使排序列存在重复值，ROW_NUMBER() OVER(ORDER BY 任意列)也会生成唯一的递增ID。它会自动给相同排序值的行分配不同的连续序号，示例代码如下：

SELECT 
    ROW_NUMBER() OVER(ORDER BY your_column) AS unique_id,
    other_columns
FROM your_table;

这个方法本身就足够可靠，完全不用担心排序列重复导致ID重复的问题。

利用内置表_v_vector_idx生成ID的实现方式 #

如果一定要使用_v_vector_idx表（存储系统所有数据块索引，IDX为从0开始的连续整数），可以通过以下两种方式实现：

方法1：交叉连接+LIMIT（适用于已知表行数的场景） #

先查询目标表的总行数：

SELECT COUNT(*) FROM your_table;

假设总行数为1000，执行以下语句生成从1开始的递增ID：

SELECT 
    vi.IDX + 1 AS unique_id,
    t.*
FROM your_table t
CROSS JOIN (SELECT IDX FROM _v_vector_idx LIMIT 1000) vi
ORDER BY your_sort_logic;

方法2：行号关联（通用场景） #

通过给原表和_v_vector_idx都生成行号，再进行关联，适配任意行数的表：

WITH table_with_rn AS (
    SELECT 
        ROW_NUMBER() OVER() AS rn,
        *
    FROM your_table
),
idx_table AS (
    SELECT 
        ROW_NUMBER() OVER() AS rn,
        IDX + 1 AS unique_id
    FROM _v_vector_idx
)
SELECT 
    i.unique_id,
    t.*
FROM table_with_rn t
JOIN idx_table i ON t.rn = i.rn;

其他可靠替代方案 #

方案1：使用GENERATE_SERIES（Netezza 7.2及以上版本支持） #

如果你的Netezza版本支持GENERATE_SERIES函数，可以直接生成连续序列关联：

SELECT 
    gs.id AS unique_id,
    t.*
FROM your_table t
JOIN GENERATE_SERIES(1, (SELECT COUNT(*) FROM your_table)) gs ON 
    ROW_NUMBER() OVER(ORDER BY your_column) = gs.id;

方案2：创建表时定义自增列（新建表场景） #

如果是创建新表，可以直接定义自增ID列，这是最省心的方式：

CREATE TABLE new_table (
    unique_id INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    other_column_definitions
);
-- 插入数据时无需指定unique_id
INSERT INTO new_table (other_columns) SELECT other_columns FROM your_table;

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