一

# **hccdp 实验文档（gaussdb）**

## 任务一：创建数据库对象

### 要求

1. 创建数据库 `db_hccdp`，要求兼容 oracle 数据库，设置数据库连接数上限为 2000。
2. 在 `db_hccdp` 数据库下创建用户 `user_hccdp`，要求具有系统管理员权限，用户有效期为 `2020-04-01` 至 `2030-04-01`。
3. 创建模式 `sche_hccdp`，该 schema 属主为 `user_hccdp`。

### sql 实现

```sql
create database db_hccdp 
with dbcompatibility 'a' 
connection limit 2000;

create user user_hccdp 
with sysadmin 
valid begin '2020-04-01' 
valid until '2030-04-01' 
password 'aabb!234';

create schema sche_hccdp authorization user_hccdp;
```

---

## 任务二：创建表及约束

### 业务要求

对员工信息表进行数据约束：
| 字段名                | 数据类型          | 说明                       |
| ------------------ | ------------- | ------------------------ |
| **employee_id**    | integer       | 员工ID，类型为整数               |
| **employee_name**  | varchar(50)   | 员工姓名，类型为可变长度字符串，最大长度为50  |
| **gender**         | char(1)       | 员工性别，取值为 M 或 F           |
| **age**            | integer       | 员工年龄，类型为整数               |
| **hire_date**      | date          | 员工入职日期，类型为日期             |
| **monthly_saraly** | numeric(10,2) | 员工月薪，类型为数值型，精度为10，小数位2   |
| **email**          | varchar(100)  | 员工邮箱，类型为可变长度字符串，最大长度为100 |
| **is_active**      | boolean       | 员工是否在职，类型为布尔值            |
| **department_id**  | integer       | 员工部门ID，类型为整数             |
| **remarks**        | text          | 员工备注信息，类型为文本             |

1. 创建唯一标识主键 `pk_emp`，主键字段为 `employee_id`；
2. 入职年龄字段 `age` 需确保在 18-65 岁之间，约束命名为 `ck_emp`。

### sql 实现

```sql
create table sche_hccdp.employee_info (
    employee_id integer,
    employee_name varchar(50),
    gender char(1),
    age integer,
    hire_date date,
    monthly_salary numeric(10,2),
    email varchar(100),
    is_active boolean,
    department_id integer,
    remarks text,
    constraint pk_emp primary key (employee_id),
    constraint ck_emp check (age between 18 and 65)
);
```

### 验证约束

```sql
select a.conname, b.relname 
from pg_constraint a, pg_class b 
where a.conrelid = b.oid;
```

---

## 任务三：导入表数据

### 要求

1. 使用 `gs_restore` 导入表 `sche_hccdp.employee_info` 数据（仅数据，不含结构）。

### 示例命令

```bash
gs_restore -u user_hccdp <文件路径> -p 8000 -d db_hccdp -w gauss@123 -a -e -n sche_hccdp -t employee_info
```

---

## 任务四：业务场景 sql 编写

### 业务需求

分析各部门员工月薪情况，计算：

* 员工月薪占所在部门月薪总和的比例；
* 每个部门中月薪排名前 5 的员工；
* 结果按部门 id 升序、部门内月薪降序排列。

### sql 实现

```sql
select 
    department_id,
    employee_id,
    employee_name,
    monthly_salary,
    dept_total_salary,
    round(salary_ratio * 100, 2) || '%' as salary_ratio,
    salary_rank
from (
    select 
        department_id,
        employee_id,
        employee_name,
        monthly_salary,
        sum(monthly_salary) over(partition by department_id) as dept_total_salary,
        monthly_salary / sum(monthly_salary) over(partition by department_id) as salary_ratio,
        rank() over(partition by department_id order by monthly_salary desc) as salary_rank
    from sche_hccdp.employee_info
) ranked_employees
where salary_rank <= 5
order by department_id, monthly_salary desc;
```

### 生成分析表

```sql
create table sche_hccdp.emp_ana01 as 
<上述完整 sql>;
```

---

## 任务五：业务 sql 改写（使用 cte）

### 要求

1. 使用 `with` 公共表表达式重写；
2. 输出结果保持一致；
3. 结果保存到 `sche_hccdp.emp_ana02` 表。

### sql 实现

```sql
create table sche_hccdp.emp_ana02 as (
with employee_salary_info as (
    select 
        department_id,
        employee_id,
        employee_name,
        monthly_salary,
        sum(monthly_salary) over(partition by department_id) as dept_total_salary,
        monthly_salary / sum(monthly_salary) over(partition by department_id) as salary_ratio,
        rank() over(partition by department_id order by monthly_salary desc) as salary_rank
    from sche_hccdp.employee_info
)
select 
    department_id,
    employee_id,
    employee_name,
    monthly_salary,
    dept_total_salary,
    round(salary_ratio * 100, 2) || '%' as salary_ratio,
    salary_rank
from employee_salary_info
where salary_rank <= 5
order by department_id asc, monthly_salary desc
);
```

