数仓建模：基于OTD流程的订单履约分析

引言：为什么需要分析订单履约流程？

订单履约（从下单到送货）是企业服务客户的关键流程。分析这个流程能帮助企业：

1. 发现问题：比如送货延迟、库存不足导致订单拆分；

2. 优化成本：减少不必要的物流费用；

3. 提高客户满意度：确保订单按时送达。

   
   

以下为 Hive数仓建模方案，包含详细的分层设计、表结构定义及实际案例

一、指标分层与定义

二、数仓分层架构

1. 数据源表（ODS层）

-- 订单原始表（每日增量分区）
CREATE EXTERNAL TABLE ods_order (
    order_id        STRING COMMENT '订单ID',
    customer_id     STRING COMMENT '客户ID',
    sku_list        ARRAY<STRING> COMMENT '商品SKU列表', 
    order_time      TIMESTAMP COMMENT '下单时间',
    total_amount    DECIMAL(10,2) COMMENT '订单金额',
    warehouse_id    STRING COMMENT '仓库ID'
) PARTITIONED BY (dt STRING) 
STORED AS PARQUET
LOCATION '/data/ods/order';

-- 库存预占日志表
CREATE EXTERNAL TABLE ods_inventory_lock (
    lock_id         STRING,
    order_id        STRING,
    sku_id          STRING,
    lock_quantity   INT,
    lock_time       TIMESTAMP,
    release_time    TIMESTAMP
) PARTITIONED BY (dt STRING)
STORED AS PARQUET
LOCATION '/data/ods/inventory_lock';

2. 明细模型（DWD层）

订单履约事实表（星型模型核心）  

CREATE TABLE dwd.fact_order_fulfillment (
    order_id        STRING,
    customer_id     STRING,
    sku_id          STRING,
    warehouse_id    STRING,
    logistics_id    STRING,
    order_time      TIMESTAMP,
    promise_delivery_time TIMESTAMP,
    actual_delivery_time  TIMESTAMP,
    order_status    STRING COMMENT '状态: paid/processing/delivered'
) PARTITIONED BY (dt STRING)
STORED AS PARQUET
LOCATION '/data/dwd/fact_order_fulfillment';

-- ETL数据处理（示例：从ODS层加工）
INSERT OVERWRITE TABLE dwd.fact_order_fulfillment PARTITION(dt='2023-08-01')
SELECT 
    o.order_id,
    o.customer_id,
    sku.sku_id,        -- 通过LATERAL VIEW炸裂sku_list
    o.warehouse_id,
    l.logistics_id,
    o.order_time,
    DATE_ADD(o.order_time, 3) AS promise_delivery_time, -- 假设承诺3天交付
    t.actual_delivery_time,
    CASE WHEN t.actual_delivery_time IS NOT NULL THEN 'delivered' ELSE 'processing' END 
FROM ods_order o
LATERAL VIEW EXPLODE(sku_list) sku_table AS sku
LEFT JOIN ods_logistics l ON o.order_id = l.order_id
LEFT JOIN ods_delivery_tracking t ON o.order_id = t.order_id
WHERE o.dt='2023-08-01';

维度表设计  

-- 仓库维度表（缓慢变化维Type 2）
CREATE TABLE dim.dim_warehouse (
    warehouse_id    STRING,
    warehouse_name  STRING,
    city            STRING,
    start_date      DATE,
    end_date        DATE
) STORED AS PARQUET;

-- 商品维度表
CREATE TABLE dim.dim_product (
    sku_id          STRING,
    category_id     STRING,
    is_active       BOOLEAN
) PARTITIONED BY (dt STRING);

3. 汇总层（DWS层）

每日仓库级履约指标  

CREATE TABLE dws.warehouse_daily_metrics (
    warehouse_id    STRING,
    date            STRING,
    total_orders    INT,
    on_time_rate    DECIMAL(5,2),
    avg_process_hours INT
) PARTITIONED BY (dt STRING);

-- 计算逻辑
INSERT OVERWRITE TABLE dws.warehouse_daily_metrics PARTITION(dt='2023-08-01')
SELECT 
    f.warehouse_id,
    f.dt AS date,
    COUNT(DISTINCT f.order_id) AS total_orders,
    ROUND(
        SUM(CASE WHEN f.actual_delivery_time <= f.promise_delivery_time THEN 1 ELSE 0 END) 
         COUNT(*) * 100, 2
    ) AS on_time_rate,
    AVG( 
        (UNIX_TIMESTAMP(f.actual_delivery_time) - UNIX_TIMESTAMP(f.order_time))  3600 
    ) AS avg_process_hours
