【PostgreSQL数据分析实战：从数据清洗到可视化全流程】电商数据分析案例-9.1 业务场景与数据准备

👉 点击关注不迷路
👉 点击关注不迷路
👉 点击关注不迷路

---

9.1 业务场景与数据准备

9.1.1 业务场景描述

电商平台的数据分析需求贯穿运营全链路，从用户行为洞察到供应链优化，从营销效果评估到风险防控，数据驱动决策已成为行业标配。

• 本案例以某跨境电商平台为背景，聚焦用户消费行为分析与销售预测，通过PostgreSQL构建完整的数据处理与分析体系。

核心业务目标

• 1. 用户分层与精准营销：通过RFM模型（最近消费、消费频次、消费金额）划分用户等级，制定差异化营销策略。
• 2. 销售趋势预测：基于历史订单数据，结合时间序列分析预测未来销量，优化库存管理。
• 3. 异常交易检测：识别高风险订单（如恶意刷单、退货异常），降低运营风险。

业务挑战

• 数据规模庞大：平台日均订单量超百万，涉及用户、商品、物流等多维度数据，需高效处理海量数据。
• 数据实时性要求高：大促期间数据增量激增（如双十一单日订单量达2.5亿单），需支持实时分析与决策。
• 数据质量参差不齐：原始数据存在缺失值（如用户地址字段缺失率12%）、异常值（如负单价订单）和重复记录，需系统性清洗。

9.1.2 数据来源与获取

数据源构成

数据类型	来源系统	典型字段
交易数据	订单系统	订单ID、用户ID、商品ID、订单金额、下单时间、支付状态、物流状态
用户数据	用户中心	用户ID、注册时间、性别、年龄、历史浏览记录、收藏夹、收货地址
商品数据	商品中心	商品ID、商品名称、类目、价格、库存、品牌、供应商、商品描述
行为数据	埋点日志	用户ID、页面访问时间、点击路径、停留时长、搜索关键词、加入购物车时间
外部数据	第三方API	汇率数据、天气数据、竞品价格、社交媒体舆情

数据获取方案

• 1. 离线批量同步
  • 使用PostgreSQL的COPY命令从CSV文件导入历史订单数据：

    COPY orders FROM '/data/orders.csv' DELIMITER ',' CSV HEADER;
    

  • 通过dblink扩展跨库同步用户信息：

    CREATE EXTENSION dblink;
    INSERT INTO users SELECT * FROM dblink('host=user_db port=5432 user=admin password=secret dbname=user_db', 'SELECT * FROM user_info') AS t(id int, name text, age int);
    

• 2. 实时数据接入
  • 利用PostgreSQL的NOTIFY / LISTEN机制接收订单变更事件：

    -- 1. 先确保触发器函数已正确创建（若未创建）
    CREATE OR REPLACE FUNCTION order_notify() 
    RETURNS TRIGGER AS $$
    BEGIN-- 发送通知到指定频道（channel），内容为新订单的 IDPERFORM pg_notify('order_channel', NEW.order_id::text);RETURN NEW; -- 触发器函数必须返回 TRIGGER 类型
    END;
    $$ LANGUAGE plpgsql;-- 2. 创建触发器（使用 PROCEDURE 替代 FUNCTION）
    CREATE TRIGGER order_trigger 
    AFTER INSERT ON orders 
    FOR EACH ROW 
    EXECUTE PROCEDURE order_notify();
    

  • 通过阿里云RDS PostgreSQL的OSS外部表功能实时写入行为日志：

    CREATE FOREIGN TABLE user_behavior (user_id int,action_time timestamp,page_url text
    ) SERVER oss_server OPTIONS (bucket 'behavior-log',format 'csv'
    );
    

9.1.3 数据结构与字段说明

核心数据表设计

1. 订单事实表（fact_orders）

字段名	数据类型	说明	业务约束
order_id	VARCHAR(32)	订单唯一标识（UUID生成）	主键
user_id	INT	用户ID	外键关联dim_users.user_id
product_id	VARCHAR(20)	商品ID	外键关联dim_products.product_id
order_amount	NUMERIC(10,2)	订单金额（含运费）	必须大于0
order_time	TIMESTAMP	下单时间	时区统一为UTC+8
payment_status	VARCHAR(10)	支付状态（待支付/已支付/支付失败）	枚举值
logistics_status	VARCHAR(20)	物流状态（待发货/运输中/已签收/退货中）	枚举值

2. 用户维度表（dim_users）

字段名	数据类型	说明	清洗规则
user_id	INT	用户ID	非空，主键
register_time	TIMESTAMP	注册时间	格式统一为YYYY-MM-DD HH:MI:SS
gender	VARCHAR(10)	性别（男/女/未知）	缺失值填充’未知’
age	INT	年龄	异常值（>150）设为NULL
address	TEXT	常用收货地址	地址标准化（如拆分省市区）

3. 商品维度表（dim_products）

