PostgreSQL: PostgreSQL高级特性综合练习

最后更新：2026-08-26

1. 你将学到

综合运用窗口函数进行考勤统计与排名
使用视图和物化视图封装复杂查询
设计索引策略优化查询性能
使用 JSONB 存储灵活的员工技能标签
集成全文搜索实现员工搜索功能
完成完整的员工管理系统数据库项目

2. 故事

Alice 是一家 SaaS 公司的数据库工程师，HR 部门需要她搭建员工管理系统的数据库。需求如下：

考勤统计：按月统计每位员工的出勤天数、迟到次数、加班时长，使用窗口函数排名
绩效排名：根据多维度评分计算综合绩效，用物化视图缓存
技能标签：每位员工的技能标签不固定，用 JSONB 灵活存储
员工搜索：按姓名、技能、部门等快速搜索员工，使用全文搜索

Alice 需要综合运用窗口函数、视图、索引、JSONB 和全文搜索来完成这个项目。

3. Concept：项目数据库设计

(1) ER 图

erDiagram
    EMPLOYEES ||--o{ ATTENDANCE : has
    EMPLOYEES ||--o{ PERFORMANCE : receives
    EMPLOYEES }o--|| DEPARTMENTS : belongs_to

    EMPLOYEES {
        int employee_id PK
        text name
        int department_id FK
        text position
        date hire_date
        jsonb skills
        tsvector search_doc
    }

    DEPARTMENTS {
        int department_id PK
        text dept_name
        text location
    }

    ATTENDANCE {
        int attendance_id PK
        int employee_id FK
        date attend_date
        text status
        time check_in_time
        time check_out_time
        numeric overtime_hours
    }

    PERFORMANCE {
        int perf_id PK
        int employee_id FK
        int year
        int quarter
        numeric technical_score
        numeric communication_score
        numeric leadership_score
        numeric overall_score
    }

▶ 示例：创建基础表结构

SQL

CREATE TABLE departments (
  department_id SERIAL PRIMARY KEY,
  dept_name TEXT NOT NULL,
  location TEXT NOT NULL
);

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INT NOT NULL REFERENCES departments(department_id),
  position TEXT NOT NULL,
  hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
  skills JSONB NOT NULL DEFAULT '[]',
  search_doc tsvector
);

CREATE TABLE attendance (
  attendance_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(employee_id),
  attend_date DATE NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('present','late','absent','leave')),
  check_in_time TIME,
  check_out_time TIME,
  overtime_hours NUMERIC(4,2) DEFAULT 0
);

CREATE TABLE performance (
  perf_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(employee_id),
  year INT NOT NULL,
  quarter INT NOT NULL CHECK (quarter BETWEEN 1 AND 4),
  technical_score NUMERIC(5,2) CHECK (technical_score BETWEEN 0 AND 100),
  communication_score NUMERIC(5,2) CHECK (communication_score BETWEEN 0 AND 100),
  leadership_score NUMERIC(5,2) CHECK (leadership_score BETWEEN 0 AND 100),
  overall_score NUMERIC(5,2) CHECK (overall_score BETWEEN 0 AND 100),
  UNIQUE (employee_id, year, quarter)
);

TEXT 📖 仅展示

CREATE TABLE

(2) 插入测试数据

▶ 示例：插入部门与员工数据

SQL

INSERT INTO departments (dept_name, location) VALUES
  ('Engineering', 'Floor 3'),
  ('Sales', 'Floor 2'),
  ('Marketing', 'Floor 1'),
  ('HR', 'Floor 1');

INSERT INTO employees (name, department_id, position, hire_date, skills) VALUES
  ('Alice Chen', 1, 'Senior Engineer', '2022-03-15',
   '["PostgreSQL","Python","Docker","Kubernetes"]'),
  ('Bob Wang', 1, 'Junior Engineer', '2023-06-01',
   '["Java","Spring","MySQL"]'),
  ('Charlie Zhang', 2, 'Sales Manager', '2021-01-10',
   '["Negotiation","CRM","English","French"]'),
  ('Diana Liu', 3, 'Marketing Specialist', '2023-09-20',
   '["SEO","Content Writing","Google Analytics"]'),
  ('Edward Wu', 1, 'DevOps Engineer', '2022-11-01',
   '["Docker","Kubernetes","AWS","Terraform"]'),
  ('Fiona Li', 4, 'HR Manager', '2020-05-15',
   '["Recruiting","Employee Relations","Payroll"]');

