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

武子康
0 阅读12分钟

TL;DR

  • 场景：离线数仓围绕“核心交易”做指标（订单数/商品数/支付金额），按区域与品类分析
  • 结论：源表需先统一时间类型、主键/外键与维度口径，否则指标会重复/缺失/不可追溯
  • 产出：一套可直接落地的源表联结路径 + 版本矩阵 + 常见坑位速查卡

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

离线数仓 核心交易 业务数据结构

业务需求

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

在大数据的分析中，"电商核心交易"是指电商平台上所有与商品交易相关的核心行为和交易数据的集合。具体来说，核心交易涵盖了商品的浏览、加购物车、下单、支付、发货、收货等一系列行为，它们直接影响电商平台的运营效率、用户体验和商业价值。

需求板块

电商平台的核心交易可以分为以下几个主要环节，每个环节都涉及大量数据的收集、存储和分析：

  • 商品浏览：用户浏览商品的行为数据，例如用户查看了哪些商品、查看时长、是否点击了相关广告或推荐商品等。这些数据能够帮助平台了解用户的兴趣点，进而优化商品推荐和个性化营销策略。
  • 加入购物车：用户将商品添加到购物车中的行为。通过分析购物车中的商品，可以获取用户的购买意图和倾向，帮助商家调整商品定价、库存和促销策略。
  • 下单：用户在电商平台上完成的订单生成行为。包括订单的创建、订单内容、用户的收货地址、选择的支付方式等数据。订单数据是电商交易中的核心，通常涉及大量的数据信息，要求系统能够高效地处理和存储。
  • 支付：支付是交易中至关重要的环节，支付数据可以通过支付方式、支付成功与否、支付金额、支付时间等维度进行分析。这部分数据可以帮助平台评估不同支付方式的受欢迎程度，并进行相应的优化。
  • 发货：商品发货数据记录了商家发货的时间、物流公司、物流单号等信息。通过对发货数据的分析，可以判断出物流时效、发货效率等关键指标，进一步优化供应链和物流流程。
  • 收货和评价：用户收到商品后的评价、退换货行为等。评价数据不仅反映了商品的质量和用户满意度，还对后续的购买决策产生影响。此外，退换货数据也能够反映出商品质量问题和物流中的痛点。

应用方向

电商平台通过大数据技术进行数据的实时采集、处理和分析，从而优化核心交易环节，提高转化率和用户满意度。以下是几个大数据在电商核心交易中的关键应用：

  • 精准推荐：基于用户的浏览、加购物车、历史购买等行为数据，通过机器学习和数据挖掘算法，电商平台可以为用户推荐其可能感兴趣的商品。精准推荐不仅提升了用户体验，也大幅提高了平台的销售转化率。
  • 实时库存管理：通过对商品浏览、加购物车、订单等数据的实时监控，电商平台可以准确预测库存需求，避免库存过剩或缺货的情况发生，从而优化供应链和物流效率。
  • 营销优化：大数据分析可以帮助商家了解不同营销活动对用户行为的影响。例如，通过对不同促销活动（如折扣、满减、秒杀等）的数据分析，商家可以了解哪些活动最受用户欢迎，从而调整营销策略。
  • 动态定价：电商平台可以根据市场需求、竞争情况、库存量、用户购买历史等因素，实时调整商品价格。大数据技术使得电商平台能够快速响应市场变化，提升价格竞争力。
  • 用户行为分析与预测：电商平台通过对用户行为数据的分析，可以对用户的购买意图和需求进行预测，进而进行个性化营销和推荐。此外，通过对用户流失率、购物车放弃率等数据的分析，可以帮助商家提升用户留存和转化率。
  • 物流优化：大数据分析能够帮助电商平台在订单生成后优化物流路线和配送计划，减少配送成本和配送时效，提高用户满意度。
  • 欺诈检测：通过对交易数据、支付数据等的深度分析，电商平台能够识别出异常交易模式，及时发现并防止欺诈行为。

业务数据库表结构

离线数仓 业务数据库结构表 业务数据库，数据源：

  • 订单交易表（trade_orders）
  • 订单商品表（order_product）
  • 产品信息表（product_info）
  • 商家店铺表（shops）
  • 商家地域组织表（shop_admin_org）
  • 支付方式表（payments）

