postgresql - 在 Postgres 中随机分配数字

Question

我正在将员工编号映射到 13 位卡号。卡号应在 9353500001234 和 9353500501234 之间。

是否有任何内置函数可以将此范围内的随机数映射到员工编号。前雇员编号5001应该分配给9353500500934？还有赋值后，是否可以将员工号传给一个函数来赋值对应的卡号？

Answer 1

尽管理论上，可以在域 1..500000 和 9353500001234...9353500501234 之间生成一个双射函数，但实际上，最简单的方法就是创建一个映射(在数学意义上)，并且 从中选择。

也就是说，您首先要创建一个表来存储成对的(employee_id, card_number):

CREATE TABLE employee_card
(
    employee_id INTEGER NOT NULL PRIMARY KEY,
    card_number BIGINT NOT NULL UNIQUE 
) ;

给定该表，我们用所有可能的 employee_id 和 card_number 填充它，随机洗牌 card_numbers。我们通过 generate_series 来做到这一点设置返回函数，连同row_number() , 使用 random()已排序 window (over clause) :

INSERT INTO employee_card (employee_id, card_number)
SELECT
    row_number() over(order by random()) AS employee_id, employee_card
FROM
    -- generate card_numbers
    generate_series(9353500001234, 9353500501234) AS s(employee_card)

我们添加一些索引以最佳方式获取数据:

-- Indexes to ease retrieving the data from this table
CREATE INDEX idx_employee_card_card_number ON employee_card(card_number, employee_id);
CREATE INDEX idx_employee_card_employee_id ON employee_card(employee_id, card_number);
VACUUM ANALYZE employee_card;

在这一点上，如果你想有两个函数，它们只是上表中的SELECT:

-- Get card_number given employee_id
CREATE OR REPLACE FUNCTION card_number_from_employee_id(_employee_id integer) 
    RETURNS BIGINT
    IMMUTABLE
    STRICT
    LANGUAGE SQL
AS
$$
    SELECT card_number FROM employee_card WHERE employee_id = _employee_id ;
$$ ;

-- Get employee_id given card_number
CREATE OR REPLACE FUNCTION employee_id_from_card_number(_card_number BIGINT)
    RETURNS INTEGER
    IMMUTABLE
    STRICT
    LANGUAGE SQL
AS
$$
    SELECT employee_id FROM employee_card WHERE card_number = _card_number ;
$$ ;

您现在可以测试一些 employee_id，并检索卡号。卡号应该看起来是随机的:

SELECT 
    employee_id, card_number_from_employee_id(employee_id)
FROM
    generate_series(10001, 10010) AS s(employee_id) ;

employee_id | card_number_from_employee_id----------: | ---------------------------:      10001 |                9353500008121      10002 |                9353500281438      10003 |                9353500124657      10004 |                9353500369382      10005 |                9353500238058      10006 |                9353500205141      10007 |                9353500297414      10008 |                9353500286020      10009 |                9353500219563      10010 |                9353500443892

And also test the reverse function:

SELECT
    card_number, employee_id_from_card_number(card_number)
FROM
    generate_series(9353500001234 + 1023, 9353500001234 + 1023 + 9) AS s(card_number);

  card_number | employee_id_from_card_number------------: | ---------------------------:9353500002257 |                        467549353500002258 |                        251249353500002259 |                       4814629353500002260 |                        994759353500002261 |                       1737329353500002262 |                       1710439353500002263 |                       2451929353500002264 |                       4182009353500002265 |                        763749353500002266 |                       251095

您可以在 dbfiddle here 查看所有设置和功能