TEXT 📖 仅展示

INSERT 0 1

▶ 示例：插入考勤数据

SQL

INSERT INTO attendance (employee_id, attend_date, status, check_in_time, check_out_time, overtime_hours)
SELECT
  e.employee_id,
  d.dt::date,
  CASE WHEN random() < 0.05 THEN 'absent'
       WHEN random() < 0.12 THEN 'late'
       ELSE 'present'
  END,
  CASE WHEN random() < 0.12 THEN '09:15' ELSE '08:55' END,
  CASE WHEN random() < 0.15 THEN '19:00' ELSE '18:00' END,
  CASE WHEN random() < 0.15 THEN 1.0 ELSE 0 END
FROM employees e
CROSS JOIN (
  SELECT generate_series('2025-01-01'::timestamp, '2025-03-31'::timestamp, '1 day') AS dt
) d
WHERE EXTRACT(dow FROM d.dt) NOT IN (0, 6);

TEXT 📖 仅展示

INSERT 0 1

▶ 示例：插入绩效数据

SQL

INSERT INTO performance (employee_id, year, quarter, technical_score, communication_score, leadership_score, overall_score)
VALUES
  (1, 2025, 1, 92, 85, 88, 89.0),
  (2, 2025, 1, 78, 72, 65, 72.3),
  (3, 2025, 1, 70, 90, 85, 82.0),
  (4, 2025, 1, 75, 88, 72, 78.3),
  (5, 2025, 1, 88, 76, 80, 82.0),
  (6, 2025, 1, 65, 92, 90, 82.3);

TEXT 📖 仅展示

INSERT 0 1

4. Concept：窗口函数——考勤统计与排名

(1) 月度考勤汇总

▶ 示例：按月统计每位员工出勤情况

SQL

SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
  COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
  SUM(a.overtime_hours) AS total_overtime
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, e.name, d.dept_name, TO_CHAR(a.attend_date, 'YYYY-MM')
ORDER BY e.employee_id, month;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

函数	说明
`COUNT(*) FILTER (WHERE ...)`	条件计数（PG 特有，比 SUM(CASE) 更清晰）
`TO_CHAR(date, 'YYYY-MM')`	按月分组

(2) 部门内考勤排名

▶ 示例：使用窗口函数在部门内排名

SQL

SELECT
  e.name,
  d.dept_name,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  SUM(a.overtime_hours) AS total_overtime,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY COUNT(*) FILTER (WHERE a.status = 'present') DESC) AS attendance_rank,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY SUM(a.overtime_hours) DESC) AS overtime_rank
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
WHERE a.attend_date BETWEEN '2025-01-01' AND '2025-03-31'
GROUP BY e.employee_id, e.name, d.dept_name
ORDER BY d.dept_name, attendance_rank;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

窗口函数	相同值处理	适用场景
`RANK()`	并列跳号（1,1,3）	允许并列的排名
`DENSE_RANK()`	并列不跳号（1,1,2）	连续排名
`ROW_NUMBER()`	不并列（1,2,3）	唯一排名

▶ 示例：累计出勤天数（Running Total）

SQL

SELECT
  e.name,
  a.attend_date,
  a.status,
  SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END)
    OVER (PARTITION BY e.employee_id ORDER BY a.attend_date) AS cumulative_present
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
WHERE e.employee_id = 1
ORDER BY a.attend_date;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

▶ 示例：本月出勤与上月对比（LAG）

SQL

WITH monthly_stats AS (
  SELECT
    e.employee_id,
    e.name,
    TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
    COUNT(*) FILTER (WHERE a.status = 'present') AS present_days
  FROM employees e
  JOIN attendance a ON e.employee_id = a.employee_id
  GROUP BY e.employee_id, e.name, TO_CHAR(a.attend_date, 'YYYY-MM')
)
SELECT
  name,
  month,
  present_days,
  LAG(present_days) OVER (PARTITION BY employee_id ORDER BY month) AS prev_month,
  present_days - LAG(present_days) OVER (PARTITION BY employee_id ORDER BY month) AS diff
