大数据-241 离线数仓 - 实战：电商核心交易数据模型与 MySQL 源表设计（订单/商品/品类/店铺/支付）

业务需求

电商系统业务中最关键的业务，电商的运营活动都是围绕这个主题展开。 选取的指标包括：订单数、商品数、支付金额，对这些指标按销售区域、商品类型分析。

需求板块

电商平台的核心交易可以分为以下几个主要环节：

  • 商品浏览：用户浏览商品的行为数据
  • 加入购物车：用户将商品添加到购物车中的行为
  • 下单：用户在电商平台上完成的订单生成行为
  • 支付：支付是交易中至关重要的环节
  • 发货：商品发货数据记录了商家发货的时间、物流公司、物流单号等信息
  • 收货和评价：用户收到商品后的评价、退换货行为等

核心业务流程

  • 商品浏览
  • 加入购物车
  • 下单
  • 支付
  • 发货
  • 收货和评价

业务数据库表结构

1. 交易订单表 (wzk_trade_orders)

CREATE TABLE `wzk_trade_orders` (
  `orderId` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `orderNo` varchar(20) NOT NULL COMMENT '订单编号',
  `userId` bigint(11) NOT NULL COMMENT '用户ID',
  `status` tinyint(4) NOT NULL DEFAULT '-2' COMMENT '订单状态',
  `productMoney` decimal(11,2) NOT NULL COMMENT '商品金额',
  `totalMoney` decimal(11,2) NOT NULL COMMENT '订单金额（包括运费）',
  `payMethod` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付方式',
  `isPay` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否支付',
  `areaId` int(11) NOT NULL COMMENT '区域最低一级',
  `createTime` varchar(25) NOT NULL COMMENT '下单时间',
  `payTime` varchar(25) DEFAULT NULL COMMENT '支付时间',
  PRIMARY KEY (`orderId`)
) ENGINE=InnoDB AUTO_INCREMENT=355 DEFAULT CHARSET=utf8;

2. 订单产品表 (wzk_order_product)

CREATE TABLE `wzk_order_product` (
  `id` BIGINT(11) NOT NULL AUTO_INCREMENT,
  `orderId` BIGINT(11) NOT NULL COMMENT '订单ID',
  `productId` BIGINT(11) NOT NULL COMMENT '商品ID',
  `productNum` BIGINT(11) NOT NULL DEFAULT '0' COMMENT '商品数量',
  `productPrice` DECIMAL(11,2) NOT NULL DEFAULT '0.00' COMMENT '商品价格',
  `money` DECIMAL(11,2) DEFAULT '0.00' COMMENT '付款金额',
  PRIMARY KEY (`id`),
  KEY `orderId` (`orderId`),
  KEY `goodsId` (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=1260 DEFAULT CHARSET=utf8;

3. 产品信息表 (wzk_product_info)

CREATE TABLE `wzk_product_info` (
  `productId` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `productName` varchar(200) NOT NULL COMMENT '商品名称',
  `shopId` bigint(11) NOT NULL COMMENT '门店ID',
  `price` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '门店价格',
  `isSale` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否上架',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否新品',
  `categoryId` int(11) NOT NULL COMMENT '最后一级商品分类ID',
  `createTime` varchar(25) NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`productId`),
  KEY `shopId` (`shopId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=115909 DEFAULT CHARSET=utf8;

4. 产品分类表 (wzk_product_category)

  • 产品分类表共分3个级别（level 1/2/3）
  • 通过 parentId 实现层级关联

5. 商家店铺表 (wzk_shops)

CREATE TABLE `wzk_shops` (
  `shopId` int(11) NOT NULL AUTO_INCREMENT COMMENT '商铺ID',
  `userId` int(11) NOT NULL COMMENT '商铺联系人ID',
  `areaId` int(11) DEFAULT '0' COMMENT '区域ID',
  `shopName` varchar(100) DEFAULT '' COMMENT '商铺名称',
  `shopLevel` tinyint(4) NOT NULL DEFAULT '1' COMMENT '店铺等级',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '商铺状态',
  PRIMARY KEY (`shopId`)
) ENGINE=InnoDB AUTO_INCREMENT=105317 DEFAULT CHARSET=utf8;

6. 商家地域组织表 (wzk_shop_admin_org)

  • 记录店铺所属区域组织架构

7. 支付方式表 (wzk_payments)

  • 记录支付方式（支付宝、微信、现金等）

错误速查（常见问题与解决方案）

症状根因定位修复方案
订单数被放大用订单表 join 明细表后直接 count(*)使用 count(distinct o.orderId)
商品数口径混乱productNum（件数）与 distinct(productId)（SKU数）混用明确：商品件数=sum(productNum)；商品SKU数=count(distinct productId)
支付金额对不上totalMoney、productMoney、明细 money 三套金额字段口径不同固定口径：优先使用支付成功后的订单金额字段
”已支付订单”过滤后仍包含未付款仅用 isPay=1 或仅用 status 判断联合判断：isPay=1 AND payTime is not null
品类分析结果只到”最后一级”仅有 categoryId=最后一级，未做 parentId 回溯用自连接回溯三级分类
区域维度结果不可解释缺少 areaId 对应名称/层级的维表增加区域维表