sql

WITH hash_bucket AS (
    SELECT  
        *,
        ROW_NUMBER() OVER (PARTITION BY bucket_no) AS bucket_rel_index,
        COUNT(1) OVER (PARTITION BY bucket_no ) AS bucket_size
    FROM    
    (
        SELECT  
            *,
            ABS(HASH(cid)) % 100000 AS bucket_no 
        FROM    
            xxxx        
    ) 
),
bucket_base AS 
(
    SELECT
        bucket_no,
        SUM(bucket_size) OVER (ORDER BY bucket_no ASC) - bucket_size AS bucket_base
    FROM
    (
        SELECT 
            DISTINCT bucket_no,
            bucket_size
        FROM
            hash_bucket
    )
)
SELECT
    t1.*,
    t2.bucket_base + bucket_rel_index AS id_index
FROM
    hash_bucket t1 
JOIN 
    bucket_base t2 
    ON t1.bucket_no = t2.bucket_no
;

核心代码如上，主要思路先做分桶进行分布式局部排序、再基于桶大小得到全局索引

1. 第一步先对id做hash分桶，然后计算每个桶的大小，桶数量可以根据需要计算的id数量来评估，这里分100000个桶，然后计算出每个id在桶内的相对位置bucket_rel_index，同时计算出桶大小bucket_size；
2. 第二步根据桶大小计算每个桶的基址；
3. 第三步将桶基址+id在桶内的相对地址得到全局唯一的绝对地址id_index；