### 验证

```sql
select sum(employee_id) from sche_hccdp.emp_ana02;
```

---

## 任务六：业务触发器编写

### 新增需求

当对 `sche_hccdp.employee_info` 进行插入操作时，将插入数据的 `employee_id` 与当前时间记录到日志表 `insert_log`。

### 1. 创建日志表

```sql
create table sche_hccdp.insert_log (
    employee_id int,
    insert_time timestamp
);
```

### 2. 创建触发器函数

```sql
create or replace function sche_hccdp.insert_func() 
returns trigger 
as $$
begin
    insert into sche_hccdp.insert_log (employee_id, insert_time)
    values (new.employee_id, current_timestamp);
    return new;
end;
$$ language plpgsql;
```

### 3. 创建触发器

```sql
create trigger insert_trigger
after insert on sche_hccdp.employee_info
for each row
execute procedure sche_hccdp.insert_func();
```

### 验证

```sql
insert into sche_hccdp.employee_info values (...);
select * from sche_hccdp.insert_log;
select * from sche_hccdp.employee_info where employee_id = 10001;
```

---

## 任务七：分区表改造

### 要求

* 新表名：`sche_hccdp.employee_info_new`
* 分区方式：`range`
* 分区字段：`hire_date`
* 每 4 个月一个分区（2020-04-01 至 2025-04-01）
* 分区命名：如 `2020-04-01` 至 `2020-08-01` 为 `p20200801`
* 追加最大值分区 `pmax`

### sql 实现

```sql
create table sche_hccdp.employee_info_new (
    employee_id integer,
    employee_name varchar(50),
    gender char(1),
    age integer,
    hire_date date,
    monthly_salary numeric(10,2),
    email varchar(100),
    is_active boolean,
    department_id integer,
    remarks text,
    constraint pk_emp_new primary key(employee_id),
    constraint ck_emp_new check (age >= 18 and age <= 65)
)
partition by range (hire_date) (
    partition p20200801 values less than ('2020-08-01'),
    partition p20201201 values less than ('2020-12-01'),
    partition p20210401 values less than ('2021-04-01'),
    partition p20210801 values less than ('2021-08-01'),
    partition p20211201 values less than ('2021-12-01'),
    partition p20220401 values less than ('2022-04-01'),
    partition p20220801 values less than ('2022-08-01'),
    partition p20221201 values less than ('2022-12-01'),
    partition p20230401 values less than ('2023-04-01'),
    partition p20230801 values less than ('2023-08-01'),
    partition p20231201 values less than ('2023-12-01'),
    partition p20240401 values less than ('2024-04-01'),
    partition p20240801 values less than ('2024-08-01'),
    partition p20241201 values less than ('2024-12-01'),
    partition p20250401 values less than ('2025-04-01'),
    partition pmax values less than (maxvalue)
);
```

### 数据迁移与验证

```sql
insert into sche_hccdp.employee_info_new 
select * from sche_hccdp.employee_info;

select count(*) from sche_hccdp.employee_info_new;
```

---

## 任务八：wdr 报告生成

### 要求

1. 手动生成快照点；
2. 基于两个快照生成 wdr 报告；
3. 输出路径：`/home/ruby/wrd_snapshot.html`；
4. 报告级别：集群实例级 detail。

### 操作步骤

```bash
touch /home/ruby/wrd_snapshot.html

-- 检查快照功能
show enable_wdr_snapshot;
select * from snapshot.snapshot;

-- 手动生成快照
select create_wdr_snapshot();
-- 等待一段时间后再执行
select create_wdr_snapshot();

-- 查看快照 id
select * from snapshot.snapshot;

-- 生成 wdr 报告（假设快照 id 为 26 和 27）
\a \t \o /home/ruby/wrd_snapshot.html
select generate_wdr_report(68, 69, 'all', 'cluster');
\o \a \t
```

---

## 任务九：修改数据库参数

### 要求

* 打开数据库审计参数 `audit_function_exec`；
* 修改数据库连接数参数 `max_connections=2000`；
* 修改方式可使用命令行或 tpops。

### 命令行实现

```bash
-- 集中式
gs_guc reload -n all -i all -c "audit_function_exec=1"
gs_guc reload -n all -i all -c "max_connections=200"

-- 分布式
gs_guc reload -z coordinator -n all -i all -c "max_connections=2000"
gs_guc reload -z coordinator -n all -i all -c "audit_function_exec=1"
```

### 验证修改

```sql
select sum(setting) 
from pg_settings 
where name in ('audit_function_exec', 'max_connections');
```

FYF

导航1234

最近发表

友情链接