图数据库在人事系统的应用

在mysql去查询历史部门树、历史部门的部门全路径需要根据生效日期（effdt）字段进行递归查询距离生效日期最近的数据，效率比较低，使用图数据图可以仅更改变动部门的上下节点的关系属性就能实现整个部门树的准确性，并且不用递归查询全路径。

具体实践

1.初始化基础数据

清空所有节点数据

MATCH (n) DETACH DELETE n;

CREATE (dept1:Department {name: 'dept1', code: '1', parent_code: '0', effdt: date('2023-01-01')});
CREATE (dept2:Department {name: 'dept2', code: '2', parent_code: '1', effdt: date('2023-01-01')});
CREATE (dept3:Department {name: 'dept3', code: '3', parent_code: '1', effdt: date('2023-01-01')});
CREATE (dept4:Department {name: 'dept4', code: '4', parent_code: '2', effdt: date('2023-01-01')});
CREATE (dept5:Department {name: 'dept5', code: '5', parent_code: '2', effdt: date('2023-01-01')});
CREATE (dept6:Department {name: 'dept6', code: '6', parent_code: '5', effdt: date('2023-01-01')});

MATCH (dept2:Department{code:'2'}), (dept1:Department{code:'1'}) CREATE (dept1)-[:PARENT {effdt: date('2023-01-01'), to: '2'}]->(dept2);
MATCH (dept3:Department{code:'3'}), (dept1:Department{code:'1'}) CREATE (dept1)-[:PARENT {effdt: date('2023-01-01'), to: '3'}]->(dept3);
MATCH (dept4:Department{code:'4'}), (dept2:Department{code:'2'}) CREATE (dept2)-[:PARENT {effdt: date('2023-01-01'), to: '4'}]->(dept4);
MATCH (dept5:Department{code:'5'}), (dept2:Department{code:'2'}) CREATE (dept2)-[:PARENT {effdt: date('2023-01-01'), to: '5'}]->(dept5);
MATCH (dept6:Department{code:'6'}), (dept5:Department{code:'5'}) CREATE (dept5)-[:PARENT {effdt: date('2023-01-01'), to: '6'}]->(dept6);

2.已存在节点进行变动，重新创建相同部门code节点

CREATE (newDept2:Department {name: 'dept2', code: '2', parent_code: '1', effdt: date('2023-03-01')});

查询出生效日期最近的code对应的父部门并进行关联

MATCH (newDept2:Department {code: '2', effdt: date('2023-03-01')}), (parentDept:Department)-[:PARENT]->(oldDept2:Department {code: '2', effdt: date('2023-01-01')}) CREATE (parentDept)-[:PARENT {effdt: date('2023-03-01'), to: newDept2.code}]->(newDept2);

查询出生效日期最近的code对应的子部门节点并进行关联

MATCH (newDept2:Department {code: '2', effdt: date('2023-03-01')}), (oldDept2:Department {code: '2', effdt: date('2023-01-01')})-[:PARENT]->(child:Department)
FOREACH (descendant IN [child] |
  CREATE (newDept2)-[:PARENT {effdt: date('2023-03-01'), to: descendant.code}]->(descendant)
);

查询日期为2024-01-01时刻的部门树

MATCH path = ()-[:PARENT*]->()
WHERE ALL(rel IN relationships(path) 
      WHERE rel.effdt <= date('2024-01-01') AND 
      NOT EXISTS {
        MATCH ()-[sameRealt:PARENT]->()
        WHERE sameRealt.to = rel.to AND sameRealt.effdt > rel.effdt AND sameRealt.effdt <= date('2024-01-01')
      }
)
RETURN path;

查询部门code为6且日期为2024-01-01时刻节点

MATCH path = ()-[:PARENT*]->(dept:Department {code: "6"})
WHERE ALL(rel IN relationships(path) 
      WHERE rel.effdt <= date('2024-01-01') AND 
      NOT EXISTS {
        MATCH ()-[sameRealt:PARENT]->()
        WHERE sameRealt.to = rel.to AND sameRealt.effdt > rel.effdt AND sameRealt.effdt <= date('2024-01-01')
      }
RETURN path;

结尾

通过图数据库，我们不仅能够清晰地梳理出企业部门的历史脉络，还能为人事管理提供更高效、智能的解决方案。部门历史树的应用，让复杂的组织变迁变得一目了然，无论是追踪员工流动、优化资源配置，还是挖掘团队协作潜力，都能事半功倍。未来，随着图数据库技术的不断发展，它必将在人力资源领域绽放出更多可能性。