互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(八)——电商核心业务知识之订单商品模块三

原创 2015年08月14日 13:37:13

订单指标表计算


#!/bin/bash

#======

#dm_b2c_orders_goods.sh    这里用shell来调用SQL

#订单指标表计算

#======

DT=`date  -d  '-1 day'  "+%Y-%m-%d" ` 注意date  -d ‘-1 day’表示取前一天

或者sysdate=`date "+%Y-%m-%d"`脚本传参数法

if  [$1]; then

   DT=$1

fi

SQL="

drop table if exists temp.tmp_dm_user_order_tag_1_"${DT}" ;

create table temp.tmp_dm_user_order_tag_1_"${DT}" as

select

a.user_id,

min(a.order_date)first_order_time,-第一次消费时间

max(a.order_date)last_order_time,-最近一次消费时间

sum(a.dat_30) month1_order_cnt, -近一个月订单数

sum(case

    when dat_30=1 then a.order_money --近一个月订金额

end) month1_order_amt,

sum(a.dat_60)month2_order_cnt,

sum(case

    when dat_60=1 then a.order_money

end) month2_order_amt,

sum(a.dat_90)month3_order_cnt,

sum(case

    when dat_90=1 then a.order_money

end) month3_order_amt,

max(a.order_money)max_order_amt,

min(a.order_money)min_order_amt,

count(case

     when jt_flag=0 then

     order_id

     end) total_order_cnt,

sum(case

     when jt_flag=0 then

     a.order_money

     end) total_order_amt,  --排除拒退累计金额

count(a.order_id)total_order_cnt_1,

sum(a.order_money)total_order_amt_1,

sum(coupon_money)total_coupon_amt,

from(

select

user_id,

order_date,

order_money,

(case

when order_date>=date_sub(‘"${DT}"’,29) and

    order_date<=’ "${DT}"’ then

     1

end) dat_30,

(case

when order_date>=date_sub(‘"${DT}"’,59) and

    order_date<=’ "${DT}"’ then

     1

end) dat_60,

(case

when order_date>=date_sub(‘"${DT}"’,89) and

    order_date<=’ "${DT}"’ then

     1

end) dat_90,

(case

when a.pay_type in (‘退货’,’拒收’) then

     1

     else

     0

end)  jt_flag

from itqsc.dm_b2c_orders a

where dt=’"${DT}"’)

groupby user_id;

 

drop table if exists temp.tmp_dm_user_order_tag_2_"${DT}" ;

create table temp.tmp_dm_user_order_tag_2_"${DT}" as

select t.user_id, t.con, t.type, t.cnt

from(select

     b.user_id,

     b.con,

     b.type,

     b.cnt,

     row_number() over(distribute by b.user_id, b.type sort by b.cnt, b.type desc) rn

     from (select

a.user_id,

coalesce(area_name, ‘ ’)|| coalesce(address, ‘’) con,

‘address’ type,

count(1) cnt

from itqsc.dm_b2c_orders a

where dt=’ "${DT}"

group by a.user_id

union all

select a.user_id,

a.pay_type con,

‘pay_type’ type,

count(1) cnt

          from itqsc.dm_b2c_orders a

where dt=’ "${DT}"

group by a.user_id) b) t

where t.rn=1;

 

drop table if exists temp.tmp_dm_user_order_tag_3_"${DT}" ;-加时间是为并行的

create table temp.tmp_dm_user_order_tag_3_"${DT}" as

selectuser_id

     count(1) month1_cart_cnt, -近30天购物车次数

     sum(number) month1_cart_goods_cnt, -近30天购物商品件数

     sum(case

         when submit_time is not null thennumber

         end) month1_cart_submit_cnt, -近30天购物车提交商品件数

     ‘’ month1_cart_rate, -30天购物车成功率

     sum(case

         when cancel_time is not null then

         number

         end) month1_cart_cancel_cnt, -近30天购物车放弃商品件数

fromitqsc.ods_b2c_cart

wheredt=’ "${DT}"

andto_date(add_time) >= date_sub(’"${DT}"’,29)

andto_date(add_time) <=’"${DT}"

groupby user_id;

)

 

 

drop table if exists temp.tmp_dm_user_order_tag_4_"${DT}" ;

create table temp.tmp_dm_user_order_tag_4_"${DT}" as

 

selecta.user_id

 from (select user_id

from temp.tmp_dm_user_order_tag_1_"${DT}")

union all

select user_id from temp.tmp_dm_user_order_tag_3_"${DT}") a

group by a.user_id;

 

insert overwrite tableitqsc.dm_b2c_orders partition (dt=' "${DT}" ')

select

a.user_id,--用户

a.first_order_time,-第一次消费时间

a.last_order_time,-最近一次消费时间

date_diff(‘"${DT}"’, a. first_order_time) first_order_ago, --首单距今时间

date_diff(‘"${DT}"’, a. last_order_time) last_order_ago, --尾单距今时间

a.month1_order_cnt,-近30天购买次数

a.month1_order_amt,-近30天购买金额

a.month2_order_cnt,-近60天购买次数

a.month2_order_amt,-近60天购买金额

a.month3_order_cnt,-近90天购买次数

a.month3_order_amt,-近90天购买金额

a.max_order_amt,-最大消费金额

