在Neo4j中构建员工月度随机工时调度并查询超时员工的方案优化

需求概述 #

• 目标：筛选所有超时工作的员工
• 数据模型规划：搭建月度调度层级（Month → Week → Day），每位员工拥有个人Schedule节点记录每周固定工时，同时通过WorkShift节点记录实际上下班时间

已创建的员工节点 #

CREATE (:Employee {employee_id: randomUUID(), name: 'John Doe', sex: 'Male', salary: 25000000, age: 35, job_title: 'Manager', date_of_joining: '2018-01-01', email_address: 'johndoe@example.com', department: 'Sales'})
CREATE (:Employee {employee_id: randomUUID(), name: 'Jane Smith', sex: 'Female', salary: 20000000, age: 28, job_title: 'Marketing Manager', date_of_joining: '2019-05-01', email_address: 'janesmith@example.com', department: 'Marketing'})

初始超时查询语句 #

尝试查询周一超时的员工：

MATCH (e:Employee)-[:HAS_SCHEDULE]->(s:Schedule)-[:HAS_SHIFT]->(w:WorkShift)
WHERE s.monday_start <= w.start AND s.monday_end >= w.end AND w.end > '15:00'
RETURN e.name, s.monday_start, s.monday_end, w.start, w.end

创建调度结构时的报错 #

尝试创建12个Month节点并关联下属Week、Day时，执行以下语句报错：

// 报错语句示例
UNWIND range(0,11) AS idx
CREATE (:Month {value: date({year: 2023, month: idx+1, day: 1}).toString()})

报错信息：

"}" (line 3, column 75 (offset: 130))"  CREATE (:Month {value: date({year: 2023, month: idx+1, day: 1}).toString()})"

班次分配尝试语句 #

尝试为员工分配班次的逻辑：

MATCH (e:Employee)-[:HAS_SCHEDULE]->(s:Schedule),
      (e)-[:HAS_SHIFT]->(ws:WorkShift)
WHERE s.monday_start <= ws.start AND s.monday_end >= ws.end
MERGE (e)-[:ON_SHIFT {day: 'Monday'}]->(ws)
RETURN e.name, ws.start, ws.end

当前问题 #

• 创建员工Schedule节点失败
• 无法完善实际上下班时间的调度关联逻辑
• 员工班次分配逻辑存在缺陷

解决方案 #

1. 修正Month节点创建语句 #

报错原因是date()函数内的idx+1表达式解析问题，先计算月份值再传入函数即可：

UNWIND range(0,11) AS idx
CREATE (:Month {
  year: 2023,
  month: idx + 1,
  start_date: date({year: 2023, month: idx + 1, day: 1}),
  value: date({year: 2023, month: idx + 1, day: 1}).toString()
})

2. 构建完整月度调度层级（Month→Week→Day） #

搭建从月份到天的完整关联结构：

// 创建2023年12个月份节点
UNWIND range(0,11) AS monthIdx
CREATE (m:Month {
  year: 2023,
  month: monthIdx + 1,
  start_date: date({year:2023, month:monthIdx+1, day:1}),
  end_date: date({year:2023, month:monthIdx+2, day:1}) - duration({days:1})
})

// 为每个月关联对应周节点（按ISO周计算）
MATCH (m:Month)
WITH m, range(date(m.start_date).week, date(m.end_date).week) AS weekNumbers
UNWIND weekNumbers AS weekNum
MERGE (w:Week {year: 2023, week: weekNum})
MERGE (m)-[:HAS_WEEK]->(w)

// 为每个周创建7天节点并关联
MATCH (w:Week)
WITH w, range(1,7) AS dayNumbers
UNWIND dayNumbers AS dayNum
CREATE (d:Day {
  year: 2023,
  week: w.week,
  day_of_week: dayNum,
  day_name: CASE dayNum
    WHEN 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    WHEN 3 THEN 'Wednesday'
    WHEN 4 THEN 'Thursday'
    WHEN 5 THEN 'Friday'
    WHEN 6 THEN 'Saturday'
    WHEN 7 THEN 'Sunday'
  END
})
MERGE (w)-[:HAS_DAY]->(d)

3. 创建员工个人Schedule节点并关联 #

为每个员工创建记录固定工时的Schedule，并关联到对应Day节点：

// 创建员工个人Schedule节点
MATCH (e:Employee)
CREATE (s:Schedule {
  employee_id: e.employee_id,
  monday_start: '09:00',
  monday_end: '17:00',
  tuesday_start: '09:00',
  tuesday_end: '17:00',
  wednesday_start: '09:00',
  wednesday_end: '17:00',
  thursday_start: '09:00',
  thursday_end: '17:00',
  friday_start: '09:00',
  friday_end: '17:00',
  saturday_start: '10:00',
  saturday_end: '16:00',
  sunday_start: 'OFF',
  sunday_end: 'OFF'
})
MERGE (e)-[:HAS_SCHEDULE]->(s)

// 将Schedule关联到对应Day节点，记录固定工时
MATCH (s:Schedule), (d:Day)
WHERE d.day_name IN ['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday']
MERGE (s)-[:FIXED_SHIFT {
  start_time: s[toString(d.day_name)+'_start'],
  end_time: s[toString(d.day_name)+'_end']
}]->(d)

4. 修正员工实际班次分配逻辑 #

先创建WorkShift节点记录实际上下班时间，再关联员工和日期，同时标记超时：

// 示例：创建John Doe周一的实际班次
MATCH (e:Employee {name: 'John Doe'}), (d:Day {day_name: 'Monday', week: 1, year:2023})
CREATE (ws:WorkShift {
  start: '09:00',
  end: '18:30',
  date: date({year:2023, month:1, day:2})
})
MERGE (e)-[:WORKED_SHIFT]->(ws)
MERGE (ws)-[:ON_DAY]->(d)

// 匹配固定工时与实际班次，标记超时关系
MATCH (e:Employee)-[:HAS_SCHEDULE]->(s:Schedule)-[:FIXED_SHIFT]->(d:Day),
      (e)-[:WORKED_SHIFT]->(ws:WorkShift)-[:ON_DAY]->(d)
WHERE time(ws.end) > time(s[toString(d.day_name)+'_end'])
MERGE (e)-[:OVERTIME_SHIFT {
  day: d.day_name,
  overtime_duration: duration.between(time(s[toString(d.day_name)+'_end']), time(ws.end))
}]->(ws)
RETURN e.name, d.day_name, s[toString(d.day_name)+'_end'] AS scheduled_end, ws.end AS actual_end, duration.between(time(s[toString(d.day_name)+'_end']), time(ws.end)) AS overtime_duration

5. 优化超时员工查询语句 #

使用时间类型比较，确保准确性：

MATCH (e:Employee)-[:HAS_SCHEDULE]->(s:Schedule)-[:FIXED_SHIFT]->(d:Day),
      (e)-[:WORKED_SHIFT]->(ws:WorkShift)-[:ON_DAY]->(d)
WHERE time(ws.end) > time(s[toString(d.day_name)+'_end'])
RETURN e.name, e.employee_id, d.day_name, ws.date AS work_date,
       s[toString(d.day_name)+'_start'] AS scheduled_start,
       s[toString(d.day_name)+'_end'] AS scheduled_end,
       ws.start AS actual_start,
       ws.end AS actual_end,
       duration.between(time(s[toString(d.day_name)+'_end']), time(ws.end)) AS overtime_duration
ORDER BY work_date, e.name

内容的提问来源于stack exchange，提问作者Aintripin