字段名	数据类型	说明	分析价值
product_id	VARCHAR(20)	商品ID	主键
product_name	VARCHAR(100)	商品名称	去重，提取关键词
category	VARCHAR(50)	商品类目（如服装/数码/家居）	分层（一级类目/二级类目）
price	NUMERIC(8,2)	单价	与历史价格对比分析
stock	INT	库存数量	监控缺货风险

表关联关系

9.1.4 数据清洗与预处理

1. 缺失值处理

处理策略矩阵

字段	缺失率	处理方法	验证方式
用户地址	12%	通过第三方地址库API补全，无法补全则保留NULL并标记为'地址不详'	对比补全前后地址有效性比例
商品描述	5%	删除缺失记录（不影响核心分析）	检查删除前后商品类目分布变化
支付状态	0.3%	关联支付流水表补全，仍缺失则标记为'状态异常'	统计异常订单占比

PostgreSQL实现

-- 填充用户地址
UPDATE dim_users u
SET address = COALESCE(u.address, a.standard_address)
FROM address_api a
WHERE u.user_id = a.user_id AND u.address IS NULL;-- 标记支付状态异常订单
UPDATE fact_orders
SET payment_status = '状态异常'
WHERE payment_status IS NULL;

2. 异常值处理

检测与修正流程

• 1. 单价异常：订单表中存在price < 0的记录（如退货订单），需将其金额标记为负数并关联退货单号。

  UPDATE fact_orders
  SET order_amount = -order_amount
  WHERE product_id IN (SELECT product_id FROM dim_products WHERE is_returnable = TRUE);
  

• 2. 时间异常：下单时间早于注册时间的记录，通过用户注册时间修正。

  UPDATE fact_orders o
  SET order_time = u.register_time + INTERVAL '1 minute'
  FROM dim_users u
  WHERE o.user_id = u.user_id AND o.order_time < u.register_time;
  

3. 重复值处理

基于窗口函数去重

-- 保留同一用户同一商品的最新订单
WITH ranked_orders AS (SELECT order_id,user_id,product_id,ROW_NUMBER() OVER (PARTITION BY user_id, product_id ORDER BY order_time DESC) AS rnFROM fact_orders
)
DELETE FROM fact_orders
WHERE order_id IN (SELECT order_id FROM ranked_orders WHERE rn > 1);

4. 数据标准化

地址字段拆分

ALTER TABLE dim_users
ADD COLUMN province VARCHAR(20),
ADD COLUMN city VARCHAR(20),
ADD COLUMN district VARCHAR(20);UPDATE dim_users
SET province = SPLIT_PART(address, '省', 1),city = SPLIT_PART(SPLIT_PART(address, '省', 2), '市', 1),district = SPLIT_PART(SPLIT_PART(address, '省', 2), '市', 2);

9.1.5 数据质量评估

关键指标监控

指标名称	计算公式	阈值	监控频率
缺失率	(缺失记录数 / 总记录数) * 100%	<5%	每日
重复率	(重复记录数 / 总记录数) * 100%	<0.1%	每日
异常值占比	(异常记录数 / 总记录数) * 100%	<2%	实时
数据一致性	关联表字段匹配率	>99%	每周

可视化监控看板

通过Grafana连接PostgreSQL，实时展示数据质量指标：

• 缺失值趋势图：按表和字段展示缺失率变化。
• 异常订单热力图：按时间和地区分布展示异常订单密度。
• 数据一致性仪表盘：监控订单表与用户表、商品表的关联匹配情况。

9.1.6 数据安全与权限管理

敏感数据处理

• 1. 用户隐私保护：对用户手机号、身份证号等敏感字段进行脱敏处理。

  CREATE OR REPLACE FUNCTION mask_phone(phone text) RETURNS text AS $$
  BEGINRETURN SUBSTRING(phone, 1, 3) || '****' || SUBSTRING(phone, 8, 4);
  END;
  $$ LANGUAGE plpgsql;UPDATE dim_users
  SET phone = mask_phone(phone);
  

• 2. 访问权限控制：通过PostgreSQL的角色管理（ROLE）和行级安全性（RLS）限制数据访问。

  CREATE ROLE analyst;
  GRANT SELECT ON fact_orders TO analyst;CREATE POLICY orders_policy ON fact_orders
  FOR SELECT TO analyst
  USING (user_id IN (SELECT user_id FROM dim_users WHERE department = '分析部'));
  

9.1.7 数据准备阶段成果

成果类型	具体内容	业务价值
清洗后数据集	包含100万条订单记录、50万用户、20万商品，缺失率<2%，异常值<1%	为后续分析提供高质量数据基础
数据字典	详细记录各表字段定义、业务逻辑、清洗规则	确保团队数据理解一致
ETL工作流	自动化数据同步与清洗流程，每日处理延迟<5分钟	提升数据处理效率与可维护性

通过以上数据准备工作，我们构建了符合电商业务场景的PostgreSQL数据模型，为后续的用户行为分析、销售预测和异常检测奠定了坚实基础。

• 接下来将进入数据建模与可视化阶段，通过SQL查询和BI工具挖掘数据价值。