数据仓库实践：人员信息拉链表的SQL实现

背景

在制造业数仓建设过程中，人员分析主题下生产人员基础信息以及历史信息相当重要，

因为一般会包含人员的【入转调离】，或者一些特殊的变动，比如公司工号编码格式变动等；

在此需求下，我们需要定时记录人员信息的变动，但如果每次都全量同步会极大增加存储成本，

故一般会使用拉链表的形式保存，达成记录历史数据同时降低存储成本；

本文将介绍【通过定时任务将每一天变动后人员基础信息最新版本同步到流水表，再通过流水表生成拉链表】的SQL实现过程；

原表数据结构

原表示例

本文示例将使用人员基础信息中常用的字段，其他字段也可灵活选取：

示例原表DDL

create table employee
(
    id     bigint       not null primary key comment 'id',
    num    varchar(100) null comment '工号',
    name   varchar(100) null comment '姓名',
    job    varchar(100) null comment '岗位',
    tel    varchar(100) null comment '联系电话',
    email  varchar(100) null comment '邮箱地址',
    status varchar(100) null comment '在职状态'
);

流水表

在原表基础上，流水表将在主键中添加 fn_date 字段作为原表数据变动的时间，或者记录同步到流水表的时间。

并在所有字段后面添加 描述字段，记录最新版本记录和前一版记录的变动内容；

流水表DDL

create table clip_dwh.employee_flow
(
    fn_date     varchar(12)   not null comment '变动时间',
    id          bigint        not null comment 'id',
    num         varchar(100)  null comment '工号',
    name        varchar(100)  null comment '姓名',
    job         varchar(100)  null comment '岗位',
    tel         varchar(100)  null comment '联系电话',
    email       varchar(100)  null comment '邮箱地址',
    status      varchar(100)  null comment '在职状态',
    description varchar(4000) null comment '变动描述',
    primary key (fn_date, id)
);

使用python生成流水表示例数据

import pathlib

import faker
import numpy as np
import arrow
import pandas as pd

result_file = "./linked.xlsx"

p = pathlib.Path(result_file)

general = faker.Faker()

id_lst = np.random.choice(range(10, 100), size=10, replace=False)
num_lst = np.random.choice(range(2024000, 2025000), size=10, replace=False)

lst = []

for i in range(10):
    item = {
        'id': id_lst[i],
        "num": num_lst[i],
        "name": "{} {}".format(general.first_name(), general.last_name()),
        "job": general.job(),
        "tel": general.phone_number(),
        "email": "{}.{}@example.org".format(general.first_name(), general.last_name()),
        "status": "Y"
    }
    # print(item)
    lst.append(item)

df_entity = pd.DataFrame(lst)

if p.exists() and p.is_file():
    with pd.ExcelWriter(result_file, mode='a', if_sheet_exists="overlay", engine="openpyxl") as writer:
        df_entity.to_excel(writer,
                           sheet_name="employee",
                           index=False)
else:
    df_entity.to_excel(result_file,
                       engine="openpyxl",
                       sheet_name="employee",
                       index=False)

fact_date = arrow.now()

flow_lst = []

for i in lst:
    i["fn_date"] = fact_date.format('YYYY-MM-DD')
    i["desc"] = "{\"id\":\"create\"}"
    shift_lst = np.random.choice(range(1, 10), size=np.random.randint(5), replace=False)
    shift_lst.sort()
    flow_part = [i]

    for k, v in enumerate(shift_lst):
        random_job = general.job()
        flow_item = {
            "fn_date": fact_date.shift(days=int(v)).format('YYYY-MM-DD'),
            'id': i["id"],
            "num": i["num"],
            "name": i["name"],
            "job": random_job,
            "tel": i["tel"],
            "email": "{}.{}@example.org".format(general.first_name(), general.last_name()),
            "status": "Y",
            "desc": "{{ \"job\":\"{}->{}\" }}".format(flow_part[k]["job"], random_job)
        }
        flow_part.append(flow_item)

    flow_lst.extend(flow_part)
df_flow = pd.DataFrame(flow_lst)

with pd.ExcelWriter(result_file, mode='a', if_sheet_exists="overlay", engine="openpyxl") as writer:
    df_flow.to_excel(writer,
                     sheet_name="employee_flow",
                     index=False)

原表生成部分结果

流水表生成部分结果

拉链表

流水表已经记录了每次变动的最新内容，而拉链表将会抛弃变动描述字段，并将fn_date 修改为表示时间范围的start_date 和 end_date

start_date 和 end_date 表示此条记录的有效时间范围，一般使用SQL的lag函数能够实现，

以下是MySQL 实现

拉链表生成SQL代码

insert into employee_linked(start_date,
                            end_date,
                            id,
                            num,
                            name,
                            job,
                            tel,
                            email,
                            status)
select start_date,
       end_date,
       id,
       num,
       name,
       job,
       tel,
       email,
       status
from (SELECT fn_date                  start_date,
             date_sub(ifnull(lag(date(fn_date), 1) OVER ( PARTITION BY id ORDER BY fn_date desc),
                             date('9999-12-31')),
                      interval 1 day) end_date,
             id,
             num,
             name,
             job,
             tel,
             email,
             status
      FROM employee_flow) a

拉链表生成结果