FROM monthly_stats
ORDER BY employee_id, month;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

5. Concept：视图与物化视图——封装绩效排名

(1) 视图 vs 物化视图

维度	视图 (VIEW)	物化视图 (MATERIALIZED VIEW)
存储数据	不存储，每次查询实时计算	存储结果，查询直接读取
数据新鲜度	实时	需手动 REFRESH
查询性能	与原查询相同	快（已预计算）
适用场景	频繁更新的数据	报表/统计，数据不常变

▶ 示例：创建绩效排名视图

SQL

CREATE VIEW v_performance_ranking AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  e.position,
  p.year,
  p.quarter,
  p.technical_score,
  p.communication_score,
  p.leadership_score,
  p.overall_score,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY p.overall_score DESC) AS dept_rank,
  RANK() OVER (ORDER BY p.overall_score DESC) AS company_rank
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM v_performance_ranking WHERE quarter = 1 ORDER BY company_rank;

TEXT 📖 仅展示

CREATE TABLE

▶ 示例：创建绩效排名物化视图

SQL

CREATE MATERIALIZED VIEW mv_performance_ranking AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  p.year,
  p.quarter,
  p.overall_score,
  RANK() OVER (ORDER BY p.overall_score DESC) AS company_rank
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
JOIN departments d ON e.department_id = d.department_id
WITH DATA;

-- Refresh when performance data changes
REFRESH MATERIALIZED VIEW mv_performance_ranking;

