主页 / dba / 问题 / 159425
Accepted
Sayad Xiarkakh
Asked: 2016-12-30 02:08:30 +0800 CST

使用不同的随机日期更新 PostgreSQL 表

  • 772

我在 PostgreSQL 中有两个表，Employee 和 Leave，如下所示： 

CREATE TEMP TABLE employee_table AS SELECT
  id::int,
  name::text
FROM ( VALUES
  (1, 'John' ),
  (2, 'David')  
) AS t(id, name);

CREATE TEMP TABLE leave_table AS SELECT
  id::int,
  leave_date::date,
  emp_id::int
FROM ( VALUES
  (1, '01/10/1993' ,1),
  (2, Null         ,1),
  (3, Null         ,1),
  (4, '02/12/1990' ,2),
  (5, Null         ,2),
  (6, Null         ,2) 
) AS t(ID,Leave_Date,Emp_ID);

我想更新 Leave 表并将 Leave_Date 列设置为每个员工的随机日期，可能是 01/01/2000 但是如果员工在 Leave 表中有多个 Null 条目，我想用两个不同的日期更新他的 null 条目这意味着一名员工在休假表中不应有两个相同的 Leave_Date 值，更新后我的休假表应如下所示： 

ID        Leave_Date    Emp_ID  
1         01/10/1993      1  
2         01/01/2000      1  
3         01/01/2001      1

如上所示，最初 john 在 Leave 表中有两个空条目，结果显示这些条目是用两个不同的日期更新的。有什么办法吗？

postgresql update

2 个回答

  1. Best Answer

    首先，当只有一个日期并且它为空时，您从未提供过测试用例，因此我们创建了它。

    INSERT INTO employee_table (id, name) VALUES (3, 'Evan Carroll');
INSERT INTO leave_table VALUES ( 10, null, 3 );

    然后我们运行一个命令来检查 an 中是否emp_id有多个条目leave_table。结果在该派生表中。我们相应地更新。在这里，我们生成一个代表 1900-2020 年开始的日期。只需更新您在问题中未定义的“随机日期”的含义即可。

    UPDATE leave_table
SET leave_date = CASE
  WHEN t.count = 1 OR t.count IS NULL
  THEN '01/01/2000'::date
  ELSE '1/1/1900'::date + ('1 year'::interval*floor(random()*120))
END
FROM (
  SELECT emp_id, count(*) FROM leave_table
  WHERE leave_date IS NULL
  GROUP BY emp_id
) AS t
WHERE leave_date IS NULL
AND t.emp_id = leave_table.emp_id;

    然后我们有了

    TABLE leave_table;
 id | leave_date | emp_id 
----+------------+--------
  1 | 1993-01-10 |      1
  4 | 1990-02-12 |      2
  2 | 1964-01-01 |      1
  3 | 1929-01-01 |      1
  5 | 1933-01-01 |      2
  6 | 1902-01-01 |      2
 10 | 2000-01-01 |      3

    现在，正如@McNets 昨天指出的那样，我有点作弊。相反，试试这个（更复杂的查询），这足以让问题用两个不同的日期更新他的 [ emp_id] 空条目

    WITH t AS (
  SELECT
    id,
    emp_id,
    leave_date,
    count(*) OVER (PARTITION BY emp_id) AS max_nulls,
    row_number() OVER (PARTITION BY emp_id)
  FROM leave_table
  WHERE leave_table.leave_date IS NULL
)
UPDATE leave_table
SET leave_date = CASE
  WHEN t.max_nulls = 1 OR t.max_nulls IS NULL
  THEN '01/01/2000'::date
  ELSE date_series_emp.ds
END
FROM t
INNER JOIN (
  SELECT distinct_emps.emp_id,
    gs.ds,
    count(*) OVER (PARTITION BY emp_id ORDER BY random()) AS row_number
  FROM ( SELECT DISTINCT emp_id FROM leave_table ) AS distinct_emps
  CROSS JOIN generate_series('1/1/1900'::date, '1/1/1990'::date, '1 month')
    AS gs(ds)
) AS date_series_emp
  USING ( emp_id, row_number )
