【PG数据库与SQL基础 DAY4】Exercises

项目需求

• 开发一个「用户订单数据统计函数」，实现以下功能：
• 接收参数：统计起始日期（start_date DATE）、统计结束日期（end_date DATE）
• 数据来源：关联「users」表（用户基础信息）、「orders」表（订单信息）
• 统计逻辑：
  • 筛选出统计日期范围内的订单（order_time 介于 start_date 和 end_date 之间）
  • 按用户分组，计算每个用户的订单总数、总消费金额、平均订单金额、首单时间、末单时间
  • 新增「用户等级」字段：总消费金额 >= 1000 为「VIP 用户」，500-999 为「普通用户」，<500 为「新用户」
  • 排除订单金额为 0 或 NULL 的异常数据
• 返回结果：结构化数据（包含 user_id、user_name、order_count、total_amount、avg_amount、first_order_time、last_order_time、user_level 字段）
• FUNCTION 里面 每一段处理逻辑要有raise notice，统计每一段逻辑处理时间并输出

任务要求

• 编写项目设计文档，包含：

  • 需求拆解（分点说明每个功能点的实现思路）
  • 数据模型设计（标注表关联关系、关键字段）
  • 函数逻辑流程图（用 processon绘制）

• 基于设计方案编写函数「stat_user_order_data」，要求：

  • 包含参数校验（若 start_date > end_date 抛出错误）
  • 处理异常数据（订单金额为 0 或 NULL 时跳过）
  • 代码添加详细注释（函数用途、参数说明、关键逻辑注释）

测试验证

• 设计测试用例：
  • 正常场景：输入 开始和结束时间，验证统计结果与实际数据一致性
  • 边界场景：start_date = end_date、无符合条件订单、存在异常订单数据
• 执行测试用例，记录测试结果（包含输入参数、输出结果、是否通过），形成测试报告

文档完善

• 补充项目开发文档，包含：
  • 函数代码（完整 SQL 语句）
  • 测试报告（测试用例、结果、问题及解决方案）
  • 优化思路（如是否添加索引）

运维优化

• 对「orders」表的「order_time」字段创建索引，使用 Explain Analyze 分析创建索引前后，查询「2025-03-01 至 2025-03-10」订单的执行效率差异，整理分析报告（包含执行时间、扫描方式）
• 使用 Explain Analyze 分析 项目函数，判断是否有可优化步骤