mzl92
90 lines
3.0 KiB
Markdown
Executable File
90 lines
3.0 KiB
Markdown
Executable File
# 招聘数据库 Schema
|
||
|
||
**路径：** `~/.hermes/data/recruitment/recruitment.db`
|
||
**引擎：** SQLite 3（WAL 模式）
|
||
**Node.js：** `better-sqlite3`
|
||
**Python：** `sqlite3`（标准库）
|
||
|
||
## candidates 表
|
||
|
||
```sql
|
||
CREATE TABLE candidates (
|
||
id TEXT PRIMARY KEY, -- MD5(name|age|edu|exp|school)[:12]
|
||
platform TEXT NOT NULL DEFAULT 'zhaopin',
|
||
account TEXT NOT NULL DEFAULT 'hr1', -- v0.2 多账号：hr1/hr2
|
||
name TEXT NOT NULL,
|
||
age INTEGER,
|
||
education TEXT, -- 本科/硕士/大专等
|
||
experience TEXT, -- 原始文本如"3年经验"
|
||
exp_years REAL, -- 数值化经验年数，便于筛选
|
||
school TEXT,
|
||
expected_city TEXT,
|
||
expected_salary TEXT,
|
||
activity TEXT DEFAULT '', -- 活跃度文本如"10分钟前有回复"
|
||
role_tag TEXT DEFAULT '', -- 岗位推荐标签：有经验岗/应届生岗/不限
|
||
score INTEGER, -- 7维度总分 7-35
|
||
score_level TEXT, -- 推荐/待定/不推荐
|
||
greeted INTEGER NOT NULL DEFAULT 0,
|
||
greeted_at TEXT,
|
||
raw_text TEXT, -- 完整简历原文
|
||
education_detail TEXT,
|
||
work_detail TEXT,
|
||
cert_detail TEXT,
|
||
card_info TEXT, -- 列表卡片原始信息 JSON
|
||
score_details TEXT, -- 评分明细 JSON
|
||
scraped_at TEXT,
|
||
updated_at TEXT,
|
||
notes TEXT DEFAULT '' -- 人工备注
|
||
);
|
||
```
|
||
|
||
## greet_log 表
|
||
|
||
```sql
|
||
CREATE TABLE greet_log (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
candidate_id TEXT NOT NULL REFERENCES candidates(id),
|
||
account TEXT NOT NULL DEFAULT 'hr1', -- v0.2 多账号：hr1/hr2
|
||
action TEXT NOT NULL, -- 'greet'
|
||
result TEXT, -- 'success' / 'button_not_found' / ...
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
|
||
);
|
||
```
|
||
|
||
## scrape_log 表
|
||
|
||
```sql
|
||
CREATE TABLE scrape_log (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
mode TEXT, -- 'full' / 'incremental'
|
||
target_count INTEGER,
|
||
new_count INTEGER,
|
||
skip_count INTEGER,
|
||
greet_count INTEGER,
|
||
error_count INTEGER,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
|
||
);
|
||
```
|
||
|
||
## 常用查询
|
||
|
||
```sql
|
||
-- 推荐人员
|
||
SELECT name, age, education, school, score, greeted
|
||
FROM candidates WHERE score_level = '推荐' ORDER BY score DESC;
|
||
|
||
-- 未打招呼的推荐/待定
|
||
SELECT name, score, score_level, school
|
||
FROM candidates WHERE score_level IN ('推荐','待定') AND greeted = 0;
|
||
|
||
-- 评分分布
|
||
SELECT score_level, COUNT(*) FROM candidates GROUP BY score_level;
|
||
|
||
-- 最近抓取的10人
|
||
SELECT name, score, score_level, scraped_at
|
||
FROM candidates ORDER BY scraped_at DESC LIMIT 10;
|
||
|
||
-- 打招呼成功率
|
||
SELECT action, result, COUNT(*) FROM greet_log GROUP BY action, result;
|
||
```
|