交易订单表

  • 记录订单信息
  • 订单状态
  • 创建时间、支付时间、修改时间
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 '订单状态: -3:用户拒收, -2:未付款的订单, -1:用户取消, 0:待发货, 1:配送中, 2:用户确认收货',
  `productMoney` decimal(11,2) NOT NULL COMMENT '商品金额',
  `totalMoney` decimal(11,2) NOT NULL COMMENT '订单金额（包括运费）',
  `payMethod` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付方式: 0:未知, 1:支付宝, 2:微信, 3:现金, 4:其他',
  `isPay` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否支付: 0:未支付, 1:已支付',
  `areaId` int(11) NOT NULL COMMENT '区域最低一级',
  `tradeSrc` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单来源: 0:商城, 1:微信, 2:手机版, 3:安卓App, 4:苹果App',
  `tradeType` int(11) DEFAULT '0' COMMENT '订单类型',
  `isRefund` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否退款: 0:否, 1:是',
  `dataFlag` tinyint(4) NOT NULL DEFAULT '1' COMMENT '订单有效标志: -1:删除, 1:有效',
  `createTime` varchar(25) NOT NULL COMMENT '下单时间',
  `payTime` varchar(25) DEFAULT NULL COMMENT '支付时间',
  `modifiedTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '订单更新时间',
  PRIMARY KEY (`orderId`)
) ENGINE=InnoDB AUTO_INCREMENT=355 DEFAULT CHARSET=utf8;

订单产品表

  • 记录订单中购买产品的信息，包括产品的数量、单价等
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 '付款金额',
  `extra` TEXT COMMENT '额外信息',
  `createTime` VARCHAR(25) DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `orderId` (`orderId`),
  KEY `goodsId` (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=1260 DEFAULT CHARSET=utf8;

产品信息表

  • 记录产品的详细信息，对应商家ID、商品属性（是否新品、是否上架）
  • 创建时间和修改时间
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 '是否上架 0: 不上架, 1: 上架',
    `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否新品 0: 否, 1: 是',
    `categoryId` int(11) NOT NULL COMMENT '最后一级商品分类ID',
    `createTime` varchar(25) NOT NULL COMMENT '创建时间',
    `modifyTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`productId`),
    KEY `shopId` (`shopId`) USING BTREE,
    KEY `goodsStatus` (`isSale`)
) ENGINE=InnoDB AUTO_INCREMENT=115909 DEFAULT CHARSET=utf8;

产品分类表

  • 备注：产品分类表 共分3个级别
CREATE TABLE `wzk_product_category` (
  `catId` int(11) NOT NULL AUTO_INCREMENT COMMENT '品类ID',
  `parentId` int(11) NOT NULL COMMENT '父ID',
  `catName` varchar(20) NOT NULL COMMENT '分类名称',
  `isShow` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否显示 0:隐藏 1:显示',
  `sortNum` int(11) NOT NULL DEFAULT '0' COMMENT '排序号',
  `isDel` tinyint(4) NOT NULL DEFAULT '1' COMMENT '删除标志 1:有效 -1:删除',
  `createTime` varchar(25) NOT NULL COMMENT '建立时间',
  `level` tinyint(4) DEFAULT '0' COMMENT '分类级别，共3级',
  PRIMARY KEY (`catId`),
  KEY `parentId` (`parentId`, `isShow`, `isDel`)
) ENGINE=InnoDB AUTO_INCREMENT=10442 DEFAULT CHARSET=utf8;

产品分类表，共分3个级别：

-- 查询第一级产品目录
SELECT catName, catid 
FROM wzk_product_category 
WHERE level = 1;

-- 查询 "电脑、办公" 的二级子类目录
SELECT catName, catid 
FROM wzk_product_category 
WHERE level = 2 
AND parentId = 32;

-- 查询 "电脑整机" 的三级子类目录
SELECT catName, catid 
FROM wzk_product_category 
WHERE level = 3 
AND parentId = 10250;

商家店铺表

  • 记录店铺的详细信息
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 '商铺状态',
  `createTime` date DEFAULT NULL COMMENT '创建时间',
  `modifyTime` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`shopId`),
  KEY `shopStatus` (`status`)
) ENGINE=InnoDB 
AUTO_INCREMENT=105317 
DEFAULT CHARSET=utf8 COMMENT='商铺表';

商家地域组织表

  • 记录店铺所属区域
CREATE TABLE `wzk_shop_admin_org` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '组织ID',
  `parentId` int(11) NOT NULL COMMENT '父ID',
  `orgName` varchar(100) NOT NULL COMMENT '组织名称',
  `orgLevel` tinyint(4) NOT NULL DEFAULT '1' COMMENT '组织级别，1:总部及大区级部门; 2:总部下属的各个部门及基部门; 3:具体工作部门',
  `isDelete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标志, 1:删除; 0:有效',
  `createTime` varchar(25) DEFAULT NULL COMMENT '创建时间',
  `updateTime` varchar(25) DEFAULT NULL COMMENT '最后修改时间',
  `isShow` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否显示, 0:是; 1:否',
  `orgType` tinyint(4) NOT NULL DEFAULT '1' COMMENT '组织类型, 0:总裁办; 1:研发; 2:销售; 3:运营; 4:产品',
  PRIMARY KEY (`id`),
  KEY `parentId` (`parentId`)
) ENGINE=InnoDB AUTO_INCREMENT=100332 DEFAULT CHARSET=utf8;

