电商场景实战之订单与销量统计

背景信息

以下案例是实时计算的合作伙伴袋鼠云通过阿里云实时计算来完成电商订单管理的案例。

业务架构图

准备工作

将RDS MySQL产生的增量数据实时同步到DataHub中的Topic。由RDS经过DTS数据同步到大数据总线（DataHub）Schema表的信息。

具体步骤请参见MySQL到DataHub数据实时同步。

编写业务逻辑

--数据的订单源表。
create table orders_real(
  dts_ordercodeofsys varchar,
  dts_paytime bigint,
  dts_deliveredtime varchar,
  dts_storecode varchar,
  dts_warehousecode varchar,
  dts_cancelled bigint,
  dts_delivered bigint,
  dts_receivercity varchar,
  dts_receiverprovince varchar,
  dts_record_id varchar,
  dts_operation_flag varchar,
  dts_instance_id varchar,
  dts_db_name varchar,
  dts_table_name varchar,
  dts_utc_timestamp varchar,
  dts_before_flag varchar,
  dts_after_flag varchar
) with (
  type='datahub',
  endPoint='http://dh-cn-****.com',
  project='your',
  topic='表名',
  accessId='您的ID',
  accessKey='您的KEY'
);

create table orderdetail_real(
  dts_ordercodeofsys varchar,
  dts_skuname varchar,
  dts_skucode varchar,
  dts_quantity bigint,
  dts_dividedamount double,
  dts_salechanneldividedamount double,
  dts_initialcost double,
  dts_record_id varchar,
  dts_operation_flag varchar,
  dts_instance_id varchar,
  dts_db_name varchar,
  dts_table_name varchar,
  dts_utc_timestamp varchar,
  dts_before_flag varchar,
  dts_after_flag varchar
) with (
  type='datahub',
  endPoint='http://dh-cn-****.com',
  project='yourPorjectName',
  topic='yourTableName',
  accessId='yourAccessId',
  accessKey='yourAccessSecret'
);


create table ads_all_count_amount(
  bill_date varchar,--下单时间。
  bill_count bigint,--总的订单总数。
  qty bigint,--总的销售量。
  primary key (bill_date)
) with (
  type='rds',
  url='jdbc:mysql://rm-XXXX.mysql.rds.aXXXXcs.com:3306/XXXX',
  tableName='yourDatabaseTableName',
  userName='yourDatabaseAccount',
  password='yourDatabasePassword'
);

--订单源表，最新交易时间的商品编号。
CREATE VIEW new_paytime AS
  SELECT
  dts_ordercodeofsys,
  MAX(dts_paytime) AS dts_paytime
FROM orders_real
  GROUP BY dts_ordercodeofsys ;

--订单详情表，有效订单的订单编码、商品名称、商品编号、数量的信息。
CREATE VIEW new_orderdetail AS
SELECT
  dts_ordercodeofsys,
  dts_skuname,
  dts_skucode,
  CASE WHEN dts_operation_flag='U'
  AND dts_before_flag='Y'
  AND dts_after_flag='N' THEN -1*dts_quantity
  WHEN dts_operation_flag='U'
  AND dts_before_flag='N'
  AND dts_after_flag='Y' THEN dts_quantity
  WHEN dts_operation_flag='D' THEN -1*dts_quantity
  ELSE dts_quantity
  END AS dts_quantity
FROM
  orderdetail_real;

--订单总单数，总销售量。
INSERT INTO ads_all_count_amount
  SELECT
  FROM_UNIXTIME(cast(a.dts_paytime/1000000 AS bigint),'yyyyMMdd') AS bill_date,
  COUNT(DISTINCT a.dts_ordercodeofsys) AS bill_count,
  SUM(b.dts_quantity) AS qty
from
  new_paytime a
  join
  new_orderdetail b
  ON a.dts_ordercodeofsys=b.dts_ordercodeofsys
  GROUP BY
  FROM_UNIXTIME(CAST(a.dts_paytime/1000000 AS bigint),'yyyyMMdd');    

难点解析

DEMO示例以及源代码

常见问题

• Q：模块二中，如何判断有效交易订单？
  A：首先是要满足dts_operation_flag=U或者dts_operation_flag=I，然后dts_before_flag代表的是变更前订单状态，dts_after_flag是变更后订单状态。所以有效交易订单如下。

  dts_operation_flag='U'
        AND dts_before_flag='N'
        AND dts_after_flag='Y' THEN dts_quantity

• Q：模块二中，为什么THEN -1*dts_quantity？

  A：订单的取消或者是交易没有成功，在总的销量里也会记录。为了保证总销量的正确性，所以把没有成交的订单数量设为负数，在计算总的销量会减去这个数量。

• Q：模块三中，为什么订单源表和订单详情需要进行JOIN操作？

  A：new_paytime查出的是最新交易的时间的所有的订单编号。new_orderdetail查询的是所有有效订单的订单编码、商品名称、商品编号、数量的信息。两张表JOIN是为了方便用户来统计订单总数和总的销量。