FROM dwd.fact_order_fulfillment f
WHERE f.dt='2023-08-01' 
GROUP BY f.warehouse_id, f.dt;

三、关键模型案例：拆单率分析

1. 业务问题

某家电企业发现订单拆单率高达35%，导致物流成本增加20%。需定位拆单原因（库存分散/物流策略）。

2. 建模方案

Step 1：创建拆单事实表  

CREATE TABLE dwd.fact_order_split (
    parent_order_id STRING,
    child_order_id  STRING,
    split_reason    STRING COMMENT 'inventory/region/logistics'
) PARTITIONED BY (dt STRING);

-- 从订单日志解析拆单记录
INSERT OVERWRITE TABLE dwd.fact_order_split PARTITION(dt)
SELECT 
    parent_order_id,
    child_order_id,
    CASE 
        WHEN split_log LIKE '%库存不足%' THEN 'inventory'
        WHEN split_log LIKE '%区域限制%' THEN 'region' 
        ELSE 'logistics' 
    END AS split_reason,
    dt
FROM ods_order_split_log; 

Step 2：拆单根因分析  

SELECT 
    split_reason,
    COUNT(child_order_id) AS split_count,
    COUNT(DISTINCT parent_order_id) AS affected_orders,
    ROUND(COUNT(child_order_id)/COUNT(DISTINCT parent_order_id),2) AS avg_split_per_order
FROM dwd.fact_order_split 
WHERE dt BETWEEN '2023-07-01' AND '2023-07-31'
GROUP BY split_reason;

输出结果：  

结论：65%的拆单因库存分散导致，需优化库存分配策略。

3. 优化后效果

• 库存分配策略调整：  

  -- 优先分配同一仓库满足订单
  INSERT OVERWRITE TABLE dim_warehouse PARTITION (dt='2023-08-01')
  SELECT 
      warehouse_id,
      warehouse_name,
      city,
      CASE 
          WHEN stock_cover_rate > 0.8 THEN 10 --高覆盖率优先
          WHEN city = customer_city THEN 5     --同城优先
          ELSE 1 
      END AS priority_score,
      other_column1,
      other_column2
  FROM dim_warehouse  -- 从原表读取旧数据
  WHERE dt = '2023-07-01';  -- 基于上一日数据计算新优先级

• 结果：拆单率下降至18%，物流成本降低15%。

四、数据治理实践

1. 数据血缘追踪

-- 使用Hive Hook记录字段级血缘
CREATE TABLE meta.data_lineage (
    source_db       STRING,
    source_table    STRING,
    source_column   STRING,
    target_db       STRING,
    target_table    STRING,
    target_column   STRING,
    transform_rule  STRING
);

-- 示例：记录dws.warehouse_daily_metrics的指标来源
INSERT INTO meta.data_lineage VALUES
('dwd', 'fact_order_fulfillment', 'actual_delivery_time', 'dws', 'warehouse_daily_metrics', 'on_time_rate', 'actual_delivery_time <= promise_delivery_time'),
('dwd', 'fact_order_fulfillment', 'order_time', 'dws', 'warehouse_daily_metrics', 'avg_process_hours', 'TIMESTAMP差值计算');

2. 数据质量监控

-- 每日分区完整性检查
SELECT 
    t1.dt AS fact_date, 
    t2.dt AS dim_date, 
    CASE WHEN t1.dt IS NULL OR t2.dt IS NULL THEN 'FAIL' ELSE 'PASS' END 
FROM 
    (SELECT DISTINCT dt FROM dwd.fact_order_fulfillment WHERE dt='2023-08-01') t1 
FULL OUTER JOIN 
    (SELECT DISTINCT dt FROM dim.dim_product WHERE dt='2023-08-01') t2 
ON t1.dt = t2.dt;

以上方案可直接用于生产环境，需根据实际业务补充分区策略、生命周期管理（如历史拉链表）等细节。

五、结论：数据如何帮企业改进订单履约？

通过数据仓库模型，企业可以：

1. 监控关键指标：比如准时送达率、拆单率；

2. 快速发现问题：比如某个仓库送货延迟；

3. 验证优化效果：比如调整库存后，拆单率是否下降。

往期精彩

SQL维度补齐技术在制造业中的五大应用场景与实战解析

Hive动态时间窗口如何实现？

SQL维度补齐技术实战：3种高效方法解析，根治时间断层难题