支付方式表

  • 记录支付方式
CREATE TABLE `wzk_payments` (
  `id` int(11) NOT NULL,
  `payMethod` varchar(20) DEFAULT NULL,
  `payName` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `payOrder` int(11) DEFAULT '0',
  `online` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payCode` (`payMethod`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

错误速查

症状根因定位修复方案
订单数被放大（比业务系统大几倍）用订单表 join 明细表后直接 count(*) 或 sum检查 SQL 是否在 join 后做了 count(orderId)、是否缺少 count(distinct orderId)；订单数用 count(distinct o.orderId)；金额用订单粒度字段或先聚合明细再 join
商品数口径混乱（SKU 数 vs 件数）productNum（件数）与 distinct(productId)（SKU 数）混用明确两套指标：商品件数=sum(productNum)；商品SKU数=count(distinct productId)
支付金额对不上财务/支付平台totalMoney、productMoney、明细 money 三套金额字段口径不同固定口径：优先使用支付成功后的订单金额字段；如用明细金额，必须按订单先汇总并定义差异处理
“已支付订单”过滤后仍包含未付款仅用 isPay=1 或仅用 status 判断支付口径建议联合：isPay=1 AND payTime is not null；状态用于履约阶段，不替代支付判定
按日统计出现大量 NULL/解析失败createTime/payTime 为 varchar(25)，格式不一致或含非法值在 ODS 清洗统一为 datetime；无法解析的打脏数据标记并隔离
MySQL 建表报错或写入异常timestamp DEFAULT '0000-00-00 00:00:00' 在严格模式/8.0 不允许改为允许的默认值（如 CURRENT_TIMESTAMP / NULL）并按业务补齐
品类分析结果只到“最后一级”仅有 categoryId=最后一级，未做 parentId 回溯用自连接回溯三级：level3.parentId→level2.parentId→level1；或在维表预展开 level1/2/3 字段
区域维度结果不可解释缺少 areaId 对应名称/层级的维表；shop_admin_org 不等价于区域增加区域维表（areaId→name/层级/父级）；明确 orders.areaId 与 shops.areaId 的优先级与含义
支付方式维表 join 失败orders.payMethod(tinyint) vs payments.payMethod(varchar) 类型/编码不一致建立映射表：tinyint code → pay_code(varchar) → pay_name；或把 payments 表字段改为数值 code
数据“软删除”未过滤导致指标偏大dataFlag=-1、isDel=-1、isDelete=1 等未统一过滤统一治理规则：ODS 层清洗成 is_valid；DWD/DWS 统一使用有效记录过滤

其他系列

🚀 AI篇持续更新中（长期更新）

AI炼丹日志-29 - 字节跳动 DeerFlow 深度研究框斜体样式架 私有部署 测试上手 架构研究，持续打造实用AI工具指南！ AI研究-132 Java 生态前沿 2025：Spring、Quarkus、GraalVM、CRaC 与云原生落地

💻 Java篇持续更新中（长期更新）

Java-218 RocketMQ Java API 实战：同步/异步 Producer 与 Pull/Push Consumer MyBatis 已完结，Spring 已完结，Nginx已完结，Tomcat已完结，分布式服务已完结，Dubbo已完结，MySQL已完结，MongoDB已完结，Neo4j已完结，FastDFS 已完结，OSS已完结，GuavaCache已完结，EVCache已完结，RabbitMQ已完结，RocketMQ正在更新... 深入浅出助你打牢基础！

📊 大数据板块已完成多项干货更新（300篇）：

包括 Hadoop、Hive、Kafka、Flink、ClickHouse、Elasticsearch 等二十余项核心组件，覆盖离线+实时数仓全栈！ 大数据-278 Spark MLib - 基础介绍 机器学习算法 梯度提升树 GBDT案例 详解

avatar
文章
阅读
粉丝