标签: 商品订单

基于订单的拉链表

什么是拉链表

对于订单、账单流水这类数据表，如果将它们导入到数据仓库中会有一些比较特殊的地方。

它们的数据量比较大。尤其是处于大促或者某些运营活动期间，每天的订单量数据可能会非常多。
它们有一些中间字段。例如，订单状态、支付状态等。记录更新前后除了状态字段的值不同，其他字段的值完全一样。
不能单纯按照时间来采集数据，因为用户可能今天下单，但转钟隔夜（也就是第二天才）支付。

所以如果每天都对它们执行全量导入，不仅非常浪费磁盘空间，而且统计的结果也可能不准确，但又不能用增量导入的方式，因为Hive是不支持数据更新的。

添码座原创大约 6 分钟

需求三：商品相关指标

实现思路

针对商品销售情况。
- 可以通过将dwd_orderitem、dwd_goods_info和dwd_category这三张表进行关联获取到商品名称、一级类目、订单总量、销售额这些属性。
- 可以基于这些表构建一个DWS层的商品订单信息的宽表dws_goods_order_all便于后期其它需求复用。
- 最后基于这个宽表，将统计出来的结果保存到APP层的app_goods_sales表中。
针对商品品类偏好Top10。
- 可以在上一个指标的基础上，按一级类目进行分组，对类目下的订单总量进行排序再取Top10。
- 最后将结果保存到app_category_top10中。

添码座原创大约 3 分钟

需求四：漏斗转化分析

实现思路

其实每一层用户的行为都可以找到对应的表。

活跃：对应于表dws_app_open_history。
商品详情页：对应于表dwd_goods_item。
下单：对应于表dwd_order。
支付：对应于表dwd_order。

添码座原创大约 1 分钟

需求一：用户信息宽表

实现思路

这个需求比较简单，只需要对dwd_user表和dwd_user_addr表执行left join操作，通过userid进行关联即可，将结果保存到dws_user_all表中就行了。

DWS层开发

通过脚本的方式初始化dws_user_all表。

> cd /home/work/warehouse_goods_order
> vi dws_shopmall_requirement01_init.sh

#!/bin/bash
# 需求一：用户信息宽表
# dws层数据库和表初始化，只需要执行一次

hive -e "
create database if not exists dws_shopmall;

create external table if not exists dws_shopmall.dws_user_all (
    id               bigint,
    username         string,
    gender           tinyint,
    birthday         string,
    email            string,
    mobile           string,
    createtime       string,
    disabled         tinyint,
    addrid           bigint,
    addrname         string,
    delivername      string,
    phone            string,
    isdefault        tinyint
) partitioned by(dt string) 
  row format delimited  
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dws/user_all/';
"

添码座原创大约 1 分钟

需求二：电商GMV

实现思路

这个需求实现起来也很简单，只需要对dwd_order表中的money字段按时间进行汇总就行了，然后将计算结果保存到表APP层的app_gmv表中。

APP层开发

通过脚本的方式初始化app_gmv表。

> cd /home/work/warehouse_goods_order
> vi app_shopmall_requirement02_init.sh

#!/bin/bash
# 需求二：电商GMV
# app层数据库和表初始化，只需要执行一次

hive -e "
create database if not exists app_shopmall;

create external table if not exists app_shopmall.app_gmv (
    gmv   decimal(10, 2) 
) partitioned by(dt string) 
  row format delimited  
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/app/gmv/';
"

添码座原创小于 1 分钟

商品订单数仓需求

在商品订单数仓的建设中，需要满足下面这么几项需求。

用户信息宽表，将服务端中的t_user和t_user_addr这两张表的字段拼接起来，形成一张宽表，便于后续查询。
电商GMV（Gross Merchandise Volume，商品交易总额），统计一段时间内的电商交易总金额。
商品相关指标，包括下面两个子指标。
- 商品销售情况，需要提取出商品名称、一级类目、订单总量、销售额这几个属性。
- 商品品类偏好Top10，统计出商品一级类目的订单总量，然后取前10名。
漏斗转化分析，就是针对用户行为逐层分析用户活跃 -> 商品详情页 -> 下单 -> 支付的转化率。

用户行为漏斗分析

添码座原创大约 2 分钟

商品订单数仓DWD层

DWD层初始化脚本，这个脚本和ODS层的初始化脚本代码完全一致，只不过数据被保存到了DWD层。

> cd /home/work/warehouse_goods_order
> vi dwd_shopmall_init.sh

#!/bin/bash
# dwd层数据库和表初始化，只需要执行一次

hive -e "
create database if not exists dwd_shopmall;