a.min_order_amt,-最小消费金额

a.total_order_cnt,-累计消费次数(不含退拒)

a.total_order_amt,-累计消费金额(不含退拒)

a.total_coupon_amt,-累计使用代金券金额

(case

 when a.total_order_cnt_1=0 ora.total_order_cnt_1 is null then

 0

 else

a.total_order_amt_1/a.total_order_cnt_1

end) user_avg_amt, -客单价(含退拒)

(case

 when a.month3_order_cnt_1=0 then

 0

 else

a.month3_order_amt/ a.month3_order_cnt

end) month3_user_avg_amt,-近90天客单价(含退拒)

b.concommon_address, --常用收货地区

c.concommon_paytype, --常用支付方式

d.month1_cart_cnt,--最近30天购物车次数

d.month1_cart_goods_cnt,--最近30天购物车商品件数

d.month1_cart_submit_cnt,--最近30天购物车提交商品件数

(case

 when d.month1_cart_goods_cnt=0 ord.month1_cart_goods_cnt is null then

 0

 else

d.month1_cart_submit_cnt /d.month1_cart_goods_cnt

end)month1_cart_rate, --最近30天购物车成功率

d.month1_cart_cancel_cnt,--最近30天购物车放弃件数

sysdate()dw_date

from temp.tmp_dm_user_order_tag_4_”${DT}” t

join temp.tmp_dm_user_order_tag_1_”${DT}” a

on(t.user_id=b.user_id)

join(select user_id, con

fromtemp.tmp_dm_user_order_tag_2_”${DT}”

where type=’address’) b

on(t.user_id=b.user_id)

join(select user_id, con

fromtemp.tmp_dm_user_order_tag_2_”${DT}”

where type=’pay_type’) c

on(t.user_id=c.user_id)

jointemp.tmp_dm_user_order_tag_3_”${DT}” d

on(t.user_id=b.user_id);

"

echo  "${SQL}"

hive –e  "$SQL"

 

 

 

 

互联网电商大数据架构图

  • china_demon
  • china_demon
  • 2016年07月11日 17:45
  • 749

互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(一)——简介入门

互联网电商大数据环境 ——数据分析实践培训精华笔记(一) 工作内容 项目:DW数据库建设/经分/客户精准营销/推荐系统需求/移动端数据分析 数据:流量数据/交易数据B2C/会员与活动数据/物流与配...
  • houxiaoqin
  • houxiaoqin
  • 2015年08月11日 09:56
  • 1196

数据仓库维度建模(针对多种业务的数据仓库总线结构)

数据仓库总线结构:多种不同的业务: 遵循一致性维度、一致性事实 reference:   http://blog.itpub.net/23716337/viewspace-1118...
  • u012075079
  • u012075079
  • 2016年08月08日 10:49
  • 204

互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(二)——BI构建

BI构建 BI应用架构 BI底层:数据源——ETL——BI数据仓库  BI服务:交互式信息板、报告并发布、即席分析、预先检测和警报、离线分析、MS Office插件、Web服务 BI应...
  • houxiaoqin
  • houxiaoqin
  • 2015年08月11日 13:37
  • 699

互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(六)——电商核心业务知识之订单商品模块

电商核心业务知识 订单商品模块(9张表) --订单主要信息表 drop table if exists itqsc.ods_b2c_orders; create external ta...
  • houxiaoqin
  • houxiaoqin
  • 2015年08月13日 14:26
  • 811

互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(九)——电商核心业务知识之订单商品模块

商品信息汇总表 select  user_id,  sum(case when pay_type not in (‘退货’,’拒收’) then      goods_amount ...
  • houxiaoqin
  • houxiaoqin
  • 2015年08月14日 13:42
  • 1109

互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(七)——电商核心业务知识之订单商品模块

订单宽表 #!/bin/bash #====== #dm_b2c_orders.sh    这里用shell来调 #订单宽表——两个表关联 #====== DT=`date  -d  '-1 da...
  • houxiaoqin
  • houxiaoqin
  • 2015年08月14日 13:26
  • 815

互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(五)——数据仓库维度建模实例

维度建模实例 库存管理业务建模案例 1. 选定某一业务过程,如:库存管理业务 2.根据各用户的需求(关注的主题),定义该业务处理的粒度。 主题一:矿厂中每种产品库存水平的日快照 主题...
  • houxiaoqin
  • houxiaoqin
  • 2015年08月12日 01:11
  • 1841

互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(三)——数据仓库理论

数据仓库理论 DW定义 数据仓库是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合,用于支撑管理决策。 1.聚焦决策者的数据建模与分析,排除对决策无用的数据 2.集成异种数据...
  • houxiaoqin
  • houxiaoqin
  • 2015年08月11日 15:00
  • 537

互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(四)——维度建模基础理论

维度建模基础理论 事实表 事实表保存了大量业务度量数据(即事实)的表。最有用的事实是数字类型、可加类型。 事实表以粒度化分:事务粒度事实表(细)、周期快照粒度事实表、累积快照粒度事实表(...
  • houxiaoqin
  • houxiaoqin
  • 2015年08月11日 15:47
  • 827
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(八)——电商核心业务知识之订单商品模块三
举报原因:
原因补充:

(最多只允许输入30个字)