MySQL/MariaDB : Historization - How do it better

Question

我导出了个人数据，我想将其导入到考虑历史记录的表中。我编写了单个 sql 步骤，但出现了两个问题:
1.有一个步骤让我得到了意想不到的约会
2:我想避免手动提交一些步骤并使用存储过程

表格是:

考虑历史记录要填写的表格:

CREATE TABLE person (
  id           INTEGER      DEFAULT NULL
, name         VARCHAR(50)  DEFAULT NULL
, effective_dt DATE         DEFAULT NULL
, expiry_dt    DATE         DEFAULT NULL
);    

包含要导入的人员数据的表格:

CREATE TABLE person_stg (
  id        INTEGER     DEFAULT NULL
, name      VARCHAR(50) DEFAULT NULL
, export_dt DATE        DEFAULT NULL
, import_flag  TINYINT      DEFAULT 0
);

-- Several exports which has to be imported
INSERT INTO person_stg (id, name, export_dt) VALUES 
  (1,'Jonn'  , '2000-01-01')
, (2,'Marry' , '2000-01-01')
, (1,'John'  , '2000-01-05')
, (2,'Marry' , '2000-01-06')
, (2,'Mary'  , '2000-01-10')
, (3,'Samuel', '2000-01-10')
, (2,'Maria' , '2000-01-15')
;

以下第一步 (1) 使用人员的第一个状态填充表人员:

INSERT INTO person
SELECT a.id, a.name, a.export_dt, '9999-12-31' expiry_dt
FROM person_stg a
LEFT JOIN person_stg b 
ON a.id = b.id
   AND a.export_dt > b.export_dt
WHERE b.id IS NULL
;
SELECT * FROM person ORDER BY id, effective_dt;

+----+--------+--------------+------------+
| id |  name  | effective_dt | expiry_dt  |
+----+--------+--------------+------------+
|  1 | Jonn   | 2000-01-01   | 9999-12-31 |
|  2 | Marry  | 2000-01-01   | 9999-12-31 |
|  3 | Samuel | 2000-01-10   | 9999-12-31 |
+----+--------+--------------+------------+

步骤 (2) 更改到期日期:

-- (2) Update expiry_dt where changes happened
UPDATE 
  person     a
, person_stg b
SET a.expiry_dt = SUBDATE(b.export_dt,1)
WHERE a.id = b.id
      AND a.name <> b.name
      AND a.expiry_dt = '9999-12-31'
      AND b.export_dt = (SELECT MIN(b.export_dt)
                         FROM person_stg c
                         WHERE b.id = c.id
                               AND c.import_flag = 0
                         )
;
SELECT * FROM person ORDER BY id, effective_dt;

+----+--------+--------------+------------+
| id |  name  | effective_dt | expiry_dt  |
+----+--------+--------------+------------+
|  1 | Jonn   | 2000-01-01   | 2000-01-04 |
|  2 | Marry  | 2000-01-01   | 2000-01-09 |
|  3 | Samuel | 2000-01-10   | 9999-12-31 |
+----+--------+--------------+------------+

第三步(3)插入人员数据的第二状态:

-- (3) Insert new exports which has changes
INSERT INTO person
SELECT a.id, a.name, a.export_dt, '9999-12-31' expiry_dt
FROM person_stg a
INNER JOIN person b
ON a.id = b.id
   AND b.expiry_dt = SUBDATE(a.export_dt,1)
   AND a.export_dt > b.effective_dt
   AND a.import_flag = 0
;
SELECT * FROM person ORDER BY id, effective_dt;


+----+--------+--------------+------------+
| id |  name  | effective_dt | expiry_dt  |
+----+--------+--------------+------------+
|  1 | Jonn   | 2000-01-01   | 2000-01-04 |
|  1 | John   | 2000-01-05   | 9999-12-31 |
|  2 | Marry  | 2000-01-01   | 2000-01-09 |
|  2 | Mary   | 2000-01-10   | 9999-12-31 |
|  3 | Samuel | 2000-01-10   | 9999-12-31 |
+----+--------+--------------+------------+            

最后一步 (4) 在 person_stg 上定义了插入的记录:

-- (4) Define imported records
UPDATE 
  person_stg a
, person     b
SET import_flag = 1
WHERE a.id = b.id
      AND a.export_dt = b.effective_dt
;

到目前为止，一切都很好。如果我重复步骤(2)，我会得到下表:

+----+--------+--------------+------------+
| id |  name  | effective_dt | expiry_dt  |
+----+--------+--------------+------------+
|  1 | Jonn   | 2000-01-01   | 2000-01-04 |
|  1 | John   | 2000-01-05   | 9999-12-31 |
|  2 | Marry  | 2000-01-01   | 2000-01-09 |
|  2 | Mary   | 2000-01-10   | 1999-12-31 |   <--- ??? Should be 2000-01-14
|  3 | Samuel | 2000-01-10   | 9999-12-31 |
+----+--------+--------------+------------+

Mary/2000-01-10 的 expiry_dt 为 1999-12-31，而不是 2000-01-14。我不明白这是怎么发生的。
所以，我的问题是:

(1a) 为什么这次更新的到期日期给出了这个奇怪的日期？

(1b) 是否有比 (2) 更好的代码？

(2) 如何自动重复步骤 (2) 到 (4)？我只需要一些存储过程的提示。 -- (4) 定义导入的记录 更新 人_stg a , 人 b 设置导入标志 = 1 其中 a.id = b.id AND a.export_dt = b. effective_dt ;

Answer 1

如果我明白您想要做什么，您就不需要多步骤的过程。您只需查找每条记录的“结束日期”。这是使用相关子查询的方法:

SELECT p.*, export_dt as effdate,
       COALESCE((SELECT export_dt - interval 1 day
                 FROM person_stg p2
                 WHERE p2.id = p.id AND
                       p2.export_dt > p.export_dt
                 ORDER BY p2.export_dt
                 LIMIT 1
                ), '9999-12-31') as enddate
FROM person_stg p;

您还可以使用变量执行某些操作。

我不确定这是否能回答您的问题，因为它用更简单的查询替换了整个过程。