-- Concurrent refresh (does not block reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_performance_ranking;

TEXT 📖 仅展示

CREATE TABLE

REFRESH 方式	锁	说明
`REFRESH MATERIALIZED VIEW`	ACCESS EXCLUSIVE	阻塞读写，但无需唯一索引
`REFRESH ... CONCURRENTLY`	SHARE	不阻塞读，需唯一索引

▶ 示例：考勤统计物化视图

SQL

CREATE MATERIALIZED VIEW mv_attendance_summary AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
  COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
  SUM(a.overtime_hours) AS total_overtime
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, e.name, d.dept_name, TO_CHAR(a.attend_date, 'YYYY-MM')
WITH DATA;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

6. Concept：索引策略优化

(1) 索引设计原则

原则	说明
为高频查询的 WHERE 条件建索引	最基本的使用场景
为 JOIN 列建索引	外键列默认无索引，需手动创建
避免过度索引	每个索引增加写入开销
复合索引注意列顺序	等值条件列在前，范围条件列在后
用 EXPLAIN ANALYZE 验证	实际执行计划是最终判断标准

▶ 示例：为核心查询创建索引

SQL

-- Index for attendance queries by employee and date range
CREATE INDEX idx_attendance_emp_date
ON attendance (employee_id, attend_date);

-- Index for performance queries by year/quarter
CREATE INDEX idx_performance_emp_quarter
ON performance (employee_id, year, quarter);

-- Index for employees by department
CREATE INDEX idx_employees_dept
ON employees (department_id);

-- Partial index: only index non-absent attendance records
CREATE INDEX idx_attendance_present
ON attendance (employee_id, attend_date)
WHERE status != 'absent';

TEXT 📖 仅展示

CREATE TABLE

索引类型	语法	适用场景
B-tree（默认）	`CREATE INDEX idx ON t(col)`	等值、范围、排序
复合索引	`CREATE INDEX idx ON t(col1, col2)`	多列组合查询
部分索引	`CREATE INDEX idx ON t(col) WHERE ...`	只索引满足条件的行
表达式索引	`CREATE INDEX idx ON t(lower(col))`	函数结果的查询

▶ 示例：EXPLAIN ANALYZE 验证索引效果

SQL

-- Before index: Seq Scan
EXPLAIN ANALYZE
SELECT * FROM attendance WHERE employee_id = 1 AND attend_date >= '2025-01-01';

-- After index: Index Scan
CREATE INDEX idx_attendance_emp_date ON attendance (employee_id, attend_date);
EXPLAIN ANALYZE
SELECT * FROM attendance WHERE employee_id = 1 AND attend_date >= '2025-01-01';

TEXT 📖 仅展示

CREATE TABLE

▶ 示例：覆盖索引避免回表

SQL

-- Include columns to avoid table access
CREATE INDEX idx_attendance_covering
ON attendance (employee_id, attend_date)
INCLUDE (status, overtime_hours);

-- This query only needs index, no table access
EXPLAIN ANALYZE
SELECT status, overtime_hours
FROM attendance
WHERE employee_id = 1 AND attend_date >= '2025-01-01';

TEXT 📖 仅展示

CREATE TABLE

7. Concept：JSONB 存储技能标签

(1) 技能标签的 JSONB 设计

▶ 示例：查询员工技能

SQL

-- Query specific skill
SELECT name, skills
FROM employees
WHERE skills @> '["PostgreSQL"]';

-- Query multiple skills (has ANY of these)
SELECT name, skills
FROM employees
WHERE skills ?| array['PostgreSQL', 'Docker'];

-- Query multiple skills (has ALL of these)
SELECT name, skills
FROM employees
WHERE skills @> '["Docker", "Kubernetes"]';

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：展开技能为数行

SQL

SELECT
  e.name,
  jsonb_array_elements_text(e.skills) AS skill
FROM employees e
ORDER BY e.name;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：统计各技能人数

SQL

SELECT
  skill,
  COUNT(*) AS employee_count
FROM (
  SELECT jsonb_array_elements_text(skills) AS skill FROM employees
) sub
GROUP BY skill
ORDER BY employee_count DESC;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

(2) JSONB 索引加速技能查询

▶ 示例：创建 GIN 索引

SQL

CREATE INDEX idx_employees_skills ON employees USING gin (skills);

-- GIN index supports @> and ?| queries
EXPLAIN ANALYZE
SELECT name FROM employees WHERE skills @> '["PostgreSQL"]';

TEXT 📖 仅展示

CREATE TABLE

▶ 示例：升级技能标签为结构化 JSONB

SQL

-- Upgrade from simple array to structured skills
ALTER TABLE employees ADD COLUMN skill_profile JSONB DEFAULT '{}';

UPDATE employees SET skill_profile = '{
  "primary": ["PostgreSQL", "Python"],
  "secondary": ["Docker"],
  "certifications": ["AWS Solutions Architect"],
  "years_of_experience": {"PostgreSQL": 5, "Python": 8}
}'::jsonb
WHERE employee_id = 1;

-- Query by primary skill
SELECT name, skill_profile -> 'primary' AS primary_skills
FROM employees
WHERE skill_profile -> 'primary' @> '["PostgreSQL"]';

-- Query by certification
SELECT name
FROM employees
WHERE skill_profile -> 'certifications' @> '["AWS Solutions Architect"]';

-- Query by years of experience
SELECT name
FROM employees
WHERE (skill_profile -> 'years_of_experience' ->> 'PostgreSQL')::int >= 3;

TEXT 📖 仅展示

UPDATE 3

8. Concept：全文搜索实现员工搜索

(1) 构建 tsvector 搜索文档

▶ 示例：创建搜索文档列和触发器

SQL

-- Add search document column
ALTER TABLE employees ADD COLUMN search_doc tsvector;