WHERE t.id = leave_table.id;

    拆开它，CTE 做到了这一点

      SELECT
    id,
    emp_id,
    leave_date,
    count(*) FILTER (WHERE leave_date IS NULL) OVER (PARTITION BY emp_id) AS max_nulls,
    row_number() OVER (PARTITION BY emp_id)
  FROM leave_table

    这会生成集合中有多少空值，以及集合中的行号，我们可以使用更新查询以 1:1 的比例加入这些行号，

     id │ emp_id │ leave_date │ max_nulls │ row_number 
────┼────────┼────────────┼───────────┼────────────
  2 │      1 │            │         2 │          1
  3 │      1 │            │         2 │          2
  5 │      2 │            │         2 │          1
  6 │      2 │            │         2 │          2
 10 │      3 │            │         1 │          1

    唯一另一个棘手的部分是内部连接选择，

      SELECT distinct_emps.emp_id,
    gs.ds,
    count(*) OVER (PARTITION BY emp_id ORDER BY random()) AS row_number
  FROM ( SELECT DISTINCT emp_id FROM leave_table ) AS distinct_emps
  CROSS JOIN generate_series('1/1/1900'::date, '1/1/1990'::date, '1 month')
    AS gs(ds)

    在那里，我们采用不同的 emp_id，并将它们加入到您称为random的日期序列中。我们对该序列进行计数（*），以从生成的基数序列中为其提供相应的随机数。

    然后我们将它加入到表中并执行更新..

    这种方法确实有一个缺点，如果输入大小曾经耗尽您的“随机日期”池（其中只有 1081 个），则根本不会执行超过该最大值的行的更新。

    • 6

  2. 最初我认为可以使用单个 SQL 命令来完成（也许有人知道解决这个问题的好方法。）：

    update Leave
set Leave_Date = (select coalesce(max(l1.Leave_Date) + interval '1 day', '1900-01-01'::date) 
                  from Leave l1 
                  where l1.Emp_ID = Leave.Emp_ID)
where Leave_Date is null;

    但是，在所有记录都更新之前，Postgres 不会提交所有更改，并且max(l1.Leave_Date)总是返回相同的值。

    我在函数中使用了 FOR-LOOP。

    正如@EvanCarroll 昨天评论的那样，我没有使用随机日期，问题是：使用不同的随机日期

    好吧，现在该函数不是简单地将一天添加到最大日期，而是添加天random数。到目前为止，我已经使用了 9 天，但是可以轻松修改此行为。（例如(interval '1 day' * (random() * 9 + 21)::int)：）

    CREATE OR REPLACE FUNCTION Leave_Update_Null_Dates()
RETURNS integer 
AS
$$
DECLARE
    lv RECORD;
BEGIN
    FOR lv IN SELECT * FROM Leave where Leave_Date is NULL LOOP
        UPDATE Leave
        SET Leave_Date = (select coalesce(max(l1.Leave_Date) + (interval '1 day' * (random() * 9)::int), '1990-01-01'::date) 
                             from Leave l1 
                             where l1.Emp_ID = lv.Emp_ID)
        WHERE ID = lv.ID;
    END LOOP;
    RETURN 0;
END;
$$
LANGUAGE 'plpgsql';

select Leave_Update_Null_Dates();

    这是结果：

    +----+---------------------+--------+
| id |      leave_date     | emp_id |
+----+---------------------+--------+
|  1 | 01.10.1993 00:00:00 |    1   |
+----+---------------------+--------+
|  2 | 06.10.1993 00:00:00 |    1   |
+----+---------------------+--------+
|  3 | 08.10.1993 00:00:00 |    1   |
+----+---------------------+--------+
|  4 | 12.02.1990 00:00:00 |    2   |
+----+---------------------+--------+
|  5 | 20.02.1990 00:00:00 |    2   |
+----+---------------------+--------+
|  6 | 25.02.1990 00:00:00 |    2   |
+----+---------------------+--------+
|  7 | 01.01.1990 00:00:00 |    3   |
+----+---------------------+--------+

    你可以在这里查看：http ://rextester.com/BWZ31281

    我希望这可以帮助你。

    • 2

相关问题