电商场景实战之实时态势感知和订单地理分布

在电商系统里，订单与订单地址一般都是分开存储的（下单人可以给多个地址下单），所以在订单创建时并没有收货地址，只有在订单提交时才真正的知道收货地址。订单地址里保存的是城市的id（city_id），为了获取地理信息，还需要一张城市表，这张表存储着城市的地理信息。目标是按日统计不同地域订单（总销售额）的分布情况。

• 订单

  CREATE TABLE source_order (
      id VARCHAR,-- 订单ID
      seller_id VARCHAR, --卖家ID
      account_id VARCHAR,--买家ID
      receive_address_id VARCHAR,--收货地址ID
      total_price VARCHAR,--订单金额
      pay_time VARCHAR --订单支付时间
  ) WITH (
      type='datahub',
      endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
      project='yourProjectName',--您的project
      topic='yourTopicName',--您的topic
      roleArn='yourRoleArn',--您的roleArn
      batchReadSize='500'
  );

• 订单地址

  CREATE TABLE source_order_receive_address ( 
       id VARCHAR,--收货地址ID 
       full_name VARCHAR,--收货人全名 
       mobile_number VARCHAR,--收货人手机号 
       detail_address VARCHAR,--收货详细地址 
       province VARCHAR,--收货省份 
       city_id VARCHAR,--收货城市 
       create_time VARCHAR --创建时间 
   ) WITH ( 
       type='datahub', 
       endPoint='http://dh-cn-hangzhou.aliyun-inc.com', 
       project='yourProjectName',--您的project 
       topic='yourTopicName',--您的topic 
       roleArn='yourRoleArn',--您的roleArn 
       batchReadSize='500' 
   );
  								

• 城市表

  CREATE TABLE dim_city ( 
       city_id varchar, 
       city_name varchar,--城市名 
       province_id varchar,--所属省份ID 
       zip_code varchar,--邮编 
       lng varchar,--经度 
       lat varchar,--纬度 
    PRIMARY KEY (city_id), 
    PERIOD FOR SYSTEM_TIME --定义为维表 
   ) WITH ( 
       type= 'rds', 
       url = 'yourDatabaseURL',--您的数据库url 
       tableName = 'yourTableName',--您的表名 
       userName = 'yourDatabaseName',--您的用户名 
       password = 'yourDatabasePassword'--您的密码 
   );

• 按日统计不同地域订单（总销售额）的分布。

  CREATE TABLE result_order_city_distribution ( 
       summary_date varchar,--统计日期 
       city_id bigint,--城市ID 
       city_name varchar,--城市名 
       province_id bigint,--所属省份ID 
       gmv double,--总销售额 
       lng varchar,--经度 
       lat varchar,--纬度 
       primary key (summary_date,city_id) 
      ) WITH ( 
          type= 'rds', 
          url = 'yourDatabaseURL',--您的数据库url 
          tableName = 'yourTableName',--您的表名 
          userName = 'yourDatabaseName',--您的用户名 
          password = 'yourDatabasePassword'--您的密码 
      );

 insert into result_order_city_distribution 
 select 
 d.summary_date 
 ,cast(d.city_id as BIGINT) 
 ,e.city_name 
 ,cast(e.province_id as BIGINT) 
 ,d.gmv 
 ,e.lng 
 ,e.lat 
 from 
 ( 
         select 
         DISTINCT 
         DATE_FORMAT(a.pay_time,'yyyyMMdd') as summary_date 
         ,b.city_id as city_id 
         ,round(sum(cast(a.total_price as double)),2) as gmv 
         from source_order as a 
         join source_order_receive_address as b on a.receive_address_id =b.id 
         group by DATE_FORMAT(a.pay_time,'yyyyMMdd'),b.city_id 
         --双流join，并根据日期和城市ID得到销售额分布 
 )d join dim_city FOR SYSTEM_TIME AS OF PROCTIME() as e on d.city_id = e.city_id 
 -- join维表，补齐城市地理信息，得到最终结果 
 ;