liqiang
b119135836
Build latest book artifacts / build (push) Canceled after 0s
dependency resolution / resolve (3.11) (push) Canceled after 0s
dependency resolution / resolve (3.13) (push) Canceled after 0s
deploy-pages / build (push) Canceled after 0s
deploy-pages / deploy (push) Canceled after 0s
i18n consistency check / check (push) Canceled after 0s
provider adoption tests / test (chapter2/context-compression) (push) Canceled after 0s
provider adoption tests / test (chapter2/prompt-injection) (push) Canceled after 0s
provider adoption tests / test (chapter2/system-hint) (push) Canceled after 0s
provider adoption tests / test (chapter3/log-sanitization) (push) Canceled after 0s
web-search-agent tests / test (push) Canceled after 0s
web-search-agent tests / agentbook (push) Canceled after 0s
79 lines
7.1 KiB
HTML
79 lines
7.1 KiB
HTML
<!doctype html><meta charset=utf-8><title>Experiment 5-10 PostgreSQL artifacts</title>
|
|
<style>body{font-family:system-ui;margin:30px;background:#f7f8fa;color:#172033}section{background:white;padding:18px;margin:16px 0;border-radius:12px}table{border-collapse:collapse}td{border:1px solid #ccd3dd;padding:5px 9px}pre{white-space:pre-wrap;background:#eef2f7;padding:12px}.ok{color:#08783e}.bad{color:#b42318}</style>
|
|
<h1>ERP Agent: SQL artifacts executed by PostgreSQL</h1><section><h2>1. 平均每个员工在职多久？</h2><pre>SELECT AVG(COALESCE(leave_date, CURRENT_DATE) - hire_date) AS average_tenure_days FROM employees</pre><table><tr><td>886.2</td></tr></table><p class=ok>PASS: independent Python reference matched</p></section><section><h2>2. 每个部门有多少在职员工？</h2><pre>SELECT department, COUNT(emp_id) AS active_count FROM employees WHERE leave_date IS NULL GROUP BY department</pre><table><tr><td>人力资源部</td><td>4</td></tr><tr><td>市场部</td><td>9</td></tr><tr><td>研发部</td><td>5</td></tr><tr><td>财务部</td><td>6</td></tr><tr><td>销售部</td><td>10</td></tr></table><p class=ok>PASS: independent Python reference matched</p></section><section><h2>3. 哪个部门员工平均级别最高？</h2><pre>SELECT department
|
|
FROM employees
|
|
GROUP BY department
|
|
ORDER BY AVG(level) DESC
|
|
LIMIT 1</pre><table><tr><td>销售部</td></tr></table><p class=ok>PASS: independent Python reference matched</p></section><section><h2>4. 每个部门今年和去年各新入职多少人？</h2><pre>SELECT department,
|
|
COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM hire_date) = EXTRACT(YEAR FROM CURRENT_DATE)) AS "this-year count",
|
|
COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM hire_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1) AS "last-year count"
|
|
FROM employees
|
|
GROUP BY department
|
|
HAVING COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM hire_date) = EXTRACT(YEAR FROM CURRENT_DATE)) > 0
|
|
OR COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM hire_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1) > 0</pre><table><tr><td>财务部</td><td>0</td><td>1</td></tr><tr><td>销售部</td><td>2</td><td>1</td></tr><tr><td>人力资源部</td><td>2</td><td>0</td></tr><tr><td>研发部</td><td>1</td><td>2</td></tr><tr><td>市场部</td><td>0</td><td>4</td></tr></table><p class=ok>PASS: independent Python reference matched</p></section><section><h2>5. 前年3月到去年5月，A部门平均工资是多少？</h2><pre>SELECT AVG(s.salary)
|
|
FROM employees e
|
|
JOIN salaries s ON e.emp_id = s.emp_id
|
|
WHERE e.department = '研发部'
|
|
AND s.pay_date BETWEEN make_date(EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 2, 3, 1)
|
|
AND make_date(EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 1, 5, 31)</pre><table><tr><td>27695.833333333332</td></tr></table><p class=ok>PASS: independent Python reference matched</p></section><section><h2>6. 去年A部门和B部门平均工资哪个高？</h2><pre>SELECT department, AVG(salary) AS average_salary
|
|
FROM employees
|
|
JOIN salaries ON employees.emp_id = salaries.emp_id
|
|
WHERE department IN ('研发部', '销售部')
|
|
AND pay_date >= date_trunc('year', CURRENT_DATE) - INTERVAL '1 year'
|
|
AND pay_date < date_trunc('year', CURRENT_DATE)
|
|
GROUP BY department</pre><table><tr><td>研发部</td><td>28569.444444444445</td></tr><tr><td>销售部</td><td>29481.55339805825</td></tr></table><p class=ok>PASS: independent Python reference matched</p></section><section><h2>7. 今年每个级别的员工平均工资是多少？</h2><pre>SELECT e.level, AVG(s.salary) AS average_salary
|
|
FROM employees e
|
|
JOIN salaries s ON e.emp_id = s.emp_id
|
|
WHERE EXTRACT(YEAR FROM s.pay_date) = EXTRACT(YEAR FROM CURRENT_DATE)
|
|
GROUP BY e.level</pre><table><tr><td>3</td><td>23456.428571428572</td></tr><tr><td>4</td><td>26248.823529411766</td></tr><tr><td>5</td><td>34534.75</td></tr><tr><td>6</td><td>28461.363636363636</td></tr><tr><td>7</td><td>29935.0</td></tr><tr><td>8</td><td>32242.69230769231</td></tr><tr><td>9</td><td>34559.90566037736</td></tr></table><p class=ok>PASS: independent Python reference matched</p></section><section><h2>8. 入职一年内、一到两年、两到三年的员工，最近一个月平均工资是多少？</h2><pre>WITH latest_salaries AS (
|
|
SELECT DISTINCT ON (emp_id) emp_id, salary
|
|
FROM salaries
|
|
ORDER BY emp_id, pay_date DESC
|
|
)
|
|
SELECT
|
|
CASE
|
|
WHEN (CURRENT_DATE - e.hire_date)::INTEGER < 365 THEN '入职一年内'
|
|
WHEN (CURRENT_DATE - e.hire_date)::INTEGER BETWEEN 365 AND 729 THEN '一到两年'
|
|
WHEN (CURRENT_DATE - e.hire_date)::INTEGER BETWEEN 730 AND 1094 THEN '两到三年'
|
|
END AS bucket,
|
|
AVG(ls.salary) AS average_latest_salary
|
|
FROM employees e
|
|
JOIN latest_salaries ls ON e.emp_id = ls.emp_id
|
|
WHERE e.leave_date IS NULL
|
|
AND (CURRENT_DATE - e.hire_date)::INTEGER < 1095
|
|
GROUP BY bucket
|
|
ORDER BY bucket</pre><table><tr><td>一到两年</td><td>27683.125</td></tr><tr><td>两到三年</td><td>31138.75</td></tr><tr><td>入职一年内</td><td>24053.75</td></tr></table><p class=ok>PASS: independent Python reference matched</p></section><section><h2>9. 去年到今年涨薪幅度最大的10位员工是谁？</h2><pre>WITH emp_salary_avg AS (
|
|
SELECT
|
|
emp_id,
|
|
AVG(salary) FILTER (WHERE EXTRACT(YEAR FROM pay_date) = EXTRACT(YEAR FROM CURRENT_DATE)) AS current_avg,
|
|
AVG(salary) FILTER (WHERE EXTRACT(YEAR FROM pay_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1) AS previous_avg
|
|
FROM salaries
|
|
GROUP BY emp_id
|
|
HAVING
|
|
AVG(salary) FILTER (WHERE EXTRACT(YEAR FROM pay_date) = EXTRACT(YEAR FROM CURRENT_DATE)) IS NOT NULL
|
|
AND AVG(salary) FILTER (WHERE EXTRACT(YEAR FROM pay_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1) IS NOT NULL
|
|
)
|
|
SELECT
|
|
e.name,
|
|
(esa.current_avg - esa.previous_avg) AS raise_amount
|
|
FROM emp_salary_avg esa
|
|
JOIN employees e ON esa.emp_id = e.emp_id
|
|
ORDER BY raise_amount DESC
|
|
LIMIT 10</pre><table><tr><td>韩平</td><td>12000.0</td></tr><tr><td>韩伟</td><td>2200.0</td></tr><tr><td>吕芳</td><td>2155.0</td></tr><tr><td>金丽</td><td>2110.0</td></tr><tr><td>许华</td><td>2065.0</td></tr><tr><td>陶松</td><td>1975.0</td></tr><tr><td>周雪</td><td>1930.0</td></tr><tr><td>吴敏</td><td>1885.0</td></tr><tr><td>张霞</td><td>1840.0</td></tr><tr><td>赵平</td><td>1795.0</td></tr></table><p class=ok>PASS: independent Python reference matched</p></section><section><h2>10. 有没有拖欠工资的情况（某个月还在职却没有发薪）？</h2><pre>WITH employee_employed_months AS (
|
|
SELECT
|
|
e.emp_id,
|
|
generate_series(
|
|
date_trunc('month', e.hire_date),
|
|
date_trunc('month', COALESCE(e.leave_date, CURRENT_DATE)),
|
|
interval '1 month'
|
|
) AS employed_month
|
|
FROM employees e
|
|
)
|
|
SELECT
|
|
em.emp_id,
|
|
to_char(em.employed_month, 'YYYY-MM') AS month
|
|
FROM employee_employed_months em
|
|
LEFT JOIN salaries s
|
|
ON em.emp_id = s.emp_id
|
|
AND date_trunc('month', s.pay_date) = em.employed_month
|
|
WHERE s.emp_id IS NULL</pre><table><tr><td>17</td><td>2026-01</td></tr></table><p class=ok>PASS: independent Python reference matched</p></section> |