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

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

9.1 业务场景与数据准备

9.1.1 业务场景描述

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

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

核心业务目标

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

业务挑战

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

9.1.2 数据来源与获取

数据源构成

数据获取方案

• 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），内容为新订单的 ID
      PERFORM 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）

2. 用户维度表（dim_users）

3. 商品维度表（dim_products）

表关联关系

9.1.4 数据清洗与预处理

1. 缺失值处理

处理策略矩阵

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 rn
  FROM 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 数据质量评估

关键指标监控

可视化监控看板

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

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

9.1.6 数据安全与权限管理

敏感数据处理

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

  CREATE OR REPLACE FUNCTION mask_phone(phone text) RETURNS text AS $$
  BEGIN
    RETURN 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 数据准备阶段成果

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

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