create external table if not exists dwd_shopmall.dwd_user (
    id              bigint,
    username        string,
    gender          tinyint,
    birthday        string,
    email           string,
    mobile          string,
    createtime      string,
    disabled        tinyint
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dwd/user/';

create external table if not exists dwd_shopmall.dwd_user_addr (
    id              bigint,
    userid          bigint,
    addrname        string,
    isdefault       tinyint,
    username        string,
    mobile          string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dwd/user_addr/';

create external table if not exists dwd_shopmall.dwd_category (
    id              int,
    parentids       string,
    name            string,
    level           int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dwd/category/';

create external table if not exists dwd_shopmall.dwd_goods_info (
    id              bigint,
    name            string,
    desc            string,
    price           double,
    categoryid      int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dwd/goods_info/';

create external table if not exists dwd_shopmall.dwd_payment (
    id              bigint,
    orderid         bigint,
    tradeno         string,
    money           double,
    type            int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dwd/payment/';

create external table if not exists dwd_shopmall.dwd_order (
    id              bigint,
    userid          bigint,
    money           double,
    type            int,
    status          int,
    payid           bigint,
    createtime      string,
    updatetime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dwd/order/';

create external table if not exists dwd_shopmall.dwd_orderitem (
    orderid         bigint,
    goodsid         bigint,
    amount          int,
    price           double,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dwd/orderitem/';
"

添码座原创大约 2 分钟

商品订单数据导入

数据格式

服务端数据包括用户、商品、支付和订单等数据，这些数据都存储在MySQL中。

CREATE DATABASE IF NOT EXISTS t_shopmall;

-- 数据库
USE t_shopmall;

-- 1. 用户信息表
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (
    id                  BIGINT NOT NULL,
    username            VARCHAR(64) NOT NULL,
    gender              TINYINT NOT NULL,
    birthday            DATETIME NOT NULL,
    email               VARCHAR(64) NOT NULL,
    mobile              VARCHAR(16) NOT NULL,
    createtime          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    disabled            TINYINT NOT NULL,
    PRIMARY KEY (id)
);

-- 2. 用户收货地址表
DROP TABLE IF EXISTS t_user_addr;
CREATE TABLE t_user_addr (
    id                  BIGINT NOT NULL,
    userid              BIGINT NOT NULL,
    addrname            VARCHAR(512) NOT NULL,
    isdefault           TINYINT NOT NULL,
    username            VARCHAR(64) NOT NULL,
    mobile              VARCHAR(16) NOT NULL,
    PRIMARY KEY (id, userid)
);

-- 3. 类目编码表
DROP TABLE IF EXISTS t_category;
CREATE TABLE t_category (
    id                 INT(11) NOT NULL,
    parentids          VARCHAR(128) NOT NULL COMMENT '所有父类目编码',
    name               VARCHAR(128) NOT NULL,
    level              TINYINT NOT NULL COMMENT '类目等级',
    createtime         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- 4. 商品信息表
DROP TABLE IF EXISTS t_goods_info;
CREATE TABLE t_goods_info (
    id                 BIGINT NOT NULL,
    name               VARCHAR(256) NOT NULL,
    desc               VARCHAR(256) NOT NULL,
    price              DECIMAL(18, 2) NOT NULL,
    categoryid         INT(11) NOT NULL,
    createtime         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- 5. 支付流水表
DROP TABLE IF EXISTS t_payment;
CREATE TABLE t_payment (
    id                  BIGINT NOT NULL,
    orderid             BIGINT NOT NULL,
    tradeno             VARCHAR(64) NOT NULL,
    money               DECIMAL(18, 2) NOT NULL,
    type                INT(11) NOT NULL COMMENT '支付类型',
    createtime          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- 6. 订单表
DROP TABLE IF EXISTS t_order;
CREATE TABLE t_order (
    id                  BIGINT NOT NULL,
    userid              BIGINT NOT NULL,
    money               DECIMAL(18, 2) NOT NULL,
    type                INT(11) NOT NULL,
    status              INT(11) NOT NULL,
    payid               BIGINT NOT NULL,
    createtime          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatetime          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- 7. 订单详情表
DROP TABLE IF EXISTS t_orderitem;
CREATE TABLE t_orderitem (
    orderid             BIGINT NOT NULL,
    goodsid             BIGINT NOT NULL,
    amount              INT(11) NOT NULL,
    price               DECIMAL(18, 2) NOT NULL,
    createtime          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (orderid, goodsid)
);

添码座原创大约 5 分钟

商品订单数仓ODS层

和之前的用户行为数仓一样，ODS和DWD这两层和业务需求没啥关系，所以可以先搞起来。

ODS层初始化脚本。

> cd /home/work/warehouse_goods_order
> vi ods_shopmall_init.sh

#!/bin/bash
# ods层数据库和表初始化，只需要执行一次

hive -e "
create database if not exists ods_shopmall;

create external table if not exists ods_shopmall.ods_user (
    id              bigint,
    username        string,
    gender          tinyint,
    birthday        string,
    email           string,
    mobile          string,
    createtime      string,
    disabled        tinyint
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/user/';

create external table if not exists ods_shopmall.ods_user_addr (
    id              bigint,
    userid          bigint,
    addrname        string,
    isdefault       tinyint,
    username        string,
    mobile          string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/user_addr/';

create external table if not exists ods_shopmall.ods_category (
    id              int,
    parentids       string,
    name            string,
    level           int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/category/';

create external table if not exists ods_shopmall.ods_goods_info (
    id              bigint,
    name            string,
    desc            string,
    price           double,
    categoryid      int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/goods_info/';

create external table if not exists ods_shopmall.ods_payment (
    id              bigint,
    orderid         bigint,
    tradeno         string,
    money           double,
    type            int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/payment/';

create external table if not exists ods_shopmall.ods_order (
    id              bigint,
    userid          bigint,
    money           double,
    type            int,
    status          int,
    payid           bigint,
    createtime      string
    update_time     string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/order/';

create external table if not exists ods_shopmall.ods_orderitem (
    orderid         bigint,
    goodsid         bigint,
    amount          int,
    price           double,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/orderitem/';
"

添码座原创大约 2 分钟