5.阿里云 大数据开发实践odpscmd电商在线交易分析

1.创建在线交易表

CREATE TABLE IF NOT EXISTS online_trading(
    create_time  DATETIME COMMENT '时间',
    good_cate    STRING COMMENT '商品种类',
    brand        STRING COMMENT '品牌'  ,
    buyer_id     STRING COMMENT '用户id',
    trans_num    BIGINT COMMENT '交易量'  ,
    trans_amount DOUBLE COMMENT '金额'  ,
    click_cnt    BIGINT COMMENT '单击次数'  ,
    addcart_cnt  BIGINT COMMENT '加入购物车次数'  ,
    collect_cnt  BIGINT COMMENT '加入收藏夹次数'  
) 

2.创建用户信息表

CREATE TABLE IF NOT EXISTS user
(
    buyer_id   STRING COMMENT '用户id',
    buyer_prov STRING COMMENT '省份',
    gender     STRING COMMENT '性别',
    age_range  STRING COMMENT '年龄段',
    zodiac     STRING COMMENT '星座'
) ;

3.创建结果数据存储表

CREATE TABLE IF NOT EXISTS summary (
prov STRING COMMENT '省份',
gender STRING COMMENT '性别',
age_range STRING COMMENT '年龄段',
zodiac STRING COMMENT '星座',
good_cate STRING COMMENT '商品种类',
brand STRING COMMENT '品牌',
trans_num BIGINT COMMENT '交易量',
trans_amount DOUBLE COMMENT '金额',
click_cnt BIGINT COMMENT '单击次数',
addcart_cnt BIGINT COMMENT '加入购物车次数',
collect_cnt BIGINT COMMENT '加入收藏夹次数'
);

4.创建用户数的汇总表:

CREATE TABLE IF NOT EXISTS user_cnt (
    buyer_cnt BIGINT COMMENT '用户数'
    );

5.创建交易分析ODPS_SQL节点，双击输入代码：

INSERT OVERWRITE TABLE summary
SELECT buyer.buyer_prov
,buyer.gender
,buyer.age_range
,buyer.zodiac
,sale.good_cate
,sale.brand
, SUM(sale.trans_num) AS trans_num
, SUM(sale.trans_amount) AS trans_amount
, SUM(sale.click_cnt) AS click_cnt
, SUM(sale.addcart_cnt) AS addcart_cnt
, SUM(sale.collect_cnt) AS collect_cnt
FROM online_trading sale
JOIN user buyer
ON sale.buyer_id = buyer.buyer_id
GROUP BY buyer.buyer_prov,buyer.gender,buyer.age_range,buyer.zodiac,sale.good_cate,sale.brand;

6.创建用户数ODPS_SQL节点，双击输入代码：

INSERT OVERWRITE TABLE user_cnt
SELECT count(distinct buyer_id) AS buyer_cnt
FROM online_trading;