-- Create function to build search document
CREATE FUNCTION employees_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search_doc :=
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.position, '')), 'B') ||
    setweight(to_tsvector('simple', COALESCE(
      array_to_string(
        ARRAY(SELECT jsonb_array_elements_text(NEW.skills)), ' '
      ), '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER trg_employees_search
BEFORE INSERT OR UPDATE OF name, position, skills ON employees
FOR EACH ROW EXECUTE FUNCTION employees_search_update();

-- Update existing rows
UPDATE employees SET name = name; -- Trigger fires, populates search_doc

TEXT 📖 仅展示

INSERT 0 1

▶ 示例：创建 GIN 索引

SQL

CREATE INDEX idx_employees_search ON employees USING gin (search_doc);

TEXT 📖 仅展示

CREATE TABLE

(2) 搜索函数

▶ 示例：员工搜索函数

SQL

CREATE FUNCTION search_employees(p_query TEXT)
RETURNS TABLE (
  employee_id INT,
  name TEXT,
  position TEXT,
  dept_name TEXT,
  skills JSONB,
  rank REAL,
  headline TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.employee_id,
    e.name,
    e.position,
    d.dept_name,
    e.skills,
    ts_rank(e.search_doc, websearch_to_tsquery('english', p_query)) AS rank,
    ts_headline(
      'english',
      COALESCE(e.name, '') || ' ' || COALESCE(e.position, ''),
      websearch_to_tsquery('english', p_query),
      'StartSel=<mark>,StopSel=</mark>'
    ) AS headline
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id
  WHERE e.search_doc @@ websearch_to_tsquery('english', p_query)
  ORDER BY rank DESC;
END;
$$ LANGUAGE plpgsql;

-- Test: search for engineer
SELECT * FROM search_employees('engineer');

-- Test: search for Docker skills
SELECT * FROM search_employees('Docker');

TEXT 📖 仅展示

CREATE TABLE

9. Concept：综合查询实战

(1) 多维度员工报告

▶ 示例：员工综合信息报告

SQL

CREATE VIEW v_employee_dashboard AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  e.position,
  e.hire_date,
  EXTRACT(YEAR FROM age(CURRENT_DATE, e.hire_date)) AS years_of_service,
  e.skills,
  p.overall_score AS latest_perf_score,
  p_r.company_rank,
  a_s.present_days AS q1_present,
  a_s.late_days AS q1_late,
  a_s.total_overtime AS q1_overtime
FROM employees e
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN LATERAL (
  SELECT overall_score FROM performance
  WHERE employee_id = e.employee_id
  ORDER BY year DESC, quarter DESC LIMIT 1
) p ON true
LEFT JOIN LATERAL (
  SELECT company_rank FROM mv_performance_ranking
  WHERE employee_id = e.employee_id
  ORDER BY year DESC, quarter DESC LIMIT 1
) p_r ON true
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) FILTER (WHERE status = 'present') AS present_days,
    COUNT(*) FILTER (WHERE status = 'late') AS late_days,
    SUM(overtime_hours) AS total_overtime
  FROM attendance
  WHERE employee_id = e.employee_id
    AND attend_date BETWEEN '2025-01-01' AND '2025-03-31'
) a_s ON true;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

▶ 示例：部门绩效对比

SQL

SELECT
  d.dept_name,
  AVG(p.overall_score) AS avg_score,
  MAX(p.overall_score) AS max_score,
  MIN(p.overall_score) AS min_score,
  COUNT(*) AS employee_count,
  RANK() OVER (ORDER BY AVG(p.overall_score) DESC) AS dept_rank
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN performance p ON e.employee_id = p.employee_id
WHERE p.year = 2025 AND p.quarter = 1
GROUP BY d.dept_name
ORDER BY avg_score DESC;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

(2) 技能差距分析

▶ 示例：部门技能分布

SQL

SELECT
  d.dept_name,
  skill,
  COUNT(*) AS employees_with_skill,
  ROUND(COUNT(*)::numeric / SUM(COUNT(*)) OVER (PARTITION BY d.dept_name) * 100, 1) AS pct
FROM employees e
JOIN departments d ON e.department_id = d.department_id
CROSS JOIN LATERAL jsonb_array_elements_text(e.skills) AS skill
GROUP BY d.dept_name, skill
ORDER BY d.dept_name, employees_with_skill DESC;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

10. 实战：完整员工管理系统数据库

Alice 把所有模块整合为一个完整的项目。

SQL

-- ============================================
-- Employee Management System - Complete Schema
-- ============================================

-- Step 1: Tables (already created above)
-- Ensure all tables, indexes, triggers exist

-- Step 2: Core indexes
CREATE INDEX idx_attendance_emp_date
ON attendance (employee_id, attend_date);
CREATE INDEX idx_performance_emp_quarter
ON performance (employee_id, year, quarter);
CREATE INDEX idx_employees_dept
ON employees (department_id);
CREATE INDEX idx_employees_skills
ON employees USING gin (skills);
CREATE INDEX idx_employees_search
ON employees USING gin (search_doc);

-- Step 3: Materialized views
CREATE MATERIALIZED VIEW mv_dept_performance AS
SELECT
  d.dept_name,
  p.year,
  p.quarter,
  AVG(p.overall_score) AS avg_score,
  AVG(p.technical_score) AS avg_tech,
  AVG(p.communication_score) AS avg_comm,
  AVG(p.leadership_score) AS avg_lead,
  COUNT(*) AS headcount
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN performance p ON e.employee_id = p.employee_id
GROUP BY d.dept_name, p.year, p.quarter
WITH DATA;

CREATE UNIQUE INDEX idx_mv_dept_perf
ON mv_dept_performance (dept_name, year, quarter);

-- Step 4: Attendance report function
CREATE FUNCTION get_attendance_report(
  p_year INT, p_month INT
) RETURNS TABLE (
  employee_id INT, name TEXT, dept_name TEXT,
  present_days BIGINT, late_days BIGINT,
  absent_days BIGINT, overtime_hours NUMERIC,
  attendance_rate NUMERIC, dept_rank BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.employee_id,
    e.name,
    d.dept_name,
    COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
    COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
    COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
    COALESCE(SUM(a.overtime_hours), 0) AS overtime_hours,
    ROUND(
      COUNT(*) FILTER (WHERE a.status IN ('present','late'))::numeric
      / NULLIF(COUNT(*), 0) * 100, 1
    ) AS attendance_rate,
    RANK() OVER (PARTITION BY d.dept_name
      ORDER BY COUNT(*) FILTER (WHERE a.status = 'present') DESC)
  FROM employees e
  JOIN attendance a ON e.employee_id = a.employee_id
  JOIN departments d ON e.department_id = d.department_id
  WHERE EXTRACT(YEAR FROM a.attend_date) = p_year
    AND EXTRACT(MONTH FROM a.attend_date) = p_month
  GROUP BY e.employee_id, e.name, d.dept_name
  ORDER BY d.dept_name, dept_rank;
END;
$$ LANGUAGE plpgsql;

-- Step 5: Employee search (already created above)

-- Step 6: Test the complete system
-- Attendance report for January 2025
SELECT * FROM get_attendance_report(2025, 1);

-- Department performance comparison
SELECT * FROM mv_dept_performance
WHERE year = 2025 AND quarter = 1
ORDER BY avg_score DESC;

-- Find employees with Docker AND Kubernetes skills
SELECT name, position, skills
FROM employees
WHERE skills @> '["Docker","Kubernetes"]';

-- Full-text search for "engineer"
SELECT name, position, rank
FROM search_employees('engineer');

-- Refresh materialized view when data changes
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_performance;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_performance_ranking;

❓ 常见问题

Q 物化视图什么时候该用 REFRESH CONCURRENTLY？

A 当物化视图查询频繁且不能接受读阻塞时用 CONCURRENTLY。前提是物化视图上必须有至少一个 UNIQUE 索引。如果可以接受短暂不可用，普通 REFRESH 更快。

Q FILTER 语法和 CASE WHEN 哪个性能更好？

A 性能基本相同，PostgreSQL 内部将 FILTER 优化为与 CASE WHEN 等效的执行计划。FILTER 语法更简洁易读，推荐使用。

Q LATERAL JOIN 和子查询有什么区别？

A LATERAL 允许子查询引用外部查询的列，类似 correlated subquery 但可以返回多行。普通子查询不能引用外部列。LATERAL 适合"为每行关联一个计算结果"的场景。

Q JSONB 技能数组查询和关联表哪个好？

A 如果技能需要独立管理（增删改、统计、关联查询），用关联表（employee_skills）更好。如果技能只是标签属性、查询模式简单，JSONB 更灵活。本项目中技能是标签性质，JSONB 更适合。

Q 为什么搜索 Docker 技能时用 english 配置而不是 simple？

A english 配置会对查询词做词干化，但 "Docker" 是专有名词，词干化不影响。simple 配置也可以，但如果搜索词包含英文常用词（如 "running"），english 配置的词干化效果更好。本项目用 english 配置统一处理。

Q 如何定期自动刷新物化视图？

A PostgreSQL 不内置自动刷新，可以使用 pg_cron 扩展定时执行 REFRESH，或用操作系统的 cron/Task Scheduler 调用 psql 命令。例如：每天凌晨刷新 SELECT cron.schedule('0 2 * * *', $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_performance$$)。

Q 复合索引的列顺序有什么讲究？

A 等值查询的列在前，范围查询的列在后。例如 (employee_id, attend_date)，先按 employee_id 等值过滤，再按 attend_date 范围扫描。顺序错误可能导致索引无法使用。

📖 小节

窗口函数实现考勤统计、排名、同比对比（LAG/LEAD）、累计求和
视图封装复杂查询，物化视图缓存计算结果，CONCURRENTLY 刷新不阻塞读
索引策略：为高频 WHERE/JOIN 列建索引，复合索引注意列顺序，部分索引减少维护开销
JSONB 存储灵活技能标签，GIN 索引加速包含查询，@> 查询最常用
全文搜索用 setweight 区分字段权重，触发器自动同步 tsvector
综合项目整合多特性：窗口函数 + 物化视图 + JSONB + 全文搜索 + 索引优化

📝 作业

⭐ 使用窗口函数查询每位员工的 Q1 出勤天数、在公司内的出勤排名（DENSE_RANK），以及比排名前一位的员工少多少天（LAG 函数）。
⭐⭐ 创建一个物化视图 mv_skill_gap，统计每个部门缺少哪些技能（公司其他部门有但该部门没有的技能）。创建 UNIQUE 索引，使用 CONCURRENTLY 刷新，并用 EXPLAIN ANALYZE 对比刷新前后的查询性能。
⭐⭐⭐ 为员工管理系统设计完整方案：创建 training_records 表（employee_id, course_name, completion_date, score, tags JSONB），编写函数 recommend_training(p_employee_id INT) 根据员工当前技能（employees.skills）和已有培训（training_records.tags）推荐缺失的培训课程，使用全文搜索匹配课程描述，返回推荐结果按相关度排序。

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

PostgreSQL: PostgreSQL高级特性综合练习

1. 你将学到

2. 故事

3. Concept：项目数据库设计

(1) ER 图

▶ 示例：创建基础表结构

(2) 插入测试数据

▶ 示例：插入部门与员工数据

▶ 示例：插入考勤数据

▶ 示例：插入绩效数据

4. Concept：窗口函数——考勤统计与排名

(1) 月度考勤汇总

▶ 示例：按月统计每位员工出勤情况

(2) 部门内考勤排名

▶ 示例：使用窗口函数在部门内排名

▶ 示例：累计出勤天数（Running Total）

▶ 示例：本月出勤与上月对比（LAG）

5. Concept：视图与物化视图——封装绩效排名

(1) 视图 vs 物化视图

▶ 示例：创建绩效排名视图

▶ 示例：创建绩效排名物化视图

▶ 示例：考勤统计物化视图

6. Concept：索引策略优化

(1) 索引设计原则

▶ 示例：为核心查询创建索引

▶ 示例：EXPLAIN ANALYZE 验证索引效果

▶ 示例：覆盖索引避免回表

7. Concept：JSONB 存储技能标签

(1) 技能标签的 JSONB 设计

▶ 示例：查询员工技能

▶ 示例：展开技能为数行

▶ 示例：统计各技能人数

(2) JSONB 索引加速技能查询

▶ 示例：创建 GIN 索引

▶ 示例：升级技能标签为结构化 JSONB

8. Concept：全文搜索实现员工搜索

(1) 构建 tsvector 搜索文档

▶ 示例：创建搜索文档列和触发器

▶ 示例：创建 GIN 索引

(2) 搜索函数

▶ 示例：员工搜索函数

9. Concept：综合查询实战

(1) 多维度员工报告

▶ 示例：员工综合信息报告

▶ 示例：部门绩效对比

(2) 技能差距分析

▶ 示例：部门技能分布

10. 实战：完整员工